Error with SqlCe parameters

Question

I made MANY parameterized queries at one time on this beautiful planet, and no one wrote such an error ... WTFudge?!?!

ERROR:

There was an error parsing the query. [ Token line number = 1, Token line offset = 20, Token in error = @table ] 

Obviously, the compiler does not like my SQL statement ... but I do not see a problem ???

Here is my code.

 using (SqlCeConnection con = new SqlCeConnection(_connection)) { string sqlString = "SELECT @colID FROM @table WHERE @keyCol = @key"; SqlCeCommand cmd = new SqlCeCommand(sqlString, con); cmd.Parameters.Add(new SqlCeParameter("@table", tableName)); cmd.Parameters.Add(new SqlCeParameter("@colID", columnIdName)); cmd.Parameters.Add(new SqlCeParameter("@keyCol", keyColumnName)); cmd.Parameters.Add(new SqlCeParameter("@key", key)); try { con.Open(); return cmd.ExecuteScalar(); } catch (Exception ex) { Console.Write(ex.Message); throw new System.InvalidOperationException("Invalid Read. Are You Sure The Record Exists", ex); } finally { if (con.State == ConnectionState.Open) con.Close(); cmd.Dispose(); GC.Collect(); } } 

as you can see it is a VERY simple SQL statement. I, although "@table", may have been stupidly reserved or something else ... therefore ive tried @tableName, @var, @everything !!! I don’t know what the problem is.

During debugging, I checked that there is actually an @table parameter in the SqlCeParameterCollection. And he was there. Clear like a day!

Answer 1

Since you are in C # (unlike stored procedures)

 string sqlString = "SELECT " + columnIdName + " FROM " +tableName "WHERE " + keyColumnName + "= @key"; 

You will need to verify that columnIdName, tableName, keyColumnName are limited by a list of values ​​(or at least limit the length, for example, 50 characters), otherwise this procedure is optimized for unreliability and SQL injection.

Answer 2

This has affected SqlCe. But in Sql Server and SqlExpress, you can use paarameter for the table name.