inserting the results of a datatable into a database table

[toon10]

Hi

I have a datatable which I want to write to a table in my database.

        Dim varSQL As String
        varSQL = ("SELECT * FROM [tblCost] WHERE [Part Number] = '" & txtPartNumber.Text & "' ORDER BY [Part Number]")

        Try
            Dim AccessCommandPartRetrieve As New System.Data.OleDb.OleDbDataAdapter(varSQL, AccessConn)
            Dim dt As DataTable
            dt = New DataTable
            AccessCommandPartRetrieve.Fill(dt)
            AccessConn.Close()
…

What I want to do is copy the results of this datatable into a table in my database.

Something like…

        AccessConn.Open()
        Dim AccessCommandInsert As New System.Data.OleDb.OleDbCommand("INSERT INTO [tblMarkUp] ([Record Type], [Part Number], Description, [Procurement Type], BOM, Component, Node, [Valid From], [Qty Per], [Raw Material], [Sub Contract], [Material Overhead], [Direct Labour], [Labour Overhead], [Factory Overhead]) 

** and then the values in the datatable!

        Try
            With AccessCommandInsert
                .Connection = AccessConn
                .CommandType = CommandType.Text
                .ExecuteNonQuery()
            End With
            AccessConn.Close()
…

I’m not sure if this is the best way to do this or how to end my INSERT INTO statement to incorporate my datatable.

Any ideas would be gratefully received.

 

[Mastakilla]

Here's how you could do it, just add your own exception handling.

 'Open the connection
 AccessConn.Open()

 'Declare and initialize the datacommand
 Dim AccessCommandInsert As New System.Data.OleDb.OleDbCommand

 AccessCommandInsert.Connection = AccessConn
 AccessCommandInsert.CommandType = CommandType.Text

 'Loop through the datarows
 For each dr as datarow in dt.rows

  'Build the query with values contained in dr, build your own string here
  AccessCommandInsert.CommandText = "INSERT INTO [YourTable] ([Field1], [Field2]) VALUES (" & dr("Field1") & ", " & dr("Field2") & ")"
  'Execute the query
  AccesCommandInsert.ExecuteNonQuery()
 End For

 'Close the connection
 AccessConn.Close()

hope it helps somehow

 

[toon10]

Thanks for the help. I'll give that a try!