none
connect to mysql by vb.net in code

    Question

  • need to connect to a MySQL 5 DB from VB.NET [2008] 3.5 Framework from code.

    Help ..
    need the connection code
    Wednesday, January 02, 2008 10:36 AM

Answers

  • ConnectionStrings.com is a good resource.  Check here...

     

    http://www.connectionstrings.com/?carrier=mysql

     

     

    Wednesday, January 02, 2008 3:45 PM
  • download mysql .net connector from mysql site, then use this connection string

    Data Source=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;

     

    Thursday, January 03, 2008 1:22 AM
  • Well, some of that depends upon what db provider your using.

     

    As far as the ADO.NET object mode goes, the object model should be fairly consistent (Connection, Command classes) but the connection object would have a different connection string.  You can find some examples of the different connection string at the followin.

     

    http://www.connectionstrings.com/?carrier=mysql

     

     

    Also what are you trying to do, retrieve, insert, update, delete etc.  

     

     

     

     

     

    Thursday, January 03, 2008 7:17 AM
  • Hi,

     

    Based on your post, you need to retrive table's data, modify the data in your application and then save the data into the mysql database.

     

    In this scenario mysql data provider is consistent with other data provider. We need to fetch the data into the dataset and use mysqldataadapter object to save the data into the database. I recommend that you read .NET Framework Developer's Guide ADO.NET about how to using ado.net to work with mysql database. Here is the simple code example to read the table1 data from the mysql database into the client application and use mysqldataadapter.update method to save the data into the database. And visit http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-3/ to get the more information about how to use mysql in vb.net. If you have any further questions, please tell us.

    Code Block

    Imports MySql.Data.MySqlClient

    Public Class Form4

    Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=billy;")

    Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM Table1", con)

    Dim ds As DataSet = New DataSet()

    Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()

    Dim cb As MySqlCommandBuilder

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    con.Open()

    DataAdapter1.SelectCommand = sql

    DataAdapter1.Fill(ds, "table1")

    DataGridView1.DataSource = ds

    DataGridView1.DataMember = "table1"

    con.Close()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    cb = New MySqlCommandBuilder(DataAdapter1)

    Dim i As Integer = DataAdapter1.Update(ds.Tables("table1"))

    MessageBox.Show("modify the number " & i.ToString & " rows")

    End Sub

    End Class

     

     

     

    Best regards,

    Riquel

    Sunday, January 06, 2008 10:01 AM

