SQL UPDATE Where Clause ')'

Question

how can I end this SQL UPDATE statement? The debugger is telling me that

String constants must end with double quote

and is hiighlighting the end of the WHERE Clause "', con)

  cmd = New SqlCommand("UPDATE PersonsA SET(Members_ID='" & midtxt.Text & "'Gamer_Tag='" & gttxt.Text & "'Screenname='" & sntxt.Text & "'First_Name='" & fntxt.Text & "'Last_Name='" & lntxt.Text & "'DoB='" & dobtxt.Text & "'E_Mail_Address='" & emailtxt.Text & "'Position='" & teamptxt.Text & "'UG_Studio'" & ugptxt.Text & "'Cautions='" & ugctxt.Text & "'Record='" & recordtxt.Text & "'Event_Attendance='" & eventatxt.Text & "'Members_Status='" & memberstatcombo.Text & "'GTA_V_Crew_Member='" & gtavcrewmembercombo.Text & "'Games_Owned='" & gamesownedtxt.Text & "'Rep_Group='" & RepGroupcombo.Text & "'WHERE Members_ID='" & midtxt.Text & "', con)

Add a double quote after the single quote and also add commas between the items — Heslacher, Jan 19 '14 at 15:17
If you used a parameterized query this kind of errors are highly improbable. — Steve, Jan 19 '14 at 15:18
Change `"', con)` to `"'", con)` - The syntax higlighting in your question makes it easy to identify — Basic, Jan 19 '14 at 15:20

score 2 · Answer 1 · answered Jan 19 '14 at 15:22

2

In the end change

"', con)

with

"'", con)

And give a try.

answered Jan 19 '14 at 15:22

Tabrej Khan

894
6
15

No, still resulted with the same error. But I think it's to do with the structure of it, I'll try @M.Ali's answer, I think that's probably my problem. Thanks for your help. – user2980316 Jan 19 '14 at 15:29

score 2 · Answer 2 · edited May 23 '17 at 12:28

2

Not only you have missed the closing double quote, added a wrong parenthesys before the SET but also your UPDATE syntax is wrong because you miss the comma that should separate the fields.

Switching to a parameterized query will clear a lot of things

  cmd = New SqlCommand("UPDATE PersonsA SET Members_ID=@id, Gamer_Tag=@game, " & _
        "Screenname=@screen, First_Name=@first, Last_Name=@last, DoB=@dob," & _
        "E_Mail_Address=@email, Position=@pos, UG_Studio=@ustudio, Cautions=@caution," & _
        "Record=@record, Event_Attendance=@event, Members_Status=@memstatus, " & _
        "GTA_V_Crew_Member=@gtva, Games_Owned=@gameowned, Rep_Group=@repgroup" & _
        "WHERE Members_ID=@id", con)

  cmd.Parameters.AddWithValue("@id",midtxt.Text)
  .... follow with other parameters ....
  cmd.ExecuteNonQuery()

As you can see, using a parameterized query simplify your command text and remove all that ugly string concatenation where subtle syntax errors are hidden from view.
Of course a parameterized query removes also any possibility of Sql Injections

A simple review about UPDATE statement

edited May 23 '17 at 12:28

Community

1
1

answered Jan 19 '14 at 15:24

Steve

213,761
22
232
286

Thanks for your reply, though the start did seam a little condescending. I've adopted your method show here and I'm receiving a rather strange error 'Member_Status could not be converted to data type int'. It's text that's in the field and the SQL column is set to varchar(35). Why am I receiving this error when at no point have I told it to treat Member_Status as an integer? – user2980316 Jan 19 '14 at 21:31
1

Sorry to have given this impression, it was not my intention. It is in effect a strange error. Could you add to your question the actual code that use the parameters? – Steve Jan 19 '14 at 21:37
Also I have just noticed that you marked your question with the MySql tag but then you use SqlCommand that is a class used only for Sql Server. – Steve Jan 19 '14 at 21:40
Hi, it's difficult to tell people intention in text, my apologize; you're being a great help here! The Server is MS SQL Server ('08 or 05 not sure.) I've created a new question for the error; – user2980316 Jan 19 '14 at 21:48
http://stackoverflow.com/questions/21223091/my-sql-converting-to-int-when-it-shouldnt-o-o – user2980316 Jan 19 '14 at 21:48

score 1 · Answer 3 · answered Jan 19 '14 at 15:19

SqlCommand("UPDATE PersonsA SET(Members_ID='" & midtxt.Text &
                               ^ this Parenthesis is not needed

Correct Syntax for UPDATE Statement is like

 UPDATE PersonsA
  SET Members_ID= SomeValue,
      ColumnName = SomeValue,
      .
      .
      ... and so on...
 WHERE Clause

score 0 · Answer 4 · answered Jan 19 '14 at 15:19

0

at some point in there you switch from the pattern '" ... "' to "' ... '"

answered Jan 19 '14 at 15:19

Randy

16,480
1
37
55

SQL UPDATE Where Clause ')'

4 Answers4