CODEWITHSUNDEEP
asp.netsqlvb.netms-access-2003
Brian
Brian

Reputation: 2051

Syntax Error on INSERT into User Table in MS Access 2003

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?

Upvotes: 1

Views: 1097

Answers (1)

Guffa
Guffa

Reputation: 700212

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.

Upvotes: 3

Related Questions