Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 14 total
Thread Using TDataSet to find a record based on date
Wed, Jul 19 2017 6:42 AMPermanent Link

Richard Mace

Hi,

I am using EWB with EDB as the database and am wanting to get all records based on a certain date, where the actual database field is a timestamp.

Is that quite straight forward?

Thanks for any pointers.

Richard
Wed, Jul 19 2017 7:05 AMPermanent Link

Uli Becker

Richard,

> I am using EWB with EDB as the database and am wanting to get all records based on a certain date, where the actual database field is a timestamp.

Have you already defined a dataset (query)? If so: what exactly is the
problem?

Uli
Wed, Jul 19 2017 9:04 AMPermanent Link

Richard Mace

Hi Uli,

<< Have you already defined a dataset (query)? If so: what exactly is the
problem? >>

I hadn't, I was just querying how it was best to do that, but think I have it now.

I'll update here, if I have any probs.

Richard
Wed, Jul 19 2017 9:48 AMPermanent Link

Richard Mace

It can't seem to find the record.

Here is my code, where DS is a TDataSet and the _created field is actually a TimeStamp within EDB:

 with PR do
   begin
     Open;
     InitFind;
     DS.Columns['_created'].AsDate := Now;
     DS.Columns['Sample_Type'].AsInteger := 1;
     if Find(False,True) then
       begin
         with PR do
           begin
             ShowMessage('Found record');
             Id := DS.Columns['Id'].AsInteger;
           end;
       end;
   end;

Basically, the ShowMessage is not happening.

Any ideas?

Thanks
Wed, Jul 19 2017 10:16 AMPermanent Link

Uli Becker

Richard,

> It can't seem to find the record.

In your first post you mentioned (...to get *all* records based on a
certain date...), with your code you will get *one* record anyway.

The only way to get what you want is to create a dataset based on a
query. You have to define it.

Try something like this:
SELECT * FROM MyTable
WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}

And what you are doing here:

> DS.Columns['_created'].AsDate := Now;

will never work. From the manual:

Elevate Web Builder 2 Manual » Component Reference » TDataValue
property AsDate: DateTime
TDataValue.AsDate Property

What you are trying is to look for a record with the *current*
timestamp. This record doesn't exist of course.

Uli

Thu, Jul 20 2017 8:55 AMPermanent Link

Richard Mace

Uli Becker wrote:

> Try something like this:
> SELECT * FROM MyTable
> WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}

Hi Uli,

Would I put this SQL into the TDataset? e.g.

Dataset.Params.Add('SELECT * FROM MyTable') etc.

If so, do I then do an Open, to execute the SQL and then do a Dataset.First?

Thanks for your help

Richard
Thu, Jul 20 2017 10:53 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Richard,

<< Would I put this SQL into the TDataset? e.g.

Dataset.Params.Add('SELECT * FROM MyTable') etc. >>

No, SQL always goes into the dataset on the *server* side of the equation.  In the case of EWB, you would put it in the dataset that you've defined using the Database Manager in the IDE.

Tim Young
Elevate Software
www.elevatesoft.com
Thu, Jul 20 2017 1:21 PMPermanent Link

Richard Mace

Tim Young [Elevate Software] wrote:

<No, SQL always goes into the dataset on the *server* side of the equation.  In the case of EWB, you would put it in the dataset that you've defined using the Database Manager in the IDE.>

Ah, yes, I see that now. Thanks.

Could you give me an example of syntax?

I effectively want to create a SQL statement like below.

Select * from MyTable where created_date = passed_in_date and anInterger = a_passed_in_integer

My created_date field is a TIMESTAMP but I only want to pass in a date and get all records for that date.

Thanks in advance

Richard
Thu, Jul 20 2017 2:54 PMPermanent Link

Uli Becker

Richard,

> Could you give me an example of syntax?

I already posted a sample:

SELECT * FROM MyTable
WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}

As for integers:

SELECT * FROM MyTable
WHERE CAST("_created" AS DATE)=DATE{"_created"='2018-7-19'}
AND anInteger={anInteger=7}

I suggest to start with studying the sample applications and/or reading
the manual on this basic stuff.

Uli
Tue, Jul 25 2017 8:17 AMPermanent Link

Richard Mace

Hi Uli/Tim,

I'm getting an error when trying to load from a dataset.

The dataset, is SQL as follows:

SELECT * FROM PWReadings
WHERE CAST("_created" AS DATE)=DATE{'SearchDate'}

Where the field "_created" is a TIMESTAMP.

and I pass in a parameter, within EWB such as:

Dataset.Params.Add('SearchDate=' + DateToStr(Now));
DBase.LoadRows(Dataset);

However, I am getting the error:

"ElevateDB Error #700 An error was found in the statement at line 2 and column 32 (ElevateDB Error #401 The column DATENULL does not exist in the table PWReadings)"

Any further pointers would be appreciated.

Thanks

Richard
Page 1 of 2Next Page
Jump to Page:  1 2
Image