All replies

  • ConnectionStrings.com is a good resource.  Check here...

     

    http://www.connectionstrings.com/?carrier=mysql

     

     

    Wednesday, January 02, 2008 3:45 PM
  • Which version of 2008 are you using  Express or pay version ?

     

    If your using the express version then the IDE is intentionally limited to Access, SQL Express and SQL Compact although you can code up manually to access any database, you'll only get the designer support for the above databases.   If you have a pay version of VB/VS then you can use the IDE with other database types, in which case you will need a MYSQL database provider.

     

    http://dev.mysql.com/tech-resources/articles/dotnet/index.html

     

     

     

    Wednesday, January 02, 2008 6:31 PM
  • download mysql .net connector from mysql site, then use this connection string

    Data Source=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;

     

    Thursday, January 03, 2008 1:22 AM
  •  spotty2428 wrote:

    Which version of 2008 are you using  Express or pay version ?

     

    If your using the express version then the IDE is intentionally limited to Access, SQL Express and SQL Compact although you can code up manually to access any database, you'll only get the designer support for the above databases.   If you have a pay version of VB/VS then you can use the IDE with other database types, in which case you will need a MYSQL database provider.

     
    http://dev.mysql.com/tech-resources/articles/dotnet/index.html

     

    yup am using licenced version of Visual Studio 2008. 3.5 framework

    Thursday, January 03, 2008 6:18 AM
  • I installed the "mysql-connector-odbc-5.1.1" ... now the visual studio will connect to mysql DB with its GUI. but i need to connect to it via code

    Please can anybody tell me, where can i get that connection code?

    Thursday, January 03, 2008 7:00 AM
  • Well, some of that depends upon what db provider your using.

     

    As far as the ADO.NET object mode goes, the object model should be fairly consistent (Connection, Command classes) but the connection object would have a different connection string.  You can find some examples of the different connection string at the followin.

     

    http://www.connectionstrings.com/?carrier=mysql

     

     

    Also what are you trying to do, retrieve, insert, update, delete etc.  

     

     

     

     

     

    Thursday, January 03, 2008 7:17 AM
  •  spotty2428 wrote:

    Well, some of that depends upon what db provider your using.

     

    As far as the ADO.NET object mode goes, the object model should be fairly consistent (Connection, Command classes) but the connection object would have a different connection string.  You can find some examples of the different connection string at the followin.

     

    http://www.connectionstrings.com/?carrier=mysql

     

     

    Also what are you trying to do, retrieve, insert, update, delete etc.  

     

     


    ok let me not confuse you all more! :-)
    my apologies for not being clear.

    as of now...
    i have installed the mysql .net connector 5.1

    added reefrence to it and even though the connector only gives a GUI support for Visual Studio 2005 (2.0 Framework)..  it still retains all its functionality if u access it via VB.NET code in a 3.5 Framework Code.
    I tried the folowing code to test a connection with my MySQL DB

    Code Block

            Dim connx As MySqlConnection
            connx = New MySqlConnection()
            connx.ConnectionString = "server=" & "localhost" & ";" _
              & "user id=" & "root" & ";" _
              & "password=" & "MyDBPass" & ";" _
              & "database=myDBname"

            Try
                connx.Open()
                MessageBox.Show("Connection Opened Successfully")

                connx.Close()
            Catch myerror As MySqlException
                MessageBox.Show("Error Connecting to Database: " & myerror.Message)
            Finally
                connx.Dispose()
            End Try


    and ofcourse before everything i had to import the following:

    Code Block

    Imports MySql.Data.MySqlClient


    now i am familiar with ADODB Recordsets and all... (which i had been using with the same database till now in MS Access.)

    My Question is how do i now access my tables and then view / update them record by record?

    I hope this clarifies a lot and i am not wasting anybody's time :-)

    Thanks a lot for your replies..
     :-)

    Thursday, January 03, 2008 9:07 AM
  • Hi,

     

    Based on your post, you need to retrive table's data, modify the data in your application and then save the data into the mysql database.

     

    In this scenario mysql data provider is consistent with other data provider. We need to fetch the data into the dataset and use mysqldataadapter object to save the data into the database. I recommend that you read .NET Framework Developer's Guide ADO.NET about how to using ado.net to work with mysql database. Here is the simple code example to read the table1 data from the mysql database into the client application and use mysqldataadapter.update method to save the data into the database. And visit http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-3/ to get the more information about how to use mysql in vb.net. If you have any further questions, please tell us.

    Code Block

    Imports MySql.Data.MySqlClient

    Public Class Form4

    Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=billy;")

    Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM Table1", con)

    Dim ds As DataSet = New DataSet()

    Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()

    Dim cb As MySqlCommandBuilder

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    con.Open()

    DataAdapter1.SelectCommand = sql

    DataAdapter1.Fill(ds, "table1")

    DataGridView1.DataSource = ds

    DataGridView1.DataMember = "table1"

    con.Close()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    cb = New MySqlCommandBuilder(DataAdapter1)

    Dim i As Integer = DataAdapter1.Update(ds.Tables("table1"))

    MessageBox.Show("modify the number " & i.ToString & " rows")

    End Sub

    End Class

     

     

     

    Best regards,

    Riquel

    Sunday, January 06, 2008 10:01 AM
  • hi ,
    thanks for the reference. :-) really appreciate it..
    i am looking into the microsoft's ADO.NET guide...
     as my DB will be large (ranging from a 1000 records to 100,000,0 records in some tables.. and as teh same table but different records would be accessed and updated by many  at a time, i needed to from time to time access only one record and then update it and at the same time another user could access another record from the same table and update that.

     i will look into your references and if i can't find what i am looking for.. i will pester u a bit more if u dont mind. :-)

    Thanks a ton..

    cheers
    Tuesday, January 08, 2008 10:26 AM
  • Imports MySql.Data.MySqlClient

     

    Public Class Form2

     

    'Proyecto,add reference,mysql

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim connx As MySqlConnection

    connx = New MySqlConnection()

    connx.ConnectionString = "server=" & "localhost" & ";" _

    & "user id=" & "root" & ";" _

    & "password=" & "" & ";" _

    & "database=classicmodels"

    '& "user id=" & "root" & ";" _

    ' & "password=" & "MyDBPass" & ";" _

    ' & "database=myDBname"

    Try

    connx.Open()

    MessageBox.Show("Connection Opened Successfully, Esta conection abrio con exito Ronald")

    connx.Close()

    Catch myerror As MySqlException

    MessageBox.Show("Error Connecting to Database: " & myerror.Message)

    Finally

    connx.Dispose()

    End Try

    End Sub

    End Class

    Tuesday, March 25, 2008 4:52 AM
  • I have the same code, fist it work, but then when i close my project and open it , it does not work! I don't know how? Can you hep me!

    ERROR [HY000] [MySQL][ODBC 5.1 Driver]Can't create TCP/IP socket (10022)
    ERROR [HY000] [MySQL][ODBC 5.1 Driver]Can't create TCP/IP

    Saturday, August 22, 2009 3:43 AM
  • i had already use the code but still its not wrkng

    my code

    

    Imports MySql.Data.MySqlClient
    Public Class Form1

        Private mysql_connect As New MySqlConnection
        Private Sub connect_mysql()
        End Sub


        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim database As String = "inventory"
            Dim server As String = "Data Source=C:\PROGRAM FILES X86\MYSQL\MYSQL SERVER 5.1\BIN\MYSQL.EXE"
            Dim user As String = "root"
            Dim password As String = "pooja"


            If Not mysql_connect Is Nothing Then mysql_connect.Close()
            mysql_connect.ConnectionString = String.Format("server={C:\PROGRAM FILES X86\MYSQL\MYSQL SERVER 5.1\BIN\MYSQL.EXE}; user id={root}; password={pooja}; database={inventory}", server, user, password, database)



            Try
                mysql_connect.Open()
            Catch ex As MySqlException
                MsgBox("Connecting To Database Error:[" & ex.Message & "]")
            End Try
        End Sub

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        End Sub
    End Class
    erro message displayed is

    format exception wass unhandled exception

    • Proposed as answer by Pooja Rathi Friday, July 05, 2013 2:52 PM
    Friday, June 28, 2013 1:35 PM