testing from asp if sql query was successful

Results 1 to 2 of 2

Thread: testing from asp if sql query was successful

  1. #1
    myrna Guest

    Default testing from asp if sql query was successful

    hi all<BR><BR>how can i trap a sql error from an asp page? <BR><BR>i have a stored procedure:<BR><BR>CREATE PROCEDURE select_all_records<BR>AS<BR><BR>/* quite a no. of processes here */<BR>...<BR>...<BR>SELECT * from dbMain.dbo.EMPLOYEES<BR><BR>------<BR><BR>i have mainPG.ASP page that calls the stored procedure:<BR><BR>set dbConnection = Server.CreateObject( "ADODB.Connection" )<BR>set myRecordset = Server.CreateObject( "ADODB.Recordset" )<BR>dbConnection.Open Application( "ConnectionString" )<BR>myRecordset = dbConnection.Execute("select_all_records")<BR><BR>-----<BR><BR>in my asp page..how can i test if the stored proc has been successfully executed?<BR>and if it has been unsuccessful, how can i rollback the stored proc.<BR><BR><BR>thanks a lot<BR>

  2. #2
    Sreedharan Surendran Guest

    Default RE: testing from asp if sql query was successful

    First of all, I am glad to see someone using a stored procedure. Most of the other persons seem to be using embedded SQL.<BR><BR>Now, when you write a stored procedure, you should first test it in Query Analyzer. That will give you a better error message, and you can debug quickly.<BR><BR>After the stored procedure runs successfully, you will still want to get error messages, which can be due to integrity violations. For this, the ADODB object will return the error. Use that. Ideally you should have a standard page to which you will redirect from within your current to show the error message. <BR><BR>As for rolling back, if there is only a single statement, then, if it fails, it of course has not made any change to the database, so you will not be worried about rolling back. But if you have multiple statements, then you need to be sure that all complete successfully before commiting. For this, at the beginning of the stored procedure, write "Begin Transaction". At the end, give a "Commit Transaction". Now, your different statements will commit only if it passes successfully through all the statements. In between, you can check as required for the error condition. Some errors will terminate the stored procedure, but some "soft" errors will only terminate the particular procedure. <BR><BR>Hope this is helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts