Form macro not handling date correctly

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.

Hello,

Regardless of any formatting of dates used, dates in a database are always stored as YYYY-MM-DD and therefore dates used in any SQL need to use this format. Here is a simple conversion of converting a test string to proper format for use in SQL:

myDate = "02/01/2019"
convertedDate =  Format(myDate, "'YYYY-MM-DD'")
1 Like

Thanks, Ratslinger, that did the trick. I just changed CtrlDate.currentValue to Format(CtrlDate.currentValue, "'YYYY-MM-DD'") in my SQL statement.

@jvglynnjr

As you have been helped, please help others to know the question has been answered by clicking on the :heavy_check_mark: in upper left area of answer which satisfied the question. You should do this on all answered questions.