title: using sql commands to look for records - tree structure in sql
body: <p>So I have this table with the names and birth/death dates of all my ancestors that I'm using for a genealogy website. The key is the ID and all the children's ParentID items will be set to the same as the parent's ID.</p>
  
  <pre><code>ID - int (key)
  FirstName - string
  Lastname - string
  Born - int
  Died - int
  ParentID - int
  hasChildren - bool
  </code></pre>
  
  <p>I want to show it in some type of tree structure (that's another problem for later) - for now I'm just showing them in a GridView. I have a button at the beginning of each row called Branch. When you click Branch on someone's record it calls a select function to retrieve the ID of the selected record so I can make a SELECT command to search all records with a ParentID that match that ID of the selected record. In doing so I will repopulate the grid with all children of the selected record (the parent). Since I don't want to show the ID and ParentID in the grid, I can't retrieve it using:</p>
  
  <pre><code>GridViewRow row = GridView1.Rows[GridView1.SelectedIndex];
  </code></pre>
  
  <p>what I had in mind was something like:</p>
  
  <pre><code>        SqlDataSource ds = (SqlDataSource)GridView1.DataSourceObject;
      string selectstring = "SELECT [ID],[FirstName],[MiddleName],[LastName],[BirthDate],[DeathDate],[ParentID],[hasChild] FROM [test2].[dbo].[Geneology] where ParentID = " + x;
      SqlDataSource2.SelectCommand = selectstring;
  </code></pre>
  
  <p>where x is the string of the selected records ID. I can't seem to figure out how to retrieve the ID of the selected record without showing it in the gridview. I selected all columns when i made the datasource but made the ID and ParentID hidden. Now they aren't included in the rows of the gridview. Any ideas on how to go about this? I hope this explains it well enough. </p>
  
  <p>[edit] OK - I fixed it. I had to set the DataKey to be ID so the GridView.SelectedValue returns the ID. Its working now. I just need to figure out how to go back one level without repopulating the grid with all records.</p>