Click here to Skip to main content
15,921,606 members
Home / Discussions / Database
   

Database

 
Questionscript Pin
rezarafiee6-Jul-08 2:15
rezarafiee6-Jul-08 2:15 
AnswerRe: script Pin
Saksida Bojan6-Jul-08 3:26
Saksida Bojan6-Jul-08 3:26 
AnswerRe: script Pin
Pete O'Hanlon6-Jul-08 9:14
mvePete O'Hanlon6-Jul-08 9:14 
QuestionEmail Alert after Insert on table Pin
obarahmeh5-Jul-08 21:42
obarahmeh5-Jul-08 21:42 
AnswerRe: Email Alert after Insert on table Pin
N a v a n e e t h6-Jul-08 4:15
N a v a n e e t h6-Jul-08 4:15 
GeneralRe: Email Alert after Insert on table Pin
obarahmeh6-Jul-08 23:52
obarahmeh6-Jul-08 23:52 
GeneralRe: Email Alert after Insert on table Pin
N a v a n e e t h7-Jul-08 0:38
N a v a n e e t h7-Jul-08 0:38 
QuestionOleDb INSERT statement, works if I do NOT prepare it, fails if I do... Pin
kfrankinmn5-Jul-08 20:57
kfrankinmn5-Jul-08 20:57 
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. Frown | :(


       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", OleDbType.Double);
               cmd.Parameters.Add("@avg_fuel", OleDbType.Double);
               cmd.Parameters.Add("@labor_rate", OleDbType.Double);

               cmd.Parameters.Add("@company", OleDbType.VarChar, 10);
               cmd.Parameters.Add("@maint_lock", OleDbType.Integer);
               cmd.Parameters.Add("@change_dt", OleDbType.Date);
               cmd.Parameters.Add("@change_login_user", OleDbType.VarChar, 8);

               // because SqlServer OLEDB is so very crippled...
               foreach (OleDbParameter p in cmd.Parameters)
               {
                   p.IsNullable = true;
               }

//
// The mystery: Why does commenting out this line allow the subsequent .ExecuteNonQuery() succeed?!?
// And why does un-commenting it cause it to fail?!?
//
//               cmd.Prepare();
//
           }


// note, I don't really have hard-coded values assigned like this in my "real" program,
// but these are broadly representative of the kinds of values that ARE inserted.
// and since the simple test function fails identically to the real program, and should
// be a lot easier for some kind reader to understand...

           // parm 0,1,2,3
           cmd.Parameters["@crew_size"].Value = 1;
           cmd.Parameters["@due_date"].Value = DateTime.Now;
           cmd.Parameters["@due_fl"].Value = "F";
           cmd.Parameters["@est_do"].Value = 314.15;

           // parm 4,5,6,7
           cmd.Parameters["@job_qty"].Value = 1;
           cmd.Parameters["@labor_time"].Value = TimeSpan.FromHours(1.5).TotalMilliseconds;
           cmd.Parameters["@location"].Value = "NORMM";
           cmd.Parameters["@occurance"].Value = 3;

           // parm 8,9,10,11
           cmd.Parameters["@priority"].Value = 5;
           cmd.Parameters["@reported_by"].Value = "FORECAST";
           cmd.Parameters["@source_fl"].Value = "A";
           cmd.Parameters["@job_reason"].Value = "S";

           // parm 12,13,14,15
           cmd.Parameters["@work_req_no"].Value = 1641781;
           cmd.Parameters["@first_date"].Value = DateTime.Now - TimeSpan.FromDays(7);
           cmd.Parameters["@last_date"].Value = DateTime.Now + TimeSpan.FromDays(4);
           cmd.Parameters["@span_fl"].Value = "Y";

           // parm 16,17,18,19
           cmd.Parameters["@avg_meter"].Value = 0;
           cmd.Parameters["@avg_meter2"].Value = null;
           cmd.Parameters["@avg_fuel"].Value = 10;
           cmd.Parameters["@labor_rate"].Value = 45;

           // parm 20,21,22,23
           cmd.Parameters["@company"].Value = "1";
           cmd.Parameters["@maint_lock"].Value = 0;
           cmd.Parameters["@change_dt"].Value = DateTime.Now;
           cmd.Parameters["@change_login_user"].Value = "FORECAST";

           ListParms(cmd);

           try
           {
               int rows = cmd.ExecuteNonQuery();
               return true;
           }
           catch (Exception E)
           {
               Console.WriteLine(E.ToString());
               return false;
           }
       }



If anyone has any advice, I would be terribly grateful!
Thanks!
AnswerRe: OleDb INSERT statement, works if I do NOT prepare it, fails if I do... Pin
Kjetil Svendsen6-Jul-08 22:22
Kjetil Svendsen6-Jul-08 22:22 
GeneralRe: OleDb INSERT statement, works if I do NOT prepare it, fails if I do... Pin
kfrankinmn7-Jul-08 2:06
kfrankinmn7-Jul-08 2:06 
GeneralRe: OleDb INSERT statement, works if I do NOT prepare it, fails if I do... Pin
Kjetil Svendsen7-Jul-08 21:01
Kjetil Svendsen7-Jul-08 21:01 
QuestionUpdate transaction makes no changes to database Pin
Nigel Mackay5-Jul-08 20:52
Nigel Mackay5-Jul-08 20:52 
QuestionOracle InstantClient Pin
Muammar©5-Jul-08 19:41
Muammar©5-Jul-08 19:41 
QuestionSQL Server 2005 and vb.net >>>> Connection String Pin
kindman_nb5-Jul-08 12:37
kindman_nb5-Jul-08 12:37 
AnswerRe: SQL Server 2005 and vb.net >>>> Connection String Pin
N a v a n e e t h5-Jul-08 16:44
N a v a n e e t h5-Jul-08 16:44 
QuestionApostrophe In Field-Value Problem Pin
xbiplav5-Jul-08 10:57
xbiplav5-Jul-08 10:57 
AnswerRe: Apostrophe In Field-Value Problem Pin
Kjetil Svendsen6-Jul-08 22:35
Kjetil Svendsen6-Jul-08 22:35 
QuestionNeed tips for designing Database for multilingual websites (6 different languages) Pin
amistry_petlad5-Jul-08 4:46
amistry_petlad5-Jul-08 4:46 
Questionselect problem Pin
Stephan Hoppe4-Jul-08 12:24
Stephan Hoppe4-Jul-08 12:24 
AnswerRe: select problem Pin
leoinfo4-Jul-08 14:59
leoinfo4-Jul-08 14:59 
GeneralRe: select problem Pin
Stephan Hoppe5-Jul-08 6:13
Stephan Hoppe5-Jul-08 6:13 
QuestionHow to get date only from the table Pin
BalasubramanianK4-Jul-08 3:20
BalasubramanianK4-Jul-08 3:20 
AnswerRe: How to get date only from the table Pin
leoinfo4-Jul-08 3:50
leoinfo4-Jul-08 3:50 
QuestionTrouble joining between 3 tables Pin
dlarkin773-Jul-08 10:14
dlarkin773-Jul-08 10:14 
AnswerRe: Trouble joining between 3 tables Pin
Paul Conrad3-Jul-08 11:15
professionalPaul Conrad3-Jul-08 11:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.