Syntax error in INSERT INTO Statement when writing to Access

Question

I am trying to write a program where I can write data to an Access DB but I keep getting the error “Syntax error in INSERT INTO statement”.

FirstLast is the name of the table; First and Last are columns in it.

Imports System.Data.OleDb

Public Class Form1
    Dim theConnectionString As New OleDbConnection
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        theConnectionString.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\user\Documents\Database1.accdb"

        Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO FirstLast (First, Last) VALUES (@First, TextBox1.text)", theConnectionString)

        cmd.Parameters.Add("@First", OleDbType.Char, 255).Value = TextBox1.Text
        cmd.Parameters.Add("@Last", OleDbType.Char, 255).Value = TextBox1.Text

        Try
            theConnectionString.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            theConnectionString.Close()
        End Try

    End Sub
End Class

You’re naming your parameters @First and @Last — so shouldn’t you use those instead of ? in the query? — Ry-
– Ry- ♦, Commented May 24, 2013 at 1:40
@mwilson: No, they should be switched for your parameter names. Those don't have to be the same as your column names at all. — Jon Skeet
– Jon Skeet, Commented May 24, 2013 at 1:45
I'm hardly an Access expert, but isn't the actual problem simply that you have VALUES (@First, TextBox1.text) in your SQL, where it should say VALUES (@First, @Last)? If so, I'm voting to close this question as "merely a typo". — Ilmari Karonen
– Ilmari Karonen, Commented Dec 15, 2015 at 3:30

Jon Skeet · Accepted Answer · 2013-05-24 01:41:14Z

5

I suspect you just need to use named parameters instead of positional ones:

INSERT INTO FirstLast (First, Last) VALUES (@First, @Last)

Note that you should also use Using statements for the connection and command, so that you clean up after yourself automatically without needing an explicit Finally block. (You can get rid of your entire Try/Catch/Finally given that you're just rethrowing the exception anyway.)

As noted in the comment below, you also probably meant to use TextBox2.Text for the @Last parameter, rather than using TextBox1.Text again.

answered May 24, 2013 at 1:41

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Richard Schneider Over a year ago

Also the @Last value is wrong. You most likely want to use TextBox2.Text

mwilson Over a year ago

Do I need any special imports for this? I still can't get it working... I don't quite follow on the Try Catch yet. I am still learning that portion. Is it okay as is or is this causing the issue?

Jon Skeet Over a year ago

@mwilson: No, you don't need any extra imports. "I still can't get it working" is far too vague for me to help you, I'm afraid... and I'm about to get on a plane. If you're not familiar with Try/Catch blocks I suggest you stop what you're doing and learn about them. It's not a good idea to use code with language features you don't understand. It means you've got very little chance of being able to diagnose problems yourself.

Collectives™ on Stack Overflow

Syntax error in INSERT INTO Statement when writing to Access

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related