parameterized query with where like clause

Question

I am trying to perform parameterized query with where clause like I got no errors but I am not getting data but when I perform same query on sql server studio I got correct result

what the wrong with it ?

one last question in some case I would may not to pass one of these like parameter then how to avoid binding that parameter to query ?

public void GetPatientInfoFilter()
{
    string connStr = ConfigurationManager.ConnectionStrings["SRJDconnstr"].ToString();
    string cmdStr = @"SELECT ID,
                                             DocNUM,
                                             NAM+' '+LFNAME as FirstLastName,
                                             FNAME,
                                             SEX,
                                             BIRTHDAY,
                                             PHONE,
                                             MOBILE,
                                             ADDRESS
                                  FROM SICK
                               WHERE DocNUM LIKE @DocNUM
                                    AND NAM+' '+LFNAME LIKE @FLNAME
                                    AND FNAME LIKE @FNAME";

    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
    {
        conn.Open();
        cmd.CommandText = cmdStr;
        cmd.CommandType = CommandType.Text;

        dtPatientInfo.Rows.Clear();
           cmd.Parameters.Add(new SqlParameter("@DocNUM", SqlDbType.VarChar,10)).Value = "%" + TB_DocNum.Text + "%";
            cmd.Parameters.Add(new SqlParameter("@FLNAME", SqlDbType.VarChar,200)).Value = "%" + TB_FirstLastName.Text + "%";
            cmd.Parameters.Add(new SqlParameter("@FNAME", SqlDbType.VarChar,100)).Value = "%" + TB_FatherName.Text + "%";


        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dtPatientInfo);
        dataGridView1.DataSource = dtPatientInfo;
    }
}

here is how I perform query in SSMS

SELECT ID,
                                                     DocNUM,
                                                     NAM+' '+LFNAME as FirstLastName,
                                                     FNAME,
                                                     SEX,
                                                     BIRTHDAY,
                                                     PHONE,
                                                     MOBILE,
                                                     ADDRESS
                                          FROM SICK
                                       WHERE DocNUM LIKE '%1%'
                                            AND NAM+' '+LFNAME LIKE '%sa%'
                                            AND FNAME LIKE '%b%'

Answer 1

Are you putting the % wildcards into the textboxes? I bet you want this:

string cmdStr = 
    @"SELECT ID,
         DocNUM,
         NAM+' '+LFNAME as FirstLastName,
         FNAME,
         SEX,
         BIRTHDAY,
         PHONE,
         MOBILE,
         ADDRESS
     FROM SICK
     WHERE DocNUM LIKE '%' + @DocNUM + '%'
         AND NAM+' '+LFNAME LIKE '%' + @FLNAME + '%'
         AND FNAME LIKE '%' + @FNAME + '%'";

You might also consider removing the leading wild cards, and only doing "starts with" matches, since what we have here prevents any possibility of any indexes helping this query.