Wrox Programmer Forums
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 10th, 2003, 06:29 AM
Registered User
Join Date: Jun 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Executing a Stored Procedure using ASP.

Hi All,

Can someone let me know how to get ASP to execute a Stored Procedure and also how to send parameters to the procedure.

I've started with the following, but I can't get it to work and get the error:

Object doesn't support named arguments: 'cn.ActiveConnection'

/includes/van_drivers.asp, line 14

WTD_DB refers to a DSN string which then refers to an ODBC connection already set-up. This does work as I use WTD_DB for the rest of my application, which works perfectly.

Set cn = Server.CreateObject("ADODB.Connection")
cn.ActiveConnection = WTD_DB

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = cn
objCmd.CommandText = "sp_CreateVanDriver"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters("EMPLOYEE_NO") = Request.Form("EMPLOYEE_NO")
Set objCmd = Nothing

Any ideas?


Old July 10th, 2003, 06:47 AM
Authorized User
Join Date: Jun 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts

HI treadMill

Here is some sample code that I have used in the past.
This function is used to add an address to the database.

The connection string is held in another function named dbConnString() and is therefore used to return the connection details.
[Not included here]

Its not perfect but it may help!
Hope it makes sense.


Private Function addAddress(strAddrL1,strAddrL2,strAddrL3,strHomeTo wn, strHomeCity,intCountry, strAddrCode, strHPhone, strMPhone, strWPhone, _
    intAddrType, lngNewAddressID, strErrMsg)

    Dim objConn, objCmd, objParam, objRS, intParamIndex

    set objConn = server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = dbConnString()
    objConn.CursorLocation = adUseServer
    If objConn.State And adStateOpen Then 'the connection is open
        set objCmd = Server.CreateObject("ADODB.Command")
        With objCmd
            .ActiveConnection = objConn
            .CommandType = adCmdStoredProc
            .CommandText = "usp_ins_param_address" 'name of QRY or SP
            set objParam = .CreateParameter("Line1",adChar,adParamInput,"100" ,strAddrL1)
            .Parameters.Append objParam
            set objParam = .CreateParameter("Line2",adChar,adParamInput,"100" ,strAddrL2)
            .Parameters.Append objParam
            set objParam = .CreateParameter("Line3",adChar,adParamInput,"100" ,strAddrL3)
            .Parameters.Append objParam
            set objParam = .CreateParameter("Town",adChar,adParamInput,"100", strHomeTown)
            .Parameters.Append objParam
            set objParam = .CreateParameter("City",adChar,adParamInput,"100", strHomeCity)
            .Parameters.Append objParam
            set objParam = .CreateParameter("Country",adInteger,adParamInput, ,intCountry)
            .Parameters.Append objParam
            set objParam = .CreateParameter("AddrCode",adChar,adParamInput,"1 5",strAddrCode)
            .Parameters.Append objParam
            set objParam = .CreateParameter("HomePhone",adChar,adParamInput," 20",strHPhone)
            .Parameters.Append objParam
            set objParam = .CreateParameter("MobilePhone",adChar,adParamInput ,"20",strMPhone)
            .Parameters.Append objParam
            set objParam = .CreateParameter("WorkPhone",adChar,adParamInput," 20",strWPhone)
            .Parameters.Append objParam
            set objParam = .CreateParameter("AddrType",adInteger,adParamInput ,,intAddrType)
            .Parameters.Append objParam
            'completed creating the params, so discard the object
            set objParam = nothing
            On Error Resume Next
            .Execute 'the command to insert the address
                If Err.number <> 0 Then
                    strErrMsg = Err.Description & "Source : " & Err.Source & _
                                    "<br />Error in Module: dbFuncs.addAddress()" & _
                                    "<br />Caused a RollBackTrans() while inserting customer address."
                    addAddress = 1
                    set objConn = Nothing
                    set objCmd = Nothing
                    Exit Function
                End If

                'now get the address ID just created using pre-written query
                .CommandText = "usp_sel_getMaxAddressID" 'name of MS Access query
                set objRS = .Execute 'the command

                If not objRS.EOF then
                    'get the new id and assign it to the parameter passed in
                    lngNewAddressID = objRS.Fields("MaxOfAddr_ID")
                    addAddress = 0
                    'force a rollback
                    strErrMsg = "Error in Module: dbFuncs.addAddress()" & _
                                    "Unable to retrieve AddressID. Caused a RollBackTrans()<br />" & _
                                    "The recordset was empty.<br />"
                    lngNewAddressID = -1
                    addAddress = 1
                    set objConn = Nothing
                    set objCmd = Nothing
                    Exit Function
                End If
            'commit the changes to the database

            On Error GoTo 0 'turn error handling back on

        End With 'objCmd

        set objRS = nothing
        set objCmd.ActiveConnection = nothing
        set objCmd = nothing
        'no connection
        strErrMsg = "Error in Module: dbFuncs.addAddress()" & _
                        "Unable to connect to the database."
        addAddress = 1 'bad return code
    End If
    On Error GoTo 0 'turn error handling back on
    If not objConn is nothing then
        if objConn.State then objConn.Close()
        set objConn = nothing
    End if

End Function 'addAddress
Old July 10th, 2003, 06:48 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post

The connection object doesn't have an ActiveConnection property, that's why you get the error. Just ditch that line and use Call cn.Open(WTD_DB).

Re the parameters: you must create a parameter object for each parameter in your stored procedure, you can't just set its values.
Dim params
Set params = objCmd.Parameters
params.Append objCmd.CreateParameter("EMPLOYEE_NO", datatype, direction, size, value)
watch out for those datatype and direction parameters though. You'll see loads of examples using things like adVarChar and adParamInput but ASP doesn't know what those constants mean unless you either:
1. explicitly declare them yourself (or include adovbs.inc if you want overkill!)
2. add a ref to the ADO typelib

Old July 24th, 2003, 04:21 PM
Registered User
Join Date: Jul 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts

After looking at some of the ADODB.Command posts in the archives, I thought people might need to know a couple things in general (considering that many of the posts involving the command object never got resolved).

One of the greatest reasons to use ADODB.Command is to be able to not worry about single quotes (') destroying your SQL statement. This comes into play most often when using a database that contains people's names. True, quote stuffing is that long of a function to write, but I still enjoy using Command

However, many people often try using customized SQL statements with the ADODB.Command object. Generally, you want to have stored procedures running asp SQL code in the first place (due to performance issues). Doing stored procedures with ADODB.Command is much easier than customized SQL strings.

I hope this is helpful to other people, like myself, who often just float around the search engines finding topics on wrox pages that never tell me exactly what i wish to know :)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing an UPDATE Stored Procedure ashg657 Access VBA 1 June 9th, 2006 06:29 AM
working with stored procedure and asp rashoova Classic ASP Databases 0 February 1st, 2006 10:19 AM
Create and executing an oracle stored procedure Venstar Oracle 1 January 10th, 2006 05:10 AM
Executing a stored procedure from ASP tlbacon527 Classic ASP Databases 2 January 23rd, 2004 03:50 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.