Solved

Classic ASP Parameterized Queries

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating TABS in ASP Classic NOT using DIV 22 68
Group by correlation 4 57
UTC (timezone) without using an API 16 46
FileUp - Classic ASP 5 17
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

773 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