Rollback and Commit command in Transaction?

Sandeep Shekhawat

I have a field in table that is auto increment. There increment and seed is (1,1) .So My first row enter in database table then this field value is 1. After that i am inserting second row but from the catch block my transaction is rollback(i am using commit in try block after insert statement.). so my second row did not insert. now i am insert again a row that time it inserted and my auto increment field value is 3 and total rows are 2. that is my problem "If row does not insert in to database table then why increment auto increment field of database table ".

If i am say something wrong pls ignore it.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

ashrafuddin15y ago
Dear Sandeep,

I think you can use Transaction Scope in DotNet.It will help you in checking that the record is not inserted unless all the transaction is complete.
Take a look at following link

http://www.codeproject.com/KB/dotnet/TransactionScope20.aspx

With Regards,
Ashrafuddin.T
Sandeep Shekhawat15y ago

Hi Suthish Sir

select @username=user_name,@password=passowrd from dbo.LoginInfo(@mail)
insert into tbl_login(user_name, password, blitzacc_no) values(@username,@password,@account_no)

By this query when user name already exists then second inserting is failed and whole transaction rollback.
Suthish Nair15y ago
You need to identify why second insertion getting failed.
Sandeep Shekhawat15y ago
Please give me any solution or suggestion ? I am using Sql Server 2005.
Sandeep Shekhawat15y ago
Hi Dorababu
My Store procedure is (parameter a lot of so ignore parameter).

ALTER procedure [dbo].[sp_liveaccount]
@name varchar(30), @sur_name varchar(30), @dob varchar(10),
@address varchar(max), @pin varchar(10), @country_id int,
@telph_work varchar(15), @telph_home varchar(15), @mobile varchar(50),
@fax varchar(50), @mail varchar(30), @bank_name varchar(30),
@bank_address varchar(max),@account_name varchar(30),@account_number varchar(30),@swift_code varchar(30),
@website varchar(30),@deal bit,@experience bit,
@tradtypeexp varchar(50),@FX varchar(5),@cfd varchar(5),
@indicies varchar(15), @metals varchar(15),@future varchar(10),
@acc_purpose varchar(15),@other varchar(max),@declaration bit,
@hearus_id int,@gender varchar(10),@regaddress varchar(max),@city varchar(30)

as
declare @account_no varchar(50)
declare @username varchar(30)
declare @password varchar(30)
begin try
begin tran LiveAccount

select @account_no= dbo.AccountNumber()
insert into tbl_liveaccount( blitzacc_no,name,sur_name, dob, address, pin,
country_id, telph_work, telph_home, mobile, fax, mail, bank_name, bank_address,
account_name, account_number, swift_code, website, deal, experience, tradingtypeexp,
FX, cfd, indicies, metals, future, acc_purpose, other, declaration, hearus_id, reg_date,gender,regaddress,city)
values(@account_no,@name,@sur_name,@dob,@address,@pin,@country_id,@telph_work,@telph_home,
@mobile,@fax,@mail,@bank_name,@bank_address,@account_name,@account_number,@swift_code,@website,
@deal,@experience,@tradtypeexp,@FX,@cfd,@indicies,@metals,@future,@acc_purpose,@other,@declaration,@hearus_id,
convert(varchar(10),getdate(),103),@gender,@regaddress,@city)

select @username=user_name,@password=passowrd from dbo.LoginInfo(@mail)
insert into tbl_login(user_name, password, blitzacc_no) values(@username,@password,@account_no)
commit tran LiveAccount
end try

begin catch
rollback tran LiveAccount
end catch
Dorababu Meka15y ago
Can you show us the query you have written