Executeupdate says table not found

[dmcdivitt]

This is my first libreoffice base application. I keep getting the following error:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: Table not found in statement [delete from dates].

I have a table named “dates” that has one column “page_date”. That will be joined with a task table containing tasks for a report and all tasks will be printed on a separate page for each date. I have a form prompting for from date and thru date and a create button. Clicking the create button runs a macro having the following code:

dim connect as object, statement as object 'see also ThisDatabaseDocument 
connect = thiscomponent.parent.datasource.getconnection("","")
if isnull(connect) then
  msgbox "not connected"
  exit sub
  end if
statement = connect.createstatement()
statement.executeupdate("delete from dates")

It fails on the line to erase the dates table. I also tried creating a connection using a context and CreateUnoService after registering the database and have the same result. Instead of “delete from dates” I tried “delete from PUBLIC.dates” with same result.

[RobertG]

Depending on database you are using the table names might be set upper cased.
“delete from dates” will be looking for a table “DATES”.
Try

statement.executeupdate("delete from ""dates""")

It will look for “dates”, not for “DATES”.

[dmcdivitt]

(post deleted by author)

[dmcdivitt]

@RobertG , thanks for the fix. Further on down in other code I needed to do something similar:

while d_page <= d_last
  statement.executeupdate("insert into ""dates""(""page_date"") values('" & format(d_page,"yyyy-mm-dd") & "')"
  d_page = dateadd("d",1,d_page)
  wend

Having to embed double-quotes around named database items is screwy, but I;m happy learning something new and getting away from ms access and sql server in my retirement.