Invalid column name exception

Question

Im trying to get a simple SQL statement in my code, and get a DataTable, and for some reason I get this weird exception :

Invalid column name

This is my code :

public DataTable GetAllVideoID(string stringId)
    {
        dt = new DataTable();
        dataObj = new DataObj();
        sql = "SELECT * FROM TBL_VIDEOS WHERE TBL_VIDEOS.ID=" + stringId;

        return dt = dataObj.SelectDataTable(sql);
    }

public DataTable SelectDataTable(string sql)
    {
        try
        {
            conn = new SqlConnection(conString);
            conn.Open();
            adapter = new SqlDataAdapter(sql, conn);

            dt = new DataTable();

            adapter.Fill(dt);



            return dt;
        }
        catch (Exception e)
        {

            throw e;
        }
        finally { conn.Close(); }

    }

When I run this on my management tool, just the statemnet - it works perfectly. So I dunno ..

Structure of my DB : ID,TITLE,V_DESCIPTION,UPLOAD_DATE,V_VIEW,USERNAME,RATING,V_SOURCE,FLAG

thanks :)

Could you show us the table structure of `TBL_VIDEOS`? Maybe one or more columns has a reserved word / keyword etc.? — Aaron Bertrand, Jul 20 '12 at 21:30
Use sql-parameters to avoid sql-injection. Sidenote: You don't have to open/close a connection when using a DataAdapter. `Fill` will open/close it implicitely. Don't `throw exception` but only `throw` to keep the stack trace. — Tim Schmelter, Jul 20 '12 at 21:34
@thormayer, msdn is your friend: http://msdn.microsoft.com/en-us/library/bb738521.aspx — Justin Skiles, Jul 20 '12 at 21:37
@thormayer: These tips wouldn't answer this question, so i dont want to answer it. — Tim Schmelter, Jul 20 '12 at 21:37
try to put a break point where `sql = "SELECT ` line and inspect the value of `sql`. I suspect the `stringId` — codingbiz, Jul 20 '12 at 21:51
stringId returning something that does not exists in the Table, and I want that the DataTable Row will check afterwards if its exists or not.. — thormayer, Jul 20 '12 at 21:53
But ID is a numeric field, right? What is the value of your stringID? "-1", "0" or something that could give problems like a string text? — Steve, Jul 20 '12 at 21:57

score 5 · Accepted Answer · answered Jul 20 '12 at 21:55

5

changing your sql to

sql = "SELECT * FROM TBL_VIDEOS WHERE TBL_VIDEOS.ID='" + stringId+"'";

could fix this. That puts a single quote around the value of stringId e.g. .ID = '10'

If stringId = 'user' your query becomes

 sql = "SELECT * FROM TBL_VIDEOS WHERE TBL_VIDEOS.ID=user
 instead of
 sql = "SELECT * FROM TBL_VIDEOS WHERE TBL_VIDEOS.ID = 'user'

but user is not a valid variable in sql, so you get invalid column name

answered Jul 20 '12 at 21:55

codingbiz

26,179
8
59
96

thank you . it seems like I wanted to pass a string instead of an int. – thormayer Jul 20 '12 at 22:02
3

@thormayer Your column name is very confusing. ID is not typically a string. (We also don't really need a TBL_ prefix to know that a table is a table.) – Aaron Bertrand Jul 20 '12 at 22:07

score 0 · Answer 2 · edited Oct 04 '17 at 13:49

0

Your SelectDataTable method is absolutely fine. The only thing which comes to my mind is to replace this:

sql = "SELECT * FROM TBL_VIDEOS WHERE TBL_VIDEOS.ID=" + stringId;

by

sql = string.Format("SELECT * FROM TBL_VIDEOS WHERE TBL_VIDEOS.ID 
        = '{0}'", stringId);

edited Oct 04 '17 at 13:49

M. Adeel Khalid

1,786
2
21
24

answered Jul 20 '12 at 23:20

NG.

5,695
2
19
30

Invalid column name exception

2 Answers2