syntax error in update statement

Question

0.00/5 (No votes)

See more:

I am using ms access and vb.net, and I'm having the problem in update button.

The error is: syntax error

The code is:

Try
  cmdUpdate.CommandText = "UPDATE erf SET (clnm = '" & clnm.Text & "', sn='" & sname.Text & "',sec='" & sec.Text _
    & "') WHERE Enrollno = " & Enrollno.Text & " "
  cnnOLEDB.Close()
  cnnOLEDB.Open()
  cmdUpdate.CommandType = CommandType.Text
  cmdUpdate.Connection = cnnOLEDB
  cmdUpdate.ExecuteNonQuery()
  MsgBox("Record Added Successfully...", MsgBoxStyle.Information)
Catch ex As Exception
  MessageBox.Show("error .." & ex.Message, "update record")
  cmdUpdate.Dispose()
  ' MsgBox("Record Added Successfully...", MsgBoxStyle.Information)
Finally
  cnnOLEDB.Close()
End Try

Posted 16-Nov-09 20:33pm

riiya

Updated 17-Nov-09 5:22am

Shog⁹

v4

Add a Solution

4 solutions

Solution 5

try this upadate command code
cmdUpdate.CommandText = UPDATE erf SET ([clnm]=[@clnm], [sn]=[@sn], [sec]=[@sec]) WHERE [Enrollno]=[@Enrollno]"
:omg:

Posted 25-Jul-10 21:53pm

Niranjan001

Solution 4

Others have commented on the need to use parameterised queries in order to not be open to SQL injection attacks. With this code, I could easily erase your entire database. The other issue is simply that you appear to be writing SQL inline in your presentation layer. This makes for messy, hard to maintain, disorganised code.

Posted 17-Nov-09 11:46am

Christian Graus

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shameel · Accepted Answer · 2009-11-16T23:18:00

Solution 3

Most probably, your Enrollno.Text is empty and thus the string becomes, "UPDATE erf SET (clnm = '', sn='',sec='') WHERE Enrollno = ", Handle this scenario in your code. Also, I suspect that the brackets could be culprits, you cannot use brackets in the SET clause.

Posted 16-Nov-09 23:18pm

Shameel

Updated 16-Nov-09 23:19pm

v2

Comments

Tarun.K.S 13-Jul-11 16:04pm

5d! Compensating his downvotes and an excellent answer.

[no name] 13-Jul-11 16:12pm

Thanks Tarun. btw, can you see who downvoted my answers?

Tarun.K.S 14-Jul-11 3:20am

As far as I know, its not possible for anyone except the CodeProject staff to see the downvotes. You very well know that it was this who downvoted and he did apologize in the answer you downvoted. I was actually going to redirect you to Sugs and Bugs to report it to the CP staff but now that's not needed. Surely it was a shameless and cowardly act to downvote your answers but it has certainly backfired as you got more points, I have a special respect for your knowledge and the way you dealt with this. Cheers!

Filip C · Accepted Answer · 2009-11-16T21:00:00

you should use Using & DbParameters!
your code is not Sql injection proof like this..

' Verify Enrollno.Text is not an empty string prior to attempting the Update<br />  If String.IsNullOrEmpty(Enrollno.Text) Then Return  ' Assuming this code is in a Procedure or Function, of course.<br />        Using cnnOLEDB As New Data.OleDb.OleDbConnection("connectionstring goes here")<br />            Using cmdUpdate As Data.OleDb.OleDbCommand = cnnOLEDB.CreateCommand<br />                cmdUpdate.CommandText = "UPDATE erf SET (clnm=@clnm, sn=@sn, sec=@sec) WHERE Enrollno=@Enrollno"<br />                cmdUpdate.Parameters.AddWithValue("@clnm", clnm.text)<br />                cmdUpdate.Parameters.AddWithValue("@sn", sname.text)<br />                cmdUpdate.Parameters.AddWithValue("@sec", sec.text)<br />                cmdUpdate.Parameters.AddWithValue("@Enrollno", Enrollno.text)<br />                Try<br />                    cmdUpdate.ExecuteNonQuery()<br />                    MsgBox("Record Added Successfully...", MsgBoxStyle.Information)<br />                Catch ex As Exception<br />                    MsgBox("error .." & ex.Message, "update record")<br />                End Try<br />            End Using<br />        End Using