Transaction control using OLEDB
Hello,
I am trying to implement a trasaction in OleDb. I have an Update query and an selectquery. First i run the update query and then run the select query. The value i get from select query if less than 100 than it will commit otherwise rollback. But when i use the transaction using update and select it gives err. if i use multiple update it works fine. can anybody tell me how to do that. I am enlisting the code as well.
===========================================================
public void transactionTest()
{
OleDbConnection conn=null,conn1=null;
OleDbCommand cmd=null,cmd1=null;
OleDbTransaction transact=null;
OleDbDataReader resultSet=null;
OleDbDataReader resultSet1=null;
String strMarks="";
int iMarks=0;
int effectedRows=0;
string queryString=" UPDATE Marks SET marks = marks+100"+
" WHERE CAEID='cse5000A1' And "+
" studentid='student'";
string clientFilePath=@"C:\\STTS.mdb";
string myString=@"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";"+
" User ID=Admin;Data Source="+clientFilePath+";"+
" Mode=Share Deny None;Extended Properties=\"\";"+
" Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";"+
" Jet OLEDB:Database Password=\"\";Jet OLEDB:Engine Type=5;"+
"Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;"+
"Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;"+
"Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
try
{
conn=new OleDbConnection(myString);
conn.Open();
transact=conn.BeginTransaction();
cmd =new OleDbCommand();
cmd.Connection=conn;
cmd.Transaction=transact;
cmd.CommandText=queryString;
effectedRows = cmd.ExecuteNonQuery();
/*queryString=" UPDATE Marks SET marks = 'Hell'"+
" WHERE CAEID='cse5000A1' And "+
" studentid='student'";
cmd.CommandText=queryString;
effectedRows = cmd.ExecuteNonQuery(); */
//cmd.Transaction=transact.Begin();
if(effectedRows>0)
{
queryString="select marks from marks"+
" WHERE CAEID='cse5000A1' And "+
" studentid='student'";
cmd.CommandText=queryString;
resultSet1=cmd.ExecuteReader();
if(resultSet1.Read())
{
strMarks=resultSet1.GetValue(0).ToString();
iMarks=int.Parse(strMarks);
if(iMarks>100)
{
transact.Rollback();
MessageBox.Show("Trascation RollBack.");
}
else
{
transact.Commit();
MessageBox.Show("Transaction Commit.");
}
}
else{
transact.Rollback();
MessageBox.Show("No Rows Effected.");
}
}
else
{
transact.Rollback();
MessageBox.Show("No Rows Effected.");
}
transact.Commit();
MessageBox.Show("hoiseyy");
}
catch (Exception ex)
{
transact.Rollback();
MessageBox.Show("Error in Transaction"+ex.ToString(),"Log In Failure",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
//resultSet1.Close();
conn.Close();
//conn1.Close();
}