Gridview paging issue

Question

I have got one issue which made me stack and i am unable to find any solution for it, badly need your help: Actually i need to do some sort of searching on the employee table like i need to search all the employees who are female or have got phd and etc and i made a function for this which query the employee table using if condition and the result of this search comes in a gridview with allowpaging to true the PROBLEM is that whenever i click next to go to other page in gridview it does a full post back and binds again to the gridview datasource and as i gave gridview datasource the below function(DetailedSearch) with if cluases when it enters to the function it starts reading with the first if condition without going to that specific if condition and then it throws exception Object reference not set to an instance of an object

here is my code(it is just a portion of it not full code):

    public DataTable DetailedSearch()
    {
        con.Open();
        SqlDataAdapter da=new SqlDataAdapter();


         if (FirstName != string.Empty)
         {
             if (FirstName != string.Empty && LastName != "--Letters--")
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'"+"and lastname like '"+LastName.ToString()+"%'", con);
             }
             else if(FirstName!=string.Empty && Gender!="{Please Select}")
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and gender='" + Gender.ToString() + "'", con);

             }
             else if(FirstName!=string.Empty && MaritalStatus!="{Please Select}")
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and maritalstatus='" + MaritalStatus.ToString() + "'", con);
             }
             else if(FirstName!=string.Empty && Qualification!="{Please Select}")
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and qualification='" + Qualification.ToString() + "'", con);
             }
             else if(FirstName!=string.Empty && GraduationDate!=DateTime.MinValue)
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and graduationdate='" + GraduationDate.ToString() + "'", con);
             }
             else if(FirstName!=string.Empty && Province!="{Please Select}")
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and province='" + Province.ToString() + "'", con);
             }
             else if(FirstName!=string.Empty && LastEmployer!=string.Empty)
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and lastemployer='" + LastEmployer.ToString() + "'", con);
             }
            else if(FirstName!=string.Empty && EnteredBy!="{Please Select}")
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and enteredby='" + EnteredBy.ToString() + "'", con);
             }
             else if (FirstName != string.Empty && DateEntered != DateTime.MinValue)
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'" + "and dateentered='" + DateEntered.ToString() + "'", con);
             }
             else
             {
                 da = new SqlDataAdapter("select * from employee where firstname ='" + FirstName.ToString() + "'", con);
             }
         }
         else if (LastName != "--Letters--")
         {
                if (LastName != "--Letters--" && Province != "{Please Select}")
                 {
                     da = new SqlDataAdapter("select * from employee where lastname like'" + LastName.ToString() + "%'" + "and province='" + Province.ToString() + "'", con);
                 }
                else if (Gender != "{Please Select}" && LastName != "--Letters--")
                {
                    da = new SqlDataAdapter("select * from employee where gender='" + Gender.ToString() + "'" + "and lastname like '" + LastName.ToString() + "%'", con);
                }
        DataTable dt = new DataTable();
        da.Fill(dt);
        con.Close();
        return dt;

    }

Please help me out what to do as am totaly blank and no logic is coming to my mind, i have to put if clauses as the query might not be the same based on the search but how to make gridview paging aware of this :(

Thanks in advance

Ortiga Ortiga · Accepted Answer · 2012-08-11T03:49:56

Really, there are so many things that can be improved in that code...

You're doing FirstName != string.Empty repeatedly, but you already tested for it in the first if.
You should change to one singleif(!String.IsNullOrEmpty(FirstName))
You should use sql parameters instead of string concatenation, as this avoids potential sql injection
You shouldn't be passing the default user interface of the fields, like "Please Select". In case the default value is selected, pass a null or empty value

Although this does not answer or question specifically, it is a good guidance - in fact, in the case you try to apply those changes, it may even solve your issue in the process

Gridview paging issue

1 Answers