[Solved] Base - Insert into a date database field either no value either a date value

asked 2020-01-28 15:53:11 +0100

greb gravatar image

updated 2020-01-28 18:10:20 +0100

Hello,

I come here after many tests and web browsing…

I have a Base-embedded Firebird database. From a dialog box, I get in a macro the value from the date control, in order to insert it into the table through a SQL command.
The fact is that it is not mandatory to fill the date control, and so some record will come with no date value, while other yes.

My questions are

  • how to declare the variable in the macro (variant ? string ? date ? integer ? long ?)
  • which value and form give it
  • how to input it in the SQL command.

For the moment, I've tried:

DIM mydateyear AS VARIANT
DIM mydatemonth AS VARIANT
DIM mydateyear AS VARIANT
DIM mydate AS VARIANT
...

mydateyear = Dlg.getControl("DateControl").Date.Year
mydatemonth = Dlg.getControl("DateControl").Date.Month
mydateday = Dlg.getControl("DateControl").Date.Day
If dateyear = 0 Then 'meaning no date has been selected'
    mydate = "NULL"
Else
    mydate = mydateyear & "-" & mydatemonth & "-" & mydateday ' to get the appropriate format yyyy-mm-dd'
End If
...
sSQL = "INSERT INTO ""myTable"" (""myDateField"") VALUES ('"+mydate+"')"

This works well when a date is provided in the control in the dialog. But i get an error when no date is provided. Alternatively, I've tried the following:

'same as above'
sSQL = "INSERT INTO ""myTable"" (""myDateField"") VALUES (" & mydate & ")"

This option works well with no date input (NULL option), but not when a date is provided.

What could be the compromise so that in both cases (date/no date) the code works?

Thank you very much for your help! Benjamin


[EDIT] & [SOLUTION]

Having tried again, and looked deeper inside documentation (particularly OpenOffice.org french document Initiation à l'utilisation de SQL avec OOo Basic dans Base), it seemed that it was a problem of quote…

So here is the option that seemed to work in both cases (input date/no input date):

...
If dateyear = 0 Then 'meaning no date has been selected'
    mydate = "NULL"
Else
    mydate = "'" & mydateyear & "-" & mydatemonth & "-" & mydateday & "'"
    ' to get the appropriate format yyyy-mm-dd'
End If
...
sSQL = "INSERT INTO ""myTable"" (""myDateField"") VALUES (" & mydate & ")"
edit retag flag offensive close merge delete

Comments

Dear @greb,

Thanks.

lonk gravatar imagelonk ( 2020-10-18 11:05:21 +0100 )edit

Dear @Ionk Sorry for delay in replying to your comment… Did you manage to sort out for you? I suggest that you ask a new question thread if needed. Thanks

greb gravatar imagegreb ( 2020-10-29 07:40:20 +0100 )edit