Cannot retrieve Values as expected from database.Need the right query to get the answer

Question

There are four textboxes where user enter values into them and based on the values results gets displayed in gridview.

The problem is When user leaves some textbox the results to be displayed only based on other three textboxes.But my query is not working here. Am facing problem here.

protected void LoadGridData5()
    {
        try
        {
            GridView1.Visible = false;
            con.Open();

            string ID = IDTEXT.Text;
            string ROLE = DropDownList2.SelectedValue.ToString();
            string str = TextBox1.Text.ToString();

            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            cmd.Parameters.Add("@NAME", SqlDbType.NVarChar).Value =str;
            cmd.Parameters.Add("@ROLE", SqlDbType.VarChar).Value =ROLE;
            cmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value =DOBTEXT.Text;
            SqlCommand cmd = new SqlCommand("SP_OPERATORS", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "OPERATOR");
            cmd.ExecuteNonQuery();         
            //GridView1.DataSource = ds;
            //GridView1.DataBind();
            //con.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
            else
            {
                ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
                GridView1.DataSource = ds;
                GridView1.DataBind();
                int columncount = GridView1.Rows[0].Cells.Count;
                GridView1.Rows[0].Cells.Clear();
                GridView1.Rows[0].Cells.Add(new TableCell());
                GridView1.Rows[0].Cells[0].ColumnSpan = columncount;
                GridView1.Rows[0].Cells[0].Text = "No Records Found";
            }
        }
        catch
        {
            //Response.Redirect("Error.aspx");
        }
        finally
        {
            con.Close();
        }

    }

Here is the stored procudure

CREATE PROCEDURE SP_OPERATORS
@ID INT,
@NAME NVARCHAR(50),
@DOB DATETIME,
@ROLE VARCHAR(50)

AS
Set NoCount ON

Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

 Set @SQLQuery = 'Select * From [OPERATOR] where (1=1) ' 

 If @NAME Is Not Null 
     Set @SQLQuery = @SQLQuery + ' And (NAME LIKE '''+ '%' + @NAME + '%' + ''')'
 If @ID Is Not Null 
     Set @SQLQuery = @SQLQuery + ' And (ID=@ID)'
 If @DOB Is Not Null 
     Set @SQLQuery = @SQLQuery + ' And (DOB=@DOB)'
 If @ROLE Is Not Null 
     Set @SQLQuery = @SQLQuery + ' And (ROLE=@ROLE)'

      If (@NAME Is Not Null) AND (@DOB Is Not Null )
      Set @SQLQuery = @SQLQuery + ' And (NAME LIKE '''+ '%' + @NAME + '%' + ''')'+'And (DOB=@DOB)'

      If (@NAME Is Not Null) AND (@ROLE Is Not Null )
      Set @SQLQuery = @SQLQuery + ' And (NAME LIKE '''+ '%' + @NAME + '%' + ''')'+' And (ROLE=@ROLE)'

      If (@DOB Is Not Null) AND (@ID Is Not Null )
      Set @SQLQuery = @SQLQuery + ' And (DOB=@DOB)'+' And (ID=@ID)'

      If (@ROLE Is Not Null) AND (@ID Is Not Null )
      Set @SQLQuery = @SQLQuery + '  And (ROLE=@ROLE)'+' And (ID=@ID)'

      If (@ROLE Is Not Null) AND (@DOB Is Not Null )
      Set @SQLQuery = @SQLQuery + '  And (DOB=@DOB)'+' And (ROLE=@ROLE)'

      If (@NAME Is Not Null) AND (@ID Is Not Null ) AND (@ROLE Is Not Null )
      Set @SQLQuery = @SQLQuery + ' And (NAME LIKE '''+ '%' + @NAME + '%' + ''')'+' And (ID=@ID)'+ ' And (ROLE=@ROLE)'

      If (@NAME Is Not Null) AND (@ID Is Not Null ) AND (@DOB Is Not Null )
      Set @SQLQuery = @SQLQuery + ' And (NAME LIKE '''+ '%' + @NAME + '%' + ''')'+' And (ID=@ID)'+ '  And (DOB=@DOB)'

      If (@ROLE Is Not Null) AND (@ID Is Not Null ) AND (@DOB Is Not Null )
      Set @SQLQuery = @SQLQuery + ' And (ROLE=@ROLE)'+' And (ID=@ID)'+ '  And (DOB=@DOB)'

      If (@NAME Is Not Null) AND (@ID Is Not Null ) AND (@DOB Is Not Null ) AND (@ROLE Is Not Null)
      Set @SQLQuery = @SQLQuery + ' And (NAME LIKE '''+ '%' + @NAME + '%' + ''')'+' And (ID=@ID)'+ ' And (ROLE=@ROLE)' + ' And (DOB=@DOB)'

      Set @ParamDefinition =     
       ' @ID INT,
 @NAME NVARCHAR(50),
 @DOB DATETIME,
 @ROLE VARCHAR(50)'

 Execute sp_Executesql     @SQLQuery, 
            @ParamDefinition, 
            @ID, 
            @NAME, 
            @ROLE, 
            @DOB


  If @@ERROR <> 0 GoTo ErrorHandler
   Set NoCount OFF
   Return(0)

  ErrorHandler:
  Return(@@ERROR)
  GO

Answer 1

Your call to da.Fill(ds) will execute the query, no need for the extra cmd.ExecuteNonQuery after it.

For your own sake, please please please either switch the constructed string you're using for the query to either a call to a stored procedure, or to a parameterised query. You're open to SQL Injection if someone puts a malicious value in any of those text boxes. There are more details on this here (http://www.techrepublic.com/article/shorten-development-time-by-using-parameterized-queries-in-adonet/) and here (https://msdn.microsoft.com/en-us/library/bbw6zyha(v=vs.110).aspx)

It's also not terribly clear what you're trying to do with the query. At the moment it's going to try and match all of the parameters that you specify, as you're using 'AND' between each predicate. If you're trying to do a search, you may want some of those to be 'ORs' or you may want to only add the predicates where the value is supplied. At the moment, for example, if you leave your ID textbox blank, you'll only return results where the ID of the OPERATOR is blank, and that doesn't sound like your intention.