Question How to hardcode a connection to a MySQL DB and code a Datatable? I need help

[tgf-47]

My system use to run on from a access database. Now I created the database on a MySQL server.
I can connect to it using the connection wizzard, but I prefer using my old methods as shown below.

I used this to connect to my access database:

Private Function MYDATA() As DataTable
Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=wc.mdb;Persist Security Info=False"
Dim cmd As String = "SELECT * FROM st_trans"
Dim ad As New System.Data.OleDb.OleDbDataAdapter(cmd, conn)
Dim topics As New DataSet()
ad.GetFillParameters()
ad.Fill(topics)
MYDATA = topics.Tables(0)
End Function


I'm not very clued up on MySQL, but I tried to change the Function as follows:

Private Function MYDATA() As DataTable
Dim conn As String = "Server=192.168.0.36;Port=3306;Database=wc;Uid=root;Pwd=ssf2;"
Dim cmd As String = "SELECT * FROM st_trans"
Dim ad As New System.Data.SqlClient.SqlDataAdapter(cmd, conn)
Dim topics As New DataSet()
ad.GetFillParameters()
ad.Fill(topics)
MYDATA = topics.Tables(0)
End Function

This doesn't work. When it gets to "ad.Fill(topics)", it bombs out.
This tells me I'm going all wrong here.

Does anyone know how to hardcode a connection and selection like I use to do with access, but only in MySQL?

using vb.net 2008

 

[jmcilhinney]

Please post in the most appropriate forum for the topic. Thread moved.

 

[Caraldur]

Mysql Connection

Tgf-47,

First you need to make sure that you have the mysql connector for .Net which allows VB Studios to connect to MySQL database. Here is the link.MySQL :: Download Connector/Net

Once you have that installed you need to add the following line to the top of your code

Imports MySql.Data.MySqlClient

Next you should declare your mysql connection string and declare a variable as your mysql connection like below. I usually declare these right after my Public Class <form name> heading.

Dim con As New MySqlConnection 
Dim ConStr As String = "Server=<enter server address>;Database=<enter database name>;Uid=<enter user>;Pwd=<enter user password>;"

You should add this line of code to the load event of your form.

con.ConnectionString = ConStr 'set mysql connection string to MySQL connection

Here is an example of the code I use to update a table in my database using parameters.

    Sub updateInventory(ByVal partnumber As String, ByVal qty As Integer)
        Try
            Dim cmd As MySqlCommand 'declare command
            'declare sql string and set it's value
            Dim sqlStr As String = "update partinfo set inventory = inventory - @Qty where partnumber = @PartNumber"

            'set cmd to new MySQL command
            cmd = New MySqlCommand(sqlStr, con)
            con.Open() 'open connection to DB

            'set parameters for update
            With cmd.Parameters
                .Add("@PartNumber", MySqlDbType.VarChar).Value = partnumber
                .Add("@Qty", MySqlDbType.Int32).Value = qty
            End With

            cmd.ExecuteNonQuery() 'execute command
            con.Close() 'close connection

        Catch ex As Exception
            MsgBox("Error: " & ex.Message.ToString) 'return error
        End Try

    End Sub

That should take care of you problem and I imagine there is a better way to do this but this works for me. Good luck and let me know if you are still having problems.

-Fred