Connecting to mysql database in ASP.NET 3.5 with connection string defined in web.config file

I am new to ASP.NET 3.5.  I am very familiar with traditional ASP and developed several successful projects with database links. I am using visual studio 2008 to develop webpages and testing on the local host.  

With traditional asp, I am able to connect to the mysql database with the following command

objdbconnconnectionstring=”Driver={MySQL ODBC 5.1 Driver}; Server=localhost;Database=rcdrugs;Uid=root;Pwd=pass1234;

In ASP.NET 3.5, I inserted the following command in the connectionstrings tags in the web.config file.

<add name="csmysqlrcd" connectionString="Driver={MySQL ODBC 5.1 Driver}; Server=localhost;Database=rcdrugs;Uid=root;Pwd=pass1234;"
              providerName="System.Data.Odbc" />

In the aspx file, following script is used

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSourcemysql">
        <asp:SqlDataSource ID="SqlDataSourcemysql" runat="server"
            ConnectionString="<%$ ConnectionStrings:csmysqlrcd %>"
            ProviderName="<%$ ConnectionStrings:csmysqlrcd.ProviderName %>"
 SelectCommand="SELECT * FROM [tblitemlist]"></asp:SqlDataSource>

I am not able to get connected to the mysql database and populate the grid

Any thoughts how to resolve the problem
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I used the code in the second link i.e.
It is giving an error as shown below
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30269: 'Protected Sub Page_Load(sender As Object, e As System.EventArgs)' has multiple definitions with identical signatures.

Source Error:


Line 3:      Inherits System.Web.UI.Page
Line 4:  
Line 5:      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Line 6:          Imports System.Data
Line 7:  Imports System.Data.Odbc

Source File: C:\Inetpub\wwwroot\asp24hr\mysqldemo.aspx.vb    Line: 5

the error denotes that you have more than one Page_Load event in ur code behind. pls attach your mysqldemo.aspx.vb file for checking..
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Thank you. The original problem is solved after your input. The contents of the file (mysqldemo.aspx.vb) is as follows
Now it is giving another error on line 12

Exception Details: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Line 12:         oDataAdapter.Fill(oDataSet)

But I am using the same ODBC driver successfully in my traditional asp programs
Imports System.Data
Imports System.Data.Odbc
Partial Class mysqldemo
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sConString As String = "Driver={MySQL ODBC 3.51 Driver};" & "Server=localhost;" & "Database=rcdrugs;" & "user id=root;" & "password=pass1234"
        Dim oConnection As OdbcConnection = New OdbcConnection(sConString)
        Dim sSQL As String = "SELECT itemid,iname from tblitemlist"
        Dim oDataAdapter As OdbcDataAdapter = New OdbcDataAdapter(sSQL, oConnection)
        Dim oDataSet As DataSet = New DataSet()
        GridView1.DataSource = oDataSet

    End Sub
End Class
in General, you can connect to Mysql in two ways. by using ODBC or  by using Mysql Connector,

if u are selecting ODBC , then you should have installed  in ur machine  and use the same code now u have.

another way

Mysql Connector/ .Net comprises several classes that are used to connect to the database, execute queries and statements, and manage query results.

if you go for Mysql Connector, then it is better...for this, you have to install     in ur machine and have this MySql.Data.dll  in ur bin folder.

and then follow this code

Good Luck.

Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I have gone through the forums on the subject.  The following link
The reply on May 7, 2006 seems to be applicable to my case
I downloaded the .net driver from mysql website.  I identified the corresponding dll file
i.e. MySQL.VisualStudio.dll
I do not know where to copy it.  I am at the development stage. there is no bin folder.
Can anyone guide me how to proceed from this stage
i have already suggested two methods for you. in the forum link, the comment clearly says "use the .Net Driver" .. if you are ready to go in Mysql .Net Driver, then follow the steps below

you have to install     in ur machine and have this MySql.Data.dll  in ur bin folder. (right click on ur project  -> add ASP.NET Folder -> Bin). right click on project , choose Add Referrence, Click on Browse, Choose the location of Mysql.Data.Dll. Thats it.

and then follow this code

Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I followed all the steps that you described.
 Last link i.e.
describes that the code should be written in the aspx file as opposed to the code being routinely written in the corresponding aspx.vb
When wrote the code in aspx file, I getting the following error:
C:\Inetpub\wwwroot\asp24hr\msqltest6new.aspx: ASP.NET runtime error: Could not load file or assembly 'MySql.Data' or one of its dependencies.
This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.      

Any help?
you can write the code in code-behind itself. thats not a problem. couldnot load assembly error says that  you didnt have the Mysql.Data.Dll inside bin folder.  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I figured out the problem. During installation of the driver, it creates 2 folders with two Mysql.Data.dlls
a set of version 2.0+ dll's
and a set version 4.0 dll's
I copied the 4.0 dll which is not suitable for .NET 3.5 it is suited for .NET 4.0.
I copied the 2.0+ dll in the bin folder and referenced it again.
It is not giving that error anymore.
Thanks for the support
Glad to Help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.