MS SQL Invalid column name error

Question

This code returns the following error: "System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'a51'"

a51 is the correct value inside of the record I'm looking for in the EstablishmentCode column of the Establishments table. Account ID is used to find all entries on the Establishments table with that account ID and populate a dataset with Establishment Code values. Account ID value comes from a session variable. Then I use each of these values in a loop where each iteration calls a datareader while loop. Hope I explained this clearly, but I would gladly clarify more if needed. Here's my code.

myConnection.Open(); SqlCommand getEst = new SqlCommand("SELECT EstablishmentCode FROM Establishments WHERE AccountID = " + ID, myConnection); da = new SqlDataAdapter(getEst); ds = new DataSet(); da.Fill(ds); int maxrows = ds.Tables[0].Rows.Count; for (int x = 0; x < maxrows; x++) { getPhones = new SqlCommand("SELECT * FROM DispatcherPhones WHERE EstablishmentCode = " + ds.Tables[0].Rows[x].ItemArray.GetValue(0).ToString(), myConnection); myReader = getPhones.ExecuteReader(); while (myReader.Read()) { Response.Write("<section id='phone" + myReader["Phone"].ToString() + "' style='padding:20px'>"); Response.Write("<section>Phone Number<br><div class='phone'>" + myReader["Phone"].ToString() + "</div></section>"); Response.Write("<section>Location Code<br><div class='name'>" + myReader["EstablishmentCode"].ToString() + "</div></section>"); Response.Write("<section>Active<br><div class='name'>" + myReader["Active"].ToString() + "</div></section>"); Response.Write("<section class='flex phoneButtonSection'>"); Response.Write("<button type=\"button\" onclick=\"showPhoneForm('" + myReader["ID"].ToString() + "');\">CHANGE</button>"); Response.Write("<button type=\"button\" onclick=\"deletePhones('" + myReader["ID"].ToString() + "');\">DELETE</button>"); Response.Write("</section>"); Response.Write("</section>"); } myReader.Close(); } myReader.Close(); myConnection.Close();

You need to get in the habit of parameterizing your queries to prevent sql injection. — Sean Lange
– Sean Lange, Commented Feb 23, 2016 at 17:36
Looking at SELECT EstablishmentCode FROM Establishments WHERE AccountID = " + ID, I'm thinking that ID is set to A51. First, you want 'A51' which is a string value rather than A51 which is a column name (that presumably doesn't exist). Second, like @SeanLange said, you should create queries through parameterization rather than concatenation. Not only will this prevent SQL injection, but you wouldn't encounter this error in the first place. — Bob Kaufman
– Bob Kaufman, Commented Feb 23, 2016 at 17:36
You would also benefit from only selecting columns you need instead of *. There seems to be some kind of logical issue with the way this is put together. Not sure what the solution is but there seems to be a disconnect. — Sean Lange
– Sean Lange, Commented Feb 23, 2016 at 17:39
well, it's definitely true that they should be parameterized. So I'll fix that and see if it fixes the larger problem as well. — user192632
– user192632, Commented Feb 23, 2016 at 17:56

Mureinik · Accepted Answer · 2016-02-23 17:40:27Z

String literals in SQL are denoted by single quotes ('s) which are missing for your value:

getPhones = new SqlCommand ("SELECT * " + "FROM DispatcherPhones "WHERE EstablishmentCode = '" + // Here -------------------^ ds.Tables[0].Rows[x].ItemArray.GetValue(0).ToString() + "'" // And here , myConnection);

Mandatory comment: concatinating strings in order to create SQL statements may leave your code exposed to SQL injection attacks. You should consider using prepared statements instead.

Seems to have fixed the problem and as everyone else mentioned, injection is an important reason to always use parameters, no matter what type of string or value you are putting in a sql statement.

Collectives™ on Stack Overflow

MS SQL Invalid column name error

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related