Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2002-03-22 : 07:13:14
|
| Hi everybody,My problem is as follows :I have an Unit of Measurement entry module in Visual Basic 6.0 - SQL Server 7.0 whose screen looks something like this :________________________________________________________________Unit of measurement : kgs----------------------------------------------------------------S/N.|UOM converted.|Part No.|Description............|.Conv. fact----------------------------------------------------------------..1.|pcs...........|214-1K00|Resistor 214 1K........|.......1000..2.|pcs...........|214-100K|Resistor 214 100K......|........700----------------------------------------------------------------<Save> <Cancel>----------------------------------------------------------------<Add> <Modify> <Delete> <Find> <Prev> <Next> <First> <Last> <Exit>_______________________________________________________________When the <Modify> command button is clicked, I lock the rows in the detail block by opening an ADO recordset using the command :ADORecordSet.Open "BEGIN TRAN SELECT * FROM Conv_Master WITH (HOLDLOCK, ROWLOCK) WHERE Conv_Master.uom = 'kgs '", ADOConnection, Options:=adCmdTextAfter the <Save> command button is clicked, I execute the SQL commands on the connection for saving the rows modified and added e.g.ADOConnection.Execute "INSERT INTO Conv_Master ( uom, uom_conv, part_no, conv_fact ) VALUES( 'kgs ', 'pcs ', '9010-902', 2 ) COMMIT TRAN"But this command doesn't execute (error message "Timeout expired"), apparently because the rows are locked. But this shouldn't happen because the BEGIN TRAN of the previous command is on the same connection. Only if I close the row locking recordset before executing this command, it works.On the other hand I have an Item-Party master entry module whose screen looks something like this :______________________________________________________________________________________Part No. : 214-1K00--------------------------------------------------------------------------------------S/N..Party code.....Party name.......................Status.............Party part no.--------------------------------------------------------------------------------------..1..SPA-0001.......A.K. Nimakwala...................Supplier...........RM-0001--------------------------------------------------------------------------------------<Save> <Cancel>--------------------------------------------------------------------------------------<Add> <Modify> <Delete> <Find> <Prev> <Next> <First> <Last> <Exit>______________________________________________________________________________________In this, on clicking the <Modify> command button, I lock the rows in the detail block by opening an ADO recordset using the command :ADORecordSet.Open "BEGIN TRAN SELECT * FROM ItemParty_Master WITH (HOLDLOCK, ROWLOCK) WHERE ItemParty_Master.part_no = '100-100K'", ADOConnection, Options:=adCmdTextAfter the <Save> command button is clicked, I execute the SQL commands on the connection for saving the rows modified and added e.g.ADOConnection.Execute "INSERT INTO ItemParty_Master ( part_no, party_code, party_part_no ) VALUES( '100-100K', 'MFA-0001', 'RM-0002 ' ) COMMIT TRAN"In this case this command works absolutely fine, as if all the commands were with the BEGIN TRAN of the first command and COMMIT TRAN of the second command.In both the entry modules I used the same locking and saving commands, but SQL Server seems to be reacting differently in them. What could be the problem ? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
Transaction problem