Help with ADO.NET tranaction for master/detail

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to insert into 3 tables. One record to table A, One record to
table B, and 1 or more records to Table C. The query string for Table C is a
list of insert statements, separated by semicolons, that is built at runtime
because I do not know how many Table C records to expect.

It fails on table C insert. My code snippet is blow. Any suggestions?
if (sqlConn.State == ConnectionState.Closed)

sqlConn.Open();

sqlTran = sqlConn.BeginTransaction();

SqlCommand sqlCmd_1 = new SqlCommand("sProc_Entity_History_Detail",
this.sqlConn);



sqlCmd_1.CommandType = CommandType.StoredProcedure;

sqlCmd_1.Transaction = sqlTran;



//process records for Table 1

SqlParameter p0 = sqlCmd_1.Parameters.Add("@Emp_Id", SqlDbType.VarChar,25);

p0.Value = quoteId;

....

SqlParameter p24 = sqlCmd_1.Parameters.Add("@Market", SqlDbType.VarChar,22);

p24.Value = marketNrc;

bool bOk_1 = (sqlCmd_1.ExecuteNonQuery()== 1); //write to temp table



//process records for Table2

SqlCommand sqlCmd_2 = new SqlCommand("sProc_Entity_History", this.sqlConn);

sqlCmd_2.CommandType = CommandType.StoredProcedure;

sqlCmd_2.Transaction = sqlTran;

SqlParameter p25 = sqlCmd_2.Parameters.Add("@Emp_Id", SqlDbType.VarChar,25);

p25.Value = quoteId;

....

SqlParameter p32 = sqlCmd_2.Parameters.Add("@Source", SqlDbType.VarChar,10);

p32.Value = source;

bool bOk_2 = (sqlCmd_2.ExecuteNonQuery()==1);



//process records for Table 3

SqlCommand sqlCmd_3 = new SqlCommand(strQryBuilder, this.sqlConn);

sqlCmd_3.CommandType = CommandType.Text;

sqlCmd_3.Transaction = sqlTran;

bool bOk_3 = (sqlCmd_3.ExecuteNonQuery()==1);



if (bOk_1 && bOk_2 && bOk_3)

{

sqlTran.Commit();

return true;

}

else

{

sqlTran.Rollback();

return false;

}
 
You don't appear to be adding parameters, i guess because you aren't knowing
what they are. However you may have a syntactical error there which is
causing the problem - for instance, an " ' " character (apostrophe) or
something else. Also, do not use GO in between them if you are because that
is known to cause much problems.
 
Thanks.

I am not using GO in between the sql statements. I could not find any
sysntatical errors.
 
Back
Top