Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

Help with ADO

hello-

i am using ado to pull info from ldap. i am trying to find a way to limit the number of records that are returned. so far, i havent had any luck. he is a snippet of my code.

thanks - p


    '' Consts
    Const METHOD = "DPhoneBookManager.Find"
    Const CDATA_OPEN = "<![CDATA["
    Const CDATA_CLOSE = "]]>"
   
    '' Vars
    Dim mcnnConnection As ADODB.Connection
    Dim mrsPeople As ADODB.Recordset
    Dim mcmdQuery As ADODB.Command
    Dim msPeople As String

    Set mcnnConnection = CreateObject("ADODB.Connection")
    Set mrsPeople = CreateObject("ADODB.Recordset")
    Set mcmdQuery = CreateObject("ADODB.Command")
   
    '' Set the connection properties.
    With mcnnConnection
        .Provider = "ADsDSOObject"
        .Properties("User ID") = ""
        .Properties("Password") = ""
        .Properties("Encrypt Password") = False
        .Open "ADs Provider"
    End With
       
    '' Set the command properties.
    With mcmdQuery
        mcmdQuery.ActiveConnection = mcnnConnection
        mcmdQuery.CommandText = vsSearchFilter
        mcmdQuery.Properties("Page Size") = 99
    End With
   
    Dim z As Integer
    For z = 0 To mcmdQuery.Properties.Count - 1
        Debug.Print mcmdQuery.Properties.Item(z).Name & " = " & mcmdQuery.Properties.Item(z).Value
    Next z
   
   
    Set mrsPeople = mcmdQuery.Execute

    Debug.Print "Record count " & mrsPeople.RecordCount
0
pipe
Asked:
pipe
  • 4
  • 4
  • 3
  • +2
1 Solution
 
mdouganCommented:
You could try setting the CacheSize on the recordset:

'' Set the command properties.
   With mcmdQuery
       mcmdQuery.ActiveConnection = mcnnConnection
       mcmdQuery.CommandText = vsSearchFilter
       mcmdQuery.Properties("Page Size") = 99
   End With
   
   Set mrsPeople = New ADODB.Recordset
   mrsPeople.Source = mcmdQuery
   mrsPeople.CacheSize = 100
   mrsPeople.Open

   Set mrsPeople.ActiveConnection = Nothing
   i = 1
   while not mrsPeople.EOF
        Debug.Print i & " " & mrsPeople.Fields(0).value
        i = i + 1
   Wend

You should print 100 records.....
0
 
S2Commented:
In the command text Add "TOP 10" for first 10 records only,
like this "SELECT TOP 2 * FROM table1"
0
 
pipeAuthor Commented:
when i try to set this:

mrsPeople.Source = mcmdQuery


i get a type mismatch. i am using ado 2.7

any thoughts?
0
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!

 
pipeAuthor Commented:
i added...set ....testing to see if it works.
0
 
TravisHallCommented:
CacheSize won't help you here. It has nothing to do with the number of records the query returns, only with how many are sent to the client each time the client runs out of records. If you set CacheSize to 10, you will get 10 records to start with, then when you move to the eleventh record 10 more records will be retrieved, and so on and so forth.

S2's suggestion of using TOP in your SQL query should do the trick. TOP is generally combined with an ORDER BY clause, so that you have some idea which records will be returned.
0
 
mdouganCommented:
Sorry, I think I forgot a Set statement:

Set mrsPeople.Source = mcmdQuery

TravisHall, you'll note in the code above that I'm setting the activeconnection to nothing after opening the recordset.  The thinking is that the first 100 records will be cached and returned in the recordset, then closing the connection would keep the rest from being retrieved.  It was just a guess, don't know if it will work, but I'll give it a shot.
0
 
TravisHallCommented:
Setting ActiveConnection to Nothing gives you a disconnected recordset - but I don't believe you can produce a disconnected recordset unless you are using a client-side cursor (CursorLocation property set to adUseClient - otherwise, you'll get an error). And if you are using a client-side cursor, you will retrieve all the records, regardless of CacheSize (though, you can probably do that retrieval in lots of 100, not that it will make any difference).

If you don't like TOP, you could try setting the MaxRecords property of the recordset (which is intended to do exactly what was requested, I believe).
0
 
pipeAuthor Commented:
welp, i am pulling the records using LDAP. unfortunately i cannot find something similar to TOP in that protocal. i was hoping i could remedy that by setting some properties on the command or recordset objects but it doesnt seem to work.
0
 
Anthony PerkinsCommented:
mdougan is on the right track.

Since this is LDAP the SQL TOP keyword is not an option. In addition MaxRecords is not supported by the LDAP Provider.  Take a look at this article from MSDN:
HOWTO: Work Around the Limitation that ADO MaxRecord Is Not Implemented in DS OLEDB Provider ADsDSOObject (Q269361)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269361

Anthony
0
 
mdouganCommented:
Well, it's probably somewhere in the middle.  Travis has a point about the client-side cursor.  You're going to be running the entire query, but, if the CacheSize is set to 100, and as Anthony's article says, set the AbsolutePage to 1, then you'll have the first page of data.  Then, I guess that you have to close the RS after you finish processing the required number.

I guess it depends on what pipe wants to do, maybe it's good to leave the RS open, and just retrieve 100 at a time...
0
 
TravisHallCommented:
With a client-side cursor, I would think that the entire recordset would be retrieved and stored in the client's cursor, but with the Filter property set correctly (to adFilterFetchedRecords), it seems that you will only get the current page's records regardless. So, by setting PageSize and CacheSize correctly, you build a cache containing a subset of the required records, and restrict yourself to only using them with the Filter. You don't need to worry about having too many records, because Filter is giving you just what you want.

However, I suspect that you will still end up with ADO retrieving all the data in the recordset, so if you are hoping to improve performance by only retrieving a subset of the data, it won't work. I guess it depends what you want this for.

The other downside, of course, is that you have used Filter to restrict the number of records you work with, which means that you can't pull off any of the other neat tricks you can normally do with the Filter property. As soon as you change Filter to something else, you are back to checking against the entire recordset.

On the other hand, I could be completely incorrect in my understanding of how the entire thing works.

Anyway, I'd say Anthony has given the answer most likely to work, or at least provide useful information.
0
 
mdouganCommented:
agreed
0
 
pipeAuthor Commented:
wow, good job finding that article. this seems to work sometimes. im seeing some flakey errors but hopefully i can work them out. thanks again.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now