Using Stored Procedure in Classical Asp .. Execute and Get Results

Using Stored Procedure in Classical ASP .. execute and get results

Looked at this for a few minutes, and it's been a long time since I've worked with classic asp, but I did see three things to look at:

1. Do you need to Open the connection before calling objCommandSec.Execute?
2. Can you try writing out a string literal inside the loop, that does not depend at all on the recordset... only that you are in fact looping through the code, so see if records are coming back to the recordset.
3. Have you checked the html source, to see if perhaps malformed html is hiding your results? I remember this happening a few times with tables in classic asp loops, where data would be hidden somehow between two rows, or a closing table tag in the wrong place would end the table, and later rows would not be visible.

execute stored procedure in classic asp and return values

If I got your meaning...then do like below:

 SqlConnection con = new SqlConnection("Data Source=  ; initial catalog= Northwind ; User Id=  ; Password=  '");

        con.open();

After creating SqlConnection you will create Storedprocedure like below:

        CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,@RegionDescription NCHAR(50)) AS
        SET NOCOUNT OFF
        UPDATE Region
        SET RegionDescription = @RegionDescription

Create a SqlCommand object with the parameters as the name of the stored procedure that is to be executed and the connection object con to which the command is to be sent for execution.

        SqlCommand command = new SqlCommand("RegionUpdate",con);

Change the command objects CommandType property to stored procedure.

    command.CommandType = CommandType.StoredProcedure;

Add the parameters to the command object using the Parameters collection and the SqlParameter class.

        command.Parameters.Add(new SqlParameter("@RegionID",SqlDbType.Int,0,"RegionID"));

        command.Parameters.Add(new SqlParameter("@RegionDescription",SqlDbType.NChar,50,"RegionDescription"));

Specify the values of the parameters using the Value property of the parameters

        command.Parameters[0].Value=4;

        command.Parameters[1].Value="SouthEast";

Excecute the stored procedure using the ExecuteNonQuery method which returns the number of rows effected by the stored procedure.

         int i=command.ExecuteNonQuery();

The code above is the right way...You can replace @KeepSTDNT and @RemoveSTDNT exactly instead of @RegionID and @RegionDescription...I have used it myself...if you wanna more help...tell then

Classic ASP - ADO execute Stored Procedure passing in parameters

Here is how you would do it, you won't need to create a recordset object since it is an update stored procedure:

'Set the connection
'...............

'Set the command
DIM cmd
SET cmd = Server.CreateObject("ADODB.Command")
SET cmd.ActiveConnection = Conn


'Prepare the stored procedure
cmd.CommandText = "apUpdateBill"
cmd.CommandType = 4  'adCmdStoredProc

cmd.Parameters("@RECORD_NUMBER") = Request.Form("Record_Number") 
cmd.Parameters("@ErrorType") = Request.Form("ErrorType") 
cmd.Parameters("@INSURANCE_CODE") = Request.Form("INSURANCE_CODE")
cmd.Parameters("@CompanyId") = Request.Form("CompanyID") 
cmd.Parameters("@INS_ID_NUM") = Request.Form("INS_ID_NUM")

'Execute the stored procedure
'This returns recordset but you dont need it
cmd.Execute

Conn.Close
SET Conn = Nothing

Execute Stored Procedure from Classic ASP

Try

With objCommandSec
 Set .ActiveConnection = Conn
 .CommandType = 4
 .CommandText = "theStoredProc"
 .Parameters.Append .CreateParameter("@inVar1", 200, 1, 255, VALUE1)
 .Parameters.Append .CreateParameter("@inVar2", 200, 1, 255, VALUE2)
 .Parameters.Append .CreateParameter("@outVar1", 200, 2, 255)
 .Parameters.Append .CreateParameter("@outVar2", 200, 2, 255)

 .Execute

 Response.Write .Parameters(3).Value
End With 

You should also avoid .Refresh if you know the parameter details as it involves a trip back to the server.

Not capturing return value from stored proc in asp classic

The issue is the mix up between the use of OUTPUT and RETURN in stored procedures in SQL Server.

To clear things up, your stored procedure is expecting an OUTPUT parameter instead of a RETURN value which is something different.

Try changing your output parameter statement to;

objCmd.Parameters.Append objCmd.CreateParameter("@UserNameExists", adInteger, adParamOutput)

For reference adParamReturnValue is used to return the value of the RETURN statement if used and is limited to INT values.

---

Related Topics