INSERT INTO语句VB中使用Access的语法错误

I've been struggling with this for a while now and just don't understand what is wrong. I'm doing this for my college coursework and have little to no experience in coding. All I am trying to do here is insert Values from my windows form into my Access database.

我一直在努力解决这个问题,只是不明白出了什么问题。我正在为我的大学课程做这件事,几乎没有编码经验。我在这里尝试的只是将我的Windows窗体中的值插入到我的Access数据库中。

It keeps coming up with the same error which is

它不断出现同样的错误

"Syntax error in INSERT INTO statement"

“INSERT INTO语句中的语法错误”

Dim sqlconn As New OleDb.OleDbConnection
    Dim sqlquery As New OleDb.OleDbCommand
    Dim connString As String
    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Computer science\Coursework\Database.accdb"
    sqlconn.ConnectionString = connString
    sqlquery.Connection = sqlconn
    sqlconn.Open()
    Dim Query As String = "INSERT INTO Client_form (MemberNumber, Forename, Surname, Age, SkillLevel, Session) VALUES (" & NewMem.ToString & ", '" & TextBox1.Text & "', '" & TextBox2.Text & "', " & TextBox3.Text & ", '" & TextBox5.Text & "', '" & TextBox4.Text & "');"
    'sqlquery.CommandText = "INSERT INTO Client form(MemberNumber, Firstname, Surname, Age, Skill level, Session)VALUES(@MemberNumber, @Firstname, @Surname, @Age, @Skill level, @Session)"
    'sqlquery.Parameters.AddWithValue("@MemberNumber", NewMem)
    'sqlquery.Parameters.AddWithValue("@Firstname", TextBox1.Text)
    'sqlquery.Parameters.AddWithValue("@Surname", TextBox2.Text)
    'sqlquery.Parameters.AddWithValue("@Age", Convert.ToInt16(TextBox3.Text))
    'sqlquery.Parameters.AddWithValue("@Skill level", TextBox5.Text)
    'sqlquery.Parameters.AddWithValue("@Session", TextBox4.Text)
    'sqlquery.CommandText = "INSERT INTO Client_form (MemberNumber, Firstname, Surname, Age, Skill level, Session) VALUES (345, t, e, 5, 7, am);"
    sqlquery.CommandText = Query
    MsgBox(sqlquery.CommandText.ToString)
    sqlquery.ExecuteNonQuery()
    sqlconn.Close()

Any kind of guidance will be greatly appreciated, Thanks!

任何形式的指导将不胜感激,谢谢!

2 个解决方案

#1

try this ..

试试这个 ..

Dim sqlconn As New OleDb.OleDbConnection
Dim sqlquery As New OleDb.OleDbCommand
Dim connString As String
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Computer science\Coursework\Database.accdb"
sqlconn.ConnectionString = connString
sqlquery.Connection = sqlconn
sqlconn.Open()
Dim Query As String = "INSERT INTO Client_form([MemberNumber], [Firstname], [Surname], [Age], [Skilllevel], [Session])VALUES(@MemberNumber, @Firstname, @Surname, @Age, @Skill level, @Session)"
'sqlquery.CommandText = "INSERT INTO Client form([MemberNumber], [Firstname], [Surname], [Age], [Skilllevel], [Session])VALUES(@MemberNumber, @Firstname, @Surname, @Age, @Skill level, @Session)"
sqlquery.Parameters.AddWithValue("@MemberNumber", NewMem.Text)
sqlquery.Parameters.AddWithValue("@Firstname", TextBox1.Text)
sqlquery.Parameters.AddWithValue("@Surname", TextBox2.Text)
sqlquery.Parameters.AddWithValue("@Age",TextBox3.Text)
sqlquery.Parameters.AddWithValue("@Skilllevel", TextBox5.Text)
sqlquery.Parameters.AddWithValue("@Session", TextBox4.Text)
sqlquery.CommandText = Query
MsgBox(sqlquery.CommandText.ToString)
sqlquery.ExecuteNonQuery()
sqlconn.Close()

Also remove the space between Skill(space)level. Change also the datatype of age into shortext. I hope it will works.

同时删除技能(空格)级别之间的空间。将age的数据类型也改为shortext。我希望它会奏效。

#2

I removed redundant lines, also note to parse the values with their proper type. you might want to try this

我删除了冗余行,还要注意用正确的类型解析值。你可能想试试这个

    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Computer science\Coursework\Database.accdb"
    Dim cmd As New OleDbCommand("INSERT INTO Client form(MemberNumber, Firstname, Surname, Age, SkillLevel, Session)VALUES(@MemberNumber, @Firstname, @Surname, @Age, @SkillLevel, @Session)")

    cmd.Parameters.AddWithValue("@MemberNumber", NewMem.ToString)
    cmd.Parameters.AddWithValue("@Firstname", TextBox1.Text)
    cmd.Parameters.AddWithValue("@Surname", TextBox2.Text)
    cmd.Parameters.AddWithValue("@Age", TextBox3.Text)
    cmd.Parameters.AddWithValue("@SkillLevel", TextBox5.Text)
    cmd.Parameters.AddWithValue("@Session", TextBox4.Text)

    Using con As New OleDbConnection(connString)
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using

#1