Connecting To Mysql Databse

hi, i need help with connecting to a mysql database, i am developing my application in microsoft visual studio with vb.net and require help with the code for the connection string, as i am not using ado.net to connect to the database, i am using a vb class file to connect to the database.

Private Sub OpenDatabase(ByVal DatabaseName As String, ByVal TableName As String)'declare a variable to store our SQLDim SQL As String'declare a new command builderDim CB As OleDbCommandBuilder'declare a variable to store the application pathDim DbPath As String = System.AppDomain.CurrentDomain.BaseDirectory & "App_Data\"'this is the connection string to an Access 2003 file uncomment as reguiredDim ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DbPath & DatabaseName & ";Persist Security Info=False"'this is the connection string for Office 2007'Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DbPath & DatabaseName & ";Persist Security Info=False"'opens the connection object based on the connection stringmNewConnection = New OleDbConnection(ConnectionString)Try'open the connectionmNewConnection.Open()Catch ex As ExceptionStop'DatabaseTable error!!!!!!!!!!!!!!'the program has crashed probably due to mis typing the database name'DatabaseTable error!!!!!!!!!!!!!!End Try'build up our SQLSQL = "select * from " & TableName'connect the data adapter to the connection object using the specified sqlmDataAdapter = New OleDb.OleDbDataAdapter(SQL, mNewConnection)'initialise the select command of the data adaptermDataAdapter.SelectCommand = New OleDbCommand(SQL, mNewConnection)'initialise the command builder to initialis the other commands of the data adapterCB = New OleDbCommandBuilder(mDataAdapter)Try'populate the datatable via the data adaptermDataAdapter.Fill(mTableData)Catch ex As ExceptionStop'DatabaseTable error!!!!!!!!!!!!!!'the program has crashed probably due to mis typing the table name'DatabaseTable error!!!!!!!!!!!!!!End Try'take a copy of the record structuremARow = mTableData.NewRow'close the connectionmNewConnection.Close()End Sub

Well, i don't use VB for a long time, but tell me the reason because you don't use ado.net? i guess that it could help you to do this.

 

I think that another way will be by creating a DSN and then use it on your connection string, to do this you need to download the odbc driver for MySql or the MySql Connector/NET.

 

BTW, looking at your code i see that you are using the strings for Access or Office, try this one instead:

 

MySQLConnectionString = "Server=myServer;" & _

"Database=myDatabase;" & _

"Uid=myUserID;" & _

"Pwd=myPassword;" & _

"Connect Timeout=30;"

 

Some resources that may help:

Using MySQL With .NET/C#/Visual Basic/Powershell

Define and Store MySQL ADO Connection String in VB.NET 2005.

Beginning MYSQL 5 with Visual Studio.NET 2005 (pdf)

VBMySQL.com website.

Check out the articles section of the last one, there are lots of tutorials and articles.

 

Best regards,

hi, thank you for your replay, the reason i not using ado.net is because my uni course has not covered it.

Ok and you are welcome, now relating to your problem, do you find the solution to it??? if not, you can do the following:

Download and install the MySql Connector/NET:

MySQL Connector/NET can be downloaded from MySql Connector/NET. Download to your hard disk the zip file which includes the installer and unzip it. Then to start the installation process simply double click that file. Finally make a complete installation on the default folder or to any folder that you wish.

Start Visual Basic and create a new project and a new form, you can use the default settings or make any change that you want.

Add References:

Before you begin using Connector/NET with Visual Basic, we need to add a reference to our project. By adding a reference, we are telling to Visual Basic where to find MySQL Connector/NET to access the objects and methods of Connector/NET.

 

To add a reference to your project, choose Add Reference... (Add Reference...) from the Project menu (Project). Choose the Browse tab (Browse) and locate the folder where your installation of Connector / NET is located. Finally select the file MySql.Data.dll and the Connector / NET will be added to your project.

Save your form and project, and then complete your form as you wish. To use a Connection object, first you need to define it as MySql.Data.MySqlClient.MySqlConnection or you can use the Imports sentence. If you add Imports MySql.Data.MySqlClient at the top of the source code, you can be able to refer the Connector/NET object as MySqlConnection. After this add and instantiate a MySqlConnection object as you do with any other object in Visual Basic, and replace the Connection string posted above with the following:

 

ConnectionString = "server=localhost;" & _

"user id=UserName;" & _

"password=Password;" & _

"database=DatabaseName"

If you have any problem please let me know.

 

Best regards,