User Tips: Using Return Values from a SQL Server Stored Procedure to Customize Error Messages

來源:互聯網
上載者:User
error|server 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 validate the entries (easy enough with client-side javascript)

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 Page1.asp with all of his/her entries already filled in.

Here is an example stored procedure that returns an error result if something goes awry:

Create Procedure [Proc_InsertProduct]
(
    @productname varchar(50) = 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 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 object (cmd.parameters(0)) after the command object's Execute method has been called in this case.

<%
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 you 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 will generate a JavaScript popup error message. Otherwise, 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.

<%
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 message is read and the OK button is clicked, it should take the user back to Page1.asp.

<%
function MyError(errorcode)
  dim title, message

  select case errorcode
   &nbs



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。