Display discription instead of ID in datagrid

[TSAR]

Very common but...

I have one table named "Codes" where I put all the descriptions of the codes of my application. The key is a combination of 2 fields : code_KOI (kind of information) and code_Nr. So we have :

code_ID
code_KOI
code_Nr
code_Description

Now let say I have a table named "Users" :

user_ID
user_Name
user_AAA
user_BBB
user_CCC
...

The field user_AAA is linked to the table Codes with code_KOI = 1 and code_Nr = user_AAA, user_BBB is linked to the table Codes with code_KOI = 2 and code_Nr = user_BBB, user_CCC is linked to the table Codes with code_KOI = 3 and code_Nr = user_CCC...

Now I want to display the table Users in a datagrid, but of course I want the descriptions of the fields user_AAA, user_BBB, user_CCC,... instead of a number. So if user_AAA = 5, I want the description of code_KOI = 1 and code_Nr = 5.

How can I do this ?

[nvenkat75]

Instead of using "select * from ....."

use joins with column name alias and

map the alias name in the <asp:BoundColumn DataField="<column name alias>"....> in the DataGrid

Example shown below:

"select A.PK_ASSOCIATE_ID, A.USER_LEVEL, B.EMPLOYEE_NAME AS EMPLOYEE_NAME, B.CONTACT_NO AS CONTACT_NO FROM ASSOCIATE_MASTER AS A LEFT OUTER JOIN EMPLOYEE_MASTER AS B ON (A.FK_EMPLOYEE_ID = B.PK_EMPLOYEE_ID)";


EMPLOYEE_NAME - is the alias name which is boundcolumn to the datagrid

[TSAR]

I understand what you mean, but isn't there another method to do this ?

The reason why I ask that, is that first of all my table containts a lot of these fields linked to the table 'Codes' (but with different code_KOI). Also I would like to create a more "general" solution, that I could use in the whole application.

[mendhak]

I understand what you mean, but isn't there another method to do this ?

The reason why I ask that, is that first of all my table containts a lot of these fields linked to the table 'Codes' (but with different code_KOI). Also I would like to create a more "general" solution, that I could use in the whole application.

If you want a more general solution, then you should modify Table2. Perform a vertical split on it and place the users in a different table. Associate each user with a foreign key, KOI number.

You can perform your joins easily then, without having to hardcode "1", "2" or "3" in your query.

[TSAR]

I think we don't understand each other, so I give an example.

Let say in table Codes we have these records :

KOI Nr Description
1 1 English
1 2 Dutch
1 3 French
...
2 1 France
2 2 Spain
2 3 Belgium
...
3 1 Blue
3 1 Green
...


In the User table I have these fields :

user_ID
user_Name
user_Language (KOI = 1)
user_Country (KOI = 2)
user_ColorOfEyes (KOI = 3)

[Magiaus]

Post some code, please.

Try

--Get one user language description
SELECT 
    tblCode.Code_Description 
FROM tblCode 
INNER JOIN tblUsers ON tblCode.Code_Nr = tblUser.user_language 
WHERE tblUser.user_id = @id

--if your using this in code aka as a text string .Replace("@id", value)
--Get all users languages discriptions
SELECT 
    tblCode.Code_Description 
FROM tblCode 
INNER JOIN tblUsers ON tblCode.Code_Nr = tblUser.user_language 

--um
--Force a french user to be an english user because french people think they are sexy when they are really not all that sexy

DECLARE @LANG;

SET @LANG = (SELECT tblCode.Code_Description FROM tblCode INNER JOIN tblUser ON tblCode.Code_Nr = tblUser.user_id WHERE tblUser.user_id = @id);

BEGIN
IF @LANG = 'FRENCH'
    SELECT 'ENGLISH' As Code_Description
ELSE
    SELECT 
        Code.Code_Description 
    FROM tblCode 
    INNER JOIN tblUsers ON tblCode.Code_Nr = tblUser.user_language 
END

Also, if your using VS.Net try the properties tab and columns..... or what nvenkat75 said or maybe what mendhak said or maybe both