Insert Statement somtimes works

[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

 

[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 & "')"