Syntax Error in Insert Statement

问题:

I'm new to Database connection and when I am having a problem with the cmdInsert.ExecuteNonQuery() line it says there is a syntax error with the INSERT INTO statement and I can't figure out what the problem is:

Imports System.Data
Imports System.Data.OleDb
Public Class txtNotes
    Dim cnnOLEDB As New OleDbConnection
    Dim cmdInsert As New OleDbCommand

    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\CourseworkDB"
    'the name of the database goes in here'

    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 AddFirstName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddFirstName.Click
        If txtFirstName.Text <> "" Then

            MsgBox(cmdInsert.CommandText)
            cmdInsert.CommandText = "INSERT INTO Customer (First Name) VALUES (" & txtFirstName.Text & ", '"
            cmdInsert.CommandType = CommandType.Text
            cmdInsert.Connection = cnnOLEDB
            cmdInsert.ExecuteNonQuery()
        Else
            MsgBox("Enter the required values:" & vbNewLine & "1. First Name")
        End If
        cmdInsert.Dispose()
    End Sub
End Class

回答1:

I Strongly suggest not getting into a routine of building SQL strings by concatinating strings together. You are leaving yourself wide open to SQL-Injection, especially if this is web based. You should build your commands with place-holder parameters in the string, then add the parameters to the command object. Add the parameters in the same sequence as they would appear in the command... such as

cmdInsert.CommandText = "INSERT INTO Customer (FirstName, LastName) VALUES ( @parmFirstName, @parmLastName )"
cmdInsert.Parameters.AddWithValue( "@parmFirstName", txtFirstName.Text );
cmdInsert.Parameters.AddWithValue( "@parmLastName", txtLastName.Text );

If your field names have embedded spaces, different databases work differently, some requires single backtick (the key left of the number 1) around the field. such as 'first name'. Some use square brackets, such as [first name].

回答2:

Try this

"INSERT INTO Customer (First Name) VALUES ('" & txtFirstName.Text & "')"

回答3:

Warning: Bobby is watching you.

cmdInsert.CommandText = _
"INSERT INTO Customer (First Name) VALUES ('" & txtFirstName.Text & "')"