Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
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
Updated 17-Nov-09 5:22am
v4

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.
 
Share this answer
 
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!

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

 
Share this answer
 
v4
Comments
Niranjan001 26-Jul-10 6:26am    
Reason for my vote of 3
good
try this upadate command code
cmdUpdate.CommandText = UPDATE erf SET ([clnm]=[@clnm], [sn]=[@sn], [sec]=[@sec]) WHERE [Enrollno]=[@Enrollno]"
:omg:
 
Share this answer
 
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900