I have a DetailsView based on a SqlDataSource. I have two TextBox values on the form that the user populates one of them prior to clicking a button, which then binds the DetailsView. I am trying to figure out how to write the where clause of the select query
of the SqlDataSource so that if the first TextBox value (TextBoxA) is populated, then the query uses that value and column. Otherwise if the second TextBox value (TextBoxB) is instead populated, the query runs off of that instead. I have tried the following,
which does not work if one of the TextBox values is null:
The above works only if both textboxes are populated, and then just pulls for col_A. There is an issue when one of them is null, does not query anything. What I want is if TextBoxA is populated, I want the query to use as the where clause "col_A = @param_A",
and otherwise if TextBoxB is populated, I want the query to use "col_B = @param_B". I also played around with using a CASE statement in the where clause, but could not get this to work either. Any help would be greatly appreciated!
This works same as the other one, only when both textboxes are populated. If one is null, no data is pulled. There is an issue with the empty textboxes. I can run this sql statement manually and it works. But from ASP, it does not. Does I need to do something
to the parameters if empty textboxes in the selecting event?
tullygarvan
Member
42 Points
16 Posts
How to filter SqlDataSource with multiple user entered fields
Jun 27, 2011 03:01 PM|LINK
I have a DetailsView based on a SqlDataSource. I have two TextBox values on the form that the user populates one of them prior to clicking a button, which then binds the DetailsView. I am trying to figure out how to write the where clause of the select query of the SqlDataSource so that if the first TextBox value (TextBoxA) is populated, then the query uses that value and column. Otherwise if the second TextBox value (TextBoxB) is instead populated, the query runs off of that instead. I have tried the following, which does not work if one of the TextBox values is null:
<asp:SqlDataSource ID="SourceABC" runat="server"
ConnectionString="<%$ ConnectionStrings:AConnectionString %>"
ProviderName="<%$ ConnectionStrings:AConnectionString.ProviderName %>"
SelectCommand="SELECT col_B, col_C, col_D
FROM table_A
WHERE (col_A = ISNULL(@param_A, col_A) OR
col_B = ISNULL(@param_B, col_B))"
<SelectParameters>
<asp:ControlParameter ControlID="TextBoxA" Name="param_A"
PropertyName="Text" ConvertEmptyStringToNull="true"/>
<asp:ControlParameter ControlID="TextBoxB" Name="param_B"
PropertyName="Text" ConvertEmptyStringToNull="true"/>
</SelectParameters>
</asp:SqlDataSource>
The above works only if both textboxes are populated, and then just pulls for col_A. There is an issue when one of them is null, does not query anything. What I want is if TextBoxA is populated, I want the query to use as the where clause "col_A = @param_A", and otherwise if TextBoxB is populated, I want the query to use "col_B = @param_B". I also played around with using a CASE statement in the where clause, but could not get this to work either. Any help would be greatly appreciated!
Huske
Contributor
4060 Points
756 Posts
Re: How to filter SqlDataSource with multiple user entered fields
Jun 27, 2011 03:42 PM|LINK
I had a similar task a few days ago. You query shoud look something like this:
SELECT col_B, col_C, col_D
FROM table_A
WHERE (col_A = @param_A OR @param_A IS NULL) AND (col_B = @param_B OR @param_B IS NULL)
Try this and see if it works.
Regards,
Huske
tullygarvan
Member
42 Points
16 Posts
Re: How to filter SqlDataSource with multiple user entered fields
Jun 27, 2011 07:57 PM|LINK
This works same as the other one, only when both textboxes are populated. If one is null, no data is pulled. There is an issue with the empty textboxes. I can run this sql statement manually and it works. But from ASP, it does not. Does I need to do something to the parameters if empty textboxes in the selecting event?
tullygarvan
Member
42 Points
16 Posts
Re: How to filter SqlDataSource with multiple user entered fields
Jun 27, 2011 08:47 PM|LINK
Got this working. Had to add the following selecting event and manually set the params to null when the textboxes are null:
protected void SourceA_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if ((TextBoxA.Text == DBNull.Value.ToString()) && (TextBoxB.Text == DBNull.Value.ToString()))
e.Cancel = true;
else if(TextBoxA.Text == DBNull.Value.ToString())
e.Command.Parameters["@param_A"].Value = DBNull.Value;
else if (TextBoxB.Text == DBNull.Value.ToString())
e.Command.Parameters["@param_B"].Value = DBNull.Value;
}
RDMathews
Member
2 Points
4 Posts
Re: How to filter SqlDataSource with multiple user entered fields
Nov 03, 2012 10:56 PM|LINK
This is great info. Been fighting this for several hours now.