Database

select em.ticketid, em.tickettitle, em.handlerid, em.CUSTOMERID, em.departmentid, em.site, em.platform, 
em.server, em.swrelease, em.generateddate, em.emergencyraiseddate, em.startdate, em.enddate, em.problemdesc, 
em.rootcause, em.customerimpact, em.outageevidence, em.isfirsttime, em.workdone, em.techanalysis, 
em.plannedaction, em.permanentfix, em.attid, e.EMPNAME, dept.DEPARTMENTNAME, cust.CNAME from emergencycallup em, 
employee e, department dept, customer cust where em.DEPARTMENTID = '1' and em.handlerid ='test.emp' and 
em.handlerid = e.empid and cust.customerid = em.customerid and dept.departmentid = em.departmentid

select em.ticketid, em.tickettitle, em.handlerid, em.CUSTOMERID, em.departmentid, em.site, em.platform, 
em.server, em.swrelease, em.generateddate, em.emergencyraiseddate, em.startdate, em.enddate, em.problemdesc, 
em.rootcause, em.customerimpact, em.outageevidence, em.isfirsttime, em.workdone, em.techanalysis, 
em.plannedaction, em.permanentfix, em.attid from emergencycallup em where em.DEPARTMENTID = '1' and em.handlerid 
='test.emp'  

       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;
           }
       }

create table #person
(
	PersonId varchar(2),
	Firstname nvarchar(50)
)

create table #member
(
	PersonId varchar(2),
	TeamId varchar(2)
)

create table #team
(
	TeamId varchar(2),
	TeamName nvarchar(50)
)


insert into #person values ('P1', 'Wayne');
insert into #person values ('P2', 'Rick');
insert into #person values ('P3', 'Bob');

insert into #team values ('T1', 'Team 1');
insert into #team values ('T2', 'Team 2');
insert into #team values ('T3', 'Team 3');


insert into #member values ('P1', 'T1');
insert into #member values ('P2', 'T1');
insert into #member values ('P2', 'T2');
insert into #member values ('P2', 'T3');
insert into #member values ('P3', 'T3');

drop table #member
drop table #person
drop table #team