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

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

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Fo...do-net/200612/1

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=?"


"craig" <u7564@uwe> wrote in message news:6b25258e05baf@uwe...
> 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
>
> --
> Message posted via DotNetMonster.com
> http://www.dotnetmonster.com/Uwe/Fo...do-net/200612/1
>

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

Thanks a bunch Earl!!

Earl wrote:
>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
>[quoted text clipped - 151 lines]
>> thanks,
>> Craig

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Fo...do-net/200612/1