Optional column value in SqlDataSource

Question

I have this SqlDataSource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConflictDetection="CompareAllValues"
    ConnectionString="<%$ ConnectionStrings:OracleXexdb %>" 
    ProviderName="<%$ ConnectionStrings:OracleXexdb.ProviderName %>"
    SelectCommand="select col1, col2, col3 from table where col1 < 2000 and (col2 = :col2 OR :col2 is null)"
     OnSelecting="SqlDataSource1_Selecting"
    >
    <SelectParameters>
        <asp:ControlParameter ControlID="codagent" Name="col2" PropertyName="Text" Type="String" ConvertEmptyStringToNull="true" DefaultValue=""/>
    </SelectParameters>
</asp:SqlDataSource>

codagent is an <asp:TextBox> and the user can put a value or nothing (""), if the user leaves the TextBox with nothing, the SqlDataSource retrieves no values. My goal is allow user to get all the col2 values, without filter

Am I missing something?

Do you really want only the ones where col1 is less than 2000, or if they enter null into the text box to get all of the ones including where col1 was >= 2000? And have you tried not using an empty string to null and using col2 = '' ? — Rob G
– Rob G, Commented Sep 23, 2013 at 18:34
@RobG I want all the values of col2 and only the ones where col1 is less than 2000 — Naty Bizz
– Naty Bizz, Commented Sep 23, 2013 at 18:59

Dragan Radivojevic · Accepted Answer · 2013-09-23 18:45:43Z

1

Try changing your SQL to something like this

SelectCommand="select col1, col2, col3 from table where col1 < 2000 and (col2 = :col2 OR  (:col2 is null AND 1=1)"

I'm not really sure if such expressions are possible in Oracle since I don't have any experience with it but this is the logic that is used to accomplish the same thing in SQL Server.

answered Sep 23, 2013 at 18:45

Dragan Radivojevic

2,0022 gold badges14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

R.C · Accepted Answer · 2013-09-24 03:58:15Z

1

Since you are already using the OnSelecting event of SqlDataSource, use this event to modify your Select command.

Also, since you want the user to retrieve all col2 values, in actuality, all such columns with col1<2000 will only be retrieved. [ Means, there can be col2 values corresponding to which col1> 2000, so such col2 will not be displayed at all ]

protected void SqlDataSource1_Selecting(object sender, 
SqlDataSourceSelectingEventArgs e) 
{ 
    if(string.IsNullOrEmpty(codagent.Text))
    {
      e.Command="select col1, col2, col3 from table where col1 < 2000";
    }
}

answered Sep 24, 2013 at 3:58

R.C

10.6k2 gold badges38 silver badges48 bronze badges

Comments

Naty Bizz · Accepted Answer · 2013-10-01 21:48:33Z

1

I found the way to do this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConflictDetection="CompareAllValues"
    ConnectionString="<%$ ConnectionStrings:OracleXexdb %>" 
    ProviderName="<%$ ConnectionStrings:OracleXexdb.ProviderName %>"
    SelectCommand="select col1, col2, col3 from table where col1 < 2000 and (col2 = :col2 OR (:col2 is null))"
    >

    <SelectParameters>
        <asp:ControlParameter ControlID="codagent" Name="col2" PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" DefaultValue=""/>
    </SelectParameters>

</asp:SqlDataSource>

This worked fine

answered Oct 1, 2013 at 21:48

Naty Bizz

2,3727 gold badges35 silver badges57 bronze badges

Comments

Vikash Singh · Accepted Answer · 2013-09-24 05:31:36Z

0

why don't you use subquery. following code is giving col2, col3 whether col1 is empty or not.

select (select col1 from table1 where col1 ='') as col1, col2, col3 from table1

answered Sep 24, 2013 at 5:31

Vikash Singh

8141 gold badge10 silver badges12 bronze badges

Collectives™ on Stack Overflow

Optional column value in SqlDataSource

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related