Create DataTable to insert to a MySQL database (MySQL.data)

Hi!

I have some questions regarding datatables in VB.net vs 2005.

I have a database table (MySQL 5) I want to update  using the dataadapter in MySQL.data

To do that I need to fill a datatable with all the information I want to insert if I understand this correct? :)

To get the best result I have heard here is that I need to create the datable with the same column name and the same type as in my database table.

So how do I create my datatable?

I  have read the MSDN and I found this function, is this the way I shold use to make my table? or is there a better way to create the table I need?

Thx :)

Private Sub AddDataColumn(ByVal table As DataTable)
   
   Dim typeInt32 As System.Type = _
      System.Type.GetType("System.Int32")
   Dim column As DataColumn = _
       New DataColumn("id", typeInt32)

   ' Set various properties.
   With column
      .AutoIncrement = True
      .AutoIncrementSeed = 1
      .AutoIncrementStep = 1
      .ReadOnly = True
    End With

   ' Add to Columns collection.
   table.Columns.Add(column)
End Sub

LVL 1
AWestEngAsked:
Who is Participating?
 
VBRocksCommented:
Yes, looks like you are doing good.

Here's an example:

    Private Function CreateTable() As DataTable

        Dim columnID As New DataColumn("ID")
        With columnID
            .DataType = System.Type.GetType("System.Int32")
            .AutoIncrement = True
            .AutoIncrementSeed = 1
            .AutoIncrementStep = 1
            .ReadOnly = True
        End With

        Dim column2 As New DataColumn("Column2")
        Dim column3 As New DataColumn("Column3")
        'Etc...

        Dim dt As New DataTable("MyTable")
        dt.Columns.AddRange(New DataColumn() {columnID, column2, column3})

        Return dt

    End Function


'Another very simple way to create your table is:  
    In your Server Explorer, add a connection to your database.
    Then, add a new DataSet to your project.
    Then, from your Server Explorer, drag the table you want to add to your DataSet to the DataSet
        designer.
    Finally, pretending that the name of the dataset is "DataSet1", you can create a new instance of it:
        Dim ds as New DataSet1()
       
    If you have a table named "Table1" in your DataSet, then you can access it like this:
        ds.Table1

    That way you don't have to manually create your Table.  Much easier!


           
0
 
VBRocksCommented:
Here's an example that creates and loads the DataTable with 50 records:

    Private Function CreateTable() As DataTable
        Dim columnID As New DataColumn("ID")
        With columnID
            .DataType = System.Type.GetType("System.Int32")
            .AutoIncrement = True
            .AutoIncrementSeed = 1
            .AutoIncrementStep = 1
            .ReadOnly = True
        End With

        Dim column2 As New DataColumn("Column2")
        Dim column3 As New DataColumn("Column3")
        'Etc...

        Dim dt As New DataTable("MyTable")
        dt.Columns.AddRange(New DataColumn() {columnID, column2, column3})

        Dim row As DataRow
        For i As Integer = 1 To 50
            row = dt.NewRow()

            'No need to add a value to ColumnID, since it is an AutoIncrementing column
            row.Item("Column2") = "Row " & i.ToString() & " Column 2 Data"
            row.Item("Column3") = "Row " & i.ToString() & " Column 3 Data"

            dt.Rows.Add(row)
        Next

        For Each dr As DataRow In dt.Rows
            Debug.WriteLine(dr.Item("Column2") & vbTab & dr.Item("Column3"))

        Next

        Return dt

    End Function

0
 
AWestEngAuthor Commented:
Thx man great..

Have you worked with MySQL.data ?

I have another question open regarding this, I have som eproblem with inserting data via the command function,

I solved the problem with creating a function that uses the command with MySQL.data but but it's slower then Odbc, that is stange ??
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
AWestEngAuthor Commented:
the question title is:
MySQL.data with .ExecuteNonQuery()
0
 
AWestEngAuthor Commented:
You always seems to have the correct answers :) hehe.. You know your vb.net :)

That's why I want to check f you have been working with MySQL.data :)

thx... !!!! :)
0
 
VBRocksCommented:
Thank you for the kind complements.

Actually, I don't work with MySQL at all.  I work with VB.NET 2005, and I use SQL Server and SQL
Express.

I guess they are similar enough to work.  I'll take a look at your other question and see if I can help out.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.