Ask Your Question

handle runtime error sql macro [closed]

asked 2016-08-22 21:16:26 +0100

jvglynnjr gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-09-10 20:52:38.744313

1 Answer

Sort by » oldest newest most voted

answered 2016-08-22 22:50:26 +0100

Ratslinger gravatar image

updated 2016-08-23 15:10:22 +0100


    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
     oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
  'Setting is here'
  oStatement.ResultSetType =         
  sSQL ="SELECT * FROM ""TestTable1"" WHERE ""Cust"" IS NULL"
  result = oStatement.executeQuery(sSQL)
  CursorTest = result.first
  If CursorTest = "False" Then
    MsgBox "No Records"
    Exit Sub
  End If
do UNTIL result.isAfterLast = TRUE
    sMyData = result.getInt(1)
    CustomerNumber = CustomerNumber + 1
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 =         

 '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
edit flag offensive delete link more


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.

jvglynnjr gravatar imagejvglynnjr ( 2016-08-23 14:21:44 +0100 )edit

My environment: Windows 10, LibreOffice Version: (x64) HSQLDB Split Database

I tried using above code but get error BASIC runtime error. Object variable not set on the line:

 oStatement.ResultSetType =

@Ratslinger, Is it because of a different environment? Thank you

Ardee gravatar imageArdee ( 2020-04-08 01:28:00 +0100 )edit


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.

Ratslinger gravatar imageRatslinger ( 2020-04-08 20:41:55 +0100 )edit

@Ratslinger thanks for your reply, will post as new question

Ardee gravatar imageArdee ( 2020-04-08 23:39:15 +0100 )edit

Question Tools

1 follower


Asked: 2016-08-22 21:16:26 +0100

Seen: 545 times

Last updated: Aug 23 '16