How to solve the error on oStatement.executeUpdate()

[lonk]

Sometimes there was an error on line#36.

What code should be needed more ?

Edited:

Attached new revised file:
0035DisposedoStatement_Revised.ods (51.8 KB)

Option Explicit

Sub SaveData

Dim db, oStatement, oResult As Object
Dim CursorTest As Variant
Dim sSql, dDatabase As String
dDatabase = "file://" & Environ("HOME") & "/Documents/LibreOffice/LibreOfficeAskLibreOffice/0035DisposedoStatement/" & "inventory1.odb"

	Dim fFruitCode,wWeekNumber As String
	Dim qQuantity As Double
	
	fFruitCode = ThisComponent.Sheets.getByName("UI").getCellRangeByName("C4").String
	wWeekNumber = ThisComponent.Sheets.getByName("UI").getCellRangeByName("C6").String
	qQuantity = ThisComponent.Sheets.getByName("UI").getCellRangeByName("C7").Value

'	Check available balance	
	If 	qQuantity < 0 Then
		If (getBalance(fFruitCode) + qQuantity) < 0 Then
			MsgBox "Fruit balance is insufficient.",,"Error"
			Stop
		End If
	End If
	
	On Local Error GoTo CloseConn
	sSql = "SELECT DISTINCT ""FruitCode"" FROM ""Table1"" WHERE ""FruitCode"" = '" & fFruitCode & "'"
	db = ConnectDatabase(dDatabase)
	oStatement = db.CreateStatement
	oResult = oStatement.ExecuteQuery(sSql) ' Line 36'
	CursorTest = oResult.first
'	Check if selection exists'
	If	CursorTest = "True" Then
'		When code is correct, add to table.'
		sSql = "INSERT INTO ""Table1""(""FruitCode"",""WeekNumber"",""QuantityInOutKilogram"") VALUES('" & fFruitCode & "', '" & wWeekNumber & "', '" & qQuantity & "')"
		oStatement = db.CreateStatement
		oStatement.executeUpdate(sSql)
	Else
		MsgBox "Wrong fruit code",,"Error"
	End If
         MsgBox "Data saved successfully",,"Result"
	Exit Sub

CloseConn:
	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")",,"Error"
	DisconnectDatabase(db)

End Sub

Function getBalance(pFruitCode)

Dim db, oStatement, oResult,oRowSet As Object
Dim dbf, sSql, dDatabase As String
dDatabase = "file://" & Environ("HOME") & "/Documents/LibreOffice/LibreOfficeAskLibreOffice/0035DisposedoStatement/" & "inventory1.odb"

	sSql = _
	"SELECT ""FruitCode"", SUM( ""QuantityInOutKilogram"" ) " & _
	"FROM ""Table1"" GROUP BY ""Table1"".""FruitCode"" " & _
	"HAVING ( ( ""FruitCode"" = '" & pFruitCode & "' ) ) ORDER BY ""FruitCode"" ASC"

	On Local Error GoTo CloseConnection
	dbf 		= dDatabase
	db 			= ConnectDatabase(dbf)
	oStatement 	= db.CreateStatement

	oRowSet 	= GetRowSet(db, sSql)

	While 	oRowSet.Next
	
		If 	(GetRowSet(db, sSQL).RowCount = 1) Then
			getBalance = Val(oRowSet.GetString(2))
		End If

	Wend	

	DisconnectDatabase(db)

	Exit Function
	
CloseConnection:
	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
	DisconnectDatabase(db)

End Function

Function ConnectDatabase(dbFilename$) As Object
	Dim dbContext 	As Object 	: dbContext 	= createUNOService("com.sun.star.sdb.DatabaseContext")
	Dim oDataSource As Object 	: oDataSource 	= dbContext.GetByName(dbFilename)
	ConnectDatabase = oDataSource.GetConnection("","")'>>("Username","Password")'
End Function

Sub DisconnectDatabase(db)
	db.getParent().flush
	db.Close
	db.Dispose()
End Sub

Function GetRowSet(db As Object, iSQL$) As Object
	
	Dim oRowSet As Object : oRowSet = CreateUNOService("com.sun.star.sdb.RowSet")
	With oRowSet
		.ActiveConnection 	= db
		.Command 			= iSQL
		.Execute
	End With
	GetRowSet 				= oRowSet
End Function

