vb.net - VB.NET 代码 MS Acess 作为数据库，成功编译后我的代码中出现错误。INSERT INTO 命令中的语法错误

Question

我在 INSERT INTO 命令中遇到错误。

代码：

Imports System.Data.OleDb
Imports System.IO
Public Class newemployee

    Dim read As String
    Dim datafile As String
    Dim connstring As String
    Dim cmd As New OleDbCommand
    Public da As New OleDbDataAdapter
    Dim str As String
    Public ds As New DataSet
    Public ds1 As New DataSet
    Public ds2 As New DataSet

    Dim myconnection As OleDbConnection = New OleDbConnection
    Dim er, pho As Integer
    Dim Photos1() As Byte

    Private Sub newemployee_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        read = "provider=microsoft.ace.oledb.12.0;data source="
        datafile = "C:\Users\DELL\source\repos\HRIS SYSTEM\loginformdatabase\BLUESTREAM.accdb"
        connstring = read & datafile
        myconnection.ConnectionString = connstring
        ds.Clear()
        pho = 0
        DateTimePicker1.Value = DateTime.Now

        If myconnection.State = ConnectionState.Open Then
            myconnection.Close()
        End If

        myconnection.Open()
        er = 0
        'cn.Open()
        str = "select * from bsemployee"
        cmd = New OleDbCommand(str, myconnection)
        da.SelectCommand = cmd
        da.Fill(ds, "bsemployee")

    End Sub

    'INSERT
    Private Sub Save_Click(sender As Object, e As EventArgs) Handles Button3.Click
        ds.Clear()
        str = "select * from bsemployee"
        cmd = New OleDbCommand(str, myconnection)
        da.SelectCommand = cmd
        da.Fill(ds, "bsemployee")
        Dim SLNO As Integer
        Dim ph As String
        SLNO = ds.Tables("bsemployee").Rows.Count + 1
        If SLNO >= 0 Then
            ph = SLNO
        Else
            ph = 0
        End If
        If er = 0 Then
            Try
                cmd.Connection = myconnection
                cmd.CommandText = "INSERT INTO bsemployee(SL NO,EMP SSN,PID,BSPS EMP ID,SIIM ID,FIRST NAME,LAST NAME,NAME,FATHER/HUSBAND NAME,DEPARTMENT,GENDER,DOB,AGE,CURRENT ADDRESS,PERMANENT ADDRESS,EMAIL,COUNTRY,STATE,PHONE NUMBER,PAN,ADHAR NUMER,TE DOJ,
