Update Command gives "Must Declare scalar variable "@P7CYCELDAY""

C

craig

This is driving me nuts! I've got a form that is giving me fits. I've done
the same thing that I've done on dozens of other forms. When the data
adaptor update command is executed I get the "Must declare scalar variable
@p7CYCLEDAY" Here are various pieces of my code.

Public daServObj As New OdbcDataAdapter
Public dsServObj As New DataSet
Private ServObjConnection As New OdbcConnection(strconn)
Private ServObjCmd As OdbcCommand
Dim ServObjTrans As OdbcTransaction

strconn = "Driver={SQL Server};SERVER=" & odbcservername.Trim & ";DATABASE="
& odbcdatabase.Trim & ";UID=" & odbcusername.Trim & ";PWD=" & loginpass.Trim

Try
ServObjConnection.Open()
ServObjCmd = ServObjConnection.CreateCommand
Catch ex As Exception
MsgBox("Error: Unable to open connection" & ex.Message & ": " &
ex.Source, MsgBoxStyle.OKOnly, "Create Connection")
End Try

tq = "select objno,title,rtrim(clients.fname)+' '+rtrim(clients.lname)
as clifull,startdate,target,completed,objective,cyclemon,cycleday,notes,
servobj.resp_key,rtrim(staff.llast)+', '+rtrim(staff.ffirst)+' '+staff.middle
as resfull,"
tq = tq + " discipline, assessment_key, asmdone_key, '' as title2,
mpf_key, servobj_key from servobj left outer join staff on servobj.
resp_key=staff.staff_key left outer join clients on servobj.
client_key=clients.client_key "
tq = tq + "where plan_key=" + dsPlans.Tables(0).Rows(Me.DataGrid1.
CurrentRowIndex)(3).ToString.Trim + " and plan_key>0 and servobj.client_key
=" + openclikey.ToString.Trim + " order by objno"

Dim cb As OdbcCommandBuilder = New OdbcCommandBuilder(daServObj)
Try
ServObjCmd.CommandText = tq
daServObj.SelectCommand = ServObjCmd
daServObj.Fill(dsServObj, "ServObj")
daServObj.InsertCommand = cb.GetInsertCommand()
Try
daServObj.UpdateCommand = cb.GetUpdateCommand()
Catch ex As Exception

tq = "update servobj set objno=?,title=?,startdate=?,target=?,
completed=?,objective=?,cyclemon=?cycleday=?,notes=?,resp_key=?,"
tq = tq + " discipline=?, assessment_key=?, asmdone_key=?,
mpf_key=? WHERE servobj_key=?"

