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.
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:
Thanks,
Jon