BSPS DOJ,DIVISION,DESIGNATION,TE EMAIL,BSPS EMAIL,BSPS DESIGNATION,COST CENTER,SALARY GRADE,SHIFT,BANK ACCOUNT NO,ACCOUNT CODE,BSPS PF NO,1ST LEVEL SUPERVISOR,2ND LEVEL SUPERVISOR,MANGER,OFFER LETTER ISSUE DATE,NOTICE PERIOD,BOND SIGN,NO OF YEARS,REPORTING,
BG VERIFICATION STATUS,DATE OF RESIGNATION,BASIC SALARY,HOUSE RENT ALLOWANCE,PROVIDENT FUND(Employer distribution),LEAVE TRAVEL ALLOWANCE,MEDICAL ALLOWANCE,SPECIAL ALLOWANCE,STAT BONUS,ESI,MONTHLY GAURENTED CASH,ANNUAL BASIC SALARY,ANNUAL HOUSE RENT ALLOWANCE,
ANNUAL PROVIDENT FUND,ANNUAL LEAVE TRAVEL ALLOWANCE,ANNUAL MEDICAL ALLOWANCE,ANNUAL SPECIAL ALLOWANCE,ANNUAL STAT ALLOWANCE,ANNUAL ESI,ANNUAL GARUNTEED CASH,FOOD COUPON PER ANNUM,GRATIUTY PER ANNUM,INSURANCE PREMIUM PER ANNUM,ANNUAL BONUS,TOTAL COST BENIFIT,GROSS COMPENSATION)
VALUES ('" & TextBox1.Text & "', '" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & TextBox7.Text & "''" & TextBox8.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "','" & ComboBox1.Text & "','" & DateTimePicker1.Text & "',
'" & TextBox11.Text & "','" & TextBox12.Text & "','" & TextBox13.Text & "','" & TextBox14.Text & "','" & ComboBox2.Text & "','" & TextBox15.Text & "','" & TextBox16.Text & "','" & TextBox17.Text & "','" & TextBox18.Text & "','" & DateTimePicker2.Text & "','" & DateTimePicker3.Text & "','" & TextBox20.Text & "',
'" & TextBox21.Text & "','" & TextBox22.Text & "','" & TextBox23.Text & "','" & TextBox24.Text & "','" & ComboBox3.Text & "','" & TextBox26.Text & "','" & TextBox26.Text & "','" & ComboBox4.Text & "','" & TextBox25.Text & "','" & TextBox27.Text & "','" & ComboBox13.Text & "','" & ComboBox5.Text & "','" & ComboBox6.Text & "','" & ComboBox7.Text & "',
'" & DateTimePicker4.Text & "','" & ComboBox8.Text & "','" & ComboBox9.Text & "','" & ComboBox10.Text & "','" & ComboBox11.Text & "','" & ComboBox12.Text & "','" & DateTimePicker5.Text & "','" & TextBox28.Text & "','" & TextBox29.Text & "','" & TextBox30.Text & "','" & TextBox31.Text & "','" & TextBox32.Text & "','" & TextBox33.Text & "',
'" & TextBox34.Text & "','" & TextBox35.Text & "','" & TextBox36.Text & "','" & TextBox37.Text & "','" & TextBox38.Text & "','" & TextBox39.Text & "','" & TextBox40.Text & "','" & TextBox41.Text & "','" & TextBox42.Text & "','" & TextBox43.Text & "','" & TextBox44.Text & "','" & TextBox45.Text & "','" & TextBox46.Text & "','" & TextBox47.Text & "',
'" & TextBox48.Text & "','" & TextBox49.Text & "','" & TextBox50.Text & "','" & TextBox51.Text & "')"

                cmd.ExecuteNonQuery() 'if command is executed'
                If pho = 1 Then
                    'Photo Saving
                    cmd = New OleDbCommand
                    cmd.CommandText = " INSERT INTO Photos VALUE (@PhID, @EID, @EName, @EMPImage) "
                    cmd.Parameters.AddWithValue("@PhID", ph)
                    cmd.Parameters.AddWithValue("@EID", TextBox1.Text)
                    cmd.Parameters.AddWithValue("@EName", TextBox8.Text)
                    Photos1 = File.ReadAllBytes(OpenFileDialog1.FileName)
                    cmd.Parameters.AddWithValue("@EMPImage", Photos1)
                    cmd.Connection = myconnection
                    cmd.ExecuteNonQuery()
                End If
                'MsgBox("New Class Added.")
                Dim result As Integer = MessageBox.Show("New Employee Added. Want To Add Another One.", "Added", MessageBoxButtons.YesNo)
                If result = DialogResult.No Then
                    Me.Close()
                ElseIf result = DialogResult.Yes Then
                    ds.Clear()
                    TextBox1.Clear()
                    TextBox2.Clear()
                    TextBox3.Clear()
                    TextBox4.Clear()
                    TextBox5.Clear()
                    TextBox6.Clear()
                    TextBox7.Clear()
                    TextBox8.Clear()
                    TextBox9.Clear()
                    TextBox10.Clear()
                    TextBox11.Clear()
                    TextBox12.Clear()
                    TextBox13.Clear()
                    TextBox14.Clear()
                    TextBox15.Clear()
                    TextBox16.Clear()
                    TextBox17.Clear()
                    TextBox18.Clear()
                    TextBox20.Clear()
                    TextBox21.Clear()
                    TextBox22.Clear()
                    TextBox23.Clear()
                    TextBox24.Clear()
                    TextBox25.Clear()
                    TextBox26.Clear()
                    TextBox27.Clear()
                    TextBox28.Clear()
                    TextBox29.Clear()
                    TextBox30.Clear()
                    TextBox31.Clear()
                    TextBox32.Clear()
                    TextBox33.Clear()
                    TextBox34.Clear()
                    TextBox35.Clear()
                    TextBox36.Clear()
                    TextBox37.Clear()
                    TextBox38.Clear()
                    TextBox39.Clear()
                    TextBox40.Clear()
                    TextBox41.Clear()
                    TextBox42.Clear()
                    TextBox43.Clear()
                    TextBox44.Clear()
                    TextBox45.Clear()
                    TextBox46.Clear()
                    TextBox47.Clear()
                    TextBox48.Clear()
                    TextBox49.Clear()
                    TextBox50.Clear()
                    TextBox51.Clear()
                    ComboBox1.ResetText()
                    ComboBox2.ResetText()
                    ComboBox3.ResetText()
                    ComboBox4.ResetText()
                    ComboBox5.ResetText()
                    ComboBox6.ResetText()
                    ComboBox7.ResetText()
                    ComboBox8.ResetText()
                    ComboBox9.ResetText()
                    ComboBox9.ResetText()
                    ComboBox10.ResetText()
                    ComboBox11.ResetText()
                    ComboBox12.ResetText()
                    ComboBox13.ResetText()
                    DateTimePicker1.ResetText()
                    DateTimePicker2.ResetText()
                    DateTimePicker3.ResetText()
                    DateTimePicker4.ResetText()
                    DateTimePicker5.ResetText()
                    str = "select * from bsemployee"
                    cmd = New OleDbCommand(str, myconnection)
                    da.SelectCommand = cmd
                    da.Fill(ds, "bsemployee")
                    SLNO = ds.Tables("bsemployee").Rows.Count + 1
                    If SLNO >= 0 Then
                        TextBox1.Text = SLNO
                    Else
                        TextBox1.Text = 0
                    End If
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            'insert close
        End If
        'myconnection close
    End Sub

标签： vb.net

Answer 1

我首先要看的是任何带有撇号的文本 - 例如像 O'Rourke 这样的姓氏 - 这会弄乱你的命令文本。此外，日期格式是否与数据库日期格式兼容 - 即像 25/02/2019 这样的英国格式日期与以美国为中心的数据库不太一样。任何数字格式的类似情况 - 文本框中可能有货币符号吗？