ExecuteNonQuery insert error

[Jim]

I'm trying to take the data from strSQL below and "append / insert" that
into the Access database. The problem is: although the listbox displays
correctly, the Access table gets the first record inserted to the table
multiple times - as many rows as there are in the source table.

Obviously the Access parameters aren't getting updated properly.

I've tried putting the .parameters.Add outside the for - next loop. No joy.

Also, if there is an easier or more elegant way to do this please advise.

Jim

Dim THGConnect As String = _
"Provider=SQLOL EDB;" & _
<.... etc......>
Dim objConnection As New OleDbConnection (THGConnect)
Dim strSQL As String = "select top 10 docs_id, docs_document
from thg_docs_docume nt_store"
Dim objCommand As New OleDbCommand(st rSQL, objConnection)
Dim objDataAdapter As New OleDbDataAdapte r(objCommand)
Dim objDataTable As New Data.DataTable( "temp_table ")
Dim objDataRow As DataRow

Dim AConnect As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\Acses sDB.mdb;"
Dim AccessConnectio n As New OleDbConnection (AConnect)
Dim AccessCommand As New OleDbCommand
AccessCommand.C ommandText = "usp_InsertDocs "
AccessCommand.C ommandType = CommandType.Sto redProcedure
AccessCommand.C onnection = AccessConnectio n

AccessConnectio n.Open()

Try
objConnection.O pen()
objDataAdapter. Fill(objDataTab le)

For Each objDataRow In objDataTable.Ro ws
ListBox1.Items. Add(objDataRow. Item("docs_id") )
ListBox1.Items. Add(objDataRow. Item("docs_docu ment"))

'add parametes to query
AccessCommand.P arameters.Add(" @field1", OleDbType.Char,
32).Value = objDataRow.Item ("docs_id")
AccessCommand.P arameters.Add(" @field2", OleDbType.Char,
50).Value = objDataRow.Item ("docs_document ")

RowsAffected = AccessCommand.E xecuteNonQuery( )
Next
<snip>

[Jim]

I did get it to work through trial and error. The AccessCommand and
AccessAdapter must me Dim'd within the dataRow loop.

See the correction below:

Jim wrote:

I'm trying to take the data from strSQL below and "append / insert" that
into the Access database. The problem is: although the listbox displays
correctly, the Access table gets the first record inserted to the table
multiple times - as many rows as there are in the source table.

Obviously the Access parameters aren't getting updated properly.

I've tried putting the .parameters.Add outside the for - next loop. No
joy.

Also, if there is an easier or more elegant way to do this please advise.

Jim

Dim THGConnect As String = _
"Provider=SQLOL EDB;" & _
<.... etc......>
Dim objConnection As New OleDbConnection (THGConnect)
Dim strSQL As String = "select top 10 docs_id, docs_document
from thg_docs_docume nt_store"
Dim objCommand As New OleDbCommand(st rSQL, objConnection)
Dim objDataAdapter As New OleDbDataAdapte r(objCommand)
Dim objDataTable As New Data.DataTable( "temp_table ")
Dim objDataRow As DataRow

Dim AConnect As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\Acses sDB.mdb;"
Dim AccessConnectio n As New OleDbConnection (AConnect)
REMOVE== Dim AccessCommand As New OleDbCommand
REMOVE== AccessCommand.C ommandText = "usp_InsertDocs "
REMOVE== AccessCommand.C ommandType = CommandType.Sto redProcedure
REMOVE== AccessCommand.C onnection = AccessConnectio n

REMOVE== AccessConnectio n.Open()

Try
objConnection.O pen()
objDataAdapter. Fill(objDataTab le)

For Each objDataRow In objDataTable.Ro ws
ListBox1.Items. Add(objDataRow. Item("docs_id") )
ListBox1.Items. Add(objDataRow. Item("docs_docu ment"))

INSERT== Dim AccessCommand As New OleDbCommand
INSERT== AccessCommand.C ommandText = "usp_InsertDocs "
INSERT== AccessCommand.C ommandType = CommandType.Sto redProcedure
INSERT== AccessCommand.C onnection = AccessConnectio n

INSERT== AccessConnectio n.Open()

'add parametes to query
AccessCommand.P arameters.Add(" @field1", OleDbType.Char,
32).Value = objDataRow.Item ("docs_id")
AccessCommand.P arameters.Add(" @field2", OleDbType.Char,
50).Value = objDataRow.Item ("docs_document ")

RowsAffected = AccessCommand.E xecuteNonQuery( )
Next
<snip>