在update语句中获取语法错误 [英] getting syntax error in update statement

问题描述

嗨大家好



所以目前我忙于个人项目，但是我的更新声明中出现错误，我有2个表格

form1是客户输入其个人详细信息的地方（插入声明）

form2是客户输入其凭证详细信息的地方，但凭证详细信息与个人详细信息位于同一个表格中>
这是我的form2代码

Hi Guys

so currently i'm busy with a personal project, however i am getting errors in my update statement, i got 2 forms
form1 is where the customer enters his personal details(insert statement)
form2 is where the customer enters his credential details however the credential details are on the same table as the personal details
here is my form2 code

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MAC\Documents\132.mdb")

    Dim cmd As New OleDbCommand
    con.Open()
    cmd.Connection = con

    cmd.CommandText = "UPDATE netcash " & _
        " SET Account Holder Name =" & Me.txtaccHName.Text & _
        ", Bank Name='" & Me.txtBName.Text & "'" & _
        ",Account Type='" & Me.txtAccType.Text & "'" & _
        ",Account Number='" & Me.txtAccNo.Text & "'" & _
        ",Branch Code='" & Me.txtBranchCode.Text & "'" & _
        ",Gross Salary='" & Me.txtGrossS.Text & "'" & _
        ",Nett Salary='" & Me.txtNettS.Text & "'" & _
        ",Spouse Gross='" & Me.txtSpouseG.Text & "'" & _
        ",Spouse Nett='" & Me.txtSpouseN.Text & "'" & _
        ",Total Income='" & Me.txtTIncome.Text & "'" & _
        ",Funeral Cover='" & Me.cmbfuneral.Text & "'" & _
        ",Medical Aid='" & Me.cmbmedical.Text & "'" & _
        ",Life Insurance='" & Me.cmbinsurance.Text & "'" & _
        ",Groceries='" & Me.txtGroceries.Text & "'" & _
        ",Rent/Bond='" & Me.txtRent.Text & "'" & _
        ",School fees='" & Me.txtSfees.Text & "'" & _
        ",Transport='" & Me.txtTransport.Text & "'" & _
        ",Clothing='" & Me.txtClothing.Text & "'" & _
        ",Domestic Worker='" & Me.txtDomesticW.Text & "'" & _
        ",Water and Electricity='" & Me.txtWandE.Text & "'" & _
        ",Rates='" & Me.txtRates.Text & "'" & _
        ",Landline Phone='" & Me.txtLandline.Text & "'" & _
        ",Cell phone='" & Me.txtCellphone.Text & "'" & _
        ",Levies='" & Me.txtLevies.Text & "'" & _
        ",Bank Charges='" & Me.txtBCharges.Text & "'" & _
        ",Medical Expenses='" & Me.txtMedical.Text & "'" & _
        ",Maintenance='" & Me.txtMaintenance.Text & "'" & _
        ",Total Expenses='" & Me.txtTexpenses.Text & "'" & _
        " WHERE ID Number='" & Me.txtvalueid.Tag
   
    cmd.ExecuteNonQuery()
   
    MsgBox("Record Updated Successfully")
End Sub

推荐答案

作为评论和解决方案的后续内容1尝试这样的事情（ NB未经测试）

