SQL Query works in management studio but not in C#

Question

I have a problem doing a query with my software...

I have a function which builds my query, and then execute it to fill a gridview. But it keep throwing me an exception : "The multi-part identifier "" could not be bound" with several columns.

When I try the query in SQL Management Studio, it just works fine. I really don't understand. Here's the query.

SELECT DISTINCT [NonConformite].[Numero],
CAST ([NonConformite].[Numero] AS varchar (255)) AS Champ1, 
CAST ([NonConformiteDonneesComplementaires177_4].[Texte2] AS varchar (255)) AS Champ2, 
CAST (NonConformite.dbo.[NonConformiteStatut].[Libelle] AS varchar (255)) AS Champ3, 
CAST ([NonConformite].[Description] AS varchar (255)) AS Champ4 FROM [NonConformite].[dbo].[NonConformite]  
LEFT JOIN NonConformite.dbo.[NonConformiteDonneesComplementaires] as NonConformiteDonneesComplementaires177_4 
ON [NonConformite].[Numero] = [NonConformiteDonneesComplementaires177_4].[Numero] 
LEFT JOIN NonConformite.dbo.[NonConformiteStatut] 
ON [NonConformite].[CpteurStatut] = NonConformite.dbo.[NonConformiteStatut].[Cpteur]

I work on Windows 7 Pro, with Visual Studio 2008, SQL Server 2008 and in ASP.NET C#.

Thank you for your answers !

That is very hard to read... you know you can use aliases to help make the query sane, yes? — Marc Gravell, Mar 21 '11 at 09:17
Can you try to create a procedure with this query, and try to execute procedure from code. — buda, Mar 21 '11 at 09:25

score 2 · Accepted Answer · answered Mar 21 '11 at 09:21

2

I would start by breaking the query into aliases - see if that helps things; and unless you are intentionally doing cross-db work (usually a bad idea), drop the db identifier and just use the current db:

SELECT DISTINCT nc.[Numero],
CAST (nc.[Numero] AS varchar (255)) AS Champ1, 
CAST (dc.[Texte2] AS varchar (255)) AS Champ2, 
CAST (st.[Libelle] AS varchar (255)) AS Champ3, 
CAST (nc.[Description] AS varchar (255)) AS Champ4
FROM dbo.[NonConformite] nc
LEFT JOIN dbo.[NonConformiteDonneesComplementaires] dc
ON nc.[Numero] = dc.[Numero] 
LEFT JOIN dbo.[NonConformiteStatut] st
ON nc.[CpteurStatut] = st.[Cpteur]

answered Mar 21 '11 at 09:21

Marc Gravell

1,026,079
266
2,566
2,900

1

Adding alias makes the Program more understandable, the approach u have done have some ambiquities which is throwing the Exception. According to @Marc Gravell, the Cross-db work will be a main reason for this type of Problems – Sai Kalyan Kumar Akshinthala Mar 21 '11 at 09:27
Unfortunately, I have to do cross-db work, because I am converting a standard application into a web application. And I have to use the same DB. – Pixayl Mar 21 '11 at 09:32
@Mark : The former developper made 8 DB for one application, and some of them shares table name. I tried your query, and it works fine in Visual Studio now (I added the DB name). Thank you ! – Pixayl Mar 21 '11 at 09:42

score 1 · Answer 2 · answered Mar 21 '11 at 09:32

Try running the following, and remove the comments from the bottom upwards till you can identify where the problem is.

SELECT *
--DISTINCT [NonConformite].[Numero],
--CAST ([NonConformite].[Numero] AS varchar (255)) AS Champ1, 
--CAST ([NonConformiteDonneesComplementaires177_4].[Texte2] AS varchar (255)) AS Champ2, 
--CAST (NonConformite.dbo.[NonConformiteStatut].[Libelle] AS varchar (255)) AS Champ3, 
--CAST ([NonConformite].[Description] AS varchar (255)) AS Champ4
FROM [NonConformite].[dbo].[NonConformite]  
-- LEFT JOIN NonConformite.dbo.[NonConformiteDonneesComplementaires] as NonConformiteDonneesComplementaires177_4 
    ON [NonConformite].[Numero] = [NonConformiteDonneesComplementaires177_4].[Numero] 
-- LEFT JOIN NonConformite.dbo.[NonConformiteStatut] 
    ON [NonConformite].[CpteurStatut] = NonConformite.dbo.[NonConformiteStatut].[Cpteur]

SQL Query works in management studio but not in C#

2 Answers2