Retrieving a Row from a table causes invalid column name error

Question

I am trying to delete a record in my database table. I am trying to delete it on the basis of a selected name in the dropdown list. When I debug my code there is not any record available in dataset and an exception "invalid column name" occurs, whereas if I run the same query in SQL Server, everything seems to be fine.

This is my code:

protected void SubCategory_Delete_Click(object sender, EventArgs e)
{
    try
    {
        var conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\template_castle.mdf;Integrated Security=True");
        var adpt = new SqlDataAdapter("Select * from tc_prod_subcategory where subcategory_name = ' ' "+ DropDownList2.SelectedItem.Value, conn);

        var ds = new DataSet();
        adpt.Fill(ds, "tc_prod_subcategory");

        foreach (DataRow dr in ds.Tables["tc_prod_subcategory"].Rows)
        {
            dr.Delete();
        }

        SqlCommandBuilder build = new SqlCommandBuilder(adpt);
        adpt.Update(ds, "tc_prod_subcategory");
        Updatesubcategorygrid();
        updatedelete_dropdown();
        Lblsub_catdelete.Text = "Deleted Successfully";
    }
    catch(Exception ex)
    {
        Lblsub_catdelete.Text = ex.Message;
    }
}

And this is the same query when I run it in SQL Server 2014; everything runs fine:

Select * 
from tc_prod_subcategory 
Where subcategory_name= 'Favicon'

Just use parametrized query. To figure out why it is not working you could print out the result of `"Select * from tc_prod_subcategory where subcategory_name = ' ' "+ DropDownList2.SelectedItem.Value` — Lukasz Szozda, Sep 01 '18 at 17:15
Check https://stackoverflow.com/questions/13276602/c-sharp-using-parameters-addwithvalue-in-sqldataadapter — Alexandru Clonțea, Sep 01 '18 at 17:31

score 0 · Answer 1 · answered Sep 01 '18 at 17:18

0

If you use c# version >= 6.0

you can use interpolation to concat strings in very handy and less error-prone way.

 var adpt = new SqlDataAdapter($"Select * from tc_prod_subcategory where subcategory_name = '{DropDownList2.SelectedItem.Value}'", conn);

answered Sep 01 '18 at 17:18

mukesh kudi

719
6
20

I am using version C# 5 – Rizwan ashraf Sep 01 '18 at 17:23
It does not allow me to write this way, Is there any other way to resolve this? – Rizwan ashraf Sep 01 '18 at 17:25
Yes Rizwan, you can format it using SqlParameters or using a string.Format – Daniel Loudon Sep 01 '18 at 17:49

score 0 · Accepted Answer · edited Sep 01 '18 at 18:14

The error is caused by the incorrect position of the apostophes in the where clause. It should be like:

"Select * from tc_prod_subcategory where subcategory_name = '" + DropDownList2.SelectedItem.Value + "'"

but that code is vulnerable to a SQL injection,so you should use parameters instead of concatenating strings.

var conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\template_castle.mdf;Integrated Security=True");
var adpt = new SqlDataAdapter("Select * from tc_prod_subcategory where subcategory_name = @subcategory_name", conn);
var ds = new DataSet();

adpt.SelectCommand.Parameters.AddWithValue("@subcategory_name", DropDownList2.SelectedItem.Value);

Retrieving a Row from a table causes invalid column name error

2 Answers2