As a follow-on from the comments and solution 1 try something like this (NB untested)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MAC\Documents\132.mdb")

    Dim cmd As New OleDbCommand
    con.Open()
    cmd.Connection = con

    Dim sql As StringBuilder = New StringBuilder("UPDATE netcash SET ")
    sql.Append("[Account Holder Name] = ?, Bank Name= ?, ")
    sql.Append("[Account Type] = ?, [Account Number] = ?,")
    sql.Append("[Branch Code]=?,[Gross Salary]=?,")
    sql.Append("[Nett Salary]=?,[Spouse Gross]=?,")
    sql.Append("[Spouse Nett]=?,[Total Income]=?,")
    sql.Append("[Funeral Cover]=?,[Medical Aid]=?,")
    sql.Append("[Life Insurance]=?,[Groceries]=?,")
    sql.Append("[Rent/Bond]=?,[School fees]=?,")
    sql.Append("[Transport]=?,[Clothing]=?,")
    sql.Append("[Domestic Worker]=?,[Water and Electricity]=?,")
    sql.Append("[Rates]=?,[Landline Phone]=?,")
    sql.Append("[Cell phone]=?,[Levies]=?,")
    sql.Append("[Bank Charges]=?,[Medical Expenses]=?,")
    sql.Append("[Maintenance]=?,[Total Expenses]=?")
    sql.Append(" WHERE [ID Number]=?")

    cmd.CommandText = sql.ToString()

    'NB with unnamed parameters the order below is fundamental
    cmd.Parameters.AddWithValue("AHName", txtaccHName.Text)
    cmd.Parameters.AddWithValue("BankName", txtBName.Text)
    cmd.Parameters.AddWithValue("AccType", txtAccType.Text)
    cmd.Parameters.AddWithValue("AccNumber", txtAccNo.Text)
    cmd.Parameters.AddWithValue("Branch", txtBranchCode.Text)
    cmd.Parameters.AddWithValue("GrossSalary", txtGrossS.Text)
    cmd.Parameters.AddWithValue("NettSalary", txtNettS.Text)
    cmd.Parameters.AddWithValue("SpouseGross", txtSpouseG.Text)
    cmd.Parameters.AddWithValue("SpouseNett", txtSpouseN.Text)
    cmd.Parameters.AddWithValue("TotalIncome", txtTIncome.Text)
    cmd.Parameters.AddWithValue("FuneralCover", cmbfuneral.Text)
    cmd.Parameters.AddWithValue("MedicalAid", cmbmedical.Text)
    cmd.Parameters.AddWithValue("LifeInsurance", cmbinsurance.Text)
    cmd.Parameters.AddWithValue("Groceries", txtGroceries.Text)
    cmd.Parameters.AddWithValue("RentBond", txtRent.Text)
    cmd.Parameters.AddWithValue("Schoolfees", txtSfees.Text)
    cmd.Parameters.AddWithValue("Transport", txtTransport.Text)
    cmd.Parameters.AddWithValue("Clothing", txtClothing.Text)
    cmd.Parameters.AddWithValue("DomesticWorker", txtDomesticW.Text)
    cmd.Parameters.AddWithValue("WaterElect", txtWandE.Text)
    cmd.Parameters.AddWithValue("Rates", txtRates.Text)
    cmd.Parameters.AddWithValue("Landline", txtLandline.Text)
    cmd.Parameters.AddWithValue("Cellphone", txtCellphone.Text)
    cmd.Parameters.AddWithValue("Levies", txtLevies.Text)
    cmd.Parameters.AddWithValue("BankCharges", txtBCharges.Text)
    cmd.Parameters.AddWithValue("Medical", txtMedical.Text)
    cmd.Parameters.AddWithValue("Maintenance", txtMaintenance.Text)
    cmd.Parameters.AddWithValue("TotalExpenses", txtTexpenses.Text)
    cmd.Parameters.AddWithValue("IDNumber", txtvalueid.Tag)

    If cmd.ExecuteNonQuery() > 0 Then
        MsgBox("Record Updated Successfully")
    End If

End Sub

哦亲爱的...

不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



并且还要更改列名，或用'['和']'对包围它们：

Any带有空格的列名将导致语法错误，除非使用括号进行转义。

Oh dear...
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

And also change your column names, or surround them with '[' and ']' pairs:
Any column name with spaces in will cause a syntax error unless escaped with brackets.

cmd.CommandText = "UPDATE netcash " & _
    " SET Account Holder Name =" & Me.txtaccHName.Text & _

需要：

Needs to be:

cmd.CommandText = "UPDATE netcash " & _
    " SET [Account Holder Name] =" & Me.txtaccHName.Text & _

但严重的是，连接字符串会给您带来麻烦，并且会导致数据库被删除。

But seriously, concatenating strings is going to cause you trouble, and risk your DB being deleted.

这篇关于在update语句中获取语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！