Insert Statement somtimes works

3 Apr 2006 12:28 PM

rickn

I'm doing an Insert from ODBC dataset into an OLE access table, the
Insert for multiple rows doesn't work, but if I do an Insert using the
commented out statement below it works just fine. What is wrong with
the code to insert from ODBC multiple rows???

        Dim oOleDbConnection As OleDb.OleDbConnection
        Dim objCommand As New OleDb.OleDbCommand
        Dim sConnString As String = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=C:\Documents and Settings\My
Documents\Visual Studio Projects\MAX Data Interface\Lot Table.mdb;" & _
                 "User ID=Admin;" & _
                 "Password="

        oOleDbConnection = New OleDb.OleDbConnection(sConnString)
        oOleDbConnection.Open()

        'start a loop here and loop thru and update access

        For i = 0 To DataSet1.Tables(0).Rows.Count - 1
        Next
        Try

               For Each rowX As DataRow In
DataSet1.Lot_Tracking_Hist.Rows

                'sSQL = "INSERT INTO [LotTrackingHistory] (PRTNUM_72)
Values ('insertdataOK')" ' this worked with button click
                    sSQL = "INSERT INTO [LotTrackingHistory]
(PRTNUM_72) Values (" & rowX.Item("PRTNUM_72").ToString & ")"

            Next
            With objCommand
                .Connection = oOleDbConnection
                .CommandText = sSQL
                .CommandType = CommandType.Text
                .ExecuteNonQuery()
            End With
            'Count the number of entries and display them
            'icount = (objCommand.ExecuteNonQuery())
            ' MessageBox.Show(icount)
            oOleDbConnection.Close()
            oOleDbConnection = Nothing

3 Apr 2006 2:37 PM

Norman Yuan

Since the commeted out statement worked, obviously, the field "PRTNUM_72" is
Text type, so you need single quote mark for its value in "INSERT INTO..."
statement:

sSQL = "INSERT INTO [LotTrackingHistory] (PRTNUM_72) Values ('" &
rowX.Item("PRTNUM_72").ToString & "')"

Show quote

"rickn" <ricknichol***@att.net> wrote in message
news:1144067292.823682.184310@j33g2000cwa.googlegroups.com...
> I'm doing an Insert from ODBC dataset into an OLE access table, the
> Insert for multiple rows doesn't work, but if I do an Insert using the
> commented out statement below it works just fine. What is wrong with
> the code to insert from ODBC multiple rows???
>
>        Dim oOleDbConnection As OleDb.OleDbConnection
>        Dim objCommand As New OleDb.OleDbCommand
>        Dim sConnString As String = _
>                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                 "Data Source=C:\Documents and Settings\My
> Documents\Visual Studio Projects\MAX Data Interface\Lot Table.mdb;" & _
>                 "User ID=Admin;" & _
>                 "Password="
>
>        oOleDbConnection = New OleDb.OleDbConnection(sConnString)
>        oOleDbConnection.Open()
>
>        'start a loop here and loop thru and update access
>
>        For i = 0 To DataSet1.Tables(0).Rows.Count - 1
>        Next
>        Try
>
>               For Each rowX As DataRow In
> DataSet1.Lot_Tracking_Hist.Rows
>
>                'sSQL = "INSERT INTO [LotTrackingHistory] (PRTNUM_72)
> Values ('insertdataOK')" ' this worked with button click
>                    sSQL = "INSERT INTO [LotTrackingHistory]
> (PRTNUM_72) Values (" & rowX.Item("PRTNUM_72").ToString & ")"
>
>            Next
>            With objCommand
>                .Connection = oOleDbConnection
>                .CommandText = sSQL
>                .CommandType = CommandType.Text
>                .ExecuteNonQuery()
>            End With
>            'Count the number of entries and display them
>            'icount = (objCommand.ExecuteNonQuery())
>            ' MessageBox.Show(icount)
>            oOleDbConnection.Close()
>            oOleDbConnection = Nothing
>