connect to mysql from vb.net standard thru code

[craigreilly]

I have tried to figure this out all day...
I have read that it is possible to connect to mysql in vb.net standard thru code.
I have not been able to figure this out...

please post some usable code...
server=(localhost)
database="mysql"
table="employees"
no username, no password

[brown monkey]

maybe

imports microsoft.data.odbc
dim cn as odbcconnection()
sub connect()
  cn.connectionstring="driver={[i]your_driver[/i]};database=[i]db_name[/i];"
  cn.open()
  . . .
  cn.close()
end sub

in my case i have mysql odbc 3.51 driver as my driver. hope this helps, if not, sorry... and oh... add microsoft.data.odbc.dll as a reference.

[mendhak]

Download the MySQLDirect .NET data provider from this URL:

http://crlab.com/mysqlnet/download.html


A connection string would look like:

Imports CoreLab.MySql
 
Dim oMySqlConn As MySqlConnection = New MySqlConnection() 
oMySqlConn.ConnectionString = "User ID=myUsername;" & _
                              "Password=myPassword;" & _
                              "Host=localhost;" & _
                              "Port=3306;" & _
                              "Database=myDatabaseName;" & _
                              "Direct=true;" & _
                              "Protocol=TCP;" & _
                              "Compress=false;" & _
                              "Pooling=true;" & _
                              "Min Pool Size=0;" & _
                              "Max Pool Size=100;" & _
                              "Connection Lifetime=0"

[craigreilly]

http://www.sevenobjects.com/clientSamples.aspx

This place has a mysqlclient to download. Free for commercial and non-commercial use. It would have to be loaded on the client machine - but is a DSN-less connection.

The 'localhost' can be an IP Address. I tried it across the internet, thru a firewall on both ends and it works great. If you have internal and external users, you probably need to write some code to see if they are logged on to the domain or not and change the IP Address accordingly. I am still toying with that, as I have about 10 users internally and 40 users in 30 different field offices. If you have any ideas - please let me know.

Imports System.Data.MySqlClient

Dim oConnection As New MySqlConnection
        Dim oCommand As New MySqlClient.MySqlCommand
        Dim oAdapter As New MySqlDataAdapter

        oConnection.ConnectionString = "Host=localhost; UserName=; Password=; Database=crm;"
        oCommand.CommandText = "SELECT * FROM employees, locations where employees.location_id=locations.id"
        oCommand.Connection = oConnection

        oConnection.Open()

        Dim oReader As MySqlDataReader = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
        While oReader.Read() = True
        ' !! Do something with the recordset now.
        End While

[mendhak]

Since it's just the connection string that's different, store the string in a config file of some sort, let it be different for the internal users, and different for the external users.