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

x
?
Solved

Open RS with Parameterized Queries (using ? rather than Named parameters)

Posted on 2013-12-24
1
Medium Priority
?
345 Views
Last Modified: 2013-12-24
I recently learned how to do Paramerized queries using '?' in the SQL and an array of variants for the Parameters.  But I can only figure out how to do this using ADODB.Connection object and an ADODB.Command object with a Command.Execute()

Example:
Cmd.Connection = Conn
Cmd.CommandText = "SELECT * FROM TABLE WHERE FIELD1 = ? AND FIELD2 = ?"
Set RS = Cmd.Execute(RecordsAffected,Array(10,"ABC"))

Open in new window

(where Array(10,"ABC") represents an Array of Variants the way the VBScript would)

But what I need to do is an RS.Open command where I can set properties of the Record Set such as CursorType and LockType.  For example, I'd logically like to do the following:
Cmd.CommandText = "SELECT * FROM TABLE WHERE FIELD1 = ? AND FIELD2 = ?"
Cmd.Parameters = Array(10,"ABC")
RS.Open(Cmd, adOpenDynamic, adLockPessimistic) 

Open in new window

But this is not valid code (you can not assign an array of variants to the Cmd Parameters).

Now I know how to accomplish this creating individual parameter objects and using named parameters in the SQL.  But that method seems to require that I define the data type for each parameter.  My situation is that I must convert a bunch of SQL commands to parameterized SQL commands in a program that I'm not familiar with and most field values are contained in variables (whos datatype isn't always apparent).

So I know I can take the time to look up each field in the database and determine how to create parameters for each field and use named parameters.

But I'd get things going much faster (with less room for mistakes in picking the wrong data types) if I could simply replace field values with '?' and pass in an array of variants for the parameters.

NOTE: I'm utilizing ADODB objects in a VB6 project.
0
Comment
Question by:HooKooDooKu
[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 16

Accepted Solution

by:
HooKooDooKu earned 0 total points
ID: 39738464
I sort of figured it out.

I can still use '?' for the parameters and simply create individual Parameter objects that get appended to the Cmd object.  But to deal with the array of variants, I had to create a subroutine that looked at the VarType of the parameter and set the data type base on that.

Here's the code I'm using where I look through my Parameters stored in the array of variants names m_Parameters.
        Select Case VarType(m_Parameters(I))
            Case vbInteger Or vbLong
                Set P = m_objCmd.CreateParameter(, adInteger, adParamInput)
            Case vbDate
                Set P = m_objCmd.CreateParameter(, adDate, adParamInput)
            Case vbString
                Set P = m_objCmd.CreateParameter(, adBSTR, adParamInput)
                P.Size = Len(m_Parameters(I))
            Case vbSingle
                Set P = m_objCmd.CreateParameter(, adSingle, adParamInput)
            Case vbDouble
                Set P = m_objCmd.CreateParameter(, adDouble, adParamInput)
            Case vbCurrency
                Set P = m_objCmd.CreateParameter(, adCurrency, adParamInput)
            Case vbBoolean
                Set P = m_objCmd.CreateParameter(, adBoolean, adParamInput)
            Case vbByte
                Set P = m_objCmd.CreateParameter(, adTinyInt, adParamInput)
            Case Else
                m_Parameters(I) = ""
                Set P = m_objCmd.CreateParameter(, adBSTR, adParamInput)
                P.Size = 0
        End Select
        P.Value = m_Parameters(I)
        Call m_objCmd.Parameters.Append(P)

Open in new window

0

Featured Post

Industry Leaders: 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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

604 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