Connecting to MySQL DB with ADO.net

[Arc]

I have a MySQL Database on a remote server that i would like to connect to with ADO.net... i have never used ADO.net so how would i go about this?

Thank you!

[CVMichael]

Well, I only know how to do it with ODBC

To make it work for .NET you need to install the ODBC .NET Data Provider, MySQL ODBC drivers, and I see you already have MySQL

Here's a link I found on the net that helped me do it...
http://myhome.spu.edu/tbuiten/4800/setup.asp (first 5 steps)

Then when developing, in you ASP .NET program you need to add a reference to the Microsoft.Data.Odbc.dll and Import it in the pages you use the DB (Imports Microsoft.Data.Odbc)

And here's some code I use in Global.aspx to connect with MySQL, just to get you started:

Try
            If Application("Connection") Is Nothing Then
                Dim mySQLConnStr As String = "Driver={MySQL};" & _
                                            "Server=localhost;" & _
                                            "Port=3306;" & _
                                            "Option=131072;" & _
                                            "Stmt=;" & _
                                            "Database=the_db_in_MySQL_Admin;" & _
                                            "Uid=your_user_name;" & _
                                            "Pwd=your_password;"
 
                Dim cn As OdbcConnection = New OdbcConnection(mySQLConnStr)
                Application("Connection") = cn
                cn.Open()
 
                Dim cmd As New OdbcCommand("SELECT Value FROM t_variables WHERE VarName = 'Users';", cn)
                Dim ret As Object = cmd.ExecuteScalar()
 
                If Not IsDBNull(ret) Then Application("Visitors") = CInt(Val(ret))
            End If
        Catch ex As OdbcException
            Application("Connection") = Nothing
            Application("ConnectionError") = ex.Message
        End Try

And since you use a remote server, I think you just have to change the Server param in the connection string to your server name/ip

Hope this helps

[Arc]

Hi and thanks. I installed everything the page said but when i try to run your code it says

type OdbcConnection not defined

it saus that anywhere there is an Odbc refrence...

I copied your code exactly...

[Edneeis]

Unless you are using VS.NET 2003 then you have to install the .NET ODBC drivers. Did you do that? You also need the following Imports: Imports Microsoft.Data.ODBC

[Arc]

Yah i installed them... i got the file from here http://download.microsoft.com/downlo...S/odbc_net.msi

The imports statement says Namspace or type ODBC for the imports Microsoft.Data.ODBC cannot br found.


hmmm..

[CVMichael]

You also have to add a reference to it, like in the picture below, right click on the References and choose "Add Reference..." then select "Microsoft.Data.Odbc.dll"

[Arc]

You do?? Damn i thought thats what Import was for....

[CVMichael]

The Reference thing in .NET is same as the reference in VB6, it adds a reference to the whole project, but in .NET you also have to tell it in what page you want to make the reference avalilable, then you use Imports in that page...