Executing a Stored Procedure using ASP.

treadmill · July 10th, 2003, 06:29 AM

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

nidgep · July 10th, 2003, 06:47 AM

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

pgtips · July 10th, 2003, 06:48 AM

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

wolverinero79 · July 24th, 2003, 04:21 PM

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