Begintransaction: read and write record

[jack.doe77]

Hi,

I'm using begintransaction to update multi-tables. I need to update one of the table while retrieving some records from that same table before commit. But it seem the table become locked after I inserted a record, and timeout at last.

I've looked begintransaction option and try all IsolationLevel with no luck. Is it possible to do that ? Thanks in advance.

[jack.doe77]

anyone ? please...

[pritesh2010]

huunnn. you are first start your transaction and in between you are retriving the data from the same table or query , so that time you get the error of that table is locked, right.
if yes then when you open the form that just iopen the connection. don't open with transaction. after taht retrive your recored where you want and on update button start your Transaction do what erver you want & close connection. if you now you want the some recored from the same table then then it will give you. for E.G.

protected sub button1_click()''rertrive the recored
'' it's just e.g.
con.open()
cmd=new SqlCommand("Select name from table where id= 1",con)
dim dr=new sqldataReader()
dr=cmd.ExequteReader()
if dr.HasRows=true then
while(dr.read())
textbox1.tet=dr(0).Tostring()
end while
else
''message
end if
cmd=nothing
dr.close()
con.close()
End sub

button 2 will update recorred

Protected sub button2_click
dim trans =new SqlTransaction
trans=con.BeginTransaction
''your update query
try
cmd=new sqlcommand("Update table set name='" & textbox1.text & "' where Id=1",con,trans)
cmd.ExecuteNonquery
trans.commit()
catch (ex As Exception)
trans.Rollback()
''give proper  message
End try
con.close()
End sub

Now on button3 i'll retive same data or other data

protected sub button3_click()''rertrive the recored
'' it's just e.g.
con.open()
cmd=new SqlCommand("Select name from table where id= 1",con)
dim dr=new sqldataReader()
dr=cmd.ExequteReader()
if dr.HasRows=true then
while(dr.read())
textbox1.tet=dr(0).Tostring()
end while
else
''message
end if
cmd=nothing
dr.close()
con.close()
End sub

try this

[jack.doe77]

Thanks for your reply.
I'm trying to create a Purchase Order (PO) and automatically create Pricelist from items in PO with some formula as new price, and previous price as LastPrice.
So, I used transaction to ensure my PO and Pricelist are created properly.
The problem is, after I inserted a record to PriceList table, I'm unable to read previous price from the same PriceList table for other item, as it became locked after first record inserted.