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

[Roadkill1954]

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

[mariosv]

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

[Roadkill1954]

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.

[cpb]

@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

[Shaba1]

I tried "Resultset.row in my project and it always returned 0. I also viewed the libreoffice API docs and cannot find any resultset property named row

[cpb]

@Shaba1
your comment is both deceptive & inaccurate!

my code works perfectly with most back-end databases.

it does not work with ‘Firebird’ because ‘Firebird’ will not accept ‘last()’.

i guess that you are not moving the record pointer to the last row ‘oResult.last()’.

also if you examine ‘oResult’ using the MRI extension then under ‘Properties’ you will see ‘Row’.

in future i suggest that if you encounter issues then you should seek clarification and not post misleading statements.