i tried to do dynamic search advance filter with with listbox selected items values using
asp.net SQLserver
but i have issue with the code it don't give me any value (null) when search through any searchable field
What I have tried:
alter PROC REPORT_BIND_MN_WORKS
(
@DATEfROM DATE = NULL,
@DATETO DATE= NULL,
@MAINTENANCEPLAN NVARCHAR(MAX) = NULL,
@MAINTENEACETYPE NVARCHAR(MAX) = NULL,
@SECTORID NVARCHAR(MAX) = NULL,
@REGIONID NVARCHAR(MAX) = NULL,
@MEXCHID NVARCHAR(MAX) = NULL,
@EXCHID NVARCHAR(MAX) = NULL,
@MSANID NVARCHAR(MAX) = NULL,
@CABINETNO NVARCHAR(MAX) = NULL,
@BOXNUMBER NVARCHAR(MAX) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@FDATEfROM DATE =NULL,
@FDATETO DATE =NULL,
@FMAINTENANCEPLAN NVARCHAR(MAX) =NULL,
@FMAINTENEACETYPE NVARCHAR(MAX) =NULL,
@FSECTORID NVARCHAR(MAX) =NULL ,
@FREGIONID NVARCHAR(MAX) =NULL ,
@FMEXCHID NVARCHAR(MAX) =NULL,
@FEXCHID NVARCHAR(MAX) =NULL,
@FMSANID NVARCHAR(MAX) =NULL,
@FCABINETNO NVARCHAR(MAX) =NULL,
@FBOXNUMBER NVARCHAR(MAX)=NULL
SET @FDATEfROM=@DATEfROM
SET @FDATETO=@DATETO
SET @FMAINTENANCEPLAN=@MAINTENANCEPLAN
SET @FMAINTENEACETYPE=@MAINTENEACETYPE
SET @FSECTORID=@SECTORID
SET @FREGIONID=@REGIONID
SET @FMEXCHID=@MEXCHID
SET @FEXCHID=@EXCHID
SET @FMSANID=@MSANID
SET @FCABINETNO=@CABINETNO
SET @FBOXNUMBER=@BOXNUMBER
SELECT * FROM TBLMNORDERS
WHERE
(@FDATEfROM IS NULL or cast(ADDEDATE as date)>=@FDATEfROM)
AND (@FDATETO IS NULL or cast(ADDEDATE as date)<=@FDATETO)
AND (@FMAINTENANCEPLAN IS NULL OR MAINTENANCEPLAN IN (SELECT CAST(Item AS INTEGER)FROM dbo.SplitString(@FMAINTENANCEPLAN, ',')))
AND (@FMAINTENEACETYPE IS NULL OR MAINTENEACETYPE IN(SELECT CAST(Item AS INTEGER)FROM dbo.SplitString(@FMAINTENEACETYPE, ',')))
AND (@FSECTORID IS NULL or SECTORID IN(SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@FSECTORID, ',')))
AND (@FREGIONID IS NULL or REGIONID IN(SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@FREGIONID, ',')))
ANd (@FMEXCHID is null or MEXCHID IN(SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@FMEXCHID, ',')))
AND (@FMEXCHID IS NULL or EXCHID IN(SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@FEXCHID, ',')) )
AND (@FMEXCHID IS NULL or MSANID IN(SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@FMSANID, ',')))
AND (@FCABINETNO IS NULL or CABINETNO IN(SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@FCABINETNO, ',')))
AND (@FBOXNUMBER IS NULL OR BOXNUMBER IN(SELECT CAST(Item AS INTEGER) FROM dbo.SplitString(@FBOXNUMBER, ',')) )
END
GO
protected void Btnsubmit_Click(object sender, EventArgs e)
{
GrdData.DataSource = null;
GrdData.DataBind();
string MNPLANID = "";
foreach (ListItem lih in lstmnplan.Items)
{
if (lih.Selected == true)
{
MNPLANID += lih.Value + ",";
}
}
string MNTYPEID = "";
foreach (ListItem li in lstmntype.Items)
{
if (li.Selected == true)
{
MNTYPEID += li.Value + ",";
}
}
string selectedValues = string.Empty;
foreach (ListItem lia in lstboxsector.Items)
{
if (lia.Selected == true)
{
selectedValues += lia.Value + ",";
}
}
string REGIONIDSVAR = "";
foreach (ListItem lib in lstboxregion.Items)
{
if (lib.Selected == true)
{
REGIONIDSVAR += lib.Value + ",";
}
}
string MAINEXCHIDS = "";
foreach (ListItem lic in lstmnexchange.Items)
{
if (lic.Selected == true)
{
MAINEXCHIDS += lic.Value + ",";
}
}
string EXCHANGIDS = "";
foreach (ListItem lid in lstexchange.Items)
{
if (lid.Selected == true)
{
EXCHANGIDS += lid.Value + ",";
}
}
string MSANID = "";
foreach (ListItem lie in lstmsan.Items)
{
if (lie.Selected == true)
{
MSANID += lie.Value + ",";
}
}
string CABINETID = "";
foreach (ListItem lif in lstcabinet.Items)
{
if (lif.Selected == true)
{
CABINETID += lif.Value + ",";
}
}
string BOXID = "";
foreach (ListItem lig in lstbox.Items)
{
if (lig.Selected == true)
{
BOXID += lig.Value + ",";
}
}
DateTime DATEFROM = Convert.ToDateTime(TxtDate.Text);
DateTime DATETTO = Convert.ToDateTime(TxtTo.Text);
string MNPLANDVAR = MNPLANID.TrimEnd(',');
string MNTYPEIDVAR = MNTYPEID.TrimEnd(',');
string SectorIDVAR = selectedValues.TrimEnd(',');
string Regionsidsvar = REGIONIDSVAR.TrimEnd(',');
string MNEXCHANGESIDSVAR = MAINEXCHIDS.TrimEnd(',');
string EXCHANGESIDSVAR = EXCHANGIDS.TrimEnd(',');
string MSANIDSVAR = MSANID.TrimEnd(',');
string CABINETIDSVAR = CABINETID.TrimEnd(',');
string BOXIDSVAR = BOXID.TrimEnd(',');
TextBox1.Text = TESTSECTORID.TrimEnd(',');
TextBox2.Text = TESTSECTORID.TrimEnd(',');
BusinessObject_Layer.BO_Layer objbo = new BusinessObject_Layer.BO_Layer();
objbo.DateFrom = DATEFROM;
objbo.Dateto = DATETTO;
objbo.MAINTENEACEPLANVARIABLE = MNPLANID.TrimEnd(',');
objbo.MAINTENEACETYPEVARIABLE = MNTYPEIDVAR.TrimEnd(',');
objbo.SECTORIDVARAIABLE = SectorIDVAR.TrimEnd(',');
objbo.REGIONIDVATIABLE = Regionsidsvar.TrimEnd(',');
objbo.MNEXCHVARAIBLE = MNEXCHANGESIDSVAR.TrimEnd(',');
objbo.EXCHANGESVARIABLES = EXCHANGESIDSVAR.TrimEnd(',');
objbo.MSANVARAIBLES = MSANIDSVAR.TrimEnd(',');
objbo.CABINETNOVARAIBLES = CABINETIDSVAR.TrimEnd(',');
objbo.BOXNUMBERVAILABLE = BOXIDSVAR.TrimEnd(',');
DataTable dt = BSS.BINDMNREPORTS(objbo);
if (dt.Rows.Count != 0)
{
GrdData.DataSource = dt;
GrdData.DataBind();
}
}
public DataTable BINDMNREPORTS(BusinessObject_Layer.BO_Layer objbos)
{
SqlParameter[] param = new SqlParameter[11];
param[0] = new SqlParameter("@DATEfROM", SqlDbType.Date)
{
Value = objbos.DateFrom
};
param[1] = new SqlParameter("@DATETO", SqlDbType.Date)
{
Value = objbos.Dateto
};
param[2] = new SqlParameter("@MAINTENANCEPLAN", SqlDbType.NVarChar, 5000)
{
Value = objbos.MAINTENEACEPLANVARIABLE
};
param[3] = new SqlParameter("@MAINTENEACETYPE", SqlDbType.NVarChar, 5000)
{
Value = objbos.MAINTENEACETYPEVARIABLE
};
param[4] = new SqlParameter("@SECTORID", SqlDbType.NVarChar, 5000)
{
Value = objbos.SECTORIDVARAIABLE
};
param[5] = new SqlParameter("@REGIONID", SqlDbType.NVarChar, 5000)
{
Value = objbos.REGIONIDVATIABLE
};
param[6] = new SqlParameter("@MEXCHID", SqlDbType.NVarChar, 5000)
{
Value = objbos.MNEXCHVARAIBLE
};
param[7] = new SqlParameter("@EXCHID", SqlDbType.NVarChar, 5000)
{
Value = objbos.EXCHANGESVARIABLES
};
param[8] = new SqlParameter("@MSANID", SqlDbType.NVarChar, 5000)
{
Value = objbos.MSANVARAIBLES
};
param[9] = new SqlParameter("@CABINETNO", SqlDbType.NVarChar, 5000)
{
Value = objbos.CABINETNOVARAIBLES
};
param[10] = new SqlParameter("@BOXNUMBER", SqlDbType.NVarChar, 5000)
{
Value = objbos.BOXNUMBERVAILABLE
};
Dal.Open();
DataTable dt = new DataTable();
dt = Dal.SelectData("REPORT_BIND_MN_WORKS", param);
return dt;
}