How to populate Gridview based on multiple conditions using stored procedure
I tried multiple time but not succeed, below is my stored procedure and code behind code.
Please correct this code and also share code for this using stored procedure
CREATE PROCEDURE srch_Inventory_ExtBillBook_SaleTaxInvoice
@Year datetime = NULL
,@MonthShortName varchar(3) = NULL
,@Invoice_No INT = NULL
,@BillTo nvarchar(120) = NULL
AS
BEGIN
SELECT * FROM vwInventory_ExtBillBook_SaleTaxInvoice
WHERE ((@Year IS NULL) OR Year = @Year)
AND (@MonthShortName IS NULL OR MonthShortName = @MonthShortName)
AND (@Invoice_No IS NULL OR Invoice_No = @Invoice_No)
AND (@BillTo IS NULL OR BillTo = @BillTo)
END
GO
public void BindGrid()
{
cnn.Open();
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("srch_Inventory_ExtBillBook_SaleTaxInvoice");
cmd.CommandType = CommandType.StoredProcedure;
if (ddlSearchYear.SelectedItem.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@Year", ddlSearchYear.SelectedItem.Text.Trim());
}
if (ddlSearchMonth.SelectedItem.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@MonthShortName", ddlSearchMonth.SelectedItem.Text.Trim());
}
if (txtSearchInvoiceNo.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@Invoice_No", txtSearchInvoiceNo.Text.Trim());
}
if (txtInvoiceName.Text.Trim() != "")
{
cmd.Parameters.AddWithValue("@BillTo", txtInvoiceName.Text.Trim());
}
{
//For First Column auto row number by sequence
DataColumn Col = dt.Columns.Add("RowNo", typeof(int));
Col.SetOrdinal(0);
dt.Columns["RowNo"].AutoIncrement = true;
dt.Columns["RowNo"].AutoIncrementSeed = 1;
dt.Columns["RowNo"].AutoIncrementStep = 1;
}
cmd.Connection = cnn;
sda.SelectCommand = cmd;
sda.Fill(ds);
gv_TaxSaleInvoiceList.DataSource = ds.Tables[0].DefaultView;
gv_TaxSaleInvoiceList.DataBind();
//calculateSum();
if (gv_TaxSaleInvoiceList.Rows.Count == 0)
{
lblmsg.Text = "ERROR - No Records Found !";
lblmsg.Visible = true;
}
cnn.Close();
}
The result is shown below as like - ERROR - No Records Found !