Button for Update Query in Base Macro

TNBuilder · April 23, 2024, 2:57pm

Hello, I have been trying to get my sql update query to work with a button. I am not a coder, but try to understand. I had found and tried several snippets with no luck. The code I’ve been working on is below. I end up with a syntax error at the executeUpdate stmt. What am I missing, pls?

Sub ConnectToDatabase
    Dim Context
    Dim DB
    Dim Conn
    Dim Stmt
    Dim strSQL As String

    ' Get the database context
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    DB = Context.getByName("Bibliography") ' Change the database name as needed
    Conn = DB.getConnection("", "") ' Provide username & password if required (HSQL default is blank)

    ' Create a statement
    Stmt = Conn.createStatement()

    ' Your SQL query
    strSQL = "UPDATE ""Electricity Monthly Totals"" SET ""Cost"" = " & _
             "SELECT SUM((""NoUnits\"" * ""Power"" * ((""OpDays"" * 24) / 1000)) * 0.09983) + " & _
             "(SELECT SUM(""Cost"") FROM ""Expenses Table"" WHERE ""ExpCatID"" = '8'" & _
             "AND (""AcctID"" BETWEEN '4999000' AND '4999999') AND (""Date"" BETWEEN '2024-05-01' AND '2024-05-31'))" & _
             "AND ""ConJobID"" = '14') + " & _
             "( SELECT SUM(""Cost"" ) FROM ""Expenses Table"" WHERE ""ExpCatID"" = '8'" & _
             "AND ( ""AcctID"" BETWEEN '4999000' AND '4999999' ) AND ( ""Date"" BETWEEN '2024-05-01' AND '2024-05-31' )" & _
             "AND ""ConJobID"" = '13' )  AS ""May 2024 Total Bill""" & _
             "FROM ""Elec Est Table"" WHERE ""Finish Date"" BETWEEN '2024-04-16' AND '2024-05-15'" & _
             "Where ""ElecTotalID"" = '8'"



    ' Execute the query
    Stmt.executeUpdate(strSQL)

    ' Close the database connection
    Conn.close()
End Sub

Villeroy · April 23, 2024, 3:47pm

msgbox strSQL
Copy the code from the message box into a code editor. At first sight:

The SELECT statement needs to be in parentheses.
Parentheses do not match.
Where “ElecTotalID” = ‘8’ compares the string ‘8’ with a field that is possibly an integer.

TNBuilder · April 23, 2024, 7:38pm

I placed () around the SELECT, and followed my own (). I changed the reference to that integer to a text field. But, I still have problems. Is it the other references to integers within the SELECT statement? Thx!

TNBuilder · April 23, 2024, 8:00pm

I found an extra (). But now I get a message saying “Message: The query cannot be executed. It contains no valid table…”

Villeroy · April 23, 2024, 8:14pm

Test the SELECT statements in the query desiger.
Why do you try to store calculated data anyway?

TNBuilder · April 23, 2024, 9:37pm

The UPDATE statement works in Tools–SQL. The SELECT statements works in Designer view and SQL view.
The UPDATE statement does not work in Designer view. But, it doesn’t want to do updates, only queries. And, it still does not work in the macro.

I calculate expenses from different sources to split one electric bill amoungst a business, a family, and a renter. I like to keep track of electric expenses throughout the month for my hobby/business: crypto mining.
I have a report to display monthly electric expense totals for each entity, with the mining expenses accumulating each month. A button, with the macro, would be used to update a separate table that keeps these totals and places these totals in a graph within my report. The macro is so I don’t have to open the report, or its corresponding query to update the separate table of totals, because of the length of time my computer wants to take to display the information. Basically, I’m impatient.
My query for displaying this report is what I would call, not the best designed, I’m sure. It pulls from separate tables, and displays several fields, all of which are calculated by sums of the different entities.
I’ve tried to Update my totals table from the separate saved SELECT query, but I could not get that to work, either. I assume that it has to come from a table. Apparently, I need to work on syntax for macros and sql. I’ll keep plugging. Thanks for the help.

cpb · April 24, 2024, 1:09pm

@TNBuilder,

table or field names which include spaces or special characters are always likely to be problematic.
spaces can be replaced with the underscore character ‘_’ i.e. “Expenses_Table”.
never use a slash or back-slash i.e. you have the field “NoUnits\”, why not just call it “NoUnits” ?
storing calculated values violates the rules of normalisation.
.
I may be able to help with your SQL.
.
hit the Queries icon.
hit Create Query in SQL View.
paste this code and execute:

SELECT 
	SUM("NoUnits\" * "Power" * "OpDays" * 24 / 1000 * 0.09983)
	+
	(
		SELECT SUM("Cost") 
		FROM "Expenses Table" 
		WHERE "ExpCatID" = 8
		AND "AcctID" BETWEEN 4999000 AND 4999999
		AND "Date" BETWEEN '2024-05-01' AND '2024-05-31'
		AND "ConJobID" in(13,14)
	) 
FROM
	"Elec Est Table" 
WHERE
	"Finish Date" BETWEEN '2024-04-16' AND '2024-05-15'
	AND
	"ElecTotalID" = 8

I have assumed that “ExpCatID”, “AcctID”, “ConJobID” and “ElecTotalID” are integers.
.
the last 2 lines of your sql used 2 WHERE clauses which is an obvious error, I have merged them into a single clause.
.
if the code runs and the calculation is accurate then great, else post any error message in full.

TNBuilder · April 24, 2024, 1:59pm

Yeah, I was thinking so, about the table and field names. That means I have a big job ahead of me. The NoUnits - / was a typo from an earlier version of messing with this.
Just curious, why is storing calculated numbers not normalized? And, you’re right, I need help with my sql. I may have to hit you up. Thx.

RobertG · April 25, 2024, 2:25pm

Did you really use the internal database “Bibliography” (its a dBase-database)? Name should be the name for your registered database. Might be “New” when seeing al the screenshots. And might be a HSQLDB instead of dBase.

TNBuilder · May 1, 2024, 10:34pm

Yes, embedded HSQLDB. Thanks!

UnklDonald · April 24, 2024, 10:20pm

If one of the numbers used in the calculation changes the saved and calculated values won’t match.

An example is in bookkeeping applications where it is only safe to save invoice totals until a month end close is performed and the invoices can no longer be changed.

TNBuilder · April 25, 2024, 12:57pm

Thank you for the answer. Now, if I may ask another that pertains to this topic: I understand that table and field names can cause problems. But, a lot of my tables and field names have spaces within. I use a bunch of macros to open forms and reports, both basic and python. I don’t have any problems with these macros. Only now have I run into this problem: ie the use of spaces, or possibly because of incorrectly written calculated subqueries within a macro. Being untrained makes me feel that this only might be the problem, and that it is possible another problem is lurking within my syntax. The macro I used above, and later coupled with the SQL you helped with does not work. For brief’s sake: I use calculated numbers to place in a table that I use for a graph in a report. I am sure there is a better way, but this is the way I found. The macro would be assigned to a button and is to perform those calculations and place them into the table.
BTW, I’ve put msgbox strsql and it shows me the complete query in a box then shows the error msg box saying Syntax error. But, if I get the syntax correct as I can, then it gives me an error No Valid Table in the msgbox. I will run those again and place them in the post.
My question is: Why would spaces cause this type of problem when it doesn’t with other macros ( I know it may be a dumb question) and is it possible I just have my macro written incorrectly? Thank for any type of answer.

Villeroy · April 25, 2024, 1:58pm

Spaces are not a problem as long as the name is in double-quotes.

cpb · April 25, 2024, 4:26pm

@TNBuilder,
I gave instructions on testing the SQL but as there has been no feedback don’t know the outcome.
.
I will not assist anyone in breaching the principles of normalisation.
.
the basic code in your initial post contains 4 errors.

only you have access to your revamped code if you do not wish to share it then how can anyone provide objective feedback.

TNBuilder · April 25, 2024, 7:56pm

Okay, I appreciate your info. There may be errors in my coding, I am totally willing to admit that. I will place what I have below. I have no problem sharing my files, was just unsure how much y’all wanted.
Your description of the bookkeeping example is exactly what I am trying to achieve. Daily or intermittently updated calculations from how I keep track of electricity is placed into a table, then is no longer calculated after the month’s end. I would create a new macro to deal with the new month.
Please excuse any ignorance: My database is embedded HSQLDB . My database name us New.odb but I saw somewhere to use Bibliography.

Sub UpdateCost
    Dim oContext As Object
    Dim oDatabase As Object
    Dim oConnection As Object
    Dim oStatement As Object
    Dim sSQL As String
    
    ' Get the database context
    oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    
    ' Replace "DB_NAME" with your actual database name
    oDatabase = oContext.getByName("Bibliography")
    
    ' Connect to the database (you may need to provide username and password)
    oConnection = oDatabase.getConnection("", "")
    
    ' Create the SQL statement
    sSQL = "UPDATE ""Electricity Monthly Totals"" SET ""Cost"" = " & _
           "(SELECT " & _
	"SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983)" & _
	"+" & _
	"(" & _
		"SELECT SUM(""Cost"")" & _ 
		"FROM ""Expenses Table""" & _
		"WHERE ""ExpCatID"" = 8" & _ 
		"AND ""AcctID"" BETWEEN 4999000 AND 4999999" & _
		"AND ""Date"" BETWEEN '2024-05-01' AND '2024-05-31'" & _
		"AND ""ConJobID"" in(13,14)" & _
	")" & _ 
"FROM" & _
	"""Elec Est Table""" & _ 
"WHERE" & _
	"""Finish Date"" BETWEEN '2024-04-16' AND '2024-05-15'" & _
	"AND" & _
	"""ElecTotalID"" = 8) " & _
           """Where ""Month"" = 'May24'"
    msgbox (sSQL)
    ' Execute the SQL query
    oStatement = oConnection.createStatement()
    oStatement.executeUpdate(sSQL)

cpb · April 26, 2024, 12:15am

I repeat, did you test the SQL according to my instructions?
did it execute successfully? it’s vital you confirm that it did.
.
the macro code you posted in post #15 25 Apr 20:56 contains several errors.
.
a well constructed, normalised database can easily calculate and display the required data without the need for any of this macro rubbish.
.
I do not have access to your database, am unable to test anything and without any meaningful feedback.
if the SQL does function as expected and your database is registered then try this:

Sub UpdateCost
    Dim oContext As Object
    Dim oDatabase As Object
    Dim oConnection As Object
    Dim oStatement As Object
    Dim sSQL As String
    
    ' Get the database context
    oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    
    ' Replace "DB_NAME" with your actual database name
    oDatabase = oContext.getByName("New")
    
    ' Connect to the database (you may need to provide username and password)
    oConnection = oDatabase.getConnection("", "")
    
    ' Create the SQL statement
    sSQL = "UPDATE ""Electricity Monthly Totals"" SET ""Cost"" = " & _
    "(" & _
    	"SELECT " & _
			"SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983)" & _
			"+" & _
			"(" & _
				"SELECT SUM(""Cost"") " & _ 
				"FROM ""Expenses Table"" " & _
				"WHERE ""ExpCatID"" = 8 " & _ 
				"AND ""AcctID"" BETWEEN 4999000 AND 4999999 " & _
				"AND ""Date"" BETWEEN '2024-05-01' AND '2024-05-31' " & _
				"AND ""ConJobID"" in(13,14)" & _
			") " & _ 
		"FROM " & _
			"""Elec Est Table"" " & _ 
		"WHERE " & _
			"""Finish Date"" BETWEEN '2024-04-16' AND '2024-05-15' " & _
			"AND " & _
			"""ElecTotalID"" = 8 " & _
			"AND " & _
			"""Month"" = 'May24'" & _
	")"
          
'    msgbox (sSQL)
    ' Execute the SQL query
    oStatement = oConnection.createStatement()
    oStatement.executeUpdate(sSQL)
End Sub

TNBuilder · April 28, 2024, 9:03pm

Hi cpb, Yes, after a few modifications to get my prior syntax errors and make the tables right, the sql given to me on 4/24 @ 9:09 works correctly. Thank you, this is a condensed version of what I had. This is how I made it work:

SELECT 
	SUM("NoUnits" * "Power" * "OpDays" * 24 / 1000 * 0.09983)
	+
	(
		SELECT SUM("Cost") 
		FROM "Expenses Table" 
		WHERE "ExpCatID" = 8
		AND "AcctID" BETWEEN 4999000 AND 4999999
		AND "Date" BETWEEN '2024-04-01' AND '2024-04-30'
		AND "ConJobID" in(13,14)
	) 
FROM
	"Elec Est Table" 
WHERE
	"FinishDate" BETWEEN '2024-03-16' AND '2024-04-15'

However, I could not make this work in the UPDATE statement in the macro. I get syntax errors, so I know I am messing up somewhere. It looks like this:

Sub UpdateCost
    Dim oContext As Object
    Dim oDatabase As Object
    Dim oConnection As Object
    Dim oStatement As Object
    Dim sSQL As String
    
    ' Get the database context
    oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    
    ' Replace "DB_NAME" with your actual database name
    oDatabase = oContext.getByName("New")
    
    ' Connect to the database (you may need to provide username and password)
    oConnection = oDatabase.getConnection("", "")
    
    ' Create the SQL statement
    sSQL ="Update ""Electricity Monthly Totals""" & _
               "Set ""Cost"" =  " & _
                    "(SELECT " & _
	                "SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983)" & _
                    "+" & _
	                "(" & _
		            "SELECT SUM(""Cost"")" & _ 
		            "FROM ""Expenses Table""" & _ 
		            "WHERE ""ExpCatID"" = 8" & _
		            "AND ""AcctID"" BETWEEN 4999000 AND 4999999" & _
		            "AND ""Date"" BETWEEN '2024-04-01' AND '2024-04-30'" & _
		            "AND ""ConJobID"" in(13,14)" & _
	                ")" & _ 
               "FROM" & _
	           """Elec Est Table""" & _ 
               """WHERE""" & _
	           """FinishDate"" BETWEEN '2024-03-16' AND '2024-04-15' )  AS ""Apr 2024 Total Bill""" & _
               "FROM ""Elec Est Table"" WHERE ""FinishDate"" BETWEEN '2024-03-16' AND '2024-04-15' )" & _
               "Where ""ElecTotalID"" = '8'"
          
'    msgbox (sSQL)
    ' Execute the SQL query
    oStatement = oConnection.createStatement()
    oStatement.executeUpdate(sSQL)
End Sub

Yet, I was able to get to a different error message with the macro code using my longer version of sql, and assuming that I corrected syntax errors. The error message stated in a Message Box that no valid table:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: The query cannot be executed. It contains no valid table..

So, I think that I am messing up with syntax up to a point , then I get that correct, then I get this last message about invalid tables. This is what started me thinking that spaces in names were causing problems, but @Villeroy said that double quotes should work earlier in the thread. So, I am not sure what to think.

I can use your sql, or mine, to perform the SELECT statement and acquire the correct total. I can also use SQL–TOOLS and perform the update statement manually there. And it works too. My UPDATE statement looks like this:

Update "Electricity Monthly Totals"
Set "Cost" =  
  (SELECT SUM( ( "NoUnits" * "Power" * ( ( "OpDays" * 24 ) / 1000 ) ) * .09983 ) + 

( SELECT SUM( "Cost" ) FROM "Expenses Table" WHERE "ExpCatID" = '8' AND ( "AcctID" BETWEEN '4999000' AND '4999999' ) AND ( "Date" BETWEEN '2024-05-01' AND '2024-05-31' ) AND "ConJobID" = '14' ) + 

( SELECT SUM( "Cost" ) FROM "Expenses Table" WHERE "ExpCatID" = '8' AND ( "AcctID" BETWEEN '4999000' AND '4999999' ) AND ( "Date" BETWEEN '2024-05-01' AND '2024-05-31' ) AND "ConJobID" = '13' )  AS "May 2024 Total Bill"

FROM "Elec Est Table" WHERE "FinishDate" BETWEEN '2024-04-16' AND '2024-05-15' )
 
