Solved

Classic ASP Parameterized Queries

Posted on 2008-09-30
1
2,183 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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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