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
– Aaron Bertrand, Commented Jul 20, 2012 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
– Tim Schmelter, Commented Jul 20, 2012 at 21:34
@thormayer, msdn is your friend: msdn.microsoft.com/en-us/library/bb738521.aspx — Justin Skiles
– Justin Skiles, Commented Jul 20, 2012 at 21:37
@thormayer: These tips wouldn't answer this question, so i dont want to answer it. — Tim Schmelter
– Tim Schmelter, Commented Jul 20, 2012 at 21:37

codingbiz · Accepted Answer · 2012-07-20 21:55:49Z

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

thank you . it seems like I wanted to pass a string instead of an int.
@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.)

M. Adeel Khalid · Accepted Answer · 2017-10-04 13:49:21Z

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

Collectives™ on Stack Overflow

Invalid column name exception

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related