Prob with Param in UpdateCommand

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

Greetings,

I'm trying to update a detail table which does not have
any unique fields (that is the exercise) and have a
problem with parameters in the Where clause, message says
my Where params not supplied. I have an int var and 2
string vars to retrieve the original values of the fields
in the row to be updated. If I don't use a where clause,
the params for the new values work OK except that all the
rows get the same values. Thus, I have to use a Where
clause but don't know to implement the Params in the Where
clause. Here is what I have so far:

sqlDA.UpdateCommand = New SqlCommand("Update tbl2 Set fld1
= @prm1, fld2 = @prm2 Where (ID = @ID And fld1 = @fld1 And
fld2 = @fld2)", conn)

-------------------------------------------------------
---params with new values---------this works OK without
Where clause----------

sqlParm = sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@prm1", SqlDbType.VarChar, 50, "fld1"))
sqlParm = sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@prm2", sqlDbType.VarChar,50, "fld2"))

-----------------------------------------------------
-----------------------------------------------------

---params for Where Clause with original values from
variables---------
---message says these params not being supplied to Where
clause''---------------

sqlParm = sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@ID", SqlDbType.BigInt, 8, conID.ToString))
sqlParm = sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@fld1", SqlDbType.VarChar, 50, strfld1))
sqlParm = sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@fld2", SqlDbType.VarChar, 50, strfld2))
-----------------------------------------------------

Is there a way for me to reference the original field
values without using vars, conID, strfld1, strfld2? How
do I supply these params to the Where clause? Do I need
to do anything to conID.ToString - supposed to be an
Integer.

Thanks for help,
Jerry
 
OK. Here is my fix: I copied the Windows generated code
for the update command of the table that does have a
unique field. Had to include

System.Data.ParameterDirection.Input,

and

System.Data.DataRowVersion.Original

I'm sure there is a less verbose way to add these clauses,
but this works for now.

Jerry
 
Back
Top