I'm getting this error when I try to PREPARE an OleDbCommand:
Error: System.Data.OleDb.OleDbException: Statement(s) could not be prepared.
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
Works fine when talking to an Oracle database, but not when talking to SqlServer.
(A few details have been changed, to protect the guilty)
I've got a table that looks like this, in SqlServer:
COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH
------------------------------ --------- ------------------------------ ----------- -----------
CLIENT_ID 12 varchar 10 10
JOB_REASON 1 char 1 1
JOB_ID 2 numeric 10 12
SEQ 2 numeric 10 12
LABOR_TIME 2 numeric 18 20
PROJECT_ID 2 numeric 10 12
CREW_SIZE 2 numeric 5 7
ACCT_NO 12 varchar 50 50
DUE_DATE 11 datetime 23 16
EST_DO 2 numeric 28 30
EARLIEST_DATE 11 datetime 23 16
LATEST_DATE 11 datetime 23 16
LOCATION 12 varchar 6 6
LOCK_FL 1 char 1 1
PRIORITY 2 numeric 5 7
REPORTED_BY 12 varchar 11 11
SOURCE_FL 1 char 1 1
SPAN_FL 1 char 1 1
CHANGE_DT 11 datetime 23 16
EMP_ID 2 numeric 10 12
WORK_ORDER_NO 12 varchar 10 10
STATUS 1 char 1 1
RESOURCE_TYPE 12 varchar 5 5
And, I've got a program that needs to insert a bunch (15000 isn't unusual) of rows into that table, amongst a great many other things that this program does.
I have to use OLEDB, because this program needs to talk to Oracle as well as SqlServer.
So, here's what my routine looks like:
private bool SaveJobEntry( ref OleDbCommand cmd )
{
if (ins_job_cmd == null)
{
// the .InsJobEntry_string looks something like:
// insert into job_entries(client_id, job_reason, job_id, ...)
// values( ?, ?, ?, ... );
cmd = new OleDbCommand(Globals.InsJobEntry_string, Globals.Conn, Globals.CurTrans);
cmd.Parameters.Add("@client_id", OleDbType.VarChar, 12);
cmd.Parameters.Add("@job_reason", OleDbType.VarChar, 1);
cmd.Parameters.Add("@job_id", OleDbType.Integer);
cmd.Parameters.Add("@seq", OleDbType.Integer);
cmd.Parameters.Add("@labor_time", OleDbType.Integer);
cmd.Parameters.Add("@crew_size", OleDbType.Integer);
cmd.Parameters.Add("@acct_no", OleDbType.VarChar, 50);
cmd.Parameters.Add("@due_date", OleDbType.Date);
cmd.Parameters.Add("@est_do", OleDbType.Double);
cmd.Parameters.Add("@earliest_date", OleDbType.Date);
cmd.Parameters.Add("@latest_date", OleDbType.Date);
cmd.Parameters.Add("@location", OleDbType.VarChar, 6);
cmd.Parameters.Add("@lock_fl", OleDbType.VarChar, 1);
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("@work_order_no", OleDbType.Integer);
// since we're gonna do this a few thousand times, best to save the query-plan and re-use it...
cmd.Prepare();
}
cmd.Parameters["@client_id"] = Globals.client_id;
cmd.Parameters["@job_reason"] = this.job_reason;
cmd.Parameters["@job_id"] = this.job_id;
cmd.Parameters["@seq"] = this.job_seq;
cmd.Parameters["@labor_time"] = this.LaborTime.TotalMilliseconds;
... and so forth ...
try
{
cmd.ExecuteNonQuery();
}
catch (Exception E)
{
Console.WriteLine(E.ToString());
Console.WriteLine("Failed inserting JobEntry. {0}", cmd.CommandText);
foreach (OleDbParameter p in cmd.Parameters)
{
Console.Write("{0}=", p.ToString);
if (p.Value == null)
Console.WriteLine("<null>");
else
Console.WriteLine(p.Value.ToString());
}
return false; // failed :(
}
return true; // success! :)
}
That all works just fine against our Oracle databases. But in SqlServer, it's FAILING on the cmd.Prepare(); statement!
Error: System.Data.OleDb.OleDbException: Statement(s) could not be prepared.
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.