ASP.NET 101 Home » Code Samples » Using MySQL with ASP.Net

Using MySQL with ASP.Net

05. Nov, 2008 1 Comment by admin

MySQL is a very robust Database server, which works similarly to SQL Server, in that it’s a DataBase server, unlike MS Access. For this sample, we merely imported several of the tables from the Northwind database, to keep it as simple as possible.

In order to make a step up from MS Access, and learn the structure and use of a DataBase server, along with the fact that there is no added expense (MYSQL is free), here, we show how simple, coding-wise, it is, to make the transition.

There is a somewhat more detailed tutorial on the use of MySQL here:
http://aspnet101.com/aspnet101/tutorials.aspx?id=39

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.ODBC" %>
<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
BindData
End Sub
Sub BindData()
Dim strConn as string
strConn = "DRIVER={MySQL};SERVER=IP_Address;DATABASE=YourDB;" & _
"USER=YourUID;PASSWORD=YourPWD; OPTION=3;"
Dim MySQL as string = "Select CustomerID, CompanyName, ContactTitle, " & _
"Address, City, Phone from Customers"
Dim MyConn as New ODBCConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New ODBCDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"Customers")
MyDataGrid.Datasource=ds.Tables("Customers").DefaultView
MyDataGrid.DataBind()
End Sub

Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
BindData
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 4.0">
<title>MySQL with ASP.Net</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:Datagrid Runat="server"
Id="MyDataGrid"
GridLines="Both"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#BDCFE7"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="12"
BackColor="#E7EFFF"
Font-Name="Arial"
Font-Size="10"
AlternatingItemStyle-BackColor="#E7EFFF"
AlternatingItemStyle-Font-Name="Arial"
AlternatingItemStyle-Font-Size="10"
BorderColor="Black"
AllowPaging = "True"
PageSize = "10"
PagerStyle-Mode = "NumericPages"
PagerStyle-HorizontalAlign="Center"
PagerStyle-PageButtonCount = "10"
OnPageIndexChanged = "Page_Change">
</asp:DataGrid>
</form>
</body>
</html>