Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 23: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 at 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 at 7-Dec-12 11:01am
   
Is there a numeric field in the Query?
It doesn't want any apostrophe
Pratik65 at 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
0 OriginalGriff 484
1 Peter Leow 443
2 Sergey Alexandrovich Kryukov 288
3 Kornfeld Eliyahu Peter 270
4 Maciej Los 230


Advertise | Privacy | Mobile
Web01 | 2.8.150301.1 | Last Updated 8 Dec 2012
Copyright © CodeProject, 1999-2015
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