Problems with INSERT

MS Access

[zxk105]

I keep getting this error when trying to insert a new row in my MS Access database: "Syntax error in INSERT INTO statement". The database is very simple. It consists of: position TEXT(50), availability (YES/NO), jobID (AutoNumber). The code below looks fine to me but I am doing something wrong. Can anyone help me?

If Page.IsValid() Then
Dim sPath As String = "connection_string"
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & sPath & ";"
Dim objConnInsert As OleDbConnection
Dim cmdInsert As OleDbCommand
Dim strInsert As String = ""

lblStatus.Visible = False

'Open connection
objConnInsert = New OleDbConnection(connString)
objConnInsert.Open()

'Create SQL statement
If (chkAvailability.Checked = True) Then
strInsert = "INSERT INTO tblJobListing (position, availability) VALUES ('" & txtPosition.Text & "', True)"
Else
strInsert = "INSERT INTO tblJobListing (position, availability) VALUES ('" & txtPosition.Text & "', False)"
End If

'Execute query
cmdInsert = New OleDbCommand(strInsert.ToString(), objConnInsert)
cmdInsert.ExecuteNonQuery()

objConnInsert.Close()
Response.Redirect("ManageJobList.aspx")
End If

[tfarrington]

Try adding this to the end of your connection string:

User ID=Admin;Password=

I tried and the insert using your code but my connection string and it worked. Another thing to try is with YES/NO fields you can use Yes and No as data, not just True/False.

Tom

[zxk105]

I tried adding "User ID=Admin;Password="

I hope this is what you meant...

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=" & sPath & "; User ID=Admin; Password="

I also played around with YES/NO and TRUE/FALSE and I still get "Syntax error in INSERT INTO statement"

I understand what it's saying but I don't know how to fix it. When I display my INSERT statement, copy it, and try the SQL statement directly with the ISP's DB Manager, I don't get a syntax error and it works just fine. This confuses me completely. Can anyone help me out?

[Spooky]

"position" is a reserved word

Use []

[zxk105]

of course i wouldn't see a small thing like that.

it worked! thanks!