Question Problems with Microsoft Access

[MasterCaster]

I am trying to get VB.net to add a new record to an access data base but it keeps giving me an error. The code is below.

Dim inc As Integer = 0
Dim con As New OleDb.OleDbConnection
Dim sql As String
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter

Private Sub BtnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnConfirm.Click

dbProvider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
dbSource = "Data Source= S:\College\A2\Computing\Project\The photoelectric effect\Database1(2000).mdb"

con.ConnectionString = dbProvider & dbSource

con.Open()

sql = "SELECT * FROM Users"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Database1(2000)")

If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("Database1(2000)").NewRow()

dsNewRow.Item("Username") = TxtName.Text
dsNewRow.Item("Password") = TxtPass.Text

ds.Tables("Database1(2000)").Rows.Add(dsNewRow)
da.Update(ds, "Database1(2000)")
End If

MsgBox("Account created")

con.Close()
End Sub

The error says that there is a syntax error in INSERT INTO statement and it appears over the underlined line of code. Please help, thanks in advance.

 

[jmcilhinney]

The most usual cause is a column name that is a reserved word or contains illegal characters. The possible solutions, in order of preference, are:

1. Change the name(s) of the offending column(s) in the database so no reserved words or illegal characters are used.

2. Don't use a wildcard (*) for the column list in the SELECT statement. Write out the column list in full, which will require you to escape the offending column name(s) in [brackets]. The CommandBuilder will then follow your lead in the UPDATE and INSERT statements.

3. Don't use a CommandBuilder. Create the adapter's DeleteCommand, InsertCommand and UpdateCommand yourself.