Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 10th, 2003, 06:29 AM
Registered User
 
Join Date: Jun 2003
Location: Banbury, Oxon, United Kingdom.
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
cn.Open()

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


Any ideas?

Thanks,

Treadmill
Reply With Quote
  #2 (permalink)  
Old July 10th, 2003, 06:47 AM
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Regards



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
    objConn.Open
    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
            objConn.BeginTrans()
            .Execute 'the command to insert the address
                If Err.number <> 0 Then
                    objConn.RollBackTrans()
                    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
                Else
                    'force a rollback
                    objConn.RollBackTrans()
                    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
            objConn.CommitTrans()

            On Error GoTo 0 'turn error handling back on

        End With 'objCmd

        set objRS = nothing
        set objCmd.ActiveConnection = nothing
        set objCmd = nothing
    Else
        '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
Reply With Quote
  #3 (permalink)  
Old July 10th, 2003, 06:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
Code:
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

hth
Phil
Reply With Quote
  #4 (permalink)  
Old July 24th, 2003, 04:21 PM
Registered User
 
Join Date: Jul 2003
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 :)

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:01 AM.


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