Transaction control using OLEDB

Question

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