SQL Insert Error

cynnerg

I am using this SQL query to insert into an access database using oleDbCommand: INSERT INTO Events (ID, Code, Hour, Day, Location, Name, Cost) VALUES (0, 'code', '5:05 pm', '11/3/2004', 'loc', 'name', '23.56') I keep getting an insert error, although when testing the query through Access it works fine. I have tried both using command parameters and hard-coding the data in as it is above, with the same results. Any suggestions?

cynnerg

Here is the entire source that I have so far in this section: ds = New DataSet myCommand.Fill(ds, "Data") id = ds.Tables("DATA").Rows.Count() txtID.Text = id strQuery = "INSERT INTO Events (ID, Code, Hour, Day, Location, Name, Cost) VALUES (" _ & Int(id) & ", '" _ & txtCode.Text & "', '" _ & txtTime.Text & "', '" _ & txtDate.Text & "', '" _ & txtLocation.Text & "', '" _ & txtName.Text & "', '" _ & txtCost.Text & "')" cmd = New OleDbCommand(strQuery, myConnection) cmd.ExecuteNonQuery()

Guillermo Rivero

The ID column, is Autonumeric ? The Hour and Day Column, what type are they ? Free your mind...

cynnerg

ID is standard number. Handling the increment in code. All other columns are text.

Guillermo Rivero

I don't see nothing wrong with your select statement. Did you check the size of the fields ? What's the error description ? Free your mind...

Guillermo Rivero

Are you repeating a value on a column that is indexed as primary key ? Free your mind...

cynnerg

Uhhhm, no. And no. In fact, right now that table is empty. And everything is configured as a text value. I was really hoping it was the SQL, but now it looks like maybe the code itself. Or something else. Funny thing. That exact same code (with different fields and parameters) works perfectly in the same database, different table. I'll have to look at it more thoroughly, I guess. Thanks

Roger Wright

One possibility is that Access requires literal date data to be set off by #, as in #11/03/2004#. This may apply to time values, as well, though Clippy won't tell me that no matter how hard I ask. "Another day done - All targets met; all systems fully operational; all customers satisfied; all staff keen and well motivated; all pigs fed and ready to fly" - Jennie A.

rahtrow

Do you have a specific error message? What is the schema definition of the Events table? (what are the field types and sizes for each field in the table) Any identity/autogenerated columns? Is there primary key defined?