Search code examples
c#sqlasp.netdatabaseeval

how to use values of 2 different tables in asp.net with eval from database

Hi I want to put name of the city instead of id of the city

in my data base I have id of city in my table1 and i have another table that Included id and name of the city

now I can use this code

<h5 class="urun-fiyat"><%# Eval("cityid")%></h5>

to see the id of the city but i need name of the city

i dont have city name in table1 and i should take it with inner join or some other way from table2

what should i do ?!

Solution

  • The general (and easy) approach?

    Build a query that pulls in the "text" City value based on the city_id.

    In most cases, I suggest createing a view.

    So, say we have this:

    SELECT People.FirstName, People.LastName, People.Hotel_ID
FROM People

    that gives this:

    FirstName LastName Hotel_ID
    Alex Smith 77
    Bob Phoenix 102
    Correy Lollas 102
    Ronz Howerd 102
    Scott Stevens 77
    Marianne Tucker 77
    Bob Hamilton 77
    Super Man 77
    John Smith 82
    Princes Leia 77
    Joe Blow NULL
    Luke Skywalker 77
    Hans Solo 102
    Darth Vader 102
    Albert Kallal 82

    As you can see, the Hotel_ID is not much use.

    So, in the query builder, then we build a left join, say like this:

    enter image description here

    So, now, we "look up" by using a simple left join, and we now have the Hotel Name as a text value from the other table.

    So, sql becomes this:

    SELECT People.Firstname, People.LastName, People.Hotel_ID, tblHotels.HotelName
FROM People 
LEFT JOIN tblHotels ON People.Hotel_ID = tblHotels.ID

    And now output becomes this:

    Firstname LastName Hotel_ID HotelName
    Alex Smith 77 Jasper Park Lodge
    Bob Phoenix 102 Big White Resort
    Correy Lollas 102 Big White Resort
    Ronz Howerd 102 Big White Resort
    Scott Stevens 77 Jasper Park Lodge
    Marianne Tucker 77 Jasper Park Lodge
    Bob Hamilton 77 Jasper Park Lodge
    Super Man 77 Jasper Park Lodge
    John Smith 82 Canadian Rocky Mountain Resorts
    Princes Leia 77 Jasper Park Lodge
    Joe Blow NULL NULL
    Luke Skywalker 77 Jasper Park Lodge
    Hans Solo 102 Big White Resort
    Darth Vader 102 Big White Resort
    Albert Kallal 82 Canadian Rocky Mountain Resorts

    So, do what is called a left join. I suggest a "left" join, since some of the Hotel_ID values may not exist, and we don't have a hotel_id, but we still want to include the row in the grid.