OleDb INSERT statement, works if I do NOT prepare it, fails if I do...

[kfrankinmn]

I've got a program that needs to insert lots of rows into a table, typically, anywhere from a couple hundred, to tens of thousands. As such, I'd like them to go as fast as possible, so I'm using a .Prepare() on my OleDbCommand. If I run the following code with the .Prepare() commented out, it works fine. If I un-comment the .Prepare(), when it gets to the .ExecuteNonQuery(), it ALWAYS fails with the same complaint: System.Data.OleDb.OleDbException: Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. Even better, it works fine on Oracle. But SqlServer, not so much. :(

   private bool SaveOccurance(ref OleDbCommand cmd, ref OleDbTransaction CurTrans)
   {
       if (cmd == null)
       {
           string stmt = @"

insert into work_occurance(
crew_size, due_date, due_fl, est_do,
job_qty, labor_time, location, occurance,
priority, reported_by, source_fl, job_reason,
work_req_no, first_date, last_date, span_fl,
avg_meter, avg_meter2, avg_fuel, labor_rate,
company, maint_lock, change_dt, change_login_user)
values(
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?,
?, ?, ?, ?)";

           cmd = new OleDbCommand(stmt, Conn, CurTrans);

           cmd.Parameters.Add("@crew\_size", OleDbType.Integer);
           cmd.Parameters.Add("@due\_date", OleDbType.Date);
           cmd.Parameters.Add("@due\_fl", OleDbType.VarChar, 1);
           cmd.Parameters.Add("@est\_do", OleDbType.Double);

           cmd.Parameters.Add("@job\_qty", OleDbType.Integer);
           cmd.Parameters.Add("@labor\_time", OleDbType.Integer);
           cmd.Parameters.Add("@location", OleDbType.VarChar, 6);
           cmd.Parameters.Add("@occurance", OleDbType.Integer);

           cmd.Parameters.Add("@priority", OleDbType.Integer);
           cmd.Parameters.Add("@reported\_by", OleDbType.VarChar, 11);
           cmd.Parameters.Add("@source\_fl", OleDbType.VarChar, 1);
           cmd.Parameters.Add("@job\_reason", OleDbType.VarChar, 1);

           cmd.Parameters.Add("@work\_req\_no", OleDbType.Integer);
           cmd.Parameters.Add("@first\_date", OleDbType.Date);
           cmd.Parameters.Add("@last\_date", OleDbType.Date);
           cmd.Parameters.Add("@span\_fl", OleDbType.VarChar, 1);

           cmd.Parameters.Add("@avg\_meter", OleDbType.Double);
           cmd.Parameters.Add("@avg\_meter2", OleDb

[Kjetil Svendsen]

Hi. Try change

cmd.Parameters["@first_date"].Value = DateTime.Now - TimeSpan.FromDays(7);
cmd.Parameters["@last_date"].Value = DateTime.Now + TimeSpan.FromDays(4);

to

cmd.Parameters["@first_date"].Value = DateTime.Now.AddDays(-7);
cmd.Parameters["@last_date"].Value = DateTime.Now.AddDays(4);

It should not work, but it have helped me before. Kjetil

[kfrankinmn]

Thanks for the reply. I tried your suggestion, but I get the same error (and it still works fine if I do not Prepare() that command). My real program just supplies an already calculated date anyways (based upon things like an acceptable deviation range, avoiding weekends and holidays, etc), like: cmd.Parameters["@first_date"].Value = this.earliest_date; cmd.Parameters["@last_date"].Value = this.latest_date; For now, I'm going to go with not preparing the statement, even if it will get called many thousands of times. It doesn't seem to make much difference in my testing so far (unlike with Oracle, where not Preparing that statement made it run significantly slower).

[Kjetil Svendsen]

Hi. Have you tried OleDbType.DBDate instead of OleDbType.Date ? Worth a try. Kjetil