#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    29
    Rep Power
    0

    Calling SQL Server SP From ASP


    Here's my neat, clean call to a SQL Server stored procedure:

    Code:
    <%
    Set Cmd = Server.CreateObject("ADODB.Command")
    With Cmd
       .ActiveConnection = "Provider=sqloledb;" & _
                           "Data Source=99.99.999.999;" & _
                           "Initial Catalog=xxx;" & _
                           "User ID=xxx;" & _
                           "Password=xxxx"
       .CommandText = "Get_Customer" ' name of stored procedure
       .CommandType = adCmdStoredProc ' 4
    
       .Parameters.Append .CreateParameter _
                ("@PIN", adVarWChar, adParamInput, 13, "111 111-1234")
    
       .Parameters.Append .CreateParameter _
                ("@ErrorCode", adVarWChar, adParamReturnValue, 15)
    
       Set Rs_Customer = Server.CreateObject("ADODB.Recordset")
    
       On Error Resume Next
       Set Rs_Customer = .Execute
    
    %>
    Unfortunately, when the command object arrives at SQL Server, it seems to say "Exec Get_Customer;1"

    There's no sign of the input parameter value, and we can't figure out where the ";1" is coming from.

    With the second parameter commented out, this routine works perfectly calling an MS Access stored procedure. But something seems to be keeping the input parameter from being passed properly to SQL Server.

    Any ideas?
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    I have just created a function with a stored procedure calling example here. Try this example.

    PHP Code:
    <%
    Function 
    ProcessProcedure (pin)

         
    Dim StoredProcResponse        
        Dim rsGenRec
        Dim params

        StoredProcResponse 
    ""

        
    set rsGenRec Server.CreateObject("ADODB.Command")
        
    rsGenRec.ActiveConnection "Provider=sqloledb;" _
                                        
    "Data Source=99.99.999.999;" _
                                        
    "Initial Catalog=xxx;" _
                                        
    "User ID=xxx;" _
                                        
    "Password=xxxx"   
       
        
    rsGenRec.CommandText "Get_Customer"
        
    rsGenRec.CommandType adCmdStoredProc

        
    ' Set up the arguments
        Set params = rsGenRec.CreateParameter ("@PIN", adVarChar, adParamInput, 13, pin)
        rsGenRec.Parameters.Append params

        
        Set params = rsGenRec.CreateParameter ("@ErrorCode", adVarChar, adParamOutput, , 0)
        rsGenRec.Parameters.Append params

        ' 
    Execute the procedure and recover results
        
    'Set rsORS = rsGenRec.Execute       ' Used if we need the returned record set.
        
        
    rsGenRec.Execute
        StoredProcResponse 
    rsGenRec("@ErrorCode"' The output parameter
        set rsGenRec=nothing
         
                    
          ProcessProcedure = StoredProcResponse 

    end Function
    %> 
    Last edited by Shiju Rajan; May 21st, 2003 at 06:39 AM.
    SR -
    webshiju.com
    www.lizratechnologies.com

    "The fear of the LORD is the beginning of knowledge..."

IMN logo majestic logo threadwatch logo seochat tools logo