C # Invalid ASP.NET Object Name

Question

I am trying to retrieve the entries in my gridview dgvEmployees from my tblEmployees table. I'm not sure what's wrong, possibly due to the syntax? But the code worked before using MS Visual C # 2010 Express (WinForms only). I am currently creating a web page with winforms using MS Visual Studio (ASP.NET - C #). Here is my code:

  SqlConnection sConn; SqlDataAdapter daEmp; DataSet dsEmp; const string sStr = "Server = MYSERVER\\SQLEXPRESS; Database = EMPLOYEES; Integrated Security = SSPI"; protected void Page_Load(object sender, EventArgs e) { sConn = new SqlConnection(sStr); daEmp = new SqlDataAdapter("Select * from tblEmployees", sConn); dsEmp = new DataSet(); daEmp.Fill(dsEmp, "tblEmployees"); dsEmp.Tables["tblEmployees"].PrimaryKey = new DataColumn[] { dsEmp.Tables["tblEmployees"].Columns["EmployeeID"] }; dgvEmployees.DataSource = dsEmp.Tables["tblEmployees"]; } 

Here's the error message on this line (daEmp.Fill(dsEmp, "tblEmployees");

 Invalid object name 'tblEmployees' 

Please, help. Thanks!

Answer 1

The error relates to the SQL query, not to the DataSet . In other words, the problem is not in C #. You need to check the connection string and make sure that the table exists in the database.

daEmp = new SqlDataAdapter("Select * from tblEmployees", sConn);

This request is bad: Select * from tblEmployees

You can verify this by changing the query to: Select * from IDONTEXIST

You will see a similar error:

invalid IDONTEXIST object name

Answer 2

Now you run the application on the website, so the user who will connect to the SQL server is the one that uses the application pool in IIS when you use Integrated Security = SSPI in your connection string.

You need to either:

• Provide access to the database for the application pool user (not recommended for standard).
• Change the user to the pool of connections to the user who has access to the database.
• Specify the user who has access in the connection string.

Answer 3

First check the connection string:

• Make sure it connects to the instance of SQL Server that you think is.
• Make sure that it sets the database context for the connection to the database that you think is.
• Make sure it connects to the credentials that you think are.
• Ensure that these credentials are mapped to a SQL Server user who you think should
• That the SQL Server user has a default schema, which, in your opinion, has and has the corresponding rights granted in the database.

Almost certainly, your problem is related to one or more of the problems listed above.

If your connection database context is in a different database than you think, you probably won't find the objects you are looking for.

If your links to objects do not meet the criteria of the scheme, there may be a problem with resolving links to objects. References to objects in your SQL queries should always at least be schematically qualified. Instead of talking

 select * from tblEmployees 

you have to say

 select * from dbo.tblEmployees 

where dbo is the schema that owns the object. Any references to objects that do not meet the criteria of the schema are checked at runtime in the following order.

• First, for the object with the desired name, the current user default scheme is examined.
• If this fails, the dbo ("database owner") is examined for the object with the desired name.

For stored procedures, the search is more complicated:

• In the current database, program the default user schema.
• Pull the dbo schema in the current database.

If the name of the stored procedure begins with 'sp_',

• Set the default user schema in the "master" database.
• Try the "dbo" schema in the "master" database.

If the object in question belongs to another scheme, it will not be found unless it is specified according to the owner’s scheme.

Due to the multiple search problem, lack of qualification of the schema can prevent caching of the execution plan, which means that the query plan must be recompiled every time the query is executed. Needless to say, this is ... a suboptimal performance impact.

In addition, you can get ... interesting ... results if your database user "dev" and the corresponding developer, 6 months ago, created a table or other object named "dev.foo" during development. Now you live in production and connect as a custom "dev". Executing select * from foo will bind to dev.foo according to the actual production table created by the DBA, "dbo.foo". Your users will be wondering why their data is missing, or you will rip your hair off, wondering why the application whines around the missing columns when they seem to be all there when you look at it through SQL Management Studio.