Getting error as Invalid column name C#.NET

Question

I am new to C#.net. I am developing a small application where the data in one drop down list gets populated based on the value selected in another drop down list. Upon viewing the output, I am getting the error Invalid column name 'CategoryBasedList'. Can you please point out where I went wrong. I am attaching the code below. Thanks in advance.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


public partial class UserLogin : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       if (!IsPostBack)
        {
          populate();
        }
    }
    protected void ddlCategoryBasedList_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

    protected void ddlListOfBooks_SelectedIndexChanged(object sender, EventArgs e)
        {

        string constr = ConfigurationManager.ConnectionStrings["CONN_STR"].ToString();
        SqlConnection con = new SqlConnection(constr);
        con.Open();
        SqlCommand com = new SqlCommand("Select distinct(CategoryBasedList) from Category where CategoryList = '" +  (ddlListOfBooks.SelectedValue).ToString() + "'", con);
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataSet ds = new DataSet();
        da.Fill(ds);
        ddlCategoryBasedList.DataTextField = ds.Tables[0].Columns["CategoryBasedList"].ToString();
        ddlCategoryBasedList.DataSource = ds.Tables[0];
        ddlCategoryBasedList.DataBind();

        }
    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {

        }
    public void populate()
    {
    string constr = ConfigurationManager.ConnectionStrings["CONN_STR"].ToString();
    SqlConnection con = new SqlConnection(constr);
    con.Open();
    SqlCommand com = new SqlCommand(" Select distinct(CategoryList) from Category", con);
    SqlDataAdapter da = new SqlDataAdapter(com);
    DataSet ds = new DataSet();
    da.Fill(ds);
    ddlListOfBooks.DataTextField = ds.Tables[0].Columns["CategoryList"].ToString();
    ddlListOfBooks.DataSource = ds.Tables[0];
    ddlListOfBooks.DataBind();
    }
}

The database structure of the above mentioned table - CATEGORY is as follows.

CREATE TABLE [dbo].[Category](
    [[CategoryBasedList]]] [varchar](100) NOT NULL,
    [CategoryList] [varchar](100) NOT NULL,
    [Authors] [varchar](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF 
GO

Your slq tries to acces a column that is not there: `new SqlCommand("Select distinct(CategoryBasedList) from Category where CategoryList = '" + (ddlListOfBooks.SelectedValue).ToString() + "'", con);` check the columnname in the table `Category ` — Patrick Artner, Feb 23 '18 at 10:49
Leaving anything else aside, the very first thing to do is stop building SQL like that. Otherwise when it stops failing, you'll have an insecure application that's vulnerable to SQL injection attacks. Use parameterized SQL instead. — Jon Skeet, Feb 23 '18 at 10:53
@PatrickArtner, have just edited the question mentioning the database structure, Can you please check.. — Joyce Virgin, Feb 23 '18 at 10:54
`[[CategoryBasedList]]]` - you are creating it like this ? no errors? unbalanced brackets included? — Patrick Artner, Feb 23 '18 at 10:56
Please see [Bobby Tables](http://bobby-tables.com/) for an explanation of why it's important to be careful when putting together database queries and commands. And then see the [C# example](http://bobby-tables.com/csharp) for how to rectify it. — mason, Feb 23 '18 at 14:06

score 3 · Accepted Answer · answered Feb 23 '18 at 10:58

3

You need to sort out brackets around CategoryBasedList. As you show it now, field name is actually [CategoryBasedList]], not as you might expect CategoryBasedList. Just rename that column name to remove extra brackets and you should be good to go.

Also, as already mentioned, you need to be aware about SQL Injections. Instead of constructing query by string concatination you need to use SQL Parameters

answered Feb 23 '18 at 10:58

trailmax

34,305
22
140
234

Yes...I got it. Thank you so much for your answer – Joyce Virgin Feb 23 '18 at 12:00

Patrick Artner · Answer 2 · 2018-02-23T11:06:32.307

Your slq tries to acces a column that is not there:

new SqlCommand(@"
Select distinct(CategoryBasedList) from Category 
where CategoryList = '" + (ddlListOfBooks.SelectedValue).ToString() + "'", con);

check the columnname in the table Category: according to your DDL your DB-Column is named [CategoryBasedList].

After fixing that, google Sql Injection and/or read Why do we always prefer using parameters in SQL statements? Use SqlParameters to supply parameters to your sql-statements.

Additionally you should start using using(var con = new SqlConnection(constr)) { ..... } to correctly dispose of your IDIsposable-Implementing DB-Objects in case they throw exceptions.

For pointers you can look here: in a "using" block is a SqlConnection closed on return or exception?

Thank you for your kind answer and the valuable suggestions. — Joyce Virgin, Feb 23 '18 at 12:02

Getting error as Invalid column name C#.NET

2 Answers2