Insert SQL Error

[Guest]

I am writing a VB NET program, and I have set up the following InsertCommand
property and parameters:

dap1.InsetCommand = New OleDb.OleDbCommand( _
"INSERT INTO entry " & _
"(date, child, activity, starttime, endtime) " & _
"VALUES (?, ?, ?, ?, ?)", cnn1)
Dim prm11 As OleDb.OleDbParameter = _
dap1.InsertCommand.Parameters.Add("@date", _
OleDb.OleDbType.VarChar, 20, "date")
Dim prm12 As OleDb.OleDbParameter = _
dap1.InsertCommand.Parameters.Add("@child", _
OleDb.OleDbType.VarChar, 20, "child")
Dim prm13 As OleDb.OleDbParameter = _
dap1.InsertCommand.Parameters.Add("@activity", _
OleDb.OleDbType.VarChar, 25, "activity")
Dim prm14 As OleDb.OleDbParameter = _
dap1.InsertCommand.Parameters.Add("@starttime", _
OleDb.OleDbType.VarChar, 8, "starttime")
Dim prm15 As OleDb.OleDbParameter = _
dap1.InsertCommand.Parameters.Add("@endtime", _
OleDb.OleDbType.VarChar, 8, "endtime")

When I run my program, I receive an error with the INSERT INTO statement.
I am using a database created in Access 2003. When I use the exact same
code with the Northwind database, it runs fine.

Any ideas?

 

[Guest]

Make sure your parameters are in order. OleDb is not very forgiving if one is
out of track. In addition, ensure you are using the correct data types.

It is also preferable to make the objects and then add them to the command
object, as you can more easily debug the code.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

Data Types always screw me up. I changed the Access database to all text
fields to attempt to get the code to work. Therefore, the data type I am
using below is VarChar. I originally wanted to use a date/time type in
Access, but I didn't know what type to convert it to.

Also, I am wondering if any of the field names I am using are reserved words
(date, child, activity, starttime, and endtime).

 

[Val Mazur]

Hi Tom,

What is the actual error message you get? If it work with one database but
does not work with another, it could be difference in a tables structure.
Also pay attention to the reserved words. DATE is the reserved word and if
you would like to use it as a field's name you would need to wrap it into
square brackets

"INSERT INTO entry " & _
"([date], child, activity, starttime, endtime) " & _ .....

 

[Ron Allen]

Tom,
Date is reserved so that will be a problem using OleDb as it enforces
the same reserved words as SQL and ODBC. See
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/tsqlref/ts_ra-rz_9oj7.htm for
VS2003 or search for 'Reserved Keywords' for more keywords.
A date would be OleDbType.Date if you want the date/time or use DBDate
for just the date part. Both would convert to a DateTime if not null.

Ron Allen