LO Base SQL Insert in Macro - 2

edit

Hi,

I am trying to do a similar thing to the previous thread (LO Base SQL Insert in Macro).. and am starting with the code that's already posted there, and made some tweaks..

I get an runtime error message at the line "Stmt.executeUpdate(strSQL)". I'm on HDSQLDB embedded, MacOS Mojave 10.14.6 and LibreOffice 6.4.5 (mac 64-bit). Will appreciate the help.

Thanks! -Steph

Error Message:

BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException Message: Wrong data type: java.lang.IllegalArgumentException.

The code i am working with:

Sub Insert2TblOrders 
Dim context 
Dim DB 
Dim Conn 
Dim Stmt 
Dim Result 
Dim TodaysDate as String 
Dim strSQL As String
    Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("IKK_V7") '<-
Conn = DB.getConnection("","")
    Stmt=Conn.createStatement()
    TodaysDate =Format(Now(),"dd/mm/yyyy")
    strSQL=
"INSERT INTO "&"""INVOICES"""& _
    "( " & """WARGANO""" & _
     ", INVOICEDAMOUNT"& _
    ", INVOICEDATE"& _
    ", INVOICEID"& _
    ", STATUSID"& _
    ", ORDER_ID)"& _   
"(Select "& _
    "WARGANO"& _
    ", sum(AMOUNT) AS INVOICEDAMOUNT"& _
    ", "&"'"& TodaysDate &"'" & " AS INVOICEDATE"& _
    ", concat('INV',""WARGANO"") AS INVOICEID"&_
    ", '103' AS STATUSID"& _
    ", ORDER_ID"& _
    " From "&"""ORDERS"""&"  group by    WARGANO,INVOICEID,STATUSID,ORDER_ID);"
Stmt.executeUpdate(strSQL) 
Conn.close() 
End Sub

(edit: codified code)