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

x
?
Solved

ADO recordset getting no records from ADO command

Posted on 2003-11-13
13
Medium Priority
?
353 Views
Last Modified: 2011-10-03
I have the following code which comes from a class module I use for managing an Access 2000 database (from vb6 sp5)

Public Function SeleccionId(cnnDIMENSION As ADODB.Connection, ByRef rsResultado As ADODB.recordset, di_clave As Variant) As Boolean
On Error GoTo Error_SeleccionId
   
Dim lcdi_clave As ADODB.Parameter
   
    Set cmdDIMENSION = New ADODB.Command
    Set lcdi_clave = New ADODB.Parameter
   
    lcdi_clave.Type = adInteger
    lcdi_clave.Direction = adParamInput
    lcdi_clave.Size = 4
    lcdi_clave.Value = di_clave
    cmdDIMENSION.Parameters.Append lcdi_clave
   
    cmdDIMENSION.ActiveConnection = cnnDIMENSION
    cmdDIMENSION.CommandText = "DIMENSION_Select_Id_Qry"
    cmdDIMENSION.CommandTimeout = 30
    Set rsResultado = cmdDIMENSION.Execute()
   
    SeleccionId = True
    Set cmdDIMENSION = Nothing
    Exit Function
   
Error_SeleccionId:
    SeleccionId = False
    Set cmdDIMENSION = Nothing
   
End Function

I have tested the ADODB.connection that the function recieves as parameter and it's working fine, the recordset it recieves is an empty one (just declared and instantiated before being sent)... the idea is that this command activates an Access Query named "DIMENSION_Select_Id_Qry", which redaction I put here:

SELECT [di_clave], [di_nombre], [di_descripcion]
FROM DIMENSION
WHERE (di_clave like di_clave_qry);

This query, ran from Access works fine, and as you see, it has only one parameter "di_clave_qry". The problem here is that after the line Set rsResultado = cmdDIMENSION.Execute(), the rsResultado recordset gets no records at all (recordcount = -1), and I don't know what might be wrong. Could you please help me find the error?? I'm using ADO 2.7. Thanks beforehand.
0
Comment
Question by:pire
[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
13 Comments
 
LVL 39

Expert Comment

by:appari
ID: 9744825
dont check recordcount property because its not the same all the times and it depends on the cursor type. to check whether recordset is empty or not check for EOF like this

Public Function SeleccionId(cnnDIMENSION As ADODB.Connection, ByRef rsResultado As ADODB.recordset, di_clave As Variant) As Boolean
On Error GoTo Error_SeleccionId
   
Dim lcdi_clave As ADODB.Parameter
   
    Set cmdDIMENSION = New ADODB.Command
    Set lcdi_clave = New ADODB.Parameter
   
    lcdi_clave.Type = adInteger
    lcdi_clave.Direction = adParamInput
    lcdi_clave.Size = 4
    lcdi_clave.Value = di_clave
    cmdDIMENSION.Parameters.Append lcdi_clave
   
    cmdDIMENSION.ActiveConnection = cnnDIMENSION
    cmdDIMENSION.CommandText = "DIMENSION_Select_Id_Qry"
    cmdDIMENSION.CommandTimeout = 30
    Set rsResultado = cmdDIMENSION.Execute()
   
 if  rsResultado.EOF then
    Msgbox "No Data "
    SeleccionId = False
  else
    SeleccionId = True
  end if
    Set cmdDIMENSION = Nothing
    Exit Function
   
Error_SeleccionId:
    SeleccionId = False
    Set cmdDIMENSION = Nothing
   
End Function
0
 
LVL 39

Expert Comment

by:appari
ID: 9744840
ignore " ", my windows OS is Japanese so by mistake typed something in japanese,

" if  rsResultado.EOF then " should be "if  rsResultado.EOF then"
0
 
LVL 4

Expert Comment

by:JNSTAUB
ID: 9746015
SELECT [di_clave], [di_nombre], [di_descripcion]
FROM DIMENSION
WHERE (di_clave like di_clave_qry);
 where is your parameter lcdi_clave  in this query, the right query may be:
WHERE (di_clave like lcdi_clave);

i am not sure of the answer
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Expert Comment

by:kamlesh_agrawal
ID: 9746169
The recordcount works only when if you use cursor location as aduseclient...


replace this statement
    Set rsResultado = cmdDIMENSION.Execute()
   
with

     rsResultado.Cursorlocation = adUseClient
        Set rsResultado = cmdDIMENSION.Execute()


Hope it would help u ... make sure u've included adovbs.inc file at the top.. you can search "adovbs.inc" file in google and download it..


Kamlesh A.
0
 
LVL 2

Expert Comment

by:kamlesh_agrawal
ID: 9746185
Oops it is in VB .. then it is ok if you dont include the file...
I thought it is ASP

bye
Kamlesh A.
0
 
LVL 1

Expert Comment

by:atp_expert
ID: 9747580
I feel that, the recordset has data.. Just check if the recordset if EOF =BOF, if that is case then the recordset is empty.  

Change the cursor location to client side cursor. The default cursor type is forward only.
0
 
LVL 1

Accepted Solution

by:
fsaims earned 600 total points
ID: 9748090
SELECT [di_clave], [di_nombre], [di_descripcion]
FROM DIMENSION
WHERE (di_clave like di_clave_qry);

Dear Pire in your above query di_clave_qry is taken as value, not as parameter. Change your Query with this

SELECT [di_clave], [di_nombre], [di_descripcion]
FROM DIMENSION
WHERE (di_clave like ?);
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 9751704
I think fsaims is right. Also if you use wild chars, then you need to consider:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/Articles/Q162/9/77.asp&NoWebContent=1
(ACC97: ASP Query Cannot Be Used with the LIKE Predicate)
- Hope, this uses ADO so apply to VB too.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q163/8/93.asp&NoWebContent=1

http://support.microsoft.com/default.aspx?kbid=813696
(FIX: Parameter Query on Linked Tables Returns No Records)

Hope it helps.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9752964
Please maintain your open questions:

1 11/23/2002 100 Problem installing and running CR8 with ...  Open Crystal Reports
2 01/15/2003 100 Creating ContinuosTickets  Open Crystal Reports
3 07/08/2003 250 Error when accessing DataWindow in desig...  Open PowerBuilder
4 09/23/2003 500 USB mass storage device was formatted an...  Open Hardware
5 09/26/2003 150 Ideal drive distribution on IDE setup  Open Hardware
6 09/29/2003 100 Some software unexpectedly closes while ...  Open Windows XP
7 10/13/2003 100 Finding a full open source project.  Open .NET

Thanks,
Anthony
0
 

Author Comment

by:pire
ID: 9766375
Well... I've given maintenance to my EE account, and I'm reviewing the answers that the experts so kindly have given to this question, I'll post results ASAP. Thanks.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 10548276
Moderator, my recommended disposition is:

    Accept fsaims's comment(s) as an answer.

Dan Rollins -- EE database cleanup volunteer
0

Featured Post

Technology Partners: 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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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