How to get ResultSet has a record ?

General discussion on OpenOffice Base and its databases.

How to get ResultSet has a record ?

Postby Jill R. Lipe » Sat Jun 21, 2014 8:52 am

Hello,

I want to learn whether I have a record from my ResultSet query
Example code
Code: Select all
ResultSet = Statement.executeQuery("SELECT * FROM mytable WHERE id=1" )
if Not IsNull(ResultSet) Then
    MsgBox("There is a record")
else
    MsgBox("No record in this ID")
end if

But this code does not work! I get always "There is a record" even though I enter different ID number
that does not exist in my database
What can i do ?

Thank you for your help,
Jill R. Lipe
 
Posts: 33
Joined: Mon Mar 24, 2014 6:30 am

Re: How to get ResultSet has a record ?

Postby LiverPAN » Sat Jun 21, 2014 11:37 am

Hi,

Perhaps, you could try the example code below:
Code: Select all
ResultSet = Statement.executeQuery("SELECT COUNT(*) as TABLE_COUNT FROM mytable WHERE id=1" )

num_of_rows = ResultSet.getINT(1)

if num_of_rows > 0 Then
   MsgBox("There is a record")
else
   MsgBox("No record in this ID")
end if


Explanation: Rather than using the SELECT statement you used, have the database engine return the number of records ( COUNT(*) ) that meet the criteria in the WHERE clause. Then, you can assign the one number returned, to a BASIC variable, in this instance, I used num_of_rows . . . and . . . your if statement can return the correct MsgBox . :)
I hope this helps you
Thanks
LiverPAN
 
Posts: 24
Joined: Tue Mar 25, 2014 6:30 am

Re: How to get ResultSet has a record ?

Postby UniSET » Sat Jun 21, 2014 12:09 pm

Hi,

Please be sure, for any table or column name in your SELECT statement, if it contains a lower case letter, or, a space, you surround
it by double quotes . . . and . . . since you are using OpenOffice Basic, that would be two double quotes.
ResultSet = Statement.executeQuery("SELECT COUNT(*) as TABLE_COUNT FROM mytable WHERE id = 1" )

Code: Select all
While ResultSet.next

      If ResultSet.getINT(1) = 0 Then
         MsgBox("No record in this ID")
      Else
         MsgBox("There is a record")
      End If

   WEnd


I hope this will help you.
Thanks,
UniSET
 
Posts: 22
Joined: Tue Mar 25, 2014 6:33 am


Return to OpenOffice.org Base

Who is online

Users browsing this forum: No registered users and 0 guests

cron