Calling SQL Server SP From ASP

Dev Shed Forums Sponsor:

Dell PowerEdge Servers

May 20th, 2003, 05:33 PM

cdarling

Junior Member

Join Date: Jan 2003

Posts: 29

Time spent in forums: < 1 sec
Reputation 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?

May 21st, 2003, 06:36 AM

Shiju Rajan

.Net Developer

Join Date: Feb 2000

Location: London

Posts: 987

Time spent in forums: 3 h 26 m 22 sec
Reputation Power: 9

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

%>

__________________
SR -
webshiju.com
www.lizratechnologies.com

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

Last edited by Shiju Rajan : May 21st, 2003 at 06:39 AM.

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Calling SQL Server SP From ASP

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!