Syntax error with Update command - how to use?

[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

-----Original Message-----
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.UpdateCo mmand = New SqlCommand("Upd ate tbl2 Set fld1= @prm1, Set fld2 = @prm2 Where (ID = " & intID & " And
fld1 = '" & str1 & "' fld2 = '" & str2 & "')", conn)
sqlDA.UpdateCo mmand.CommandTy pe = CommandType.Tex t

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

Thanks in advance,
Jerry
.

[LAL]

You need to add "AND" between your 2nd & 3rd where conditions
"Jerry" <an*******@disc ussions.microso ft.com> wrote in message
news:b6******** *************** *****@phx.gbl.. .

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

-----Original Message-----
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.UpdateCo mmand = New SqlCommand("Upd ate tbl2 Set

fld1

= @prm1, Set fld2 = @prm2 Where (ID = " & intID & " And
fld1 = '" & str1 & "' fld2 = '" & str2 & "')", conn)
sqlDA.UpdateCo mmand.CommandTy pe = CommandType.Tex t

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

Thanks in advance,
Jerry
.

[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.UpdateCom mand = New SqlCommand("Upd ate 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.UpdateCom mand.Parameters .Add(New
SqlParameter("@ prm1", SqlDbType.VarCh ar, 50, "fld1"))
sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ prm2", sqlDbType.VarCh ar,50, "fld2"))

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

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

sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ ID", SqlDbType.BigIn t, 8, conID.ToString) )
sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ fld1", SqlDbType.VarCh ar, 50, strfld1))
sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ fld2", SqlDbType.VarCh ar, 50, strfld2))
-----------------------------------------------------

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

Thanks,
Jerry

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

"Jerry" <an*******@disc ussions.microso ft.com> wrote in messagenews:b6******* *************** ******@phx.gbl. ..

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

>-----Original Message-----
>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.UpdateCo mmand = New SqlCommand("Upd ate tbl2 Set

fld1

>= @prm1, Set fld2 = @prm2 Where (ID = " & intID & " And
>fld1 = '" & str1 & "' fld2 = '" & str2 & "')", conn)
>sqlDA.UpdateCo mmand.CommandTy pe = CommandType.Tex t
>
>Try
> conn.Open()
> Dim sqlParm As New SqlParameter
> sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
>SqlParameter(" @prm1", SqlDbType.VarCh ar, 50, "fld1"))
>sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
>SqlParameter(" @prm2", SqlDbType.VarCh ar, 50, "fld2"))
> dr = ds1.Tables("tbl 2").Rows(5)
> dr(0) = CType(txtID.Tex t, Integer)
> dr(1) = txtfld1.Text)
> dr(2) = txtfld2.Text
> sqlDA.Update(ds 1, "tbl2") <----> errors out here <--

--- > conn.Close()
>Catch...
>-------------------------------------------------------
>
>Thanks in advance,
>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.Par ameterDirection .Input,

and

System.Data.Dat aRowVersion.Ori ginal

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

Jerry

-----Original Message-----
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.UpdateCo mmand = New SqlCommand("Upd ate tbl2 Set fld1= @prm1, fld2 = @prm2 Where (ID = @ID And fld1 = @fld1 Andfld2 = @fld2)", conn)

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

sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ prm1", SqlDbType.VarCh ar, 50, "fld1"))
sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ prm2", sqlDbType.VarCh ar,50, "fld2"))

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

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

sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ ID", SqlDbType.BigIn t, 8, conID.ToString) )sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ fld1", SqlDbType.VarCh ar, 50, strfld1))
sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
SqlParameter("@ fld2", SqlDbType.VarCh ar, 50, strfld2))
-----------------------------------------------------

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

Thanks,
Jerry

-----Original Message-----
You need to add "AND" between your 2nd & 3rd whereconditions

"Jerry" <an*******@disc ussions.microso ft.com> wrote in

message

news:b6****** *************** *******@phx.gbl ...

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 notcompletely clear on the fundamentals here. I update the table inmy dataset, then I update the table on the server throughthe 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
>-----Original Message-----
>Greeting,
>
>I am trying to update a detail table that does nothave a >unique key (that is the exercise). With the codebelow I >am getting the error message "Incorrect syntax near
>keyword Set" (note: sql code is all on one line). Imay >be guilty of tring to use Com ADO syntax. In the Where >clause I am using declared variables instead ofparameter >vars. Is that my error? Please could someone correctmy >syntax error below?
>------------------------------------------------------ ----

>setting intID, str1 and str2 up here --- then
>....
>Dim sqlDA As SqlDataAdapter = New SqlDataAdapter("Select *
>From tbl2", conn)
>sqlDA.UpdateCo mmand = New SqlCommand("Upd ate tbl2 Set
fld1
>= @prm1, Set fld2 = @prm2 Where (ID = " & intID & "

And >fld1 = '" & str1 & "' fld2 = '" & str2 & "')", conn)
>sqlDA.UpdateCo mmand.CommandTy pe = CommandType.Tex t
>
>Try
> conn.Open()
> Dim sqlParm As New SqlParameter
> sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
>SqlParameter(" @prm1", SqlDbType.VarCh ar, 50, "fld1"))
>sqlParm = sqlDA.UpdateCom mand.Parameters .Add(New
>SqlParameter(" @prm2", SqlDbType.VarCh ar, 50, "fld2"))
> dr = ds1.Tables("tbl 2").Rows(5)
> dr(0) = CType(txtID.Tex t, Integer)
> dr(1) = txtfld1.Text)
> dr(2) = txtfld2.Text
> sqlDA.Update(ds 1, "tbl2") <----> errors out here <- -

--- > conn.Close()
>Catch...
>------------------------------------------------------

- >
>Thanks in advance,
>Jerry
>.
>

.

.