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
– Lukasz Szozda, Commented Sep 1, 2018 at 17:15

Camilo Terevinto · Accepted Answer · 2018-09-01 18:14:11Z

0

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);

edited Sep 1, 2018 at 18:14

Camilo Terevinto

32.2k7 gold badges95 silver badges127 bronze badges

answered Sep 1, 2018 at 17:47

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mukesh kudi · Accepted Answer · 2018-09-01 17:18:52Z

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 1, 2018 at 17:18

mukesh kudi

7296 silver badges20 bronze badges

3 Comments

Rizwan ashraf Over a year ago

I am using version C# 5

Rizwan ashraf Over a year ago

It does not allow me to write this way, Is there any other way to resolve this?

Daniel Loudon Over a year ago

Yes Rizwan, you can format it using SqlParameters or using a string.Format

Collectives™ on Stack Overflow

Retrieving a Row from a table causes invalid column name error

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related