problem: In a macro that executes an SQL query, when the user-inputted key value is not found, there is a run-time error. How can I have the macro exit the subroutine when this occurs rather than trigger a run-time error? (which opens a debugging screen, confusing the user?) i.e. I want there to be a messagebox to warn the user to retry input whenever a value that is not already in the table is entered.
You must set SCROLL_SENSITIVE:
if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
Thisdatabasedocument.CurrentController.connect
endif
oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
'Setting is here'
oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
sSQL ="SELECT * FROM ""TestTable1"" WHERE ""Cust"" IS NULL"
result = oStatement.executeQuery(sSQL)
'CHEK IF ANY RECORDS RETURNED'
CursorTest = result.first
If CursorTest = "False" Then
MsgBox "No Records"
Exit Sub
End If
do UNTIL result.isAfterLast = TRUE
sMyData = result.getInt(1)
result.next
CustomerNumber = CustomerNumber + 1
loop
result.last
MsgBox "Number of Records = " & result.getRow()
Edit 08/23/16
The above shows a full routine to get active connection, setting the result scroll type, checking to see if there are any results and then processing results.
Here are only the statements for what you wanted:
'Setting is here'
YOURCONNECTION.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
'CHEK IF ANY RECORDS RETURNED - "result" is return from your query'
Dim CursorTest AS Boolean
CursorTest = result.first
'Test for zero records returned.
If CursorTest = "False" Then
MsgBox "No Records"
Exit Sub
End If
Not clear at all what this is doing. Why would I need that loop? Also, not looking for a null value in the table, just a select criterion that doesn’t find a record.
My environment: Windows 10, LibreOffice Version: 6.4.2.2 (x64) HSQLDB Split Database
I tried using above code but get error BASIC runtime error. Object variable not set
on the line:
oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
@Ratslinger, Is it because of a different environment?
Thank you
Environment should not be a factor.
Just entered the same code in an HSQLSB split on Ubuntu 18.04 & LO v6.4.2.2 and it worked without a problem. If you continue to have a problem, ask as a new question with full code used.