Displaying SQL Server database value in Gridview showing error

Question

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];
}

I assume you are working a WinForms and actually are using a DataGridView?? Please always tag appropriately or else you waste WPF folks' time! (and vice versa..) — TaW
– TaW, Commented Oct 8, 2014 at 14:50

Tim Schmelter · Accepted Answer · 2014-10-08 14:57:04Z

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    dept_id=@dept_id
AND    sem_id=@sem_id 
AND    subject_id=@subject_id 
AND    batch_id=@batch_id 
AND    cls_id=@cls_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 )).

Midhun Mundayadan · Accepted Answer · 2014-10-08 14:41:31Z

0

Use are missing to call databind method here.Use following code :

GridView1.DataBind();//This line is missing in your code`

try the below format

 DataAdapter adapter=new DataAdapter(SqlCommand,SqlConn);
 DataTable tbl=new Datatable();
 adapter.Fill(tbl);
 GridView1.DataSource=tbl;
 GridView1.DataBind();//This line is missing in your code

`

answered Oct 8, 2014 at 14:41

Midhun Mundayadan

3,1923 gold badges24 silver badges34 bronze badges

1 Comment

Tim Schmelter Over a year ago

"Incorrect syntax near the keyword 'and'." It's also winforms not webforms.

Collectives™ on Stack Overflow

Displaying SQL Server database value in Gridview showing error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related