CODEWITHSUNDEEP
c#sql-servervisual-studiosqlclient
Semyon Labzov
Semyon Labzov

Reputation: 77

Query works fine,but SQLCommand throws 'invalid name'

I've this query string, which works fine when I manually run it:

private const string _sold_products_query = @"SELECT rp.Id, pl.ProductName, rp.Price, rp.[StatusDate] FROM [dbo].product_name_list as pl INNER JOIN [dbo].sold_products as rp ON pl.Id = rp.FK_product_name_list;";

if I do this still doesn't work:

private const string _sold_products_query = @"SELECT rp.Id, pl.ProductName, rp.Price, rp.StatusDate FROM dbo.product_name_list as pl INNER JOIN dbo.sold_products as rp ON pl.Id = rp.FK_product_name_list;";

Same if put brackets everywhere like that:

private const string _sold_products_query = @"SELECT [rp].[Id], [pl].[ProductName], [rp].[Price], [rp].[StatusDate] FROM [dbo].[product_name_list] as pl INNER JOIN [dbo].[sold_products] as rp ON [pl].[Id] = [rp].[FK_product_name_list];";

But when I put it as argument in SQLCommand object and run methon ExecuteReader it throws this exception:

System.Data.SqlClient.SqlException: 'Invalid column name 'StatusDate'.'

code:

SqlCommand command = new SqlCommand(_sold_products_query, conn);
SqlDataReader dataReader = command.ExecuteReader();

Query tables:

enter image description here

enter image description here

CREATE TABLE statements:


    CREATE TABLE [product_name_list] (
        [Id]                    int NOT NULL PRIMARY KEY IDENTITY(1,1),
        [ProductName]           varchar(255) NOT NULL UNIQUE
    );
    
    CREATE TABLE [sold_products] (
        [Id]                    int NOT NULL PRIMARY KEY IDENTITY(1,1),
        [FK_product_name_list]  int FOREIGN KEY REFERENCES product_name_list(Id),
        [Price]                 decimal,
        [StatusDate]                    Date
    );

Connection string works fine, because another query works. Does anyone have an idea why it doesn't work?

Upvotes: 0

Views: 126

Answers (2)

Preben Huybrechts
Preben Huybrechts

Reputation: 6111

How to debug this:

exec sp_columns sold_products from your C# code will give you all column info from the table sold_products if there is a difference with what you see in SSMS you are connection to a different instance or database.

Your SqlConnection or DbConnection have a property ConnectionString.

Get connection info from SSMS

At the bottom of every SSMS window is the following status bar. (Screenshot is from SSMS 18) SSMS Status Bar

  1. Is your Instance
  2. Is your login name
  3. Is the current connected database.

(you can also see this in the properties window in SSMS)

The Connectionstring properties should match your SSMS connection.

Extra

In ssms you can even connect to multiple instances at once if you use the Registered Servers window (CTRL+ALT+G). Connected will show X/Y. X = the number of open connections, Y being the number of attempted connections. Usefull if you need to query multiple instances at once.

Multiple connections

Upvotes: 3

Tanzy
Tanzy

Reputation: 710

it may be the [] brackets causing the issue. Try removing them or changing to the following.

private const string _sold_products_query = @"SELECT [rp].[Id], [pl].[ProductName], [rp].[Price], [rp].[StatusDate] FROM [dbo].[product_name]_list as pl INNER JOIN [dbo].[sold_products] as rp ON [pl].[Id] = [rp].[FK_product_name_list];";

generally you will use the square brackets on everything or nothing. In my view it's much better this way, rather than using square brackets on some data and not others.

Upvotes: -1

Related Questions