Using SqlDataSource with MySQL

[msch-prv]

Hi, I am trying to tie a SQLDataSource control to MySQL without
success. The connection string works ok with an ObjectDataSource.
(Native asp.net 2.0 MySQL dll loaded in /bin)

For some reason, the compiler believes an SQL db is being accessed ("An
error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)").

How can I modify the code so that the compiler "sees" a MySQL db?

Thanks for any hints, Mark


web.config:
<connectionStrings>
<add name="MySQLConn" connectionString=
"SERVER=localhost;DATABASE=dbKatoka;UID=admin;PWD=admin;"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>

aspx:
<%@ Import Namespace="MySql.Data.MySqlClient" %>
..
<asp:SqlDataSource id="Apts_City"
ConnectionString ="<%$ ConnectionStrings:MySQLConn %>"
SelectCommand="SELECT DISTINCT AptCity FROM tblApt;"
runat="server"/>

<aspropDownList id="DropDown1" DataSourceID="Apts_City"
DataTextField="AptCity" DataValueField="AptCity"
runat="server" />

 

[Cowboy \(Gregory A. Beamer\)]

The SqlDataSource connects to SQL Server only. You will have to use an
OleDbDataSource with the proper provider for MySQL, or an OdbcDataSource
with the proper driver.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

 

[Mark Rae]

Hi, I am trying to tie a SQLDataSource control to MySQL without
success. The connection string works ok with an ObjectDataSource.
(Native asp.net 2.0 MySQL dll loaded in /bin)

Hardly surprising...

For some reason, the compiler believes an SQL db is being accessed

Well of course it does - SQLDataSources are for connecting to SQL Server...

How can I modify the code so that the compiler "sees" a MySQL db?

Don't use an SQLDataSource - connect to your MySQL database "properly" i.e.
in code using the proper MySQL native .NET data provider...

 

[Eliyahu Goldin]

Gregory,

I was about to type something similar too. But just in case I looked in the
MSDN:

------------------------------
By default, the SqlDataSource control works with the .NET Framework Data
Provider for SQL Server, but SqlDataSource is not Microsoft SQL
Server-specific. You can connect the SqlDataSource control with any database
product for which there is a managed ADO.NET provider. When used with the
System.Data.OleDb provider, the SqlDataSource can work with any OLE
DB-compliant database. When used with the System.Data.Odbc provider, the
SqlDataSource can be used with any ODBC driver and database, including IBM
DB2, MySQL, and PostgreSQL. When used with the System.Data.OracleClient
provider, the SqlDataSource can work with Oracle 8.1.7 databases and later.
The list of allowable providers is registered in the DbProviderFactories
section of the configuration file, either in the Machine.config or
Web.config file. For more information, see Selecting Data Using the
SqlDataSource Control.

 

[msch-prv]

After some searching and experimenting, I got SQLDataSource tied up to
MySQL by using a ODBC 3.51 connection string:

web.config:
<add name="MySQL_ODBC"
connectionString= "DRIVER={MySQL ODBC 3.51 Driver};

SERVER=localhost;DATABASE=dbKatoka;UID=admin;PWD=admin;OPTION=3;"
providerName="System.Data.Odbc" />

aspx:
<%@ Import Namespace="System.Data.ODBC" %>
...
<asp:SqlDataSource ID="Apts_srcddnCity"
ConnectionString="<%$ ConnectionStrings:MySQL_ODBC %>"
ProviderName="<%$ ConnectionStrings:MySQL_ODBC.ProviderName %>"
SelectCommand="SELECT DISTINCT AptCity FROM tblApt;"
runat="server" />

<aspropDownList ID="DropDown1" DataSourceID="Apts_srcddnCity"
DataTextField="AptCity" DataValueField="AptCity"
runat="server" />

 

[Mark Rae]

After some searching and experimenting, I got SQLDataSource tied up to
MySQL by using a ODBC 3.51 connection string:

This is almost certainly the least efficient method...