错误消息:UPDATE语句中的语法错误

时间:2021-12-02 15:38:02

I don't know why when ever I click on the update button, I get an error

我不知道为什么当我点击更新按钮时,我收到一个错误

Syntax error in UPDATE statement

UPDATE语句中的语法错误

I have no idea what's going wrong in my code

我不知道我的代码出了什么问题

This is my code:

这是我的代码:

Public Class Form1
    Private Function vld(ByVal ParamArray ctl() As Object) As Boolean
        For i As Integer = 0 To UBound(ctl)
            If ctl(i).text = "" Then
                ErrorProvider1.SetError(ctl(i), ctl(i).tag)
                Return False
                Exit For
            End If
        Next
        Return True
    End Function
    Dim cn As New OleDbConnection
    Dim cm As New OleDbCommand
    Dim da As OleDbDataAdapter
    Dim dt As New DataTable

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        cn.Close()
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        TxtExamtime.Format = DateTimePickerFormat.Custom
        TxtExamtime.CustomFormat = "hh:MM tt"
        cn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source=C:\psave\New folder\save.xls;Extended Properties=Excel 8.0;"
        cn.Open()
        FillDataGridView("select ID, Family Name, Given Name, Gender, DOB, Exam Date, Exam Time, Street Name, House Nr, PLZ, City from [edit$]")

    End Sub

    Private Sub FillDataGridView(ByVal Query As String)
        da = New OleDbDataAdapter(Query, cn)
        dt.Clear()
        da.Fill(dt)
        With DataGridView1
            .DataSource = dt
            .Columns(0).HeaderText = "ID"
            .Columns(1).HeaderText = "Family Name"
            .Columns(2).HeaderText = "Given Name"
            .Columns(3).HeaderText = "Gender"
            .Columns(4).HeaderText = "DOB"
            .Columns(5).HeaderText = "Exam Date"
            .Columns(6).HeaderText = "Exam Time"
            .Columns(7).HeaderText = "Street Name"
            .Columns(8).HeaderText = "House Nr"
            .Columns(9).HeaderText = "PLZ"
            .Columns(10).HeaderText = "City"
            .Columns(10).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        End With
    End Sub

    Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
        Try
            FillDataGridView("select * from [edit$] where ID='" & TxtId.Text & "'")
            TxtFamilyname.Text = dt.Rows(0).Item(1)
            TxtGivenname.Text = dt.Rows(0).Item(2)
            TxtGender.Text = dt.Rows(0).Item(3)
            TxtDob.Text = dt.Rows(0).Item(4)
            TxtExamdate.Text = dt.Rows(0).Item(5)
            TxtExamtime.Text = dt.Rows(0).Item(6)
            TxtStreet.Text = dt.Rows(0).Item(7)
            TxtHouse.Text = dt.Rows(0).Item(8)
            TxtPlz.Text = dt.Rows(0).Item(9)
            TxtCity.Text = dt.Rows(0).Item(10)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
        End Try
    End Sub

    Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
        If vld(TxtId, TxtFamilyname, TxtGivenname, TxtGender, TxtDob, TxtExamdate, TxtExamtime, TxtStreet, TxtHouse, TxtPlz, TxtCity) = False Then
            Exit Sub
        Else

        End If

        Try
            With cm
                .Connection = cn
                .CommandText = "insert into [edit$]values('" & TxtId.Text & "','" & TxtFamilyname.Text & "','" & TxtGivenname.Text & "','" & TxtGender.Text & "','" & TxtDob.Text & "','" & TxtExamdate.Text & "','" & TxtExamtime.Text & "','" & TxtStreet.Text & "','" & TxtHouse.Text & "','" & TxtPlz.Text & "','" & TxtCity.Text & "' )"
                .ExecuteNonQuery()
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
            Return
        End Try
        MsgBox("succefully Saved!", MsgBoxStyle.Information, Text)
    End Sub

    Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles Btnupdate.Click
        Try
            With cm
                .Connection = cn
                .CommandText = "Update from [edit$] set [Family Name] = '" & TxtFamilyname.Text & "' where ID ='" & TxtId.Text & "' and Given Name = '" & TxtGivenname.Text & "' and Gender = '" & TxtGender.Text & "'and DOB = '" & TxtDob.Text & "'and Exam Date'" & TxtExamdate.Text & "'and Exam Time = '" & TxtExamtime.Text & "'and Street Name = '" & TxtStreet.Text & "'and House Nr = '" & TxtHouse.Text & "'and PLZ = '" & TxtPlz.Text & "'and CITY = '" & TxtCity.Text & "'"
                .ExecuteNonQuery()
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, Text)
            Return
        End Try
        MsgBox("Succesfully updated!", MsgBoxStyle.Information, Text)
    End Sub

    Private Sub BtnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click
        Close()
    End Sub

    Private Sub BtnClear_Click(sender As Object, e As EventArgs) Handles BtnClear.Click
        TxtId.Clear()
        TxtFamilyname.Clear()
        TxtGivenname.Clear()
        TxtStreet.Clear()
        TxtHouse.Clear()
        TxtPlz.Clear()
        TxtCity.Clear()
        'To see all the data in DataGridView
        FillDataGridView("select * from[edit$]")

    End Sub

    Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click
        Try
            With cm
                .Connection = cn
                .CommandText = "Delete from [edit$] where [Family Name] = '" & TxtFamilyname.Text & "' and ID ='" & TxtId.Text & "' and [Given Name] = '" & TxtGivenname.Text & "'and Gender = '" & TxtGender.Text & "'and DOB = '" & TxtDob.Text & "'and [Exam Date]'" & TxtExamdate.Text & "'and [Exam Time] = '" & TxtExamtime.Text & "'and [Street Name] = '" & TxtStreet.Text & "'and [House Nr] = '" & TxtHouse.Text & "'and PLZ = '" & TxtPlz.Text & "'and CITY = '" & TxtCity.Text & "'"
                .ExecuteNonQuery()
            End With
            MsgBox("Succesfully Deleted!", MsgBoxStyle.Information, Text)
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, Text)

        End Try
    End Sub
