使用vb.net更新Access数据库中的记录时出错 [英] error while updating record in Access database using vb.net

问题描述

我在执行查询时遇到下面给出的Sysntax错误



语法错误=''更新语句中的语法错误''

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

推荐答案

更新声明的语法没问题。

但你必须加倍撇号每个文本框的字符串

例如：

如果在文本框中有字符串 - > ab''cd

你必须在SQL查询中写入字符串 - > ab''''cd



你可以使用这个功能：

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

并在SQL查询中使用它

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) & "'"

试试这个：

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()

这篇关于使用vb.net更新Access数据库中的记录时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！