OK, I have dataset component on From1 named "ds_date_range" who's row source is:

SELECT MAX(incident_date) AS max_date, MIN(incident_date) AS min_date
FROM incidents;


I have another dataset component on Form1 named "ds1" who's row source is:

SELECT * FROM incidents WHERE incident_date >= CAST({beg_date} AS Date)
AND incident_date <= CAST({end_date} AS Date);

_____________________________________________________________________________

This is the relevant code from Form1:

procedure TForm1.Form1Create(Sender: TObject);
begin
  Database.Load(ds_date_range);
end;

procedure TForm1.ds_date_rangeAfterLoad(Sender: TObject);
begin
     ds1.Params.Clear;
     ds1.Params.Add('beg_date='''+ds_date_range.columns['min_date'].AsString+'''');
     ds1.Params.Add('end_date='''+ds_date_range.columns['max_date'].AsString+'''');
     Database.Load(ds1);
end;

When I run the app I get the error message shown in the attached screen capture.  What have I done wrong?

I am struggling with how to use and code parameters in queries, especialty date parameters.  I have been meaning to ask what all of the single quotes are in the "Params.Add" code above; I just copied the code from the manual but really would like to understand why so many.  BTW, is the semicolon necessary at the end of the sql statements above?

Many questions....Thanks,
TD

TD wrote:

> OK, I have dataset component on From1 named "ds_date_range" who's row
> source is:

I didn't follow all of that, but if the message is showing the text you
are passing in, that is not the YYYY-MM-DD format a date should be in.


--

Matthew Jones

> procedure TForm1.ds_date_rangeAfterLoad(Sender: TObject);
> begin
>        ds1.Params.Clear;
>        ds1.Params.Add('beg_date='''+ds_date_range.columns['min_date'].AsString+'''');
>        ds1.Params.Add('end_date='''+ds_date_range.columns['max_date'].AsString+'''');
>        Database.Load(ds1);
> end;
>
> When I run the app I get the error message shown in the attached screen capture.  What have I done wrong?
>
> I am struggling with how to use and code parameters in queries, especialty date parameters.  I have been meaning to ask what all of the single quotes are in the "Params.Add" code above; I just copied the code from the manual but really would like to understand why so many.  BTW, is the semicolon necessary at the end of the sql statements above?

In another thread I showed you how to pass date params to a dataset and
I showed you as well how to format it.

Now it's your job to provide correctly formatted strings.

The error message tells you exactly what's wrong: '10/30/2014' is *NOT*
what you need as a param for the EWB dataset. The output of date strings
in JS is different. If you are not sure, just assign

ds_date_range.columns['min_date'].AsString

to a label's caption in EWB (or display a message box) and you can see
immediately the result.

> BTW, is the semicolon necessary at the end of the sql statements above?

Try to ommit it.

Uli

Uli Becker wrote:

> procedure TForm1.ds_date_rangeAfterLoad(Sender: TObject);
> begin
>        ds1.Params.Clear;
>        ds1.Params.Add('beg_date='''+ds_date_range.columns['min_date'].AsString+'''');
>        ds1.Params.Add('end_date='''+ds_date_range.columns['max_date'].AsString+'''');
>        Database.Load(ds1);
> end;
>
> When I run the app I get the error message shown in the attached screen capture.  What have I done wrong?
>
> I am struggling with how to use and code parameters in queries, especialty date parameters.  I have been meaning to ask what all of the single quotes are in the "Params.Add" code above; I just copied the code from the manual but really would like to understand why so many.  BTW, is the semicolon necessary at the end of the sql statements above?

In another thread I showed you how to pass date params to a dataset and
I showed you as well how to format it.

Now it's your job to provide correctly formatted strings.

The error message tells you exactly what's wrong: '10/30/2014' is *NOT*
what you need as a param for the EWB dataset. The output of date strings
in JS is different. If you are not sure, just assign

ds_date_range.columns['min_date'].AsString

to a label's caption in EWB (or display a message box) and you can see
immediately the result.

> BTW, is the semicolon necessary at the end of the sql statements above?

Try to ommit it.

Uli


Thanks Uli, once I knew the problem I was able to follow this thread and get the date range to work.  Now a new issue; how can I check to see if the user has entered an actual date or that it is in the mm/dd/yyyy format?  I looked but did not see a date picker component on the tool bar and I don't see that there are input masks for the Edit boxes so how would one handle this?

I know I am asking a lot of questions and I apologize if some are redundant.  Really like EWB so trying to learn all that I can.

Thanks again everyone for your help!
TD

> Now a new issue; how can I check to see if the user has entered an actual date or that it is in the mm/dd/yyyy format?  I looked but did not see a date picker component on the tool bar and I don't see that there are input masks for the Edit boxes so how would one handle this?

There is no such component in the current version of EWB, maybe version
2.0 will have one. At the moment you have to workaround that yourself -
have a look at the demos ng, you'll find a datepicker by Walter Matte there.

Uli

TD wrote:

> Uli Becker wrote:
>
> > procedure TForm1.ds_date_rangeAfterLoad(Sender: TObject);
> > begin
> >        ds1.Params.Clear;
> >
> > ds1.Params.Add('beg_date='''+ds_date_range.columns['min_date'].AsStr
> > ing+'''');
> > ds1.Params.Add('end_date='''+ds_date_range.columns['max_date'].AsStr
> > ing+'''');        Database.Load(ds1); end;
> >
> > When I run the app I get the error message shown in the attached
> > screen capture.  What have I done wrong?
> >
> > I am struggling with how to use and code parameters in queries,
> > especialty date parameters.  I have been meaning to ask what all of
> > the single quotes are in the "Params.Add" code above; I just copied
> > the code from the manual but really would like to understand why so
> > many.  BTW, is the semicolon necessary at the end of the sql
> > statements above?
>
> In another thread I showed you how to pass date params to a dataset
> and I showed you as well how to format it.
>
> Now it's your job to provide correctly formatted strings.
>
> The error message tells you exactly what's wrong: '10/30/2014' is NOT
> what you need as a param for the EWB dataset. The output of date
> strings in JS is different. If you are not sure, just assign
>
> ds_date_range.columns['min_date'].AsString
>
> to a label's caption in EWB (or display a message box) and you can
> see immediately the result.
>
>  > BTW, is the semicolon necessary at the end of the sql statements
> above?
>
> Try to ommit it.
>
> Uli
>
> Now a new issue; how can I check to
> see if the user has entered an actual date or that it is in the
> mm/dd/yyyy format?  

Personally I would always assume that a user enters dates, numbers,
etc, using their locale formats.
I tend to have an OnChange or OnExit action for edit components which
assigns the data to a typed variable if not directly linked to a
database field.  This will usually throw an exception if the format is
incorrect and you can then ask the user to check their input.

But this may  not always 'work' for web applications where the web
server or app may not be aware of the user's locale.  In such cases I
tend either to  display the expected format eg "mm.dd.yyyy" or follow
the common practice of having a separate control for each element of
the date.

Once you have the data in a typed variable or database field, you can
format it any way you need in order to safely transmit it to computers
in other countries with different locales, or to meet SQL's ANSI format
requirements.

Malcolm

> Now a new issue; how can I check to
> see if the user has entered an actual date or that it is in the
> mm/dd/yyyy format?  

"Malcolm" wrote:

Personally I would always assume that a user enters dates, numbers,
etc, using their locale formats.
I tend to have an OnChange or OnExit action for edit components which
assigns the data to a typed variable if not directly linked to a
database field.  This will usually throw an exception if the format is
incorrect and you can then ask the user to check their input.

But this may  not always 'work' for web applications where the web
server or app may not be aware of the user's locale.  In such cases I
tend either to  display the expected format eg "mm.dd.yyyy" or follow
the common practice of having a separate control for each element of
the date.

Once you have the data in a typed variable or database field, you can
format it any way you need in order to safely transmit it to computers
in other countries with different locales, or to meet SQL's ANSI format
requirements.

Malcolm

Thanks Macolm.  I'll probably create a function that looks through the input character by character to see if it is correct format.  Maybe EWB 2.0 has input masks and date/time popup selectors, can't wait to see it!

TD