?
Solved

Classic ASP Parameterized Queries

Posted on 2008-09-30
1
Medium Priority
?
2,188 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

765 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