Error|server when I started developing Web pages this interact with a SQL Server database, I probably started like Everbod Y Else:with inline SQL statements. I then progressed to using the connection object to call stored procedures and eventually using the command object . I eventually realized how useful return values from stored procedures could is, since I could use them to return a value B ased on a potential error condition, I Check for in the stored procedure.
Recently, I was developing a online catalog and had a situation to deal with:
User enters data into a form
Need to validate the entries (easy enough with client-side JavaScript)
Need to insert the "data into a SQL Server database" 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 then submits to page2.asp which attempts to in SERT the user-entered information into the database. If the product already exists, go back to Page1.asp, displaying a and populating the fields with what the user ent Ered. While this is a possible approach, the 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. (I like pop-ups because by their nature, they draw the more attention than a message displayed on a page.) Also, I wanted the user taken back to page1.asp with all of his/her entries already filled in.
This is a example stored procedure that returns a error result if something goes:
Create Procedure [Proc_insertproduct]
(
@productname varchar = NULL,
@price money = null
}
As
If exists (select ProductName from tblproducts where ProductName = @productname)
Return 55555
Else
Insert INTO Tblproducts (ProductName, Price)
VALUES (@productname, @price)
return @ @error
A simple sample Stored procedure this checks to the if the product already exists. If so, it returns 55555, otherwise it inserts the product and returns the error code (which'll 0 if successful). Now in 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 ' the ' the ' the ' ' Parameters collection of the ' Command object (cmd.param Eters (0)) After the command object, Execute method, has been called in the.
<%
Option Explicit
Response.Buffer = True
Response.exires =-1441
Dim connect, cmd, returnvalue
Set connect = Server.CreateObject ("Adodb.connection")
Set cmd = Server.CreateObject ("Adodb.command")
Connect.open yourconnectionstring
Set cmd.activeconnection = Connect
Cmd.commandtype = 4 ' sp (I know, magic numbers, but add a comment and there go)
Cmd.commandtext = "Proc_insertproduct"
Cmd.parameters (1) = Request.Form ("ProductName")
Cmd.parameters (2) = Request.Form ("Price")
Cmd.execute
%>
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 call the Myerror subroutine, which would Generate a JavaScript popup error message. Otherwise, if there is no error, send the user in to some other page, one, perhaps, displays a confirmation message O f The database action just performed.
<%
returnvalue = cmd.parameters (0)
' Did an error occur?
If returnvalue <> 0
' Some sort of error occurred
Response.Write Myerror (returnvalue)
Else
' No errors ...
Response.Redirect ("whereever.asp")
End If
Set cmd = Nothing
Connect.close
Set connect = Nothing
%GT;
The Myerror subroutine now needs to use some Client-side Javascript code to send the user back a pop-up message; Once this popup is read and the OK button are clicked, it should take the user back to Page1.asp.
<%
function Myerror (errorcode)
Dim title, Message
Select Case ErrorCode
&nbs
