• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

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
0
MikeR062998
Asked:
MikeR062998
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now