We help IT Professionals succeed at work.

Bind variables with recordset object?

roblinx
roblinx asked
on
314 Views
Last Modified: 2012-06-21
Hi Experts...

Our website makes extensive use of ADODB calls to an Oracle database.  I've been asked by our Oracle dba to take a look at some of the more-often used queries on our web site to try to improve efficiency.  One suggestion was through the use of parameterized (bind) queries.  

My understanding is that these types of queries can only be used with adodb.command objects.  My problem is that I typically use the adodb.recordset object to pull back recordsets, because I need to read the RecordCount property, and I can only do this if I can specify the cursorlocation, cursortype, and locktype properties.

Here's a snippet of what I do now:

dim oConn
Set oConn= Server.CreateObject("ADODB.Connection")
oConn.Open("dsn=dsnName;uid=USER;pwd=PASS;")
Set rsUsers = Server.CreateObject("ADODB.Recordset")
rsUsers.ActiveConnection = oConnRelais
rsUsers.Source = "select * from users where username = '" & strUserName "'"
rsUsers.CursorType = 0
rsUsers.CursorLocation = 3
rsUsers.LockType = 2
rsUsers.Open()

I would like to parameterize the query, so that I can use something like:

dim oConn
Set oConn= Server.CreateObject("ADODB.Connection")
oConn.Open("dsn=dsnName;uid=USER;pwd=PASS;")
Set objSQLCommand = CreateObject("ADODB.Command")
set objSQLCommand.ActiveConnection = oConn

Dim prmSQLBind1
Set prmSQLBind1 = objSQLCommand.CreateParameter("Bind1", adVarChar, adParamInput,50)
strSQLQuery = "select * from users where username = ?"
objSQLCommand.CommandText = strSQLQuery
objSQLCommand.Parameters.Append prmSQLBind1
objSQLCommand(0).value = strUserName

Set rsUsers = CreateObject("ADODB.Recordset")
Set rsUsers = objSQLCommand.Execute

But... when I do this, I can't set the cursorlocation, cursortype, and locktype properties.  They get ignored.

Any help?  Do I need to provide more info?

Thanks in advance!

Rob
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Wow, that was fast!  Even better, it worked.  :-)

Thanks Angel!

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.