• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

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:GridView>
        <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
0
Srinivas Mantha
Asked:
Srinivas Mantha
  • 6
  • 5
1 Solution
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I used the code in the second link i.e. http://www.aspxcode.net/..............
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



0
 
dejaanbuCommented:
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..
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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()
        oDataAdapter.Fill(oDataSet)
        GridView1.DataSource = oDataSet
        GridView1.DataBind()

    End Sub
End Class
===========================================================================
0
 
dejaanbuCommented:
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
http://dev.mysql.com/downloads/connector/odbc/  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
http://dev.mysql.com/downloads/connector/net/     in ur machine and have this MySql.Data.dll  in ur bin folder.

and then follow this code

http://www.15seconds.com/issue/050407.htm


Good Luck.





0
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I have gone through the forums on the subject.  The following link

http://forums.asp.net/t/987978.aspx/1
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
0
 
dejaanbuCommented:
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
http://dev.mysql.com/downloads/connector/net/     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

http://www.15seconds.com/issue/050407.htm

0
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I followed all the steps that you described.
 Last link i.e. http://www.15seconds.com/issue/50407.htm
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.      
C:\Inetpub\wwwroot\asp24hr\msqltest6new.aspx      

Any help?
0
 
dejaanbuCommented:
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.  
0
 
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
0
 
dejaanbuCommented:
Glad to Help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now