Microsoft Access and VB.NET Help - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Microsoft Access and VB.NET Help

CUBESDCUBESD Posts: 1Member
Hi,

I need some assistance with Microsoft Access and Visual Basic.NET.

I'm trying to insert records into a database but keep getting a 'INSERT INTO syntax error'. Here's the code snippit:

Try
Dim Con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=./ds/dsstore.mdb;Jet OLEDB:Database Password=password;")
Dim Com As OleDb.OleDbCommand = New OleDb.OleDbCommand("INSERT INTO useraccess(FirstName,Surname,IDNumber,UserState,UserLevel,Username,Password) VALUES('" & txtFirstName.Text & "','" & txtSurname.Text & "'," & txtIDNumber.Text & ",'" & cboUserState.Text & "','" & cboUserLevel.Text & "','" & txtUsername.Text & "','" & txtConfirmPassword.Text & "')", Con)
Con.Open()
Com.ExecuteNonQuery()
Con.Close()
frmMain.tsStatus.Text = "Waiting..."
Catch ex As Exception
notifyUAC.BalloonTipIcon = ToolTipIcon.Error
notifyUAC.BalloonTipTitle = ex.Source
notifyUAC.BalloonTipText = ex.Message
notifyUAC.ShowBalloonTip(4000)
Finally
frmMain.tsStatus.Text = "System Ready"
Call ResetFields()
Call LoadCatalogue(Me.lvCatalogue)
End Try

In the database I've got the fields listed above but it also includes a primary key field labeled 'UACNumber' which is set to AutoNumber in Access.

Currently using VB 2008 and Access 2007 (DB in 2000 format).

Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    A common cause of "syntax invalid" when you are doing an Insert is because you have a Field that is also a reserved name in SQL. (Password is a reserved SQL keyword). In these cases you must surround the field name with '[' and ']' brackets.

    Try this:

    "INSERT INTO useraccess (FirstName, Surname, IDNumber, [UserState], [UserLevel], [Username], [Password]) VALUES ('" & txtFirstName.Text & "', '" & txtSurname.Text & "', " & txtIDNumber.Text & ", '" & cboUserState.Text & "', '" & cboUserLevel.Text & "', '" & txtUsername.Text & "', '" & txtConfirmPassword.Text & "')"

    I surrounded your field names that are suspect, and I added some spacing in there to separate fields.

    Hope this helps,
    -Sean Campbell
Sign In or Register to comment.