dBASE SQL Servers - datetime in sql

Author

datetime in sql

Victor

2006-11-21, 5:17 am

Hi,

I run into a problem with datetime field. I have a form with query without params and use all the same form for different request
by changing sql property on fly. Among these request there is one
which requeres selecting from date range. I'm trying to do as follows :

f.query1.sql = "SELECT * FROM Invoice WHERE Invoice.PAYDAY>='"+dtodt(Date1)+"' AND Invoice.PAYDAY<='"+dtodt(Date2)+"'

While form opening I'm getting the error - Server Error: conversion error from string "21/11/2006 00:00:00"

The field in table is timestamp. If I just use sql in the form with params and put in params in On_Open event everything works fine.

If form's query is without params and I try to change sql statement
with params I'm getting error params are not defined (example) :

f.query1.sql = "SELECT * FROM Invoice WHERE Invoice.PAYDAY>=:searkey1 AND Invoice.PAYDAY<=:searkey2"
f.query1.params["searkey1"] = dtodt(Date1)
f.query1.params["searkey2"] = dtodt(Date2)

Any advice is welcome

Thanks
Best Regards
Victor Antonov

Lysander

2006-11-21, 5:17 am

Victor schrieb:

> If form's query is without params and I try to change sql statement
> with params I'm getting error params are not defined (example) :
>
> f.query1.sql = "SELECT * FROM Invoice WHERE Invoice.PAYDAY>=:searkey1 AND Invoice.PAYDAY<=:searkey2"
> f.query1.params["searkey1"] = dtodt(Date1)
> f.query1.params["searkey2"] = dtodt(Date2)

Privet Victor,

what are you doing to activate the query again, after changing the
SQL-Statement?

I remember something that - when changing from statement without
parameters to statement with parameters, you must
deactivate/re-activate the query first/last.

So, the following _should_ work:
f.query1.active = .F.
f.query1.sql = "SELECT * FROM Invoice
WHERE
Invoice.PAYDAY>=:searkey1
AND
Invoice.PAYDAY<=:searkey2"
f.query1.params["searkey1"] = dtodt(Date1)
f.query1.params["searkey2"] = dtodt(Date2)
f.query1.active = .T.

Also always remember that in dBase you must always FIRST change the
property "SQL" of a query-object and only THEN change the property
"PARAMS[]".
In your example, you did correctly so, but a lot of people tend to
forget this from time to time. In such cases you also would get an
error message "parameter not defined".

ciao,
André

Victor

2006-11-21, 7:13 pm

Andrey,

Thanks. I tried with active and it didn't work. I figured out that
the problem was as follows :

I didn't Open form, just loaded blueprint with f=new invFORM()
and tried to change sql before f.Open(). Now I do f.Open() first
and later do change sql with params. It works !

Thanks a lot
Best Regards
Victor

Lysander Wrote:

> Victor schrieb:
>
>
> Privet Victor,
>
> what are you doing to activate the query again, after changing the
> SQL-Statement?
>
> I remember something that - when changing from statement without
> parameters to statement with parameters, you must
> deactivate/re-activate the query first/last.
>
> So, the following _should_ work:
> f.query1.active = .F.
> f.query1.sql = "SELECT * FROM Invoice
> WHERE
> Invoice.PAYDAY>=:searkey1
> AND
> Invoice.PAYDAY<=:searkey2"
> f.query1.params["searkey1"] = dtodt(Date1)
> f.query1.params["searkey2"] = dtodt(Date2)
> f.query1.active = .T.
>
>
> Also always remember that in dBase you must always FIRST change the
> property "SQL" of a query-object and only THEN change the property
> "PARAMS[]".
> In your example, you did correctly so, but a lot of people tend to
> forget this from time to time. In such cases you also would get an
> error message "parameter not defined".
>
> ciao,
> André
>

Lysander

2006-11-21, 7:13 pm

Victor schrieb:

> I didn't Open form, just loaded blueprint with f=new invFORM()
> and tried to change sql before f.Open().

Yes, that's also a known issue. There is a couple of other things
that just do not work as expected when the form is already
instantiated, but not opened.

Glad for you that you found it out so quickly!

Simone Bartoccioni

2006-11-21, 7:13 pm

With MS sql server you can use this sintax:

f.query1.sql = "SELECT * FROM Invoice WHERE
Invoice.PAYDAY>='"+dtos(Date1)+"' AND Invoice.PAYDAY<='"+dtos(Date2)+"'

Ciao
Simone

"Victor" <Victor.Antonov@tridentsa.spb.ru> ha scritto nel messaggio
news:teRtrWVDHHA.1140@news-server...
> Hi,
>
> I run into a problem with datetime field. I have a form with query without
> params and use all the same form for different request
> by changing sql property on fly. Among these request there is one
> which requeres selecting from date range. I'm trying to do as follows :
>
> f.query1.sql = "SELECT * FROM Invoice WHERE
> Invoice.PAYDAY>='"+dtodt(Date1)+"' AND Invoice.PAYDAY<='"+dtodt(Date2)+"'
>
> While form opening I'm getting the error - Server Error: conversion error
> from string "21/11/2006 00:00:00"
>
> The field in table is timestamp. If I just use sql in the form with params
> and put in params in On_Open event everything works fine.
>
> If form's query is without params and I try to change sql statement
> with params I'm getting error params are not defined (example) :
>
> f.query1.sql = "SELECT * FROM Invoice WHERE Invoice.PAYDAY>=:searkey1
> AND Invoice.PAYDAY<=:searkey2"
> f.query1.params["searkey1"] = dtodt(Date1)
> f.query1.params["searkey2"] = dtodt(Date2)
>
> Any advice is welcome
>
> Thanks
> Best Regards
> Victor Antonov
>
>

Sponsored Links