I am building a project that uses the Acess database and one field is a DBDATE type.
My Select statement does not want to work - do not know the correct syntax for this statement?
Snippet of code to show the error I have with - TheSql = "SELECT * FROM master WHERE TheDate = CurDate"
DBDATE CurDate
UINT hMaster
POINTER db
STRING DBase
' set pointers to NULL
db = NULL
hMaster = NULL
DBase = GETSTARTPATH + "scheduler.mdb"
' connect to the scheduler.mdb
db = dbConnect("Microsoft Access Driver (*.mdb)",DBase,"")
IF db = NULL
'couldn't open scheduler.mdb so try creating scheduler.mdb
dbCreateMDB(DBase)
' connec to the scheduler.mdb
db = dbConnect("Microsoft Access Driver (*.mdb)",DBase,"")
ENDIF
hMaster = dbExecSQL(db,"CREATE TABLE master (id counter(1,1), TheDate DATE, FullDate VARCHAR(40) )")
'........
STRING TheSql, FullDate, error
CurDate.day = VAL(DATE$("dd"))
CurDate.month = VAL(DATE$("mm"))
CurDate.year = VAL(DATE$("yyyy"))
'PROBLEM HERE....
' CurDate is of type DBDATE - TheDate Field in the Database Table is of type DBDATE
TheSql = "SELECT * FROM master WHERE TheDate = CurDate"
' TheSql = "SELECT * FROM master WHERE TheDate = #" + CurDate + "#"
hMaster = DBEXECSQL(db,TheSql)
error = DBGETERRORCODE(hMaster)
IF LEN(error)
MESSAGEBOX 0, "Error Code: " + error + "\n" + "Error Text: " + DBGETERRORTEXT(hMaster),_
"Select Query failed", @MB_OK|@MB_ICONINFORMATION
ENDIF
DBFREESQL(hMaster)
Thanks.
Sitting in Linux at the moment....but you are passing a string so the ODBC driver has no idea what you mean by 'CurDate'. You'll need to bind the parameter and use a ? as a placeholder, or perhaps just format a date string....
TheSql = "SELECT * FROM master WHERE TheDate = "+Date$
Which might require you to format the string in a specific day,month,year order.
Paul.
DBASE UDT is used in the TABLE 'master'
The SQL does not accept that for WHERE as I was trying it.
SQL will however accept a string in the WHERE like this:
Quote
string tst
tst = DATE$("dd'-'MM'-'yyyy")
' following works
TheSql = "SELECT * FROM master WHERE TheDate = #" + tst + "#"
Thanks Paul for the answer.