我在insert语句中遇到语法错误 [英] i am getting a syntax error in insert statement

问题描述

Imports System.Data.OleD

Public Class form2

    Dim cnnOLEDB As New OleDbConnection

    Dim cmdOLEDB As New OleDbCommand

    Dim cmdInsert As New OleDbCommand

    Dim cmdUpdate As New OleDbCommand

    Dim cmdDelete As New OleDbCommand

    Dim strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\sector.accdb"

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        cnnOLEDB.ConnectionString = strConnectionString

        cnnOLEDB.Open()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text <> "" And TextBox2.Text <> "" Then

            cmdInsert.CommandText = "INSERT INTO sector (area,date&time,curier_item,party) VALUES (" & TextBox1.Text & ", '" & DateTimePicker1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"

            'MsgBox(cmdInsert.CommandText)

            cmdInsert.CommandType = CommandType.Text

            cmdInsert.Connection = cnnOLEDB

            cmdInsert.ExecuteNonQuery()
            TextBox1.Text = ""



            MsgBox(TextBox1.Text = "Record inserted.")


        Else

            MsgBox("Enter the required values:"" ")

        End If

        cmdInsert.Dispose()

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        If TextBox1.Text <> "" And TextBox2.Text <> "" Then

            cmdUpdate.CommandText = "UPDATE sector WHERE  area  = " & TextBox1.Text & " "

            'MsgBox(cmdUpdate.CommandText)

            cmdUpdate.CommandType = CommandType.Text

            cmdUpdate.Connection = cnnOLEDB

            cmdUpdate.ExecuteNonQuery()

            MsgBox(TextBox1.Text = "Record updated.")

            TextBox1.Text = ""

        Else

            MsgBox("Enter the required values:")


        End If

        cmdUpdate.Dispose()
    End Sub
End Class

推荐答案

尝试使用参数化查询。

不要使用&如果可能，在表列名中。

使用尝试捕获阻止正确的异常处理。

参见下面的参数化查询示例：

插入查询：

Try to Use Parameterized Queries.
Do Not Use Characters like "&" In Table Column Name if possible.
Use Try Catch Block for Proper Exception Handling.
See below example of parameterized query :
Insert Query :

cmdInsert.CommandText = "INSERT INTO YourTablename (Column1, Column2, Column3) VALUES (@Column1, @Column2, @Column3)"
cmdInsert.CommandType = CommandType.Text
cmdInsert.Parameters.AddWithValue("@Column1", "Value")
cmdInsert.Parameters.AddWithValue("@Column2", "Value")
cmdInsert.Parameters.AddWithValue("@Column3", "Value")
cmdInsert.Connection = cnnOLEDB
Try
     cmdInsert.ExecuteNonQuery()
Catch ex As Exception
     MessageBox.Show(ex.Message)
End Try

更新查询：

Update Query :

cmdUpdate.CommandText = "UPDATE  YourTablename SET CoumnNameToUpdate = NewValue  WHERE  Column1  = @Column1"
cmdUpdate.CommandType = CommandType.Text
cmdUpdate.Parameters.AddWithValue("@Column1", "Value")
cmdUpdate.Connection = cnnOLEDB
Try
     cmdUpdate.ExecuteNonQuery()
Catch ex As Exception
     MessageBox.Show(ex.Message)
End Try

我希望它会对你有所帮助。 ：）

I hope it will help you. :)

试试这个

Try this

cmdInsert.CommandText = "INSERT INTO sector (area,date&time,curier_item,party) VALUES ('" & TextBox1.Text & "', '" & DateTimePicker1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"

您在更新查询中缺少设置值......

you are missing Set Value in Update query......

cmdUpdate.CommandText = "UPDATE sector WHERE  area  = " & TextBox1.Text & " "

您的查询将是。 ..

your query will be...

cmdUpdate.CommandText = "UPDATE TableName SET ColumnName = NewValue WHERE  area  = '" & TextBox1.Text & "' "

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