Why do I get Invalid Object Name for a SQL Server table, when the object is valid?

Question

I am trying to execute an SQL query at runtime:

        SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;
        cmd.CommandText = "SELECT * FROM dbo.Books_in_Storage WHERE author = 'myself'";
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Connection = connection;
        connection.Open();
        reader = cmd.ExecuteReader();
        connection.Close();

Execution reaches reader = cmd.ExecuteReader(); and gives me the error:

Invalid object name dbo.Books in Storage.

If I look at my SQL Server Object Explorer, I can see that dbo.Books in Storage is present:

Why is this happening?

Books in Storage and Books_in_Storage are not the same. Preferably, rename the table not to use spaces. — Andy G
– Andy G, Commented Dec 20, 2017 at 16:10
This is just one of the roughly 3,980,437 reasons not to use underscore or (especially) spaces in table and object names. — Yuck
– Yuck, Commented Dec 20, 2017 at 16:11
Also, "in storage" seems a red flag to me. They are probably just "Books" with perhaps a field (or related field or calculation) to indicate whether or not they are in storage. — Andy G
– Andy G, Commented Dec 20, 2017 at 16:15
It seems that you have created a database with AttachDbFileName but now you are not using anymore that property in your connection string and you don't specify any database to connect to. So your query works against the master database where there is no table with that name. (Of course the advices to not have tables with spaces are absolutely right) — Steve
– Steve, Commented Dec 20, 2017 at 16:23

Ross Bush · Accepted Answer · 2017-12-20 16:56:53Z

1

In SQL Server, you can query a table with spaces using the [] as shown below, however, it is not normal to include spaces when choosing your database table names.

SELECT * FROM [table name]

edited Dec 20, 2017 at 16:56

answered Dec 20, 2017 at 16:16

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why do I get Invalid Object Name for a SQL Server table, when the object is valid?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related