Database

       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

Products Table	

ProductCode	
A1			
B2		
C3		
D4
E5

OrderDetails Table

ID ProductCode Quantity
1  A1          1
1  B2          2
1  D4          4
2  C3          1
2  E5          2

Orders Table

ID AccountNumber DeliveryDate
1  1234          03/07/2008
2  6789          26/06/2008

ProductCode DeliveryDate Quantity
A1          03/07/2008   1
B2          03/07/2008   2
C3          NULL         NULL
D4          03/07/2008   4
E5          NULL         NULL

ProductCode DeliveryDate Quantity
A1          NULL         NULL
B2          NULL         NULL
C3          26/06/2008   1
D4          NULL         NULL
E5          26/06/2008   2