End Class

3 个解决方案

#1


An Update statement does not have a "From" in it, so it should start with...

Update语句中没有“From”,因此它应该以...开头

    Update [Edit$]

There's a couple of other things wrong too. If the ID is a number then it probably doesn't need bounding single quotes, through they won't stop it either...

还有其他一些错误。如果ID是一个数字,那么它可能不需要绑定单引号,通过它们也不会阻止它...

    Where ID = " & txtId.text & "

Columns names that have embedded white space need bounding brackets...

嵌入空格的列名称需要使用边界括号...

    and [Given Name] = '" & txtGivenName.text & "'

Finally, this statement is wide open to SQL Injection, where someone could do serious damage to your table by entering SQL into one of your text boxes. Please consider using parameters instead.

最后,这个语句对SQL注入是开放的,有些人可以通过在你的一个文本框中输入SQL来严重损坏你的表。请考虑使用参数。

You should also consider using Microsoft.ACE.OLEDB.12.0 as the one you are using is quite old now.

您还应该考虑使用Microsoft.ACE.OLEDB.12.0作为您正在使用的那个现在很老了。

You should probably have other parameters in your extended properties if you want to use Excel as a database, in particular you will need HDR=Yes...

如果要将Excel用作数据库,则应该在扩展属性中有其他参数,特别是需要HDR = Yes ...

    Extended Properties=""Excel 8.0;HDR=Yes"""

This tells OLEDB that the first line of your sheet contains the column names, otherwise it will use F1...Fn (I think but it may be C1...Cn)

这告诉OLEDB你的工作表的第一行包含列名,否则它将使用F1 ... Fn(我想它可能是C1 ... Cn)

#2


Beside some other issues with your code (e.g. you should almost always use parameterized queries), Update from [edit$] set... is wrong.

除了您的代码的其他一些问题(例如,您几乎应该总是使用参数化查询),从[edit $] set ...更新是错误的。

Just use Update [edit$] set....

只需使用Update [edit $]设置....

#3


Your Syntax for Update statment is clearly not correct :

您的更新语法语法显然不正确:

please find below code :

请找到以下代码:

 Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles Btnupdate.Click
        Try
            With cm
                .Connection = cn
                .CommandText = "Update [edit$] set [Family Name] = '" & TxtFamilyname.Text & "' where ID ='" & TxtId.Text & "' and [Given Name] = '" & TxtGivenname.Text & "' and Gender = '" & TxtGender.Text & "'and DOB = '" & TxtDob.Text & "'and [Exam Date]='" & TxtExamdate.Text & "'and [Exam Time]= '" & TxtExamtime.Text & "'and [Street Name] = '" & TxtStreet.Text & "'and [House Nr]= '" & TxtHouse.Text & "'and PLZ = '" & TxtPlz.Text & "'and CITY = '" & TxtCity.Text & "'"
                .ExecuteNonQuery()
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, Text)
            Return
        End Try
        MsgBox("Succesfully updated!", MsgBoxStyle.Information, Text)
    End Sub

Place all column names inside [ ] other wise the query will be interpreted wrongly because it might ignore the rest of the query as soon as it encounters a white space

将所有列名放在[]中,查询将被错误地解释,因为它可能会在遇到空格时忽略查询的其余部分

You have done it correctly for family name but certainly ignored other column names which have a space in between them

你已经正确地为姓氏做了它,但肯定忽略了在它们之间有空格的其他列名

#1


An Update statement does not have a "From" in it, so it should start with...

Update语句中没有“From”,因此它应该以...开头

    Update [Edit$]

There's a couple of other things wrong too. If the ID is a number then it probably doesn't need bounding single quotes, through they won't stop it either...

还有其他一些错误。如果ID是一个数字,那么它可能不需要绑定单引号,通过它们也不会阻止它...

    Where ID = " & txtId.text & "

Columns names that have embedded white space need bounding brackets...

嵌入空格的列名称需要使用边界括号...

    and [Given Name] = '" & txtGivenName.text & "'

Finally, this statement is wide open to SQL Injection, where someone could do serious damage to your table by entering SQL into one of your text boxes. Please consider using parameters instead.

最后,这个语句对SQL注入是开放的,有些人可以通过在你的一个文本框中输入SQL来严重损坏你的表。请考虑使用参数。

You should also consider using Microsoft.ACE.OLEDB.12.0 as the one you are using is quite old now.

您还应该考虑使用Microsoft.ACE.OLEDB.12.0作为您正在使用的那个现在很老了。

You should probably have other parameters in your extended properties if you want to use Excel as a database, in particular you will need HDR=Yes...

如果要将Excel用作数据库,则应该在扩展属性中有其他参数,特别是需要HDR = Yes ...

    Extended Properties=""Excel 8.0;HDR=Yes"""

