BASE Macro SQL error when record not found

[Carrbigdog]

I am attempting to write a macro that uses SQL to query a table and need to determine if there is a “Not found” result.
I copied the routine at handle runtime error sql macro
but am getting a run time error.

oContext=CreateUnoService("com.sun.star.sdb.DatabaseContext")	'One service for all SQL processes
oDB1=oContext.getByName("DogDBTest1")  	'get the database
oConnection1=oDB1.getConnection("","")		'establish connection to database
oConnection1.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE   'added to allow for NRF

oResult1 = oStatement1.executeQuery(sReadReg & sRegistryID)

CursorTest = oResult1.first
If CursorTest = "False" Then
	MsgBox (sRegistryID & " Not Found"
Else  	
	While oResult1.next()
	   MsgBox (oResult1.getString(1) & "=String(1) " & oResult1.getString(2) & "=String(2) " _
	   & oResult1.getString(5) & "=String(5)"
	wEnd

yada…
Run time error says "Property or Method not found: ResultSetType

Can anyone see errors in code or point me to alternate solution to determine if no record was found
I also attempted to use ifNull solution but that didn’t work either

[Ratslinger]

Hello,

You don’t mention at which line of code the error occurred but there are a couple of items with problems.

You create a connection but not the necessary statement for SQL:

oStatement1 = oConnection1.createStatement()

Then oStatement1 here is valid (error prior to above):

oResult1 = oStatement1.executeQuery(sReadReg & sRegistryID)

This is incorrect:

oConnection1.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE

ResultSetType is not in oConnection1. It is in oStatement1

Don’t see how sReadReg is defined so may potentially be a problem.

MsgBox (sRegistryID & " Not Found"

has a begin parenthesis - should not be there.

With:

While oResult1.next()

you have skipped the first record. Logic problem.

There may be more but you need to look closer at what code you have and what is being copied. You should know what the individual statements do and not just insert a routine which may work elsewhere. Your “copied” code is quite different from the code posted in the link. I use similar code to what was copied in many routines and it works.

[PhLo]

@Ratslinger, it has been my experience that result sets start with the cursor isBeforeFirst by default, akin to an index of -1, so .next() will not skip the first record, but rather it will start reading from the first record, as long as the cursor position hasn’t been “messed with” before the first .next() in the loop is called.

[Ratslinger]

CursorTest = oResult1.first gets the first record. Nothing is done with it. While oResult1.next() starts processing at record two which can actually be yet another problem if there is only one record in the result set.

[PhLo]

Gotcha. Yeah, when I do a similar test, I test like this: bCursorTest = oResult.isBeforeFirst : If bCursorTest = False Then... which doesn’t advance the cursor. So then .next() simply starts off at the first record.

[Ratslinger]

Yes, it is understood how it should have been done but that is not what is in the code in the OP therefore just another problem with the code posted.

[Carrbigdog]

Thanks for all the replies. Maybe someday I will be knowledgeable enough to offer some Newbie help on Base.