[RESOLVED] 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?

[cicatrix]

Everything is the same except the namespace

Istead of using System.Data.OleDb you should download MySQL data provider assembly and reference it from your project. (http://dev.mysql.com/downloads/connector/net/5.0.html)
Then use MySQL.Data instead of System.Data namespace.

[jmcilhinney]

Do you really think hard-coding the connection string is a good idea? That means that the application will need to be recompiled and re-deployed if any of the connection details change. That's not good design. Far better to have your connection properties come from an external source that can be edited if required.

Regardless, you can't use SqlClient to connect to anything but SQL Server. MySQL is not SQL Server. You should download Connector/Net from the MySQL web site, which is an ADO.NET provider specifically for MySQL. It has equivalent classes to those in SqlClient, OleDb and every other ADO.NET provider.

[tsaunders]

Look at storing your connection string in the web.config:

http://weblogs.asp.net/owscott/archi....NET-v2.0.aspx

Also look at creating a data access layer to handle all your database functions.

[tgf-47]

eventually the connection string will be loaded from an .ini file, but for now I just want to get all the rough edges out.

I got it to work as follows:

Private Function ConnectMe() As DataTable
        Dim conn As String = "Server=192.168.0.36;Port=3306;Database=wswc;Uid=root;Pwd=Jack"
        Dim cmd As String = "SELECT * FROM st_users"
        Dim ad As New MySql.Data.MySqlClient.MySqlDataAdapter(cmd, conn)
        Dim topics As New DataSet()
        ad.Fill(topics)
        ConnectMe = topics.Tables(0)
    End Function

How do I change this to a for loop or while loop?

I want all the data to go in one by one instead of the Fill option.
Reason for this is there are data in the table that I would like to edit before it gets passed to the dataset.

please help me. I posted this question in this thread: http://www.vbforums.com/showthread.p...08#post3793608

[tsaunders]

Why not just alter your query to only return what you want instead of everything from the st_users table?

Dim dr As DataRow
Dim ds As Dataset
Dim dt As DataTable
<code to fill the dataset>
dt = ds.Tables(0)
For Each dr In dt.Rows
Console.WriteLine (dr("ColName"))
Next
ds.Dispose()

[cicatrix]

How do I change this to a for loop or while loop?

I want all the data to go in one by one instead of the Fill option.
Reason for this is there are data in the table that I would like to edit before it gets passed to the dataset.

Use MySQLDataReader class:

Private Function ConnectMe() As DataTable
        Dim conn As String = "Server=192.168.0.36;Port=3306;Database=wswc;Uid=root;Pwd=Jack"
        Dim cmd As String = "SELECT * FROM st_users"
        Dim rd As New MySql.Data.MySqlClient.MySqlDataReader(cmd, conn)

        While rd.Read
          ' Each loop the data reader will read one more data row returned by your query.
          ' rd.Item("fieldname") will contain the value of the fieldname of your table
        End While

End Function

[tgf-47]

 Dim rd As New MySql.Data.MySqlClient.MySqlDataReader(cmd, conn)

It gives a long error under the rd complaining that it is not 'Friend'

[cicatrix]

Post exact error message please.

[tgf-47]

'MySql.Data.MySqlClient.MySqlDataReader.Friend Sub New(cmd as MySql.Data.MySqlClient,MySqlCommand, statement As MySql.Data.MySqlClient.PreparableStatement, behavior As System.Data.CommandBehavior)' is not accessible in this context because it is 'Friend'.

[cicatrix]

P.S. OMG Just noticed what I missed before. Try this:

Private Function ConnectMe() As DataTable
        Dim connstr As String = "Server=192.168.0.36;Port=3306;Database=wswc;Uid=root;Pwd=Jack"
        Dim cmdtext As String = "SELECT * FROM st_users"
        Dim Conn As New MySql.Data.MySqlClient.MySqlConnection(connstr)
        Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(cmd, conn)
        
        Conn.Open
        Dim rd As MySql.Data.MySqlClient.MySqlDataReader = cmd.ExecuteReader

        While rd.Read
          ' Each loop the data reader will read one more data row returned by your query.
          ' rd.Item("fieldname") will contain the value of the fieldname of your table
        End While

        Conn.Close
End Function