Syntax Error on INSERT into User Table in MS Access 2003

Question

Using VB.NET with ASP.NET and and ms-access 2003 data, I'm trying to input data from a web form to the a table in db.mdb called 'USER'.

I tried this code:

 Protected Sub btnCreateAccount_Click(sender As Object, e As System.EventArgs) Handles btnCreateAccount.Click

    Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Brian\Documents\Visual Studio 2010\WebSites\WebSite3\db.mdb;User Id=admin;Password=;")

    Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO USER (Name, Surname, Username, Country, TelNo, Password, Address) VALUES (?, ?, ?, ?, ?, ?, ?)", conn)

    If txtPass.Text = txtCPass.Text Then

        cmd.Parameters.Add("@Name", OleDbType.VarChar, 255).Value = txtName.Text
        cmd.Parameters.Add("@Surame", OleDbType.VarChar, 255).Value = txtSurname.Text
        cmd.Parameters.Add("@Address", OleDbType.VarChar, 255).Value = txtAddress.Text
        cmd.Parameters.Add("@Country", OleDbType.VarChar, 255).Value = txtCountry.Text
        cmd.Parameters.Add("@Username", OleDbType.VarChar, 255).Value = txtUsername.Text
        cmd.Parameters.Add("@Password", OleDbType.VarChar, 255).Value = txtPass.Text
        cmd.Parameters.Add("@TelNo", OleDbType.Integer).Value = txtTelNo.Text

        Try

            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()

        Catch ex As OdbcException
            Throw ex
        Finally
            conn.Close()
            lblAccount.Visible = True


        End Try

    End If

End Sub

But it's returning me an error message:

enter image description here

Any suggestions to why the problem might be cause?

Strongly suggest you get rid of the Catch clause. It messes up your stack trace to make it look like the Throw Ex line is the source of the exception. — John Saunders
– John Saunders, Commented Mar 24, 2012 at 15:01

Guffa · Accepted Answer · 2012-03-24 14:59:40Z

3

User and Password are reserved keywords. Change the names, or use square brackets around the names in the query:

    Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO [USER] (Name, Surname, Username, Country, TelNo, [Password], Address) VALUES (?, ?, ?, ?, ?, ?, ?)", conn)

Then, for then next error that you will encounter: As the parameters are not named in the query, the parameter objects in the Parameters collection has to be added in the same order as they are used in the query.

answered Mar 24, 2012 at 14:59

Guffa

703k111 gold badges760 silver badges1k bronze badges

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

2 Comments

Brian Over a year ago

Thanks !.. also just one more thing I'm getting an error on {"Data type mismatch in criteria expression."} on the cmd.ExecuteNonQuery() What might be causing this ?

Guffa Over a year ago

@Brian: Have you checked that the parameters is in the correct order? Have you checked that the parameter types correspond to the database types? Have you verified that the phone number only contains digits, so that it's possible to convert to an integer?

Collectives™ on Stack Overflow

Syntax Error on INSERT into User Table in MS Access 2003

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related