INSERT statement error

[jdy0803]

Here is my code.

cmd = New OleDbCommand()
cmd.Connection = cn
Sql = "INSERT INTO provider_schedule (provider_id, day, open_yn, start1, end1) VALUES ( '1', '" & ListView1.Items(i).Text.Trim & _
"', '" & ListView1.Items(i).SubItems(1).Text.Trim & "', '" & ListView1.Items(i).SubItems(2).Text.Trim & "', '" & ListView1.Items(i).SubItems(3).Text.Trim & "' )"
cmd = New OleDbCommand(Sql, cn)
records = cmd.ExecuteNonQuery()
cmd.Dispose()

Following error occurs at cmd.ExecuteNonQuery
Syntax error in INSERT INTO Statement

If I display Sql, it's like this.
INSERT INTO provider_schedule (provider_id, day, open_yn, start1, end1) VALUES ('1','2','1','1/1/2000 9:00:00 AM', '1/1/2000 9:00:00 PM')

Can anybody give me help?

 

[jmcilhinney]

First things first, you should NEVER use string concatenation like that to insert values into SQL code. You should ALWAYS use parameters. To learn how to do that, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

String concatenation is a common source of errors like yours, but looking at the end result, it doesn't appear to be the case this time. I would ask though, what is the data type of those first three columns? Is it a numeric type or text? If it's a numeric type, as it appears, then you should not be putting single quotes around the values. Single quotes are for text. Numbers are left bare and, if this is Access, dates should be wrapped in # symbols.

As for the actual cause of the syntax error, my guess is that 'day' is a keyword in your database. If you want to use keywords or include special characters in your identifiers (which should be avoided if possible) then you must escape that identifier in SQL code. For Microsoft databases, that means wrapping it in [brackets] while many others use `graves`. Many people and most automated tools escape every identifier, just to be on the safe side.

 

[jdy0803]

Sorry for reply.
Here is the data type of provider_schudule table of access db.
provider_id : double
day : long integer
open_yn : long integer
start1 : date/time
end1 : date/time

Can you make correct INSERT statement for this?

 

[jmcilhinney]

Can you make correct INSERT statement for this?

I'm here to help but not to think for you. I've provided some instruction and directions on where to find more. Read it and make an attempt and then post what you've done if you need further assistance.