I’m trying to pass a formatted field containing a date from a form to a table through an update query, but I can’t get it to work. The code below works when I switch the receiving column to a text-formatted one, but I need to be able to put this update into a date-formatted column.
This is the macro I’m using (relevant parts, anyway)
REM ***** BASIC *****
const TableName = "tbStockRoom"
const IDField = "SKU"
const ValueField = "On_Shelf"
REM This needs to be changed to "expDate"
const DateField = "bogus_text"
REM
global oConn as Object
global CtrlId as object, CtrlValue as object
global CtrlDate as object
Sub UpdateADDQuery(oEvent)
dim oQuery as Object
dim oStatement as Object
dim Result as long
dim SQL as String
dim oForm as Object
dim oField as Object
dim CtlView as Object
dim item as String
SQL = "update """ & TableName & """ set """ & ValueField & """ = """ & ValueField & """ + " & CtrlValue.currentValue & _
" , """ & DateField & """ = " & CtrlDate.currentValue & " where """ & IDField & """ = " & CtrlId.currentValue
oQuery = oConn.createStatement()
Result = oQuery.executeUpdate(SQL)
End Sub
Sub InitForm(oEvent)
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
CtrlId = oEvent.source.parent.getByName("Form2").getByName("fmtID")
CtrlValue = oEvent.source.parent.getByName("Form2").getByName("fmtInteger")
CtrlDate = oEvent.source.parent.getByName("Form2").getByName("fmtDate")
It runs fine as is, but when I switch DateField to a date-formated column, I get this error message:
BASIC runtime error.
An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: Wrong data type: java.lang.IllegalArgumentException.
I tried inserting a CAST() function into the SQL to force the parameter to be interpreted as date, but I must be doing it wrong because it always gives nonsense syntax. Could this have something to do with handling quotes? I don’t really understand why some of the above are single and not tripled, but like I said, this runs just fine.
Any help appreciated.