Ask Your Question
0

LO Base: What is wrong with my Insert Into Statement? [closed]

asked 2013-06-20 11:25:07 +0200

Dycius gravatar image

updated 2015-09-07 12:58:58 +0200

Alex Kemp gravatar image

Hi all,

I'm trying to do an INSERT INTO statement in a macro, however I get the following error message when the SQL is executed.

image Error Message

I will paste the whole code so you can see what's going on. It doesn't have everything yet which is why there are extra variables.

Sub Insert_Row_Days_Employees (oEvent As Object)
    Dim oDoc AS OBJECT
    Dim oDrawpage AS OBJECT
    Dim oFormStore AS OBJECT
    Dim oFormFiscalWeek AS OBJECT 
    Dim oFormFiscalDays AS OBJECT
    Dim oFormRota AS OBJECT
    Dim iStoreID AS INTEGER
    Dim iWeekID AS LONG
    Dim dSun AS DATE
    Dim dMon AS DATE
    Dim dTue AS DATE
    Dim dWed AS DATE
    Dim dThu AS DATE
    Dim dFri AS DATE
    Dim dSat AS DATE
    Dim oStatement as object
    Dim sSQL AS STRING
    Dim sColumns AS STRING
    Dim sValues AS STRING

    oDoc = thisComponent
    oDrawpage = oDoc.Drawpage
    oFormStore = oDrawpage.forms.getByName("Store")
    oFormFiscalWeek = oFormStore.getByName("FiscalWeek")
    oFormFiscalDays = oFormFiscalWeek.getByName("FiscalDays")
    oFormRota = oFormFiscalWeek.getByName("Rota")

    oStatement = oFormFiscalWeek.ActiveConnection.createStatement()

    oFormFiscalWeek.UpdateRow
    iStoreID = oFormStore.Columns.GetByName("PK_S_Num").Value
    iWeekID = oFormFiscalWeek.Columns.GetByName("PK_FW_ID").Value
    dSun = CDateFromISO(oFormFiscalWeek.GetByName("datActualStartDate").CurrentValue)

    dMon = DateAdd("d",1,dSun)
    dTue = DateAdd("d",2,dSun)
    dWed = DateAdd("d",3,dSun)
    dThu = DateAdd("d",4,dSun)
    dFri = DateAdd("d",5,dSun)
    dSat = DateAdd("d",6,dSun)

    sColumns = ""
    sColumns = sColumns & """FK_FD_FW_ID"""
    sColumns = sColumns & ", ""Sun_Actual_Date"""
    sColumns = sColumns & ", ""Mon_Actual_Date"""
    sColumns = sColumns & ", ""Tue_Actual_Date"""
    sColumns = sColumns & ", ""Wed_Actual_Date"""
    sColumns = sColumns & ", ""Thu_Actual_Date"""
    sColumns = sColumns & ", ""Fri_Actual_Date"""
    sColumns = sColumns & ", ""Sat_Actual_Date"""

    sValues = ""
    sValues = sValues & iWeekID
    sValues = sValues & ", {D '" & dSun & "'}"
    sValues = sValues & ", {D '" & dMon & "'}"
    sValues = sValues & ", {D '" & dTue & "'}"
    sValues = sValues & ", {D '" & dWed & "'}"
    sValues = sValues & ", {D '" & dThu & "'}"
    sValues = sValues & ", {D '" & dFri & "'}"
    sValues = sValues & ", {D '" & dSat & "'}"

    sSQL = "INSERT INTO ""FiscalDay"" (" & sColumns & ") VALUES (" & sValues & ")"
    MsgBox(sSQL)

    oStatement.executeUpdate(sSQL)
    oFormFiscalDays.reload
End Sub

Here is an image of what the human readable sql statement looks like:

image Human Readable SQL

Thanks, Jon

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-21 14:58:14.460010

1 Answer

Sort by » oldest newest most voted
0

answered 2014-04-27 18:50:07 +0200

Alex Thurgood gravatar image

Not sure, but shouldn't you be using the preparedStatement object rather than just the statement object ?

See for example, here : http://api.libreoffice.org/docs/idl/r...

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-06-20 11:25:07 +0200

Seen: 1,111 times

Last updated: Apr 27 '14