Hello
I'm making an update in c# ASP.NET 2.0 using the OleDb provider for
SQL Server.
I have an existing application running on 1.1 that works fine. Now I
want to convert it to 2.0.
My problem is that within the transaction, a read after an update/
insert will read the old commited values. The IsolationLevel I have
checked, it is ReadCommited (however I really should be reading MY OWN
changed values???). I've tried with IsolationLevel.Serializable but to
no help.
This is what I do:
I have a table testTable that has Id, Name and Step as columns.
OleDbConnection dbconn = new OleDbConnection("Provider=SQLOLEDB.
1;Password=xxx;Persist Security Info=True;OLE DB Services=-7;OLE DB
Services=-2;User ID=xxx;Initial Catalog=MyDb;Data Source=iit003;Use
Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use
Encryption for Data=False;Tag with column collation when
possible=False;pooled=false;");
dbconn.Open();
OleDbTransaction trans = dbconn.BeginTransaction();
OleDbCommand dbRead1 = new OleDbCommand("SELECT STEP FROM
TestTable WHERE ID = 1", dbconn, trans);
string res1 = dbRead1.ExecuteScalar().ToString();
OleDbCommand dbComm = new OleDbCommand("UPDATE
TestTable SET STEP = STEP + 1, NAME=? WHERE ID = 1", dbconn, trans);
dbComm.Parameters.Add(new
System.Data.OleDb.OleDbParameter("Name",
System.Data.OleDb.OleDbType.VarChar, 14,
System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "Name", System.Data.DataRowVersion.Current,
null));
dbComm.Parameters["Name"].Value = "Jojo";
int iAffected = dbComm.ExecuteNonQuery();
OleDbCommand dbRead2 = new OleDbCommand("SELECT STEP
FROM TestTable WHERE ID = 1", dbconn, trans);
string res2 = dbRead2.ExecuteScalar().ToString();
<==== THIS READS THE OLD VALUE!!!!
trans.Commit();
dbconn.Close();
Another funny thing is that after a reboot it works ok for a while. I
also haven't been able to reproduce this in a Windows Forms
application, only in ASP.Net.
Any ideas?
Best regards
Johan
