This tip comes from Pete Draigh
When I started developing web pages that interact with a SQL Server database, I probably started like everbody else: with inline SQL statements. I then progressed to using the connection object to call stored procedures and eventually started using the command object. I eventually realized how useful return values from stored procedures could be, since I could use them to return a value based on a potential error condition that I check for in the stored procedure.
Recently, I was developing an online catalog and had a situation to deal with:
- User enters data into a form
- Need to insert the data into a SQL Server database after checking to make sure various conditions don't exist. For example, the user could enter a product, but only if the product doesn't already exist in the catalog. That's not something that's easily accomplished with client-side validation!
Initially I decided upon a fairly popular route: create a form in
Page1.asp that submits to
Page2.asp which attempts to insert the user-entered information into the database. If the
product already exists, go back to
Page1.asp, displaying a message and populating the fields
with what the user entered. While this is a possible approach, trust me when I say that it's a pain to
code if you have a lot of form fields! Ideally I wanted a pop-up message that I could customize based on
the condition found in the stored procedure. (I like pop-ups because by their nature, they draw more
attention than a message displayed on a page.) Also, I wanted the user taken back to
with all of his/her entries already filled in.
Here is an example stored procedure that returns an error result if something goes awry:
A simple sample stored procedure that checks to see if the product already exists. If so, it returns
55555, otherwise it inserts the product and returns the error code (which will
0 if successful).
Now, on the ASP side, I use the command object to send the form contents to the stored procedure.
The stored procedure's return value is always the first item in the parameters collection of the command
cmd.parameters(0)) after the command object's
Execute method has been called in this case.
At this point the stored procedure has been executed and
cmd.parameters(0) contains the return
value. Now, if the return value is not
0, meaning some sort of error occurred, we wish to
if there is no error, send the user on to some other page, one that, perhaps, displays a confirmation message
of the database action just performed.
a pop-up message; once this popup message is read and the OK button is clicked, it should take the user
MyError essentially sends back a small HTML page that pops a message and then goes back a page
when the user clicks the 'OK' button on the pop-up. The users entries are left intact when going back to
the previous page when using this method. Maybe the case is they mistakenly entered some value and now they
can edit it instead of having to retype everything. Furthermore, you don't have to write any code to
re-populate their form entries.
Some of the situations where I've used this are when trying to delete records that may be parent records to existing children (I don't like cascading deletes), when trying to edit records that have been flagged for deletion, etc. It's useful anytime your SQL statement should fail or be aborted and you want to notify the user and make it easy for them to go back to where they were. You can include this into all of your pages that run "action queries" and create your own set of custom messages. Just add a new case to your case statement for each possible return code.
If you have any feedback or questions about this technique, please email me at firstname.lastname@example.org.
|Return to user tips...|