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
你已经正确地为姓氏做了它,但肯定忽略了在它们之间有空格的其他列名