Extracting MAX value from SQL SELECT statement into BASIC variable
SELECT MAX(Member_ID) FROM Table_Members
When I test it with Tools>SQL, it correctly displays the maximum member_ID of 100.
However, when I run the code:
' Locate the largest membership ID used to date sSQL1="SELECT MAX(Member_ID) FROM Table_Members"
result=oStatement1.executeQuery(sSQL1)
NewMemberNumber = result.first
.. it is supposed to grab the 100 and place it into the variable NewMemberNumber; but instead it places the number -1 there instead.
Is “result.first” the correct way to access the 100? If so, why might this not be working? If not, what should I be using?
BTW: The field Member_ID is stored in the table as an Integer (not sure if that makes a difference).
—
Sub test
Dim oStatement1 as Object Dim oStatement2 as Object Dim result as Object Dim sSQL1 As String Dim sSQL2 As String Dim NewMemberNumber as Integer Dim RecordToUpdate as Long
If IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then Thisdatabasedocument.CurrentController.connect endif
oStatement1 = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement() oStatement2 = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement() oStatement1.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
' Locate the largest membership ID used to date sSQL1="SELECT MAX(Member_ID) FROM Table_Members"
result=oStatement1.executeQuery(sSQL1)
NewMemberNumber = result.first
msgbox (NewMemberNumber)
' NewMemberNumber is set to -1 - it should be 100
END SUB
—
TheDatabaseGuy – you seem to repeatedly make the same mistake in the query string in failing to quote (”) table and field names.
When a result set is created the record pointer is set to before the first record. So result.next (or result.first if you use SCROLL_SENSITIVE) moves to the fitst record. In addition result.first produces a boolean result, True or false. So NewMemberNumber = result.first will mean is set to True or False not to the value of a field in the result set. You asked why this is set to -1 and not 100. As the query string was wrong there will be no result set so NewMemberNumber is false i.e.-1. If you do get a result you need to use NewMemberNumber = result.getInt(0)to get the max value.
Please! At least understand what has already been presented. You can’t just copy stuff at random and hope it works.
I’m trying! But I struggle partly because I have dyslexia which affects my ability to keep many concepts in mind at once. I get there eventually because I refuse to quit trying.
My difficulty is figuring out what works and why it works. For example, I understand that starting with:
Dim Context Dim Database Dim Connection Dim oStatement Dim result
Dim sSQL1 As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") Database=Context.getByName("Buyers_Network") Connection=Database.getConnection("","") oStatement=Connection.createStatement()
..then allows me to execute SQL commands and manipulate tables etc, but I have no idea what the last four lines actually DO. I just know I have to put them in make it work.
This is an example of what I DO just copy and paste .. because it works. I wish I knew WHY it worked but I haven’t understood that yet!
I really appreciate your help; I just hope you can bear with me while I struggle to make sense of everything.
Yes. I think I’m getting it now though.
I used to think that double quotes were only required when the column name included spaces (eg. “Member ID” rather than Member_ID) but I gather the quotes are needed with way.
result.first is used to see if there is a result set. It returns True or False only. From the code provided to you:
You can see that CursorTest variable is checked to see if False and if so there is no resulting set. Then if there is a result set, since the result set is positioned at record 1 (first) the first field (getInt(1)) is stored in variable sMyData.
When code is working in one routine and you try to make it work in another but it doesn’t, it’s pretty safe to say you’re doing something wrong. How about using the debugging I gave you the web site for?
Please! At least understand what has already been presented. You can’t just copy stuff at random and hope it works.
Also you didn’t even copy your own SQL statement correctly. Your previous code:
If you research enough, you will find why your result was -1.