Cant get sql data into listview from a different foreign table (2 tables)

Question

Been stuck on the same problem for days and i have been searching around the web for an answer.

My question is: In the code below i got two tables that has 1 foreignkey. I want to display the nvarchar in that table not the ID.

In my aspx page i got a Listview.

<asp:ListView ID="ListView_Fruit" ItemType="DifferentFruit.Model.Fruit" 
    SelectMethod="ListView_GetData" DataKeyNames="FruitID" runat="server" 
    DeleteMethod="ListView_Del" 
    UpdateMethod="ListView_Update" >
    <LayoutTemplate>
        <table>
            <tr>
                <th>FruitID</th>
                <th>Fruit Name</th>
                <th>TypeOfFruitID</th>
            </tr>
        </table>
    </LayoutTemplate>
    <ItemTemplate>
        <tr>
            <td><%# Item.FruitID  %></td>
            <td><%# Item.FruitName  %></td>
            <td><%# Item.TypeOfFruitID %></td>
        </tr>
    </ItemTemplate>

Database

Fruits 
FruitID(int) Pk
FruitName nvarchar(20)
FruitsTypeID(int) 

FruitsType 
FruitsTypeID(int) (fk)
FruitType(nvarchar(20)

Now i display every fruit from a stored procedure.

Getting all data.
    SELECT FruitID, FruitName, TypeOfFruitID
    FROM Fruits
Getting all data from FruitsType
    SELECT FruitsTypeID, FruitType FROM FruitsType
    WHERE FruitsTypeID = CAST(FruitsTypeID as nvarchar(20))

How can i display the data from the FruitsType? Now it only display the FruitsTypeID not the other row in the FruitsType. I want to display FruitType

My fruit Databaselayer

    public IEnumerable<Fruits> GettingAllFruits(int startRow, int maxRow, out int totalRows)
    {
        using (SqlConnection conn = Create())
        {

                SqlCommand cmd = new SqlCommand("appSchema.app_getFruits", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@PageIndex", startRow / maxRow + 1);
                cmd.Parameters.Add("@PageSize", maxRow);
                cmd.Parameters.Add("@RecordCount", ParameterDirection.Output)

                conn.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {

                    int fruitID = reader.GetOrdinal("FruitID");
                    int fruitName = reader.GetOrdinal("FruitName");
                    int fruitType = reader.GetOrdinal("TypeOfFruitID");

                    while (reader.Read())
                    {
                        Fruits fruit = new Fruits();
                        fruit.fruitID = reader.GetInt32(fruitID);
                        fruit.fruitName = reader.GetString(fruitName);
                        fruit.typeOfFruitID = reader.GetInt32(fruitType);
                        fruitslist.Add(fruits);
                    }
                }

                totalRows = (int)cmd.Parameters["@RecordCount"].Value;

                fruitlist.TrimExcess();

                return fruitlist;
            }
        }
    }

My stored Procedure (Updated)

BEGIN SELECT ROW_NUMBER() 
OVER ( ORDER BY 
[FruitID] DESC 
)AS RowNumber 
,[FruitID] 
,[FruitName] 
,[FruitsTypeID] 
INTO #FruitsRes 
FROM [Fruits] 
INNER JOIN FruitsType ON Fruits.TypeOfFruitID = FruitsType.FruitsTypeID 
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 
DROP TABLE #Results 
END

The stored procedure is fine but when the stored procedure is called back to the application i getting a convertion error. That says cannot convert from 'Apple' To int. Error here -->

int fruitType = reader.GetOrdinal("TypeOfFruitID");

Answer 1

You are basically looking for an Inner Join, Try this:-

SELECT FruitID, FruitName, FruitType
FROM Fruits INNER JOIN FruitsType
ON Fruits.TypeOfFruitID = FruitsType.FruitsTypeID