CODEWITHSUNDEEP
vb.netvisual-studiooledb
Tristan
Tristan

Reputation: 1790

Error Adding Data to Access Database

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!

Upvotes: 0

Views: 42

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54457

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.

Upvotes: 0

Related Questions