ASP Transactions vs. SQL Server Stored Procedures

[rtgordon]

My goal is to be able to insert two rows into two different tables. One is a detail table and the other is like a status history table (new, opened, assigned, etc.)

I would like to insert the new record into the detail table, and then, if that insert was sucessful, insert another record into the status history indicating that the record is new.

I can do this in ASP without any error checking, etc, but I would like to have some error checking that stops the transaction if the first fails, rolls back if the second fails, and redirects based on the results. My question is should I be exploring what transactions can do for me or looking into making a stored procedure? I don't see how to check the status of a sql statement with transactions. I haven't done anything with SQL server stored procedures either. So, if that is what I need to do, any links to examples/docu for getting started would be greatly appreciated.

Thanks!
Gordon

 

[bassguy]

HI,
I would certainly used a stored procedure. a stored proc can wrap several transactions into one action that can be committed upon Completion or rolled back on error. As for the method to see if it worked with your asp. I make an output parameter (an Integer) that is returned and then I have created my own list of error codes for the asp to decode.

Here is a bit of code for the Stored proc. This is just a simple add a row to a column type query, but you can see the return param stuff:




create proc CompetitorAdd
@Competitorvarchar(25) = null,
@retval varchar(50)=null output
as


/* if user submits without data in the textfield*/
if @Competitor is null
return 1

/* if user tries to duplicate an @ArtworkType */
else if (select count(*) from Competitor where Competitor
like @Competitor)>0
return 2

/* if success*/
else insert into Competitor(Competitor) values(@Competitor)
return 0



Here is the asp code that I use on the submit page



Set cn = Server.CreateObject("ADODB.Connection&quot
cn.Open = application("ModelInformation_connectionstring&quot
Set cmd = Server.CreateObject("ADODB.Command&quot
Set cmd.ActiveConnection = cn
cmd.CommandText = "{?=call competitoradd(?)}"
cmd.Parameters.Append cmd.CreateParameter("retval", adinteger,adParamreturnvalue,25)
cmd.Parameters.Append cmd.CreateParameter("competitor", advarchar,adParamInput,25)


cmd.Parameters("competitor&quot = request("competitor&quot
cmd.Execute
select case cmd("retval&quot
case 0
response.write &quot;<h3> Data Successfully Added</h3>&quot;
case 1
response.write &quot;<h3> You have an empty field, please hit the back button and fill in all fields</h3>&quot;
case 2
response.write &quot;<h3> This record Already Exists</h3>&quot;
end select
end if


I hope this can shed a bit of light on things

Bassguy

 

[rtgordon]

After spending a good portion of the day researching, I know I want to use a stored procedure. I found some pretty good examples in asp101.com and msdn. I thought that stored procdures would be out of the scope of asp101, but it turned out to be a decent article.

Thanks for the reply! Your method seems intuitive. Most of the other ways I saw today included @@error or COUNTTRAN. I'll keep looking into it, but I will probably do something like you did.

Thanks,
gordon

 

[rtgordon]

One more thing... on rolling back and commiting, is this handled in an asp transaction or within the stored procedure?

gordon