Viewing the value of the SQL Server database in Gridview showing an error

advertisements

When I try to display database values in a gridview I get an error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near the keyword 'and'.

And code is

private void button1_Click(object sender, EventArgs e)
{
    SqlDataAdapter adap;
    DataSet ds;

    SqlConnection cn = new SqlConnection(
      @"Data Source=DILIPWIN\SQLEXPRESS;Initial Catalog=radb;Integrated Security=True");
    cn.Open();

    var home = new Home();
    adap = new SqlDataAdapter(
      "select roll_num, mark from marks where mark < 50 and dept_id=" +
       home.cboxDept.SelectedValue + "  and sem_id=" + home.cboxSem.SelectedValue +
      " and subject_id=" + home.cboxSubject.SelectedValue + " and batch_id= " +
       home.cboxBatch.SelectedValue + " and cls_id=" + home.cboxClass.SelectedValue, cn);

    ds = new System.Data.DataSet();
    adap.Fill(ds, "dataGridView1");

    dataGridView1.DataSource = ds.Tables[0];
}


Use sql-parameters which probably solves this issue and also prevents future sql-injection issues:

string sql = @"
SELECT roll_num,
       mark
FROM   marks
WHERE  mark < 50
AND    [email protected]_id
AND    [email protected]_id
AND    [email protected]_id
AND    [email protected]_id
AND    [email protected]_id;";

DataSet ds = new DataSet();
using(var cn = new SqlConnection(@"Data Source=DILIPWIN\SQLEXPRESS;Initial Catalog=radb;Integrated Security=True"))
using (var da = new SqlDataAdapter(sql, cn))
{
    da.SelectCommand.Parameters.AddWithValue("@dept_id", home.cboxDept.SelectedValue );
    da.SelectCommand.Parameters.AddWithValue("@sem_id", home.cboxSem.SelectedValue );
    da.SelectCommand.Parameters.AddWithValue("@subject_id", home.cboxSubject.SelectedValue );
    da.SelectCommand.Parameters.AddWithValue("@batch_id", home.cboxBatch.SelectedValue );
    da.SelectCommand.Parameters.AddWithValue("@cls_id", home.cboxClass.SelectedValue );
    da.Fill(ds);  // you don't need to open/close the connection with Fill
}
dataGridView1.DataSource = ds.Tables[0];

You should also use the correct types. AddWithValue will try to infer the type from the value. So if those are ints you should parse them accordingly (int.Parse(home.cboxdept.SelectedValue )).