daServObj.UpdateCommand = New OdbcCommand(tq,
ServObjConnection)
' 0. objno=?,
daServObj.UpdateCommand.Parameters.Add("@OBJNO", OdbcType.
Char)
daServObj.UpdateCommand.Parameters(0).SourceColumn = "OBJNO"
' 1. title=?,
daServObj.UpdateCommand.Parameters.Add("@TITLE", OdbcType.
Char)
daServObj.UpdateCommand.Parameters(1).SourceColumn = "TITLE"
' 2. startdate=?,
daServObj.UpdateCommand.Parameters.Add("@STARTDATE", OdbcType.
DateTime)
daServObj.UpdateCommand.Parameters(2).SourceColumn =
"STARTDATE"
' 3. target=?,
daServObj.UpdateCommand.Parameters.Add("@TARGET", OdbcType.
DateTime)
daServObj.UpdateCommand.Parameters(3).SourceColumn = "TARGET"
' 4. completed=?,
daServObj.UpdateCommand.Parameters.Add("@COMPLETED", OdbcType.
DateTime)
daServObj.UpdateCommand.Parameters(4).SourceColumn =
"COMPLETED"
' 5. objective=?,
daServObj.UpdateCommand.Parameters.Add("@OBJECTIVE", OdbcType.
Text)
daServObj.UpdateCommand.Parameters(5).SourceColumn =
"OBJECTIVE"
' 6. cyclemon=?
daServObj.UpdateCommand.Parameters.Add("@CYCLEMON", OdbcType.
SmallInt)
daServObj.UpdateCommand.Parameters(6).SourceColumn =
"CYCLEMON"
' 7. cycleday=?,
daServObj.UpdateCommand.Parameters.Add("@CYCLEDAY", OdbcType.
SmallInt)
daServObj.UpdateCommand.Parameters(7).SourceColumn =
"CYCLEDAY"
' 8. notes=?,
daServObj.UpdateCommand.Parameters.Add("@NOTES", OdbcType.
Text)
daServObj.UpdateCommand.Parameters(8).SourceColumn = "NOTES"
' 9. resp_key=?,"
daServObj.UpdateCommand.Parameters.Add("@RESP_KEY", OdbcType.
Int)
daServObj.UpdateCommand.Parameters(9).SourceColumn =
"RESP_KEY"
'10. discipline=?,
daServObj.UpdateCommand.Parameters.Add("@DISCIPLINE",
OdbcType.Char)
daServObj.UpdateCommand.Parameters(10).SourceColumn =
"DISCIPLINE"
'11. assessment_key=?,
daServObj.UpdateCommand.Parameters.Add("@ASSESSMENT_KEY",
OdbcType.Int)
daServObj.UpdateCommand.Parameters(11).SourceColumn =
"ASSESSMENT_KEY"
'12. asmdone_key=?,
daServObj.UpdateCommand.Parameters.Add("@ASMDONE_KEY",
OdbcType.Int)
daServObj.UpdateCommand.Parameters(12).SourceColumn =
"ASMDONE_KEY"
'13. mpf_key=?
daServObj.UpdateCommand.Parameters.Add("@MPF_KEY", OdbcType.
Int)
daServObj.UpdateCommand.Parameters(13).SourceColumn =
"MPF_KEY"
'14. servobj_key=?"
daServObj.UpdateCommand.Parameters.Add("@SERVOBJ_KEY",
OdbcType.Int)
daServObj.UpdateCommand.Parameters(14).SourceColumn =
"SERVOBJ_KEY"
daServObj.UpdateCommand.Transaction = daServObj.InsertCommand.
Transaction
End Try
End Try


dsServObj.Tables(0).Rows(Me.DataGrid2.CurrentRowIndex).EndEdit()
daServObj.Update(dsServObj.Tables(0))


This is working in many places, so I'm sure it has to be something simple
that I'm overlooking but I just can't find it.

thanks,
Craig
 
E

Earl

You are missing a comma between cyclemon=? and cycleday=? in the query. When
you have incorrect syntax, you'll sometimes get all sorts of odd errors like
the one you are getting.

tq = "update servobj set
objno=?,title=?,startdate=?,target=?,
completed=?,objective=?,cyclemon=?cycleday=?,notes=?,resp_key=?,"
tq = tq + " discipline=?, assessment_key=?, asmdone_key=?,
mpf_key=? WHERE servobj_key=?"
 
C

craig via DotNetMonster.com

Oh Man! I knew it had to be something simple, but I can't believe I missed
that!

Thanks a bunch Earl!!
You are missing a comma between cyclemon=? and cycleday=? in the query. When
you have incorrect syntax, you'll sometimes get all sorts of odd errors like
the one you are getting.

tq = "update servobj set
objno=?,title=?,startdate=?,target=?,
completed=?,objective=?,cyclemon=?cycleday=?,notes=?,resp_key=?,"
tq = tq + " discipline=?, assessment_key=?, asmdone_key=?,
mpf_key=? WHERE servobj_key=?"
This is driving me nuts! I've got a form that is giving me fits. I've
done
Click to expand...
[quoted text clipped - 151 lines]
thanks,
Craig
Click to expand...
Click to expand...
 
Top