Click here to Skip to main content
15,353,740 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
       }
Posted
Updated 24-May-22 1:16am
v2

1 solution

It looks very much as if your variables are strings and that you are passing empty strings (i.e. '') instead of NULLs. So your query is looking for (e.g.) MAINTENANCEPLAN IN ('')
Try something like
SQL
AND	(ISNULL(@FMAINTENANCEPLAN.'') = '' OR MAINTENANCEPLAN IN (SELECT CAST(Item AS INTEGER)FROM
for each of your parameters in your SQL. Apart from that, it's very difficult to help you without sample data
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900