Handling SQL Server errors

[achmo]

Hello,

I am using a recordset and command to activate a stored procedure on SQL Srver. I pass a date as a parameter to the stored procedure, and I wanted to know what to do when errors occure. Lets say the date I pass is not in a recognizable format, I get a error on the asp page from the SP. What I want to do is continue normally when there is no error, but show an appropriate message if there was an error.

----setting rs and cmd1 here-----
cmd1.CommandText="LibraryGetSentOrders"
cmd1.CommandType=4
cmd1.Parameters.Append cmd1.CreateParameter("id1",200,1,30,startDate)
set rs=cmd1.Execute ------> HERE I MIGHT GET THE ERROR

How can I check if there was an error *before* the page "cannot be displayed"?

thanks,
Yael

 

[achmo]

just to clarify - this is only the relevant part of the code, the recordset and command are defined before, and I normally dont get errors. I just wanted to know how to handle errors that result from the data I pass.

Yael

 

[dds82]

Are you sure that that's the exact line that the error is reported to be on? Or does the error actually come later, when you try to display information from an empty recordset?

 

[JuanitaC]

There are two things that I can think of to handle this type of situation:

1) Validate all submissions so that errors cannot occur

2) At the top of your page, put ON ERROR RESUME NEXT, then check for errors at certain points like this:

----setting rs and cmd1 here-----
cmd1.CommandText="LibraryGetSentOrders"
cmd1.CommandType=4
cmd1.Parameters.Append cmd1.CreateParameter("id1",200,1,30,startDate)
set rs=cmd1.Execute '--> HERE I MIGHT GET THE ERROR
if Err<>0 then
'place error handling code here
else
'continue as normal
end if

 

[achmo]

thank you JuanitaC, it looks like a good solution.

 

[Funka]

when i am specifically looking for errors at error-prone points, i will put the ON ERROR RESUME NEXT just before the questionable code, and then follow my error checking with ON ERROR GOTO 0 -- this is ASP's silly way of saying to discontinue the &quot;resume next&quot; -- so that any other errors on the page will be alerted instead of ignored...

good luck!

 

[achmo]

hey, good idea! I *was* sorry to lose all the *other* error messages.

Yael

 

[codestorm]

gotta love try/catch codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>