Ask Your Question
0

handle runtime error sql macro

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

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 close merge delete

1 Answer

Sort by » oldest newest most voted
1

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

Ratslinger gravatar image

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

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

Comments

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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 147 times

Last updated: Aug 23 '16