How to solve “Invalid object name” error when executing query

1.8K Asked by ChrisDyer in Salesforce , Asked on Aug 21, 2021

I connect database with Visual Studio 2017. When I am trying to execute a query then it shows the following error:

enter image description here

The connection string I am using is: SqlConnection con = new SqlConnection("Data Source=ANUPAM-DESKTOPANUPAM;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

My code:

public void exicuteDatabaseQuery(String query) { con.Open(); SqlDataAdapter sda = new SqlDataAdapter(query, con); sda.SelectCommand.ExecuteNonQuery(); con.Close(); MessageBox.Show("Successfull"); } private void Button_Click(object sender, RoutedEventArgs e) { int id = Convert.ToInt32(___Id_.Text); int number = Convert.ToInt32(___Number_.Text); String InsertQuery = "INSERT INTO Member (id, number) values('"+id+"','"+number+"')"; exicuteDatabaseQuery(InsertQuery); }

Database explorer image:

enter image description here

Answered by Clare Matthews

I think you are facing SQL server invalid object name error due to SQL Server login not having it's "default database" properly set.

This will show you what your "default database" is set to:

  SELECT sp.name , sp.default_database_name FROM sys.server_principals sp WHERE sp.name = SUSER_SNAME();

You can either change the default database of your login, or you can specify the database in the connection string.

This will modify your default database:

  ALTER LOGIN  WITH DEFAULT_DATABASE = [testDB];

If you want to specify the database in the connection string, make your connection string:

  SqlConnection con = new SqlConnection("Data Source=ANUPAM-DESKTOP\ANUPAM;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Initial Catalog=testDB");

Hope this helps you solve sql server invalid object name error!

Your Answer

Answer (1)

Ranjana

The "Invalid Object Name" error typically occurs in SQL when the database server cannot find the specified table, view, or other object. Here are some common causes and solutions for this error:

1. Check Object Name and Spelling

Ensure the object name is spelled correctly and matches the name in the database.

  SELECT * FROM your_table_name;

2. Check Database Context

Ensure you are connected to the correct database. You can specify the database in your query or switch to the correct database context.

Specify Database in Query

  SELECT * FROM database_name.schema_name.table_name;

Switch Database Context

  USE database_name;

3. Check Schema

If the object resides in a schema other than the default schema (usually dbo), specify the schema name in the query.

  SELECT * FROM schema_name.table_name;

4. Check Object Existence

Ensure the object exists in the database. You can check for the existence of the object using a query like:

  SELECT * FROM information_schema.tables WHERE table_name = 'your_table_name';

Or using system catalog views in SQL Server:

  SELECT * FROM sys.tables WHERE name = 'your_table_name';

5. Refresh IntelliSense (SQL Server Management Studio)

If you are using SQL Server Management Studio (SSMS), IntelliSense might be outdated. Refresh IntelliSense with:

  plaintextCopy codeCTRL + SHIFT + R

6. Check User Permissions

Ensure the user has the necessary permissions to access the object. You can check and grant permissions with:

  GRANT SELECT ON schema_name.table_name TO your_user;

Example of Troubleshooting Steps

Verify Object Name:

  SELECT * FROM your_table_name;Check Database Context:

  USE your_database_name;SELECT * FROM your_table_name;Specify Schema:

  SELECT * FROM your_schema.your_table_name;

Check Existence:

  SELECT * FROM information_schema.tables WHERE table_name = 'your_table_name';

Verify Permissions:

  GRANT SELECT ON your_schema.your_table_name TO your_user;

Additional Tips

Case Sensitivity: Ensure the object name matches the case sensitivity of the database settings.

Synonyms: If using synonyms, ensure they are correctly defined and point to the correct object.

Linked Servers: If querying across linked servers, ensure the link is correctly configured and accessible.

By following these steps, you should be able to identify and resolve the "Invalid Object Name" error. If you continue to experience issues, providing more details about your query and database setup can help diagnose the problem further.

4 Months