C# SQL select from [tablename] error

Question

I'm trying to execute a simple SQL select statement, however the SQL Server table I'm trying to access can have a table name that includes numbers on the first position, spaces, SQL keywords etc.

If I understand correctly, putting square brackets around the table name should allow me to do this, but it doesn't seem to be working.

tr_projekt is a string that holds the name of the table I want to access - for example, with tr_projekt = "4test" I'm getting

Incorrect syntax error near 4

followed by a System.NullReferenceException.

For regular table names, like tr_projekt = "test", it works correctly.

string type = "";
SqlConnection conn = new SqlConnection(connString);            
SqlCommand command = new SqlCommand("SELECT DisplayType FROM [" + tr_projekt + "]");
command.Connection = conn;                           
conn.Open();             
type = command.ExecuteScalar().ToString();
conn.close();

Any suggestions?

Answer 1

First you must create the connection string and querys helping from string.Format(string,params[]), will be cleanest way to see what are you doing.

Put a break point on:command.Connection=conn; then point at command and find the property commandText to see your sql command copy and try to run the command directly your dbms (sql server managment as i can see,dbvisualizer, mysql workbench or any other you using)

Here there are some rules in table names: n SQL Server 2012, an object name can be up to 128 characters long.

Nonquoted identifier names must follow these rules:

• The first character must be alphanumeric, an underscore (_), an at sign (@), or a number sign (#).
• Subsequent characters can include alphanumeric characters, an underscore, an at (@) sign, a number sign, or a dollar sign.
• Embedded spaces or special characters are not allowed.

I think you could be missing some quotes or double quotes, try to acomplish make a query directly to db and then use as a model to make your query in c# code.