error while updating record in Access database using vb.net

Question

0.00/5 (No votes)

See more:

i am getting the Sysntax Error given below while Exeuting the query

Syntax Error='Syntax Error in Update Statement'

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
       Dim sqlupdate As String
       Try

           Dim i As Integer

           sqlupdate = "UPDATE Customer_Master SET Names='" & txtname.Text & "', Address='" & txtaddr.Text & "', PhoneNo='" & txtphno.Text & "' WHERE CustomerID='" & txtcid.Text & "'"
           conn.Open()
           cmd = New OleDb.OleDbCommand(sqlupdate, conn)

           i = cmd.ExecuteNonQuery()
           cmd.Dispose()
           MsgBox("Customer updated")
           txtcid.Text = ""
           txtname.Text = ""
           txtaddr.Text = ""
           txtphno.Text = ""

           Call RefreshDGV()

       Catch ex As Exception
           MessageBox.Show(ex.Message.ToString())
       Finally
           If conn.State = ConnectionState.Open Then
               conn.Close()
           End If
       End Try



   End Sub

Posted 6-Dec-12 22:19pm

Pratik65

Add a Solution

2 solutions

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)

Filippo Mereu · Answer 1 · 2012-12-06T23:00:00

Solution 1

The syntax of the Update Statement is ok.
But you must double apostrophes in strings of each textbox
For example:
if in a textbox there is the string -> ab'cd
you must write in the SQL Query the string -> ab''cd

You can use the function:

VB

Protected Function CheckSqlStr(ByVal sParam As String) As String
        Dim sRet As String

        sRet = Replace(sParam, "'", "''")

        CheckSqlStr= sRet
End Function

and use it in the SQL Query

VB

sqlupdate = "UPDATE Customer_Master SET Names='" & CheckSqlStr(txtname.Text) & "', Address='" & CheckSqlStr(txtaddr.Text) & "', PhoneNo='" & CheckSqlStr(txtphno.Text) & "' WHERE CustomerID='" & CheckSqlStr(txtcid.Text) & "'"

Posted 6-Dec-12 23:00pm

Filippo Mereu

Comments

Pratik65 7-Dec-12 10:36am

i tried your code it still showing me the same error that the syntax error in update statment

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
Dim sqlupdate As String

Try

Dim i As Integer

sqlupdate = "UPDATE Customer_Master SET Names='" & CheckSqlStr(txtname.Text) & "', Address='" & CheckSqlStr(txtaddr.Text) & "', PhoneNo='" & CheckSqlStr(txtphno.Text) & "' WHERE CustomerID='" & CheckSqlStr(txtcid.Text) & "'"
conn.Open()
cmd = New OleDb.OleDbCommand(sqlupdate, conn)

i = cmd.ExecuteNonQuery()
cmd.Dispose()
MsgBox("Customer updated")
txtcid.Text = ""
txtname.Text = ""
txtaddr.Text = ""
txtphno.Text = ""

Call RefreshDGV()

Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try

End Sub

Filippo Mereu 7-Dec-12 11:01am

Is there a numeric field in the Query?
It doesn't want any apostrophe

Pratik65 8-Dec-12 10:57am

no i have set the properties of every field in the database as text

ACE_ · Answer 2 · 2012-12-08T02:03:00

Try this:

VB

sqlupdate = "UPDATE Customer_Master SET [Names]='" & CheckSqlStr(txtname.Text) & "', [Address]='" & CheckSqlStr(txtaddr.Text) & "', [PhoneNo]='" & CheckSqlStr(txtphno.Text) & "' WHERE [CustomerID]='" & CheckSqlStr(txtcid.Text) & "'" 
conn.Open() 
cmd = New OleDb.OleDbCommand(sqlupdate, conn)
cmd.ExecuteNonQuery()