yinyang205 says:
Hi All,
I have created search web form using C#.
I have search parameters like product type, date range(from & to).
I have written query for searching by above parameters.
It works fine if I put in ALL 3 parameters, but when I am searching only by product type
not by date range (leaving date range empty) then it returns zero record.
How can I return all records by searching only Product type and leaving empty date range?
My aspx webpage code is like this:
Product Type: <asp:TextBox ID="txtProductType" runat="server">
DateFrom (MM/DD/YYYY): <asp:TextBox ID="Date1" runat="server">
DateTo (MM/DD/YYYY): <asp:TextBox ID="Date2" runat="server">
My cs code is like this:
Declare string connection
{
using (SqlCommand cmd= new SqlCommand("SELECT ProductType, DateRange
FROM TableName
WHERE ProductType like '%' + @ProductType + '%'
AND DateRange between @Date1 and @Date2", con))
{
con.Open();
cmd.Parameters.AddWithValue("ProductType", txtProductType.Text.Trim());
cmd.Parameters.AddWithValue("Date1", Date1.Text.Trim());
cmd.Parameters.AddWithValue("Date2", Date2.Text.Trim());
....
}
You need to check if both textboxes values not passed then have to check null or empty checking in where clause where you are checking dates.
modify the line as per it will handle if date contains in both textboxes it will filter the table by date values if not selected then it will check as both are empty or null and filter the records.
using (SqlCommand cmd = new SqlCommand("SELECT ProductType, DateRange FROM TableName WHERE ProductType like '%' + @ProductType + '%' AND ((DateRange between @Date1 and @Date2) or (ISNULL(@Date1,'') ='' AND ISNULL(@Date2,'') = ''))", con))