ADO - Calling a stored parameter Query with a dynamic Curser type

I am just learning about ADO.  
I’m trying to pass a parameter to a Jet query.  I understand that the parameter must be passed using the connection object. I'm able to do that, and then set the recordset = command.execute.  That  works for forward only cursers, but I would like to set the recoredset to adOpenDynamic.  What  do I need to change in the following code to be able to do this?  Thanks!

Dim cnNwind As Connection
Dim rsCustomer As Recordset
Dim comCommand As Command
'
Set cnNwind = New Connection
Set comCommand = New Command
'
'database connection
cnNwind.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
  "Data Source=C:\Data\Nwind.mdb"
'
cnNwind.Open
'
'set up command object
comCommand.CommandType = adCmdUnknown
comCommand.CommandText = "[qryTest]"
'
'create the parameter
comCommand.Parameters.Append _
  comCommand.CreateParameter("title", adBSTR, adParamInput)
'
'Set the parameter value
comCommand.Parameters("title").Value = Text1.text
'
'associate object with active connection
comCommand.ActiveConnection = cnNwind
'
'request the recordset
Set rsCustomer = comCommand.Execute
MikeR062998Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

p_biggelaarCommented:
As far as I know, it's the recordset object that determines whether you'll get a static or a dynamic cursor.

Try these lines instead of your current code regarding 'request the recordset':

'request the recordset
rsCustomer.CursorLocation = adUseClient
rsCustomer.CursorType = adOpenDynamic
rsCustomer.LockType = adLockBatchOptimistic
Set rsCustomer = comCommand.Execute

Hope this will fix it
0
MarineCommented:
<<I’m trying to pass a parameter to a Jet query.  I understand that the parameter must be passed using the connection object. >> no no no, conectin object can execture a query but it can't accept a parameter.

<<'set up command object
comCommand.CommandType = adCmdUnknown
comCommand.CommandText = "[qryTest]"
>>
comCommand.commandType=adCmdText
comCOmmand.execute
One other thing is this an Access query ? Please tell me why you want to add parameters in runtime instead of defining them in Access in your query and just pass the values to them. Then try to do this

rsCustomer.Open comCommand,cnNwind,adopenkeyset
0
svenkatapuramCommented:
MikeR

You can create the whole select statement dynamically, that is your query and pass the field and field value in the where clause

sql = "Select field1, field2 From table1 where field1 = '" & Text1.text
 & "'"

Set rs = New ADODB.Recordset
Set cnNwind = New ADODB.Connection
cnNwind.Open

rs.Open ssql, _
              cnNwind , adOpenDynamic, adLockOptimistic, adCmdText


This opens a recordset (adOpenDynamic)

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MikeR062998Author Commented:
p_bigglar, your right about the recordset determining the curser type, but you code will not work because you can not work because you can not reference an object variable before it is set.  You will get the infamous  "Object variable or With Block variable not set"
0
p_biggelaarCommented:
Oops, you're right. How about:

'request the recordset
Set rsCustomer = new ADODB.Recordset
rsCustomer.CursorLocation = adUseClient
rsCustomer.CursorType = adOpenDynamic
rsCustomer.LockType = adLockBatchOptimistic
rsCustomer.Open comCommand



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
p_biggelaarCommented:
Or even better:

'request the recordset
Set rsCustomer = comCommand.Execute
rsCustomer.CursorLocation = adUseClient
rsCustomer.CursorType = adOpenDynamic
rsCustomer.LockType = adLockBatchOptimistic
rsCustomer.Open





0
MikeR062998Author Commented:
Marine,

I’m not sure I understand you completely but I’ll try to answer.  According to Microsoft press “Desktop Apps for VB6” the Command object is used to pass parameters to a pre-existing parameter query.  The query I’m trying to pass the parameter to is in Access now but this is just a learning example.  I will eventually want to query an SQL Server or Access and probably from Visual Interdev so that I can display it on a web page.  I know that VI uses ADO so I figured that since I’m studying ADO in VB6 I might as well understand how to run the parameter query.

I haven’t tried your sample code yet – I’m at work.  I’ll try it this weekend.  Thanks
0
MikeR062998Author Commented:
svenkatapuram,

That may be how I’ll have to do it although I don’t understand why MS would give the functionality of  passing parameters to a query without the ability to do more than move forward through the recordset.

I’ll try your suggestion this weekend.

Thanks
0
MikeR062998Author Commented:
p biggelaar,

Hey, that might work!
I’ll try it this weekend, when I get home.
0
MikeR062998Author Commented:
p biggelaar,

Your last suggestion did not work. The second line trips an error because you can't set the curser after the recordset is open, and I guess setting the recordset to the conmmand.execute opens it.

But:
Your next to the last suggestion that said :

Set rsCustomer = new ADODB.Recordset
rsCustomer.CursorLocation = adUseClient
rsCustomer.CursorType = adOpenDynamic
rsCustomer.LockType = adLockBatchOptimistic
rsCustomer.Open comCommand

That one worked!

Thanks
0
p_biggelaarCommented:
Sorry for the last comment then, but I'm glad I could be of help...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.