This tells OLEDB that the first line of your sheet contains the column names, otherwise it will use F1...Fn (I think but it may be C1...Cn)

这告诉OLEDB你的工作表的第一行包含列名,否则它将使用F1 ... Fn(我想它可能是C1 ... Cn)

#2


Beside some other issues with your code (e.g. you should almost always use parameterized queries), Update from [edit$] set... is wrong.

除了您的代码的其他一些问题(例如,您几乎应该总是使用参数化查询),从[edit $] set ...更新是错误的。

Just use Update [edit$] set....

只需使用Update [edit $]设置....

#3


Your Syntax for Update statment is clearly not correct :

您的更新语法语法显然不正确:

please find below code :

请找到以下代码:

 Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles Btnupdate.Click
        Try
            With cm
                .Connection = cn
                .CommandText = "Update [edit$] set [Family Name] = '" & TxtFamilyname.Text & "' where ID ='" & TxtId.Text & "' and [Given Name] = '" & TxtGivenname.Text & "' and Gender = '" & TxtGender.Text & "'and DOB = '" & TxtDob.Text & "'and [Exam Date]='" & TxtExamdate.Text & "'and [Exam Time]= '" & TxtExamtime.Text & "'and [Street Name] = '" & TxtStreet.Text & "'and [House Nr]= '" & TxtHouse.Text & "'and PLZ = '" & TxtPlz.Text & "'and CITY = '" & TxtCity.Text & "'"
                .ExecuteNonQuery()
            End With
            FillDataGridView("select * from [edit$]")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, Text)
            Return
        End Try
        MsgBox("Succesfully updated!", MsgBoxStyle.Information, Text)
    End Sub

Place all column names inside [ ] other wise the query will be interpreted wrongly because it might ignore the rest of the query as soon as it encounters a white space

将所有列名放在[]中,查询将被错误地解释,因为它可能会在遇到空格时忽略查询的其余部分

You have done it correctly for family name but certainly ignored other column names which have a space in between them

你已经正确地为姓氏做了它,但肯定忽略了在它们之间有空格的其他列名