# Running parameter query using macro on a form.

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 & ")"
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.

edit retag close merge delete

( 2019-07-06 18:57:11 +0200 )edit

Sort by » oldest newest most voted

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

more

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.

( 2019-07-07 02:12:25 +0200 )edit

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.

( 2019-07-07 03:19:44 +0200 )edit

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.

( 2019-07-07 17:09:15 +0200 )edit