Ask Your Question
0

How do I count the number of rows selected in a macro?

asked 2020-10-03 04:17:15 +0100

Roadkill1954 gravatar image

updated 2020-10-03 04:18:42 +0100

I have a requirement that I'm sure has a simple solution but it seems to be impossible to find an answer. I'm running a BASIC macro in Base and all I want to know is how many rows are selected when I do this:

objSelStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement() objSelect = objSelStatement.ExecuteQuery("SELECT * FROM tblCatalog")

I'm able to loop through all the records in objSelect but I can't figure out how to count them in advance. I'm hoping it's something simple like:

lngRecordCount = objSelect.Count

edit retag flag offensive close merge delete

Comments

I don't think base can do it, but you can run first a SELECT to get the count.

m.a.riosv gravatar imagem.a.riosv ( 2020-10-03 13:07:33 +0100 )edit

Thanks for the reply. That's what I finally decided and ended up doing just as you suggested. It doesn't feel like a very efficient way to do things but it seems to work OK. Thanks again.

Roadkill1954 gravatar imageRoadkill1954 ( 2020-10-03 19:44:06 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-10-04 01:25:46 +0100

cpb gravatar image

@Roadkill1954,

i obviously cannot test the code but you can retrieve the record count from the result set like so:

Sub GetRecordCount
    oCon = thisdatabasedocument.currentcontroller.activeconnection
    oStatement = oCon.createstatement()
    oStatement.resultsettype = 1004 'enable scrolling in any direction
    sSQL = "select * from ""tblCatalog"""
    oResult = oStatement.executequery(sSQL)
    if not oResult.last() then exit sub 'empty result set
    iRowCount = oResult.row
    print iRowCount

    'you have the row count and can now loop through the result set
    oResult.beforefirst()
    while oResult.next()
        'process your data
    wend
End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-03 04:17:15 +0100

Seen: 61 times

Last updated: Oct 04