ExecuteNonQuery insert error

  • Thread starter Thread starter Jim
  • Start date Start date
J

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=SQLOLEDB;" & _
<.... etc......>
Dim objConnection As New OleDbConnection(THGConnect)
Dim strSQL As String = "select top 10 docs_id, docs_document
from thg_docs_document_store"
Dim objCommand As New OleDbCommand(strSQL, objConnection)
Dim objDataAdapter As New OleDbDataAdapter(objCommand)
Dim objDataTable As New Data.DataTable("temp_table")
Dim objDataRow As DataRow

Dim AConnect As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AcsessDB.mdb;"
Dim AccessConnection As New OleDbConnection(AConnect)
Dim AccessCommand As New OleDbCommand
AccessCommand.CommandText = "usp_InsertDocs"
AccessCommand.CommandType = CommandType.StoredProcedure
AccessCommand.Connection = AccessConnection

AccessConnection.Open()

Try
objConnection.Open()
objDataAdapter.Fill(objDataTable)

For Each objDataRow In objDataTable.Rows
ListBox1.Items.Add(objDataRow.Item("docs_id"))
ListBox1.Items.Add(objDataRow.Item("docs_document"))

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

RowsAffected = AccessCommand.ExecuteNonQuery()
Next
<snip>
 
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:
 
Back
Top