title: Cant get sql data into listview from a different foreign table (2 tables)
body: <p>Been stuck on the same problem for days and i have been searching around the web for an answer. </p>
  
  <p><strong>My question is</strong>: In the code below i got two tables that has 1 foreignkey. I want to display the nvarchar in that table not the ID.</p>
  
  <p>In my aspx page i got a Listview.</p>
  
  <pre><code>&lt;asp:ListView ID="ListView_Fruit" ItemType="DifferentFruit.Model.Fruit" 
      SelectMethod="ListView_GetData" DataKeyNames="FruitID" runat="server" 
      DeleteMethod="ListView_Del" 
      UpdateMethod="ListView_Update" &gt;
      &lt;LayoutTemplate&gt;
          &lt;table&gt;
              &lt;tr&gt;
                  &lt;th&gt;FruitID&lt;/th&gt;
                  &lt;th&gt;Fruit Name&lt;/th&gt;
                  &lt;th&gt;TypeOfFruitID&lt;/th&gt;
              &lt;/tr&gt;
          &lt;/table&gt;
      &lt;/LayoutTemplate&gt;
      &lt;ItemTemplate&gt;
          &lt;tr&gt;
              &lt;td&gt;&lt;%# Item.FruitID  %&gt;&lt;/td&gt;
              &lt;td&gt;&lt;%# Item.FruitName  %&gt;&lt;/td&gt;
              &lt;td&gt;&lt;%# Item.TypeOfFruitID %&gt;&lt;/td&gt;
          &lt;/tr&gt;
      &lt;/ItemTemplate&gt;
  </code></pre>
  
  <p>Database</p>
  
  <pre><code>Fruits 
  FruitID(int) Pk
  FruitName nvarchar(20)
  FruitsTypeID(int) 
  
  FruitsType 
  FruitsTypeID(int) (fk)
  FruitType(nvarchar(20)
  </code></pre>
  
  <p>Now i display every fruit from a stored procedure.</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>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</p>
  
  <p>My fruit Databaselayer</p>
  
  <pre><code>    public IEnumerable&lt;Fruits&gt; 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;
              }
          }
      }
  </code></pre>
  
  <p>My stored Procedure (Updated)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 --></p>
  
  <pre><code>int fruitType = reader.GetOrdinal("TypeOfFruitID");
  </code></pre>