vaidyanathanpc
Programmer
Hi,
I need some help in using transactions. I'm running a transaction in ASP as shown in the code below which inserts data into two tables.
Dim conSQL
Dim conSQL1
Dim strSQL
Set conSQL = Server.CreateObject ("ADODB.Connection"
conSQL.Open Application("AppConnectString"
conSQL.BeginTrans
strSQL = "INSERT INTO " &_
" TAB1 " &_
"VALUES " &_
" (1,'TAB1')"
conSQL.Execute strSQL
strSQL = "INSERT INTO " &_
" TAB2 " &_
"VALUES " &_
" (1,1,'Tab2')"
conSQL.Execute strSQL
conSQL.CommitTrans
conSQL.Close
I'm doing an insert into TAB1 and TAB2 using transactions but the values provided are such that the second insert statement fails but the first one succeeds. i.e The insert into TAB1 does not give an error but insert into TAB2 gives an error and the entire transaction rolls back and no rows are inserted into TAB1 and TAB2. i.e when I do a select * from TAB1 and TAB2, no rows are retrieved. This is when I try to insert rows through ASP.
Now I'm executing the same transaction through SQl Server Query analyzer as follows.
BEGIN TRANSACTION @TranName
INSERT INTO TAB1 VALUES (1,'TAB1')
INSERT INTO TAB2 VALUES (1,1,'TAB2')
COMMIT TRANSACTION @TranName
Here too the values provided are such that the second insert statement fails but the first one succeeds. i.e The insert into TAB1 does not give an error but insert into TAB2 gives an error. But when the transaction is performed, the first insert commits data i.e data is inserted into TAB1 but no data is inserted into TAB2. i.e when I do a select * from TAB1 and TAB2, TAB1 retrieves one row but TAB2 retrieves zero rows.
How can this be possible? I am doing the same transaction using ASP and through SQL Server Query analyzer but the results I'm getting are different.
Thanks in advance
P.C. Vaidyanathan
I need some help in using transactions. I'm running a transaction in ASP as shown in the code below which inserts data into two tables.
Dim conSQL
Dim conSQL1
Dim strSQL
Set conSQL = Server.CreateObject ("ADODB.Connection"
conSQL.Open Application("AppConnectString"
conSQL.BeginTrans
strSQL = "INSERT INTO " &_
" TAB1 " &_
"VALUES " &_
" (1,'TAB1')"
conSQL.Execute strSQL
strSQL = "INSERT INTO " &_
" TAB2 " &_
"VALUES " &_
" (1,1,'Tab2')"
conSQL.Execute strSQL
conSQL.CommitTrans
conSQL.Close
I'm doing an insert into TAB1 and TAB2 using transactions but the values provided are such that the second insert statement fails but the first one succeeds. i.e The insert into TAB1 does not give an error but insert into TAB2 gives an error and the entire transaction rolls back and no rows are inserted into TAB1 and TAB2. i.e when I do a select * from TAB1 and TAB2, no rows are retrieved. This is when I try to insert rows through ASP.
Now I'm executing the same transaction through SQl Server Query analyzer as follows.
BEGIN TRANSACTION @TranName
INSERT INTO TAB1 VALUES (1,'TAB1')
INSERT INTO TAB2 VALUES (1,1,'TAB2')
COMMIT TRANSACTION @TranName
Here too the values provided are such that the second insert statement fails but the first one succeeds. i.e The insert into TAB1 does not give an error but insert into TAB2 gives an error. But when the transaction is performed, the first insert commits data i.e data is inserted into TAB1 but no data is inserted into TAB2. i.e when I do a select * from TAB1 and TAB2, TAB1 retrieves one row but TAB2 retrieves zero rows.
How can this be possible? I am doing the same transaction using ASP and through SQL Server Query analyzer but the results I'm getting are different.
Thanks in advance
P.C. Vaidyanathan