This sample shows how to use the SQLDataSource control (yes – SQL!), to connect to a MySQL database. This can, by the way, be done with no code at all, by assigning the ConnectionsString in the SQLDataSource control itself. However, to preserve horizontal screen real estate, by using multiple lines to define the connectionstring, this sampple is done, assigning the connectionstring in the Page_Load event.
Note, that, to accomplish this, we use ‘System.Data.Odbc’ as the ProviderName in the SQLDataSource control
<%@ Page Language="VB" %>
<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
DS1.ConnectionString="Driver={MySQL ODBC 3.51 Driver};server=YourServerIP;database=" & _
"YourDB;user=YourUID;pwd=YourPWD;option=3;"
End Sub
</script>
<html>
<head runat="server">
<title>Using MySQL with ASP.Net V2.0</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="DS1" runat="server"
ProviderName="System.Data.Odbc"
SelectCommand="Select CategoryID, CategoryName, Description from Categories order by categoryID desc">
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="#E6E6FA" AlternatingRowStyle-BackColor="#F0F0F0"
BorderStyle="Ridge" BorderWidth="1px" CellPadding="1" CellSpacing="1"
HeaderStyle-BackColor="#4747D8" HeaderStyle-Font-Bold="True" HeaderStyle-ForeColor="#E7E7FF"
DataKeyNames="CategoryID" DataSourceID="DS1" GridLines="None">
<Columns>
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
ReadOnly="True" SortExpression="CategoryID" />
<asp:BoundField DataField="CategoryName"
HeaderText="CategoryName" SortExpression="CategoryName" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>