Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » Elevate Web Builder Technical Support » Support Forums » Elevate Web Builder General » View Thread |
Messages 1 to 7 of 7 total |
Need help using parameters in datasets |
Fri, Oct 31 2014 12:05 PM | Permanent Link |
TD Advanced Data Systems, Inc. | 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 Attachments: ewb parameter error.PNG |
Fri, Oct 31 2014 12:13 PM | Permanent Link |
Matthew Jones | 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 |
Fri, Oct 31 2014 1:23 PM | Permanent Link |
Uli Becker | > 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 |
Fri, Oct 31 2014 4:58 PM | Permanent Link |
TD Advanced Data Systems, Inc. | 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 |
Sat, Nov 1 2014 3:32 AM | Permanent Link |
Uli Becker | > 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 |
Sat, Nov 1 2014 5:15 AM | Permanent Link |
Malcolm Taylor | 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 |
Sun, Nov 2 2014 2:39 PM | Permanent Link |
TD Advanced Data Systems, Inc. | > 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 |
This web page was last updated on Thursday, December 12, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |