Solved

Classic ASP Parameterized Queries

Posted on 2008-09-30
1
2,184 Views
Last Modified: 2008-10-07
Although I am reluctant to give up this code ;-), I find myself stuck on something.

With this, I can fire off inline SQL and StoredProcedures by simply passing an array of values, an array of datatypes, the command type, command text, and connection string.

What I am looking to do now is to be able to get a return value from a stored procedure (mainly an ID, but ultimately a recordset)

Can anyone have a look and see if it can be done, or how it can be done?
Public Sub ExecuteQry(strQry, intCommandType, arrParamValues, arrParamDataTypes, arrReturn)  
Set objConn = CreateObject("Adodb.Connection")
 objConn.Open strConnString  
	 Set objCmd = CreateObject("ADODB.Command")  
		 objCmd.CommandText = strQry  
		 objCmd.CommandType = intCommandType  
		 If IsArray(arrParamValues) And IsArray(arrParamDataTypes) Then  
			 If UBound(arrParamValues) = UBound(arrParamDataTypes) Then  
				 For i = 0 To UBound(arrParamValues)  
					 Select Case arrParamDataTypes(i)  
						 Case 2 'Small Integer  
							 strDataLength = 2  
						 Case 3 'Integer  
							 strDataLength = 4  
						 Case 4 'Single  
							 strDataLength = 4  
						 Case 5 'Float  
							 strDataLength = 8  
						 Case 6 'Currency  
							 strDataLength = 8  
						 Case 7 'Date  
							 strDataLength = 8  
						 Case 11 'Bit  
							 strDataLength = 1  
						 Case 14 'Decimal  
							 strDataLength = 9  
						 Case 72 'GUID  
							 strDataLength = 16  
						 Case 128 'Binary  
							 strDataLength = 50  
						 Case 129 'Char  
							 If Not ReqValue(arrParamValues(i)) Then  
								 strDataLength = 1  
							 Else  
								 strDataLength = Len(arrParamValues(i))  
							 End If  
						 Case 200 'VarChar  
							 If Not ReqValue(arrParamValues(i)) Then  
								 strDataLength = 1  
							 Else  
								 strDataLength = Len(arrParamValues(i))  
							 End If  
						 Case 203 'NText  
							 If Not ReqValue(arrParamValues(i)) Then  
								 strDataLength = 1  
							 Else  
								 strDataLength = Len(arrParamValues(i))  
							 End If  
						 Case 204 'VarBinary  
							 strDataLength = 50  
						 Case Else 'Hmm...guess  
							 If Not ReqValue(arrParamValues(i)) Then  
								 strDataLength = 1  
							 Else  
								 strDataLength = Len(arrParamValues(i))  
							 End If  
					 End Select  
					 If arrParamDataTypes(i) = 14 Then  
						 Set p = objCmd.CreateParameter(, CLng(arrParamDataTypes(i)), , CLng(strDataLength), InputCleaner(arrParamValues(i)))  
						 p.NumericScale = 2  
						 p.Precision = 10  
						 objCmd.Parameters.Append p  
					 Else  
						 objCmd.Parameters.Append(objCmd.CreateParameter(, CLng(arrParamDataTypes(i)), , CLng(strDataLength), InputCleaner(arrParamValues(i))))  
					 End If  
				 Next  
				 i = Null  
			 Else  
				 strMessage = "Your values and data type arrays need to be the same length."  
			 End If  
		 End If  
		 Set objCmd.ActiveConnection = objConn  
			 If InStr(1, UCase(strQry), "SELECT") > 0 Then  
				 Set objRS = objCmd.Execute()  
					 If Not(objRS.EOF) Then  
						 arrReturn = objRS.GetRows()  
					 Else  
						 strMessage = "There are no records."  
						 Exit Sub  
					 End If  
				 Set objRS = Nothing  
				 Exit Sub  
			 ElseIf InStr(1, UCase(strQry), "INSERT") > 0 Then  
				 If InStr(1, UCase(strQry), "@@IDENTITY") > 0 Or InStr(1, UCase(strQry), "NEWID()") > 0 Then  
					 Set objRS = objCmd.Execute()  
						 If Not(objRS.EOF) Then  
							 arrReturn = objRS(0)  
						 End If  
					 set objRS = Nothing  
				 Else  
					 objCmd.Execute()  
					 strMessage = "Your command has been executed."  
				 End If  
			 ElseIf (InStr(1,UCase(strQry),"DELETE") > 0 Or InStr(1, UCase(strQry), "UPDATE") > 0 Or Left(UCase(strQry),2) = "SP") then
				objCmd.Execute()
				strMessage = "Your command has been executed."  
			 End If  
		 Set objCmd.ActiveConnection = Nothing  
	 Set objCmd = Nothing  
 objConn.Close()
Set objConn = Nothing
End Sub

