7

I'm trying to retrieve records to my data gridview dgvEmployees from my table tblEmployees. I'm not sure what's wrong though, maybe because of the syntax? But the code has worked before using MS Visual C# 2010 Express (WinForms only). I'm currently creating a webpage with winforms using MS Visual Studio (ASP.NET - C#). Here's 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!

Improve this question
3
  • Does the user running the code (so e.g. the identity that is set for the application pool of the web site) have access to the database? Commented Nov 13, 2013 at 0:52
  • It's not possible to answer without seeing the database schema. However these are the possibilities to explore: 1) Is tblEmployees stored in a schema other than "dbo"? 2) If you copy the SQL into SQL Management express (or any query running) do you have any problem? 3) Are you sure you are connecting to the correct database? 4) Is the database table not pluralized? Commented Nov 13, 2013 at 0:53
  • Correct database, yes. And when I try to INSERT INTO, it says the same error. But I can add records manually when I open the table... Commented Nov 13, 2013 at 1:35

3 Answers 3

Reset to default
5

The error is referring to the SQL query not the DataSet. In other words the issue is not with the C#. You need to check your connection string and make sure the table exists in the DB.

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

This query is bad: Select * from tblEmployees

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

You will see a similar error:

invalid object name IDONTEXIST

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

Comments

2

You are now running the application on the website so the user that will connect to SQL server is the one that is running your application pool in IIS when you use Integrated Security = SSPI in your connection string.

You need to either:

  1. Give access to the database for the application pool user (not a good idea for the default one).
  2. Change the user for the connection pool to a user who has access to the database.
  3. Specify a user who has access in the connection string.
Improve this answer

1 Comment

Thanks, my issue was that I didn't provide Initial Catalog in connection string for Cross database queries.
1

You should first verify your connection string:

  • Ensure it is connecting to the SQL Server instance you think it is.
  • Ensure that it is establishing the database context for the connection in the database you think that it is.
  • Ensure that it is connecting with the credentials you think that it is.
  • Ensure that those credentials map to the SQL Server user you think that it should
  • That that SQL Server user has the default schema you think it does and that it has appropriate rights granted in the database.

Almost certainly, your problem derives from one or more of the issues listed above.

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

If your object references are not schema-qualified, you may have a problem resolving object references. Object references in your SQL queries should always, at the very least, be schema-qualified. Instead of saying

select * from tblEmployees

you should be saying

select * from dbo.tblEmployees

where dbo is the schema that owns the object. Any object references that are not schema-qualified are looked up at run time in the following order

  1. First, the current user's default schema is probed for an object of the desired name.
  2. If that fails, the dbo schema ('data base owner') is probed for an object of the desired name.

For stored procedures, the lookup is more complex:

  1. Probe the current user's default schema in the current database.
  2. Probe the 'dbo' schema in the current database.

If the stored procedure name begins with 'sp_',

  1. Probe the current user's default schema in the 'master' database.
  2. Probe the 'dbo' schema in the 'master' database.

If the object in question belongs to another schema, it will not be found unless qualified by the owner schema.

Due to the multiple lookup issue, lack of schema-qualification can prevent execution plan caching, meaning that the query plan has to be recompiled on every execution of a query. Needless to say, this has...sub-optimal effects on performance.

Further, you may get...interesting...results if your database user is 'dev' and the dev in question, 6 months back, created a table or other object named 'dev.foo' during development. Now you're live in production and connecting as user 'dev'. Executing select * from foo will bind to dev.foo in preference to the actual production table that the DBA created, 'dbo.foo'. Your users will be wondering why their data is missing or you'll be ripping your hair out wondering why the app is whining about missing columns when they appear to be all there when you look at it via the SQL Management Studio.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.