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

been stuck on same problem days , have been searching around web answer.

my question is: in code below got 2 tables has 1 foreignkey. want display nvarchar in table not id.

in aspx page got 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 display every fruit stored procedure.

getting data.     select fruitid, fruitname, typeoffruitid     fruits getting data fruitstype     select fruitstypeid, fruittype fruitstype     fruitstypeid = cast(fruitstypeid nvarchar(20)) 

how can display data fruitstype? display fruitstypeid not other row in fruitstype. want 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()  on ( order  [fruitid] desc  )as rownumber  ,[fruitid]  ,[fruitname]  ,[fruitstypeid]  #fruitsres  [fruits]  inner join fruitstype on fruits.typeoffruitid = fruitstype.fruitstypeid  rownumber between(@pageindex -1) * @pagesize + 1 and(((@pageindex -1) * @pagesize + 1) + @pagesize) - 1  drop table #results  end 

the stored procedure fine when stored procedure called application getting convertion error. says cannot convert 'apple' int. error here -->

int fruittype = reader.getordinal("typeoffruitid"); 

you looking inner join, try this:-

select fruitid, fruitname, fruittype fruits inner join fruitstype on fruits.typeoffruitid = fruitstype.fruitstypeid