Solved

Classic ASP Parameterized Queries

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now