Data not getting in datagridview

Question

I am developing an application in C# VS 2010.

I have below code to fetch the student details with some name into the datagridview.

var CommandText = @"SELECT sid as 'Student ID', name as 'Student Name', adDate as 
                  'Admission Date',
               paidFees as 'Fees Paid', balance as 'Fees Remaining'
                   FROM Student WHERE (status = '" + status + "') AND 
                   (name LIKE '%'+'"+txtSearchName.Text.Trim() + "'+'%')";
string select = CommandText;
            con.Open();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(select, con); 

            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);
            con.Close();
            dgvSearch.ReadOnly = true;
            dgvSearch.DataSource = ds.Tables[0];

My problem is I am just getting headers of the table not data like below screenshot. Search

What is be wrong here?

Piotr Perak · Answer 1 · 2013-12-29T19:24:37.430

I think you should drop ' after opening % and before closing %.

var CommandText = @"SELECT sid as 'Student ID', name as 'Student Name', adDate as 
              'Admission Date',
              paidFees as 'Fees Paid', balance as 'Fees Remaining'
              FROM Student WHERE (status = '" + status + @"') AND 
              (name LIKE '%" + name + "%')";

LIKE part should be of form LIKE '%somename%'.

Also it us good practice to wrap SqlConnection with using construct so that you never forget to dispose of it (even when exception is thrown.

using (var conn = new SqlConnection(...))
{
}

score 1 · Accepted Answer · edited May 23 '17 at 11:56

I suggest to use a parameterized query.

var CommandText = @"SELECT sid as 'Student ID', name as 'Student Name', adDate as 
                  'Admission Date', paidFees as 'Fees Paid', balance as 'Fees Remaining'
                   FROM Student WHERE (status = @status) AND (name LIKE @stuname)";
string select = CommandText;
con.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(select, con); 
dataAdapter.SelectCommand.Parameters.AddWithValue("@status", status);
dataAdapter.SelectCommand.Parameters.AddWithValue("@stuname", "%" + txtSearchName.Text.Trim() + "%");
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
....

In this way the problems with string concatenation of quotes and wildcard chars for the LIKE statement are greatly simplified and you avoid subtle errors like the wrong quotes around the % char.

Of course this removes any possible SQL Injection attack

Data not getting in datagridview

2 Answers2