CODEWITHSUNDEEP
c#asp.netsql
Eray Geveci
Eray Geveci

Reputation: 1129

SQL Server error: ambiguous column name

I created a SQL query which receives different parameters. When I pass a parameter just to Region-ID I got an error:

System.Data.SqlClient.SqlException: Ambiguous column name 'REGION_ID'

When I pass data to other parameters its working without a problem.

I build a string for the sql query:

StringBuilder constrains = new StringBuilder();
AppendConstrain(constrains, "RAUMKLASSE_ID", RAUMKLASSE_ID);
AppendConstrain(constrains, "STADT_ID", STADT_ID);
AppendConstrain(constrains, "GEBAEUDE_ID", GEBAEUDE_ID);
AppendConstrain(constrains, "REGION_ID", REGION_ID);
AppendConstrain(constrains, "RAUMATTRIBUTE_ID", RAUMATTRIBUTE_ID);
AppendConstrain2(constrains, "r.REGION_ID", "reg.ID");
AppendConstrain2(constrains, "r.STADT_ID", "st.ID");
AppendConstrain2(constrains, "r.ETAGE_ID", "et.ID");

and here is my SQL query:

StringBuilder query =
            new StringBuilder("SELECT DISTINCT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID,reg.NAME AS REGNAME, st.NAME AS STNAME, et.BEZEICHNUNG as ETBEZEICHNUNG FROM RAZUORDNUNG rz right join RAUMATTRIBUTE ra ON rz.RAUMATTRIBUTE_ID = ra.ID right join RAUM r ON rz.RAUM_ID = r.ID, REGION reg, STADT st, ETAGE et");

I don't know where the problem is?

EDIT:

here is the append string function:

private static void AppendConstrain(StringBuilder query, string name, string value)
    {
        if (String.IsNullOrEmpty(value))
            return;

        if (query.Length > 0)
            query.Append(" AND ");

        query.AppendFormat("{0} IN ({1})", name, value);
    }

    private static void AppendConstrain2(StringBuilder query, string name, string name2)
    {
        if (String.IsNullOrEmpty(name2))
            return;

        if (query.Length > 0)
            query.Append(" AND ");

        query.AppendFormat("{0} = ({1})", name, name2);
    }

Upvotes: 2

Views: 4604

Answers (3)

Gupta Vini
Gupta Vini

Reputation: 530

In your code I can see only one proper reference to REGION_ID

AppendConstrain2(constrains, "r.REGION_ID", "reg.ID");

for the other i.e

 AppendConstrain(constrains, "REGION_ID", REGION_ID);

you need to provide the reference from which table you are referencing this id.

Upvotes: 0

Chris Moutray
Chris Moutray

Reputation: 18349

You appear to be appending the constraint twice for REGION_ID.

Qualify this line with the table:

AppendConstrain(constrains, "REGION_ID", REGION_ID);

becomes 

AppendConstrain(constrains, "<table/alias>.REGION_ID", REGION_ID);

Upvotes: 4

JK.
JK.

Reputation: 21810

You have two different references to REGION_ID:

StringBuilder constrains = new StringBuilder();
AppendConstrain(constrains, "REGION_ID", REGION_ID);
AppendConstrain2(constrains, "r.REGION_ID", "reg.ID");

Only one of those references is qualified (r.REGION_ID). You need to fully qualify both of them. It looks like the other one should be qualified as reg.REGION_ID, eg:

StringBuilder constrains = new StringBuilder();
AppendConstrain(constrains, "reg.REGION_ID", REGION_ID);
AppendConstrain2(constrains, "r.REGION_ID", "reg.ID");

Upvotes: 5

Related Questions