Open in new window

0
Comment
Question by:kevp75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 25

Accepted Solution

by:
kevp75 earned 0 total points
ID: 22639206
alright folks.  I got something new on this one.

I can return an ID (integer), however I don't seem to be able to return a text value or recordset...

New Code:

    Public Function ExecuteQry()
        Set objConn = CreateObject("ADODB.Connection")
        objConn.Open strConnectionString
            Set objCmd = CreateObject("ADODB.Command")
                objCmd.CommandText = strQry
                objCmd.CommandType = intCommandType
                If IsArray(arrParamValues) And IsArray(arrParamDataTypes) Then
                    If UBound(arrParamValues) = UBound(arrParamDataTypes) Then
                        For i = 0 To UBound(arrParamValues)
                            Select Case arrParamDataTypes(i)
                                Case 2 'Small Integer
                                    strDataLength = 2
                                Case 3 'Integer
                                    strDataLength = 4
                                Case 4 'Single
                                    strDataLength = 4
                                Case 5 'Float
                                    strDataLength = 8
                                Case 6 'Currency
                                    strDataLength = 8
                                Case 7 'Date
                                    strDataLength = 8
                                Case 11 'Bit
                                    strDataLength = 1
                                Case 14 'Decimal
                                    strDataLength = 9
                                Case 72 'GUID
                                    strDataLength = 16
                                Case 128 'Binary
                                    strDataLength = 50
                                Case 129 'Char
                                    If Not ReqValue(arrParamValues(i)) Then
                                        strDataLength = 1
                                    Else
                                        strDataLength = Len(arrParamValues(i))
                                    End If
                                Case 200 'VarChar
                                    If Not ReqValue(arrParamValues(i)) Then
                                        strDataLength = 1
                                    Else
                                        strDataLength = Len(arrParamValues(i))
                                    End If
                                Case 203 'NText
                                    If Not ReqValue(arrParamValues(i)) Then
                                        strDataLength = 1
                                    Else
                                        strDataLength = Len(arrParamValues(i))
                                    End If
                                Case 204 'VarBinary
                                    strDataLength = 50
                                Case Else 'Hmm...guess
                                    If Not ReqValue(arrParamValues(i)) Then
                                        strDataLength = 1
                                    Else
                                        strDataLength = Len(arrParamValues(i))
                                    End If
                            End Select
                            If arrParamDataTypes(i) = 14 Then
                                Set p = objCmd.CreateParameter(, CInt(arrParamDataTypes(i)), , CInt(strDataLength), InputCleaner(arrParamValues(i)))
                                p.NumericScale = 2
                                p.Precision = 10
                                objCmd.Parameters.Append p
                            Else
                                objCmd.Parameters.Append (objCmd.CreateParameter(, CInt(arrParamDataTypes(i)), , CInt(strDataLength), InputCleaner(arrParamValues(i))))
                            End If
                        Next
                        i = Null
                    Else
                        ExecuteQry = "Your values and data type arrays need to be the same length."
                    End If
                End If
                Set objCmd.ActiveConnection = objConn
                    If InStr(1, strQry, "SELECT") > 0 Then
                        Set objRS = objCmd.Execute
                            If Not (objRS.EOF) Then
                                ExecuteQry = objRS.GetRows()
                            Else
                                ExecuteQry = "There are no records."
                                Exit Function
                            End If
                        Set objRS = Nothing
                        Exit Function
                    ElseIf InStr(1, UCase(strQry), "INSERT") > 0 Then
                        If InStr(1, UCase(strQry), "@@IDENTITY") > 0 Or InStr(1, UCase(strQry), "NEWID()") > 0 Then
                            Set objRS = objCmd.Execute()
                                If Not (objRS.EOF) Then
                                    ExecuteQry = objRS(0)
                                End If
                            Set objRS = Nothing
                        Else
                            objCmd.Execute
                            ExecuteQry = "Your command has been executed."
                        End If
                    ElseIf (InStr(1, UCase(strQry), "DELETE") > 0 Or InStr(1, UCase(strQry), "UPDATE") > 0 Or Left(UCase(strQry), 2) = "SP") Then
                        If Left(UCase(strQry), 2) = "SP" And intRetDataType > "" Then
                            objCmd.Parameters.Append (objCmd.CreateParameter("@ret", intRetDataType, 2, , intRetSize))
                            objCmd.Execute
                            ExecuteQry = objCmd.Parameters("@ret")
                        Else
                            objCmd.Execute
                        End If
                    End If
                Set objCmd.ActiveConnection = Nothing
            Set objCmd = Nothing
        objConn.Close
        Set objConn = Nothing
    End Function

Open in new window

0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select only the top record in a left join 13 50
Obtain data from database .mdb 4 35
Converting Stored Procedure to SQL Statement 5 63
JQuery Data Table Default Sort 2 31
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question