Hi Folks - I am building a search page. I have a SQLDataSource (Access ACCDB) like below. I handle potential null values in the textbox dates by using default values. How can I allow NULLS in the firstname and lastname text boxes? Thanks.
SELECT [LASTNAME], [FRSTNAME], [RELATION], [CEME_NO], [BY], [DY], [AGE_YR], [WAR] FROM [tblCemetery] WHERE (([LASTNAME] LIKE '%' + ? + '%') AND ([FRSTNAME] LIKE '%' + ? + '%') AND ([BY] >= ?) AND ([BY] <= ?) AND ([DY] >= ?) AND ([DY] <= ?)) ORDER BY [LASTNAME],
[FRSTNAME]
SelectCommand="SELECT [LASTNAME], [FRSTNAME], [RELATION], [CEME_NO], [BY], [DY], [AGE_YR], [WAR] FROM [tblCemetery] WHERE (([LASTNAME] LIKE '%' + ? + '%') AND ([FRSTNAME] LIKE '%' + ? + '%') AND ([BY] >= ?) AND ([BY] <= ?) AND ([DY] >= ?) AND ([DY]
<= ?)) ORDER BY [LASTNAME], [FRSTNAME]">
<SelectParameters>
<asp:ControlParameter ControlID="txtLastName" Name="LASTNAME"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtFirstName" Name="FRSTNAME"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtStartBY" DefaultValue="1600" Name="BY"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtEndBY" DefaultValue="2000" Name="BY2"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtStartDY" DefaultValue="1600" Name="DY"
PropertyName="Text" Type="String" />
As far as I see, I think Null is a very special value that needs "Is" as its matchable verb. So you have to use manually data-binding to dynamically combine a whole executable SQL statement and with the help of SqlCommand, you can do what you want.
Another way, maybe you can use this AJAX Multiple-Filtering control:
mlaferriere
0 Points
8 Posts
Using IS NULL in SQLDataSource
Dec 07, 2012 10:14 AM|LINK
Hi Folks - I am building a search page. I have a SQLDataSource (Access ACCDB) like below. I handle potential null values in the textbox dates by using default values. How can I allow NULLS in the firstname and lastname text boxes? Thanks.
SELECT [LASTNAME], [FRSTNAME], [RELATION], [CEME_NO], [BY], [DY], [AGE_YR], [WAR] FROM [tblCemetery] WHERE (([LASTNAME] LIKE '%' + ? + '%') AND ([FRSTNAME] LIKE '%' + ? + '%') AND ([BY] >= ?) AND ([BY] <= ?) AND ([DY] >= ?) AND ([DY] <= ?)) ORDER BY [LASTNAME], [FRSTNAME]
SelectCommand="SELECT [LASTNAME], [FRSTNAME], [RELATION], [CEME_NO], [BY], [DY], [AGE_YR], [WAR] FROM [tblCemetery] WHERE (([LASTNAME] LIKE '%' + ? + '%') AND ([FRSTNAME] LIKE '%' + ? + '%') AND ([BY] >= ?) AND ([BY] <= ?) AND ([DY] >= ?) AND ([DY] <= ?)) ORDER BY [LASTNAME], [FRSTNAME]">
<SelectParameters>
<asp:ControlParameter ControlID="txtLastName" Name="LASTNAME"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtFirstName" Name="FRSTNAME"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtStartBY" DefaultValue="1600" Name="BY"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtEndBY" DefaultValue="2000" Name="BY2"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtStartDY" DefaultValue="1600" Name="DY"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtEndDY" DefaultValue="2000" Name="DY2"
PropertyName="Text" Type="String" />
</SelectParameters>
alankarp
Contributor
2042 Points
345 Posts
Re: Using IS NULL in SQLDataSource
Dec 07, 2012 11:22 AM|LINK
Hi,
I think default value is only option. you can set text box value as default value on page load
Hope this will help you.
Thanks
Profile
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Using IS NULL in SQLDataSource
Dec 08, 2012 02:53 AM|LINK
Hello,
As far as I see, I think Null is a very special value that needs "Is" as its matchable verb. So you have to use manually data-binding to dynamically combine a whole executable SQL statement and with the help of SqlCommand, you can do what you want.
Another way, maybe you can use this AJAX Multiple-Filtering control:
http://www.codeproject.com/Articles/26969/GridView-Multiple-Filter-AJAX-Control