Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.