Click here to Skip to main content
12,296,188 members (62,249 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB VB.NET
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

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:


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

sqlupdate = "UPDATE Customer_Master SET Names='" & CheckSqlStr(txtname.Text) & "', Address='" & CheckSqlStr(txtaddr.Text) & "', PhoneNo='" & CheckSqlStr(txtphno.Text) & "' WHERE CustomerID='" & CheckSqlStr(txtcid.Text) & "'"
  Permalink  
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this:
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()
  Permalink  
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 8 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100