Problem with dates in Dataset Select statement

I'm using the designer to create/modify strongly typed datasets. I have one select statement that I'm having considerable trouble with. The user selects search parameters from a form. These search parameters are used to filter the data sent to a gridview control. Three of these parameters are almost enough to make me abandon the dataset in this case.

The first two are minimum and maximum age. I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated. In ASP I'd do something like this:

strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"

I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.

The third parameter is astrological sign. Again, I calculate it based on the birth date. I don't have a field for it. I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign. I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify?

I need to get the age issue fixed. The sign is a nice to have, since I can do it another way if I have to.

BTW: I did decide to abandon the dataset in favor of a SqlDataSource control. This allowed me to build my select string in the code behind the way I would have in ASP. This resulted in paging and sorting not working properly. Sorting would be nice, paging is necessary. I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn). Any comments or feedback on this would be avidly read and appreciated.

Diane

You have to put the Datediff portion in the Filter column of the Query Designer. Then SQL will use it in the WHERE clause.|||

Please show us your code. That would be easier for us to figure out why it does not owrk for you.

Also, please take a look the following link, it shows how to use SqlDataSource with parameters.

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/sqldatasource.aspx

Here is an example from the above link:

<asp:DropDownList ID="DropDownList1" ... runat="server"/>
...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Pubs %>"
SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @.state">
<SelectParameters>
<asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

Hope this helps

|||

Yes, I tried that. I get an error, so I must be doing something wrong. I tried

(@.lowAge < DATEDIFF(yyyy, [DATE OF BIRTH], date))

And I get an error: 'date' in expression not part of the query

Diane

|||

I can't use the SqlDataSource control this way. I have a huge select statement and many of the parameters can be empty. For example. users can select to search by state or not. So in my code behind I check if a state has been selected. If so, I add it to the select string. I have to check first, or I'll have a string that looks for records with no state in them, which is not what I want. So I build the select string in the code behind. If strSQL is the string I've built, I set SqlDataSource1.SelectCommand = strSQL. This works great for the first page, but the grid disappears if I try to do any paging. Google and forum searches turned up others with the same problem, but no answers. I suspect there's an answer for this, but that it's much more complicated than using the dataset which I'd rather use anyway.

I'd hate to say it, but at this point, faster is better. I'm at the end of a long project. My customer would like this done so he can move on to the next phase, and I'm ready for something new.

Diane

|||

I've narrowed down my problem.If I want to do this: (DATEDIFF(yyyy, [DATE OF BIRTH], Date) <= @.rUpperAge), how do I specify the current date in the dataset designer? It doesn't recognize Date, Date() Now, Now() or DateTime.now. I even tried to make today's date a parameter (DATEDIFF(yyyy, [DATE OF BIRTH], @.thisDate) <= @.rUpperAge). Didn't accept it. So how do I specify that I'm looking for a date difference between the field value and today?

Diane

|||

I never used the query designer. So I cannot give you any suggestion on that.

Regarding get the today's date, since it is used to build the query, so you shoudl use the sql function: GetDate().

|||

I second that

You should use getdate() to get the current date since you are generating sql query.

Now() is a VB keyword and would be used within VB code.

|||

Thanks everyone, GetDate() as part of the select string did it. I had tried to use it this way:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & GetDate() & "') <= " & Request.Form("UpperAge") & ")"

But this worked:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], GetDate()) <= " & Request.Form("UpperAge") & ")"

Diane