Extracting MAX value from SQL SELECT statement into BASIC variable

4 replies [Last post]
TheDatabaseGuy
Offline
Last seen: 5 weeks 4 days ago
Title: ★★★
Joined: 30 Dec 2015
Posts: 35
In this code I am selecting the maximum value found in the Table_Members with:

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

Ratskinger
Offline
Last seen: 4 days 18 hours ago
Title: ★★★★
Joined: 23 Feb 2016
Posts: 91
Kind of sad
Honestly, did you even understand the code I presented? I put some effort into that. If not, did you search the web? I found all these things this morning in less than an hour! This was the first time I ever used result.first as well as result.getRow().

result.first is used to see if there is a result set. It returns True or False only. From the code provided to you:


      CursorTest = result.first
      If CursorTest = "False" Then
      	MsgBox "No Records to Update"
      	Exit Sub
      End If
	do UNTIL result.isAfterLast = TRUE
		sMyData = result.getInt(1) 

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:


   strSQL="SELECT MAX(""Member ID"") FROM ""Table_Members"""

If you research enough, you will find why your result was -1.

peterwt
Offline
Last seen: 1 day 6 hours ago
Title: ★★
Joined: 21 Jan 2016
Posts: 12
Ratskinger – your example
Ratskinger – your example database is an excelent demonstration of using a result set. Good to see you found a use for the SCROLL_SENSITIVE property of a result set!!

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.

TheDatabaseGuy
Offline
Last seen: 5 weeks 4 days ago
Title: ★★★
Joined: 30 Dec 2015
Posts: 35
Ratskinger wrote: Honestly,
Ratskinger wrote:
Honestly, did you even understand the code I presented? I put some effort into that. If not, did you search the web? I found all these things this morning in less than an hour! This was the first time I ever used result.first as well as result.getRow().

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. Smile

TheDatabaseGuy
Offline
Last seen: 5 weeks 4 days ago
Title: ★★★
Joined: 30 Dec 2015
Posts: 35
peterwt wrote:TheDatabaseGuy
peterwt wrote:
TheDatabaseGuy – you seem to repeatedly make the same mistake in the query string in failing to quote (”) table and field names.

Yes. I think I’m getting it now though. Smile

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.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.