Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Classic ASP Parameterized Queries

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

636 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