inventory1.odb (3.8 KB)
0035DisposedoStatement.ods (45.9 KB)

LibreOffice:
Version: 7.3.6.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.6-0ubuntu0.22.04.2
Calc: threaded

Base:
Firebird

OS:
Ubuntu GNOME Desktop 22.04 LTS

[Lupp]

Your bug occurs concerning a different component. I’m lacking specific experience insofar.
However there may be a relation to the bug tdf#151376 which also exposes a flaw in the internal bookkeeping, imo, and wasn’t present in earlier versions.

[Villeroy]

Why that complicated? In the attached database an additional main form on the “Output” form keeps track of negative balances.
This requires a tiny piece of generic code. Apache OpenOffice Community Forum - [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes - (View topic)

SimpleInventory_embedded.odb (58.2 KB)

[Lupp]

@Villeroy

Why that complicated?

???

[Villeroy]

Base macros are by a magnitude less comprehensible than Calc macros. Trying to intercept user input this way is a nightmare. One solution would be double-entry accounting with a proper database engine by means of transactions (subtract from one account and add to another account, do nothing if either one violates some precondition). The database engine would reject the transaction and throw an error. I don’t think, this can be done with embedded HSQL.
With simple HSQL databases I have a long lasting experience with simple measures like the demonstrated one. If the database engine can not prevent invalid input, just display any offending records on the user interface. The attached Python macro is my Swiss knife for tasks like this. It updates related form(s), listbox(es) or combo box(es) whenever some record has been updated, inserted or edited. It works without any code modification with a so called “hidden control” saving the configuration.

[cpb]

@lonk,
I see that I tried to help with this 12 days ago.
you received 2 replies and failed to respond to either, also the topic was not marked solved.
if you fail to respond then other users learn little or nothing from the thread.
this site is is not a paid for, personal service.
link to guidelines: Guidelines for asking

if this thread remains unresolved then I will post a solution in around 12 days time.
your issue is not the result of a bug.
I agree with Villeroy, this seems to be grossly overcomplicated, it appears the OP is inserting literal values via a macro, stored in a spreadsheet, into a database table. without sufficient information however we can do no more than speculate.

[lonk]

Thank you so much for your concern and sorry for looking over upon either all answers and comments .

[lonk]

Thank you, I use embedded Firebird.

[lonk]

I have revised and attached the Calc file. In my real accounting project, I use Calc sheet as UI where users enter the data and macro saves them into Firebird .odb database. I also use code to rendor report from .odb file for users will have the instant and ready-to-use report.

[Villeroy]

Tools>Options>Base>Databases
Click [Add…] and register yesterday’s database of mine as “SimpleInventory_embedded”.
Store the attachment 83298.ods to a trusted directory, open it and enter values tabbing through the form controls.
Select the inventory sheet. The document contains one line of Basic code refreshing the inventory on sheet activation.
83298.ods (23.7 KB)

P.S. same database with embedded Firebird:
SimpleInventory_embedded_FB.odb (55.3 KB)

[cpb]

you are using Firebird.
INSERT… inserts values into a buffer.
FLUSH writes the buffer to disk.

‘Sub SaveData’
when new data is INSERTed it is not FLUSHed.
all you have to do is INSERT then FLUSH.

if the database is open and a valid transaction is made via your original spreadsheet then the database save icon indicates that the buffer contains data.
close the database without saving.
reopen the database.
it’s now impossible to insert new records via the spreadsheet or manually via the database table.
if you try, then the error message is generated and if you wish to continue inserting new records into the database, the office suite requires a restart.

if you step through your original code line by line via the IDE you should be able to spot obvious errors.
here is the original code you posted, it contains edits made in relation to the above, plus a few other minor alterations.

Option Explicit

Sub SaveData

Dim db, oStatement, oResult As Object
Dim CursorTest As Variant
Dim sSql, dDatabase As String
dDatabase = "file://" & Environ("HOME") & "/Documents/LibreOffice/LibreOfficeAskLibreOffice/0035DisposedoStatement/" & "inventory1.odb"

	Dim fFruitCode,wWeekNumber As String
	Dim qQuantity As Double
	
	fFruitCode = "0001"
	wWeekNumber = "XX"
	qQuantity = 1.00

'	Check available balance	
	If 	qQuantity < 0 Then
		If (getBalance(fFruitCode) + qQuantity) < 0 Then
			MsgBox "Fruit balance is insufficient.",,"Error"
			Stop
		End If
	End If
	
	On Local Error GoTo CloseConn
	sSql = "SELECT DISTINCT ""FruitCode"" FROM ""Table1"" WHERE ""FruitCode"" = '" & fFruitCode & "'"
	db = ConnectDatabase(dDatabase)
	oStatement = db.CreateStatement
	oResult = oStatement.ExecuteQuery(sSql)
	CursorTest = oResult.first
'	Check if selection exists
	If	CursorTest = "True" Then
'		When code is correct, add to table.
		sSql = "INSERT INTO ""Table1""(""FruitCode"",""WeekNumber"",""QuantityInOutKilogram"") VALUES('" & fFruitCode & "', '" & wWeekNumber & "', '" & qQuantity & "')"
'		oStatement = db.CreateStatement
		oStatement.executeUpdate(sSql)
		db.parent.flush
	Else
		MsgBox "Wrong fruit code",,"Error"
	End If

	db.close
	Exit Sub

CloseConn:
	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")",,"Error"
'	DisconnectDatabase(db)

End Sub

Function getBalance(pFruitCode) as double

dim fQty as double
'Dim db, oStatement, , oRowSet As Object
dim db as object, oResult as object
'Dim dbf, sSql, 
dim sSql As String, dDatabase As String
dDatabase = "file://" & Environ("HOME") & "/Documents/LibreOffice/LibreOfficeAskLibreOffice/0035DisposedoStatement/" & "inventory1.odb"

	sSql = _
	"SELECT SUM( ""QuantityInOutKilogram"" ) " & _
	"FROM ""Table1"" WHERE ""FruitCode"" = '" & pFruitCode & "' " '&_
'	"GROUP BY ""FruitCode"""
	
'	"SELECT ""FruitCode"", SUM( ""QuantityInOutKilogram"" ) " & _
'	"FROM ""Table1"" GROUP BY ""Table1"".""FruitCode"" " & _
'	"HAVING ( ( ""FruitCode"" = '" & pFruitCode & "' ) ) ORDER BY ""FruitCode"" ASC"

	On Local Error GoTo CloseConnection
'	dbf 		= dDatabase
	db 			= ConnectDatabase(dDatabase)
'never executed:	oStatement 	= db.CreateStatement

oResult = GetRowSet(db, sSql)
if oResult.next then fQty = cdbl(oResult.GetString(1)) 'qty
getBalance = fQty

'	oRowSet 	= GetRowSet(db, sSql)

'	While 	oRowSet.Next
'	
'		If 	(GetRowSet(db, sSQL).RowCount = 1) Then
'			getBalance = Val(oRowSet.GetString(2))
'		End If
'
'	Wend	

db.close
'	DisconnectDatabase(db)

	Exit Function
	
CloseConnection:
	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
'	DisconnectDatabase(db)

End Function

Function ConnectDatabase(dbFilename$) As Object
	Dim dbContext 	As Object 	: dbContext 	= createUNOService("com.sun.star.sdb.DatabaseContext")
	Dim oDataSource As Object 	: oDataSource 	= dbContext.GetByName(dbFilename)

	ConnectDatabase = oDataSource.GetConnection("","")'>>("Username","Password")
End Function

'Sub DisconnectDatabase(db As Object)
'	db.getParent().flush
'	db.Close
'	db.Dispose()
'End Sub

Function GetRowSet(db As Object, iSQL$) As Object
'	Dr. Mark Alexander Bain
	Dim oRowSet As Object : oRowSet = CreateUNOService("com.sun.star.sdb.RowSet")
	With oRowSet
		.ActiveConnection 	= db
		.Command 			= iSQL
		.Execute
	End With
	GetRowSet 				= oRowSet
End Function

---

2 attachments, a database with an empty table and its spreadsheet partner.
the code that matters was done before my first post and before you updated your first post.
subsequently I have added bits and pieces, partly to provide info but mainly to aid integrity.
using a spreadsheet in preference to a database form makes no sense to me.
if you choose to download, load the spreadsheet, go to Function GetConnection, set the path to the database “M_fb.odb”, save, close and reload.
in testing I input over 1000 records in small batches over time without error. inserts are made via the menu ‘Run_Macro’. the database may be open or closed and used at will.
M_fb.ods (17.9 KB)
M_fb.odb (6.6 KB)