Running parameter query using macro on a form.

[LO_user1]

Hi, I have to insert record in a Subform by pushing a button on Mainform using following macro. This macro use a query with a parameter “:sid”. This parameter value have to be taken from Mainform control “Stud_ID”.

SUB Insert_Fee
    DIM oDatabaseFile AS OBJECT
    DIM oQuery AS OBJECT
    DIM stQuery AS STRING
    Dim oForm  As Object
    Dim Result
    Dim iStudNumber  As Integer
    Dim oStmt As Object
    oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
    iStudNumber = oForm.Columns.getByName("Stud_ID").Value
    oQuery = oDatabaseFile.getQueryDefinitions()
    stQuery = oQuery.getByName("Insert_Fee").Command
    stQuery = Replace(stQuery, ":sid","?")
    oConnection = Thisdatabasedocument.CurrentController.ActiveConnection
    oStmt = oConnection.createStatement()
    oStmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
    oStmt = oConnection.prepareCall(stQuery)
    oStmt.setInt(1, iStudNumber)
    oResult = oStmt.execute()
    oStmt.close()
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
    oForm.Filter = "( Stud_ID = " & iStudNumber & ")"
	oForm.Reload()
END SUB

The query name is “Insert_Fee” which is following. Subform taken from table “fee_trans”.

INSERT INTO "fee_trans"("Type","Month","ST_ID","Amount") VALUES ('TF','JUN',(SELECT "ST_ID" FROM "Fee_Define" WHERE "ST_ID" = :sid),(SELECT "Fee" FROM "Fee_Define" WHERE "ST_ID" = :sid))

This macro insert 3 values only but the fourth one remain empty with no error showing. I am using LO version 6.2 on Windows 10. Database is local HSQLDB.
Can you please check the macro code and query and point out where is the problem.

[SM_Riga]

Edited for readability.

[ztminhas]

Hello

is there is a reason why oStmt was redefined:

oStmt = oConnection.createStatement()
oStmt.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
oStmt = oConnection.prepareCall(stQuery)

Thank you

[Ratslinger]

Hello,

It appears your macro is working just as you coded it to do.

stQuery = Replace(stQuery, ":sid","?")

:sid appears twice in your Insert statement. Therefore you need two different substitutions.

oStmt.setInt(1, iStudNumber)

is one but you need to fill the second:

oStmt.setInt(2, iStudNumber)

Now as for the form, you are reloading oForm and filtering the result with iStudNumber. Seems this would display one record. If not what is wanted, why filter?

Also, please include vital information in your questions - specific LO version; OS; database used.

Above may differ depending upon what is being used.

Edit:

Have done some further reviewing. Seems I have previously presented you with how to set multi-parameters in this post → Using SQL cursor and loop in LO base macro. .

Also, these posts may interest you:

SOLVED: Read values from selected rows in a table control

Base Table Grid Control restore the rows selection after applying a filter

[LO_user1]

Hi, Ratslinger! Thank you for your quick answer. I have corrected my code as you guided and it is working fine. The filter portion is Ok so I edited my question. (You can edit your answer) The code is working with one parameter when I use it with mysql database and this query is a procedure there. I call it as (“CALL Insert_fee(?)”) as you advised in my previous question.

[Ratslinger]

Simply confused at this latest comment. My answer already answers your question since you dropped the record displayed portion.

The previous question (you mentioned & link in my answer) has two parameters:

"CALL `fetch_students`(?,?)"

This is also what you have in your sample above :

stQuery = Replace(stQuery, ":sid","?")

Since :sid occurs twice, there will be two ? in the statement which need to be filled. My answer already states the second one needing to be added:

oStmt.setInt(2, iStudNumber)

As far as I can tell your question was already answered before you modifications or comment. Am I missing something? Maybe the confusion is you stating - You can edit your answer. Don’t know why it needs editing.

[LO_user1]

Actually my previous question and your answer was on a procedure with cursor and that have two parameters to insert students marks. But later I used the same macro you provided with the simple SQL insert query shown in this question with one parameter in the procedure to insert student fee. I understood that it need two time for local query because [:sid] was used two time but one time with mysql procedure because you have one parameter defined there although you are using in two places.