PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2008 [RESOLVED] How to hardcode a connection to a MySQL DB and code a Datatable? I need help-VBForums
Results 1 to 11 of 11

Thread: [RESOLVED] How to hardcode a connection to a MySQL DB and code a Datatable? I need help

  1. #1

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

    Resolved [RESOLVED] How to hardcode a connection to a MySQL DB and code a Datatable? I need help

    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?

  2. #2
    PowerPoster cicatrix's Avatar
    Join Date
    Dec 2009
    Location
    Moscow, Russia
    Posts
    3,654

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

    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.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,892

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

    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.

  4. #4
    New Member
    Join Date
    Apr 2010
    Posts
    6

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

    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.

  5. #5

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

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

    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:

    vb Code:
    1. Private Function ConnectMe() As DataTable
    2.         Dim conn As String = "Server=192.168.0.36;Port=3306;Database=wswc;Uid=root;Pwd=Jack"
    3.         Dim cmd As String = "SELECT * FROM st_users"
    4.         Dim ad As New MySql.Data.MySqlClient.MySqlDataAdapter(cmd, conn)
    5.         Dim topics As New DataSet()
    6.         ad.Fill(topics)
    7.         ConnectMe = topics.Tables(0)
    8.     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

  6. #6
    New Member
    Join Date
    Apr 2010
    Posts
    6

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

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

    Code:
    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()

  7. #7
    PowerPoster cicatrix's Avatar
    Join Date
    Dec 2009
    Location
    Moscow, Russia
    Posts
    3,654

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

    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:

    Code:
    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

  8. #8

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

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

    Quote Originally Posted by cicatrix View Post
    Code:
     Dim rd As New MySql.Data.MySqlClient.MySqlDataReader(cmd, conn)
    It gives a long error under the rd complaining that it is not 'Friend'

  9. #9

  10. #10

    Thread Starter
    Addicted Member tgf-47's Avatar
    Join Date
    Feb 2010
    Location
    CapeTown, South Africa -34.01244,18.337415
    Posts
    209

    Re: [RESOLVED] How to hardcode a connection to a MySQL DB and code a Datatable? I nee

    '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'.

  11. #11
    PowerPoster cicatrix's Avatar
    Join Date
    Dec 2009
    Location
    Moscow, Russia
    Posts
    3,654

    Re: [RESOLVED] How to hardcode a connection to a MySQL DB and code a Datatable? I nee

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

    Code:
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width