Where "ElecTotalID" = '9'

But alas, when I try to place this update statement in a macro, correct syntax as best as possible, and assign to a button, I receive syntax or no valid table errors. I think I am close, but just not quite there. Thank you for the help you have given and for any future endowment.

cpb · April 28, 2024, 11:35pm

when concatenating lines it’s wise to add a space before the end of line " (double quote).
e.g. 4999999AND should read 4999999 AND
.
remember because of the method you are using to connect, the database called “New” must be registered.
replace your existing sSQL with this:
EDIT: sorry just noticed you have added the field "FinishDate"

    sSQL = "UPDATE ""Electricity Monthly Totals"" SET ""Cost"" = " & _
    "(" & _
    	"SELECT " & _
			"SUM(""NoUnits"" * ""Power"" * ""OpDays"" * 24 / 1000 * 0.09983) " & _
			"+ " & _
			"(" & _
				"SELECT SUM(""Cost"") " & _ 
				"FROM ""Expenses Table"" " & _
				"WHERE ""ExpCatID"" = 8 " & _ 
				"AND ""AcctID"" BETWEEN 4999000 AND 4999999 " & _
				"AND ""Date"" BETWEEN '2024-05-01' AND '2024-05-31' " & _
				"AND ""ConJobID"" in(13,14) " & _
			") " & _ 
		"FROM ""Elec Est Table"" " & _
		"WHERE ""FinishDate"" BETWEEN '2024-03-16' AND '2024-04-15' " & _
	") " & _
	"WHERE ""ElecTotalID"" = 9"

TNBuilder · April 29, 2024, 1:06am

Thank you, that cleaned up the syntax for me, and thanks for the tips.
In my Options, it says that my “New” database is listed as registered. Is there another way I should confirm? I am still getting the No Valid Table error
2024-04-28 (2)

RobertG · April 29, 2024, 6:58am

Please show the sql-code by msgbox sSQL.