locked
Using IS NULL in SQLDataSource RRS feed

  • Question

  • User1276204459 posted

    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] &gt;= ?) AND ([BY] &lt;= ?) AND ([DY] &gt;= ?) AND ([DY] &lt;= ?)) 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>

    Friday, December 7, 2012 6:14 AM

Answers

All replies

  • User-422529730 posted

    Hi,

    I think default value is only option. you can set text box value as default value on page load

    SqlDataSource1.InsertParameters["LASTNAME"].DefaultValue = txtLastName.Text; 
    SqlDataSource1.InsertParameters["FRSTNAME"].DefaultValue = txtFirstName.Text;

    Hope this will help you.

    Thanks

    Friday, December 7, 2012 7:22 AM
  • User3866881 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 7, 2012 10:53 PM