SQL INSERT INTO statement for MS Access

Question

I am trying to learn how to use MS Access with my VB.net program. I am practicing learning how to use the INSERT INTO statement but I keep getting an error.

Here is my code:

Imports System.Data.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim myConnection As OleDbConnection
    Dim DBpath As String = "C:\Documents and Settings\nordeen1\Desktop\Test.mdb"
    Dim sConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBpath & ";Persist Security Info=True"
    myConnection = New OleDbConnection(sConnectionString)
    myConnection.Open()
    Dim SQLstr As String
    SQLstr = "INSERT INTO Test (Text, [Date], Number) VALUES ('testing', #2/6/1990#, 5)"
    Dim cmd As New OleDbCommand(SQLstr, myConnection)
    cmd.ExecuteNonQuery()
End Sub
End Class

I get this error "OleDbException was unhandled. Syntax error in INSERT INTO statement." at cmd.ExecuteNonQuery()

Any suggestions are greatly appreciated! Thanks!

score 2 · Accepted Answer · answered Mar 07 '13 at 16:30

2

TEXT and NUMBER are also reserved so they should be delimited,

SQLstr = "INSERT INTO Test ([Text], [Date], [Number]) VALUES ('testing', #2/6/1990#, 5)"

MS ACCESS Reserved Keyword

answered Mar 07 '13 at 16:30

John Woo

258,903
69
498
492

1

That worked! I will be accepting this as my answer. It is possible to pass through variables in the Values section? – gromit1 Mar 07 '13 at 16:45
1

@nordeen1 yes, parameterized the values. Read some about `OleDbCommand`.`Parameters` – John Woo Mar 07 '13 at 16:47

score 2 · Answer 2 · answered Mar 07 '13 at 16:32

2

Use CDATE for your Date :

   "INSERT INTO Test ([Text], [Date], [Number]) 
              VALUES ('testing',  CDATE('1990-06-02 00:00:00'), 5)"

answered Mar 07 '13 at 16:32

Maryam Arshi

1,974
1
19
33

SQL INSERT INTO statement for MS Access

2 Answers2