I checked your code. You are calling different sps based on different textbox values ones at a time for only one condition is match in first order to last one.
You need to create one common Nullable parameterize sp where you can pass only those parameter value of textboxes which are not empty.
Refer the below sample code for your reference and implement it in your code per your code logic.
Here I used Northwind Database for sample example.
SQL
CREATE PROCEDURE SearchOrderDetails
@OrderID INT = NULL
,@CustomerID VARCHAR(10) = NULL
,@EmployeeID INT = NULL
,@FromOrderDate DATETIME = NULL
,@ToOrderDate DATETIME = NULL
AS
BEGIN
SELECT * FROM Orders
WHERE ((@OrderID IS NULL) OR OrderID = @OrderID)
AND (@CustomerID IS NULL OR CustomerID = @CustomerID)
AND (@EmployeeID IS NULL OR EmployeeID = @EmployeeID)
AND ((@FromOrderDate IS NULL AND @ToOrderDate IS NULL)
OR (@FromOrderDate IS NOT NULL AND @ToOrderDate IS NULL AND OrderDate = @FromOrderDate)
OR (@FromOrderDate IS NOT NULL AND @ToOrderDate IS NOT NULL AND OrderDate BETWEEN @FromOrderDate AND @ToOrderDate))
END
GO
HTML
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<cc1:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</cc1:ToolkitScriptManager>
<div>
<table>
<tr>
<td>
Order Id
</td>
<td>
<asp:TextBox ID="txtOrderId" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Customer ID
</td>
<td>
<asp:TextBox ID="txtCustomerID" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Employee ID
</td>
<td>
<asp:TextBox ID="txtEmployeeID" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
From Order Date
</td>
<td>
<asp:TextBox ID="txtFromOrderDate" runat="server"></asp:TextBox>
<asp:ImageButton ID="imgPopup" ImageUrl="images/calendar.png" ImageAlign="Bottom"
runat="server" />
<cc1:CalendarExtender ID="Calendar1" PopupButtonID="imgPopup" runat="server" TargetControlID="txtFromOrderDate"
Format="MM/dd/yyyy">
</cc1:CalendarExtender>
</td>
</tr>
<tr>
<td>
To Order Date
</td>
<td>
<asp:TextBox ID="txtToOrderDate" runat="server"></asp:TextBox>
<asp:ImageButton ID="imgPopup2" ImageUrl="images/calendar.png" ImageAlign="Bottom"
runat="server" />
<cc1:CalendarExtender ID="CalendarExtender1" PopupButtonID="imgPopup2" runat="server"
TargetControlID="txtToOrderDate" Format="MM/dd/yyyy">
</cc1:CalendarExtender>
</td>
</tr>
</table>
<br />
<br />
<asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="Search" />
<br />
<br />
<asp:GridView runat="server" ID="gvOrderDetails" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="Order ID"></asp:BoundField>
<asp:BoundField DataField="CustomerID" HeaderText="Customer ID"></asp:BoundField>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID"></asp:BoundField>
<asp:BoundField DataField="ShipCountry" HeaderText="Ship Country"></asp:BoundField>
<asp:BoundField DataField="ShipAddress" HeaderText="Ship Address"></asp:BoundField>
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" DataFormatString="{0:dd-MM-yyyy}"
HtmlEncode="false"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:Label ID="lblmsg" runat="server" Visible="false"></asp:Label>
</div>
</form>
</body>
</html>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
BindGrid();
}
}
protected void Search(object sender, EventArgs e)
{
BindGrid();
}
private void BindGrid()
{
lblmsg.Visible = false;
DataSet ds = new DataSet();
String strConnString = ConfigurationManager.ConnectionStrings["ConStr1"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("SearchOrderDetails");
cmd.CommandType = CommandType.StoredProcedure;
if (txtOrderId.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@OrderID", txtOrderId.Text.Trim());
}
if (txtCustomerID.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@CustomerID", txtCustomerID.Text.Trim());
}
if (txtEmployeeID.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@EmployeeID", txtEmployeeID.Text.Trim());
}
if (txtFromOrderDate.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@FromOrderDate", Convert.ToDateTime(txtFromOrderDate.Text.Trim()).ToString("yyyy-MM-dd"));
}
if (txtToOrderDate.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@ToOrderDate", Convert.ToDateTime(txtToOrderDate.Text.Trim()).ToString("yyyy-MM-dd"));
}
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(ds);
gvOrderDetails.DataSource = ds;
gvOrderDetails.DataBind();
if (gvOrderDetails.Rows.Count == 0)
{
lblmsg.Text = "No Records Found";
lblmsg.Visible = true;
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
BindGrid()
End If
End Sub
Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
BindGrid()
End Sub
Private Sub BindGrid()
lblmsg.Visible = False
Dim ds As DataSet = New DataSet()
Dim strConnString As String = ConfigurationManager.ConnectionStrings("ConStr1").ConnectionString
Dim con As SqlConnection = New SqlConnection(strConnString)
Dim sda As SqlDataAdapter = New SqlDataAdapter()
Dim cmd As SqlCommand = New SqlCommand("SearchOrderDetails")
cmd.CommandType = CommandType.StoredProcedure
If txtOrderId.Text.Trim() <> "" Then
cmd.Parameters.AddWithValue("@OrderID", txtOrderId.Text.Trim())
End If
If txtCustomerID.Text.Trim() <> "" Then
cmd.Parameters.AddWithValue("@CustomerID", txtCustomerID.Text.Trim())
End If
If txtEmployeeID.Text.Trim() <> "" Then
cmd.Parameters.AddWithValue("@EmployeeID", txtEmployeeID.Text.Trim())
End If
If txtFromOrderDate.Text.Trim() <> "" Then
cmd.Parameters.AddWithValue("@FromOrderDate", Convert.ToDateTime(txtFromOrderDate.Text.Trim()).ToString("yyyy-MM-dd"))
End If
If txtToOrderDate.Text.Trim() <> "" Then
cmd.Parameters.AddWithValue("@ToOrderDate", Convert.ToDateTime(txtToOrderDate.Text.Trim()).ToString("yyyy-MM-dd"))
End If
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(ds)
gvOrderDetails.DataSource = ds
gvOrderDetails.DataBind()
If gvOrderDetails.Rows.Count = 0 Then
lblmsg.Text = "No Records Found"
lblmsg.Visible = True
End If
End Sub
Screenshot
