Multiple-step OLE DB operation generated errors

J

Jeff

Hello,

I have an Excel Spreadsheet that I am pulling information
into using VBA. I am able to propogate data into the
spreadsheet using ADO.

My problem is that when I attempt to update the info
using a different ADO query, it updates the first row,
but then gives me the error message
"Multiple-step OLE DB operation generated errors"

Here is the code I am using:

Dim cnnConnectSpec As String
Dim cmd As New ADODB.Command
Dim cnn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim prm As ADODB.Parameter
Dim prm1 As ADODB.Parameter
Dim prm3 As ADODB.Parameter
Dim wksStocks As Worksheet
Dim i As Integer
Dim FinalRow As Long

Set wksPrePlan = ThisWorkbook.Worksheets("Preplanning")
FinalRow = wksPrePlan.Cells(65536, 1).End(xlUp).Row

cnn.Open "DSN=ELCDEVEL;uid=user;pwd=passwd"

cmd.CommandText = "UPDATE LEGSUM set LS_USER_2 = ? " & _
", LS_USER_3 = ? " & _
" WHERE LS_TRIP_NUMBER = 62142 "
cmd.CommandType = adCmdText

'cmd.Prepared = True

' Create new parameter for PrePlan Driver
Set prm = cmd.CreateParameter("PP_DRV", adVarChar, _
adParamInput, 4)
cmd.Parameters.Append prm
' Create new parameter for PrePlan Driver
Set prm1 = cmd.CreateParameter("PP_PUNIT", adVarChar, _
adParamInput, 4)
cmd.Parameters.Append prm1
' Create new parameter for PrePlan Driver
Set prm2 = cmd.CreateParameter("TRIPNUM", adInteger,
adParamInput, 5)
cmd.Parameters.Append prm2

' Connect to the data source.
cmd.ActiveConnection = cnn

For i = 2 To FinalRow
cmd("PP_DRV") = wksPrePlan.Cells(i, 8)
cmd("PP_PUNIT") = wksPrePlan.Cells(i, 9)
cmd("TRIPNUM") = wksPrePlan.Cells(i, 1)
wksPrePlan.Cells(i, 12) = _
"Uploaded Trip"
cmd.Execute
Next i

'clean up
'objRs.Close
cnn.Close
Set objRs = Nothing
Set cnn = Nothing
Set cmd = Nothing
 
J

Jamie Collins

Jeff said:
Hello,

I have an Excel Spreadsheet that I am pulling information
into using VBA. I am able to propogate data into the
spreadsheet using ADO.

My problem is that when I attempt to update the info
using a different ADO query, it updates the first row,
but then gives me the error message
"Multiple-step OLE DB operation generated errors"

Without you posting details such as DBMS product, schema info and test
data, it's hard to reproduce and therefore diagnose the problem. As a
guess, it could be a data type mismatch or a problem with a null
value.

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top