Syntax error with Update command - how to use?

[Jerry]

Greeting,

I am trying to update a detail table that does not have a
unique key (that is the exercise). With the code below I
am getting the error message "Incorrect syntax near
keyword Set" (note: sql code is all on one line). I may
be guilty of tring to use Com ADO syntax. In the Where
clause I am using declared variables instead of parameter
vars. Is that my error? Please could someone correct my
syntax error below?
----------------------------------------------------------
setting intID, str1 and str2 up here --- then
....
Dim sqlDA As SqlDataAdapter = New SqlDataAdapter("Select *
From tbl2", conn)
sqlDA.UpdateCommand = New SqlCommand("Update tbl2 Set fld1
= @prm1, Set fld2 = @prm2 Where (ID = " & intID & " And
fld1 = '" & str1 & "' fld2 = '" & str2 & "')", conn)
sqlDA.UpdateCommand.CommandType = CommandType.Text

Try
conn.Open()
Dim sqlParm As New SqlParameter
sqlParm = sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@prm1", SqlDbType.VarChar, 50, "fld1"))
sqlParm = sqlDA.UpdateCommand.Parameters.Add(New
SqlParameter("@prm2", SqlDbType.VarChar, 50, "fld2"))
dr = ds1.Tables("tbl2").Rows(5)
dr(0) = CType(txtID.Text, Integer)
dr(1) = txtfld1.Text)
dr(2) = txtfld2.Text
sqlDA.Update(ds1, "tbl2") <----> errors out here <-----
conn.Close()
Catch...

 

[Armin Zingler]

sqlDA.UpdateCommand = New SqlCommand("Update tbl2 Set fld1
= @prm1, Set fld2 = @prm2 Where (ID = " & intID & " And
fld1 = '" & str1 & "' fld2 = '" & str2 & "')", conn)
sqlDA.UpdateCommand.CommandType = CommandType.Text

Remove the 2nd "Set":

.... Set fld1 = @prm1, fld2 = @prm2 ...

You should/could also replace the other values by parameters.

--
Armin

How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

 

[Jerry]

Well, here is some weirdness. First, I noticed that I
have 2 Set keywords (silly me). so I removed the
2nd "Set" but still got a syntax error. Then I removed
the Where clause, and now it works perfectly. Is this
correct? Or am I just getting lucky? I'm not completely
clear on the fundamentals here. I update the table in my
dataset, then I update the table on the server through the
dataAdapter. I think I have a handle on the dataAdapter
part. But wait! I think I get it. I specify which row
to update in the table in the dataset, then update the
server table. Man, dotnet is great. It's so simple.

Jerry

 

[LAL]

You need to add "AND" between your 2nd & 3rd where conditions

 

[Jerry]

Thanks. I fixed that. And, of course, without the Where
clause I get the same values for all the rows. But now I
get a new error message that the params in the Where
clause are not being supplied. Here is what I have now:


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))
-----------------------------------------------------

Am I doing this correctly to add the old values to these
params in the Where clause?

Thanks,
Jerry

 

[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