C# MS Access DateTime insert query error Please

[Annie]

hello guys,

I have little experience working with C# and MS Access ...

I am having an insert query with one datetime field and a boolean and couple
of text and number fields as below:

"Insert into order (OrderClientID, OrderDate, OrderOfferID, OrderIsSpecial,
OrderShipment) Values (6, # 16/09/2005 2:38:51 PM # , 1 , 0 , 'hello there
it is test')"

The second parametter is Date format, and the 4th one is boolean. I tryed
false and 0 but both didn't work.

I create my string as below:

string strTable="Insert into order";

string strFields=" (OrderClientID, OrderDate, OrderOfferID, OrderIsSpecial,
OrderShipment)";

string strValues= " Values (" + personID +

", # " + System.DateTime.Now.ToString() +

" #, " + 1 +

", " + 0 +

" ,'" + shipment + "')";

string insertStr = strTable + strFields + strValues;



it raises SQL insert error while try to add.



what is going wrong here?

 

[Kalpesh]

What sql error is getting raised ?

You can try running the same query in Access.
1) Open the access db
2) Goto queries tab
3) Click "New Query"
4) Paste your insert statement in "SQL" view

Execute the query & see what error does it raise
It will be easy to find the source of error there. If you find what is
the problem, change the query

Does this help ?

Kalpesh

 

[Guest]

Try using parameterized queries whenever possible. They're easier and safer.
Example:

string qry = "INSERT INTO MyTable (MyDateCol) VALUES(?)";

OdbcParameter parm = new System.Data.Odbc.OdbcParameter("@DateTimeValue",
System.Data.SqlDbType.DateTime);
parm.Value = DateTime.Now;

//Now create a command and set "qry" as the command text and add "parm" to
//the parameters collection.


I'm positive I've done this before, but it's been a while, so my syntax may
not be 100% correct. (I'm pretty sure "?" is used to represent the param in
the query string...) In any case, this methodology is the "best" way to do it
when you can't use a stored procedure.

 

[Annie]

Well, my question was what was wrong with my query? I already tested the
query by running it in MS Access
but still error ... I don't know what is wrong? what format MS Access
accepts as input for date and boolean
fields from C#. that was basically my question ...

thanks anyways ,,,

 

[Kalpesh]

What is the position of the cursor when the error is raised while
running query in MS-Access query editor ?

Also, there need not be space before & after the date value

Does it help?

Kalpesh