SQL Stored Proc Function

spyke01 · #1 (**permalink**) 06-10-08, 12:13 PM

Hi guys, i'm trying to create a function that will handle calling stored procedures and then return a recordset to the calling code.

I found out that you cant pass the parameters as a string array and then do a for each and append them. So now im trying to find another way.

Basically what i need to be able to do is type something like

asp Code:

Set RS_createCustomer = Server.CreateObject("ADODB.RecordSet")
    
    ' Create Parameters
        '.Parameters.Append .CreateParameter("ReturnVal", adInteger, adParamReturnValue) 
        .Parameters.Append  .CreateParameter("@CustomerEnteredBy", adVarChar, adParamInput, 100, ae_num)
        .Parameters.Append  .CreateParameter("@CustomerName", adVarChar, adParamInput, 100, Customer)
        .Parameters.Append  .CreateParameter("@CustomerNumber", adVarChar, adParamInput, 6, cust_num)
        .Parameters.Append  .CreateParameter("@CustomerContactFirstName", adVarChar, adParamInput, 20, cust_fname)
        .Parameters.Append  .CreateParameter("@CustomerContactLastName", adVarChar, adParamInput, 25, cust_lname)
        .Parameters.Append  .CreateParameter("@CustomerContactEmail", adVarChar, adParamInput, 100, cust_email)
        .Parameters.Append  .CreateParameter("@CustomerAddress1", adVarChar, adParamInput, 100, cust_address1)
        .Parameters.Append  .CreateParameter("@CustomerAddress2", adVarChar, adParamInput, 100, cust_address2)
        .Parameters.Append  .CreateParameter("@CustomerCity", adVarChar, adParamInput, 35, cust_city)
        .Parameters.Append  .CreateParameter("@CustomerState", adChar, adParamInput, 2, cust_state)
        .Parameters.Append  .CreateParameter("@CustomerZip", adVarChar, adParamInput, 10, cust_zip)
        .Parameters.Append  .CreateParameter("@CustomerPhone1", adVarChar, adParamInput, 15, cust_phone1)
        .Parameters.Append  .CreateParameter("@CustomerPhone2", adVarChar, adParamInput, 15, cust_phone2)
        .Parameters.Append  .CreateParameter("@CustomerIsNew", adInteger, adParamInput, 1, cust_new)
        .Parameters.Append  .CreateParameter("@CustomerNotes", adLongVarChar, adParamInput, 2, cust_notes)
    
    
    RS_createCustomer = execQuery("CreateCustomerRecord", Cmd_createCustomer)

And then the function will handle using the parameters and then function will use the parameters sent to it. heres the function i have so far

asp Code:

'===================================================================
    ' SQL Wrapper
    '===================================================================
    Function execQuery(ProcedureName, Params)      
        ' Create our Customer row in the DB and fill it in
        Set DataConn_execQuery = Server.CreateObject("ADODB.Connection")
        Set Cmd_execQuery = Server.CreateObject("ADODB.Command")
        Set RS_execQuery = Server.CreateObject("ADODB.RecordSet")
        Set prm = Server.CreateObject("ADODB.Parameter")
            
        DataConn_execQuery.Open MAIN_DATABASE_CONNECTION_STRING
        DataConn_execQuery.CommandTimeout=10
        
        With Cmd_execQuery
            .ActiveConnection = DataConn_execQuery
            .CommandText = ProcedureName
            .CommandType = 4                        
        End With
        
        ' Execute the query and set the result set variable
        RS_execQuery = Cmd_execQuery.Execute
        
        'Close objects
        Set Cmd_execQuery = Nothing
        DataConn_execQuery.Close
        Set DataConn_execQuery = Nothing
        
        ' Return our result
        execQuery = RS_execQuery
    End Function

I'm at a loss as to how to do this, any ideas?

spyke01 · #2 (**permalink**) 06-10-08, 01:41 PM

this is a revised semi working version

asp Code:

' Create our Customer row in the DB and fill it in
    Set RS_createCustomer = Server.CreateObject("ADODB.RecordSet")
    Dim ParamsArray(15)
    
    ' Create Parameters
    ParamsArray(0) = """@CustomerEnteredBy"", " & adVarChar & ", " & adParamInput & ", 100, " & ae_num
    ParamsArray(1) = """@CustomerName"", " & adVarChar & ", " & adParamInput & ", 100, " & Customer
    ParamsArray(2) = """@CustomerNumber"", " & adVarChar & ", " & adParamInput & ", 6, " & cust_num
    ParamsArray(3) = """@CustomerContactFirstName"", " & adVarChar & ", " & adParamInput & ", 20, " & cust_fname
    ParamsArray(4) = """@CustomerContactLastName"", " & adVarChar & ", " & adParamInput & ", 25, " & cust_lname
    ParamsArray(5) = """@CustomerContactEmail"", " & adVarChar & ", " & adParamInput & ", 100, " & cust_email
    ParamsArray(6) = """@CustomerAddress1"", " & adVarChar & ", " & adParamInput & ", 100, " & cust_address1
    ParamsArray(7) = """@CustomerAddress2"", " & adVarChar & ", " & adParamInput & ", 100, " & cust_address2
    ParamsArray(8) = """@CustomerCity"", " & adVarChar & ", " & adParamInput & ", 35, " & cust_city
    ParamsArray(9) = """@CustomerState"", " & adChar & ", " & adParamInput & ", 2, " & cust_state
    ParamsArray(10) = """@CustomerZip"", " & adVarChar & ", " & adParamInput & ", 10, " & cust_zip
    ParamsArray(11) = """@CustomerPhone1"", " & adVarChar & ", " & adParamInput & ", 15, " & cust_phone1
    ParamsArray(12) = """@CustomerPhone2"", " & adVarChar & ", " & adParamInput & ", 15, " & cust_phone2
    ParamsArray(13) = """@CustomerIsNew"", " & adInteger & ", " & adParamInput & ", 1, " & cust_new
    ParamsArray(14) = """@CustomerNotes"", " & adLongVarChar & ", " & adParamInput & ", 2, " & cust_notes
    
    ' Execute our stored procedure
    RS_createCustomer = execQuery("CreateCustomerRecord", ParamsArray)

asp Code:

'===================================================================
    ' SQL Wrapper
    '===================================================================
    Function execQuery(ProcedureName, Params)      
        ' Create our Customer row in the DB and fill it in
        Set DataConn_execQuery = Server.CreateObject("ADODB.Connection")
        Set Cmd_execQuery = Server.CreateObject("ADODB.Command")
        Set RS_execQuery = Server.CreateObject("ADODB.RecordSet")
        Set prm = Server.CreateObject("ADODB.Parameter")
            
        DataConn_execQuery.Open MAIN_DATABASE_CONNECTION_STRING
        DataConn_execQuery.CommandTimeout=10
        
        With Cmd_execQuery
            .ActiveConnection = DataConn_execQuery
            .CommandText = ProcedureName
            .CommandType = 4                        
        End With
        
        ' Create Parameters
        if UBound(Params) > 0 then
            for each key in Params
                response.write key & "<br />"
                key = split(key, ",")
                
                if key(4) <> "" then
                    prmValue = key(4)
                else
                    prmValue = ""
                end if 
                    
                With prm
                    .Name = key(0)
                    .Type = key(1)
                    .Direction = key(2)
                    .Size = key(3)
                    .Value = prmValue
                End With
                
                Cmd_execQuery.Parameters.Append prm
            next
        end if
        
        ' Execute the query and set the result set variable
        RS_execQuery = Cmd_execQuery.Execute
        
        'Close objects
        Set Cmd_execQuery = Nothing
        DataConn_execQuery.Close
        Set DataConn_execQuery = Nothing
        
        ' Return our result
        execQuery = RS_execQuery
    End Function

However, im getting

Quote:

ADODB.Parameter error '800a0c93'

Operation is not allowed in this context.

/byob08_dev2/includes/functions.asp, line 294

which refers to

Quote:

.Name = key(0)

spyke01 · #3 (**permalink**) 06-10-08, 02:28 PM

nm, heres the solution for anyone else who needs it

asp Code:

'===================================================================
    ' handles conversion for execQuery Function
    '===================================================================
    Function execQueryConvertString(iType, value)
        ' varChar
        if iType = 200 then
            if value = "" then
                retVal = trim(CStr(""))
            else
                retVal = trim(CStr(value))
            end if
        ' Char
        elseif iType = 129 then
            if value = "" then
                retVal = trim(CStr(""))
            else
                retVal = trim(CStr(value))
            end if    
        ' Int
        elseif iType = 3 then
            if value = "" then
                retVal = trim(CInt(""))
            else
                retVal = trim(CInt(value))
            end if  
        ' LongVarChar
        elseif iType = 201 then
            if value = "" then
                retVal = trim(CStr(""))
            else
                retVal = trim(CStr(value))
            end if    
        end if
        
        execQueryConvertString = retVal
    End Function
    
    '===================================================================
    ' SQL Wrapper
    '===================================================================
    Function execQuery(ProcedureName, Params)      
        ' Create our Customer row in the DB and fill it in
        retVal = 0
        Set DataConn_execQuery = Server.CreateObject("ADODB.Connection")
        Set Cmd_execQuery = Server.CreateObject("ADODB.Command")
        Set RS_execQuery = Server.CreateObject("ADODB.RecordSet")
            
        DataConn_execQuery.Open MAIN_DATABASE_CONNECTION_STRING
        DataConn_execQuery.CommandTimeout=10
        
        With Cmd_execQuery
            .ActiveConnection = DataConn_execQuery
            .CommandText = ProcedureName
            .CommandType = 4    
            
            .Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)     
        End With
        
        ' Create Parameters
        if isArray(Params) then
            for each key in Params
                key = split(key, ",")
                
                if UBound(key) <> -1  then
                    if UBound(key) > 3 then
                        if key(4) <> "" then
                            prmValue = execQueryConvertString(key(1), key(4))
                        else
                            prmValue = execQueryConvertString(key(1), "")
                        end if     
                    else 
                        prmValue = execQueryConvertString(key(1), "")
                    end if
                    
                    Set prm = Server.CreateObject("ADODB.Parameter")
                    With prm
                        .Name = key(0)
                        .Type = key(1)
                        .Direction = key(2)
                        .Size = key(3)
                        .Value = prmValue
                    End With
                    
                    Cmd_execQuery.Parameters.Append prm
                end if
            next
        end if
        
        ' Execute the query and set the result set variable
        RS_execQuery = Cmd_execQuery.Execute
        
        ' Assign result to retVal       
        If CInt(Cmd_execQuery.Parameters("RETURN_VALUE")) <> 0 Then
            'Do Error stuff here
            retVal = "Error"
        End If
        
        'Close objects
        Set Cmd_execQuery = Nothing
        DataConn_execQuery.Close
        Set DataConn_execQuery = Nothing
        
        ' Return our result
        execQuery = retVal
    End Function