Error Adding Data to Access Database

Question

I am trying to add a new record to an Access database. I am new to Visual Basic but have been searching for an answer to this. Here is my code so far:

Dim ID As Integer = CInt(IDBox.Text)
    Dim password As Integer = CInt(PasswordBox.Text)
    Dim first As String = FirstName.Text
    Dim last As String = LastName.Text
    Dim access As Integer = CInt(AccessLevel.Text)

    Dim conn As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Tristan\Documents\Visual Studio 2015\Projects\Keno\Keno\Users.accdb")
    conn.Open()
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    da = New OleDb.OleDbDataAdapter("SELECT * FROM Users", conn)
    da.Fill(ds, "Users")
    Dim cb As New OleDbCommandBuilder(da)
    Dim dsNewRow As DataRow

    dsNewRow = ds.Tables("Users").NewRow()
    dsNewRow.Item("ID") = ID
    dsNewRow.Item("First_Name") = first
    dsNewRow.Item("Last_Name") = last
    dsNewRow.Item("Password") = password
    dsNewRow.Item("Access_Level") = access

    ds.Tables("Users").Rows.Add(dsNewRow)
    cb.GetInsertCommand()
    da.Update(ds, "Users")
    conn.Close()

    MsgBox("User added successfully!")

Running this gets an error:

An unhandled exception of type System.Data.OleDb.OleDbException occurred in System.Data.dll

Additional information: Syntax error in INSERT INTO statement.

Any help is appreciated!

Why are you converting the PW textbox to Int? You should never, evet store PWs as plaintext - they should be hashed. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented May 25, 2016 at 12:03
I know this is not normally how passwords should be handled, but security is not really a concern. I will have administrative control over every machine that is running this software. This "password" is just a basic PIN number so that no one can accidentally use the software. — Tristan
– Tristan, Commented May 25, 2016 at 21:56

jmcilhinney · Accepted Answer · 2016-05-25 03:01:33Z

0

The issue is almost certainly the fact that "Password", which you have used as a column name, is a reserved word. Any identifiers used in SQL code that are reserved words or contain special characters, e.g. spaces, must be escaped. A command builder doesn't do that by default. You have to set the QuotePrefix and QuoteSuffix properties yourself to make it do so. For an Access database, you would use "[" and "]" as the property values respectively.

answered May 25, 2016 at 3:01

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

2 Comments

Tristan Over a year ago

Can you please give an example relative to my code? I am a beginner and not sure how to set the properties you mentioned and allow the use of "Password" as a column name.

jmcilhinney Over a year ago

What effort have you made to find out how to set a property? You may be a beginner when it comes to programming but I'm guessing that you've used the web and a search engine before. Apart from that, you already do know how to set a property.

Collectives™ on Stack Overflow

Error Adding Data to Access Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related