[RESOLVED] How can you stop and cycle through a column(s) of data? [2005]

[gjon]

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}

If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.

I tried this:

while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();
//take the read data from the reader and display it for testing purposes
if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + "<br />";
}
}

But for some reason the relationships print out multiple times rather than just until there are no more relatives in the database.

Ok, this is where I am really confused. Why does the reading of data loop all over again from the beginning? I would think that when it read the last row that then it would just exit out of the loop. Just like what's happening with the first loop with while(reader.Read()).


while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + "<br />";
}

[brin351]

If you want something like the following where you have a table with a one to many relationship with another table, there are a few different options you could use depending on how you need it presented.
----------------------------
-person
--relative
--relative
-John Smith
--mother
--father
--sister
etc...
-----------------------------------
1. with a reader return records with a join in the query so you get resultes like
john smith - mother
john smith - father
john smith - sister
sue black - father
sue black .........
etc................

bind the reader to a gridview control
use the gridview rowDataBinding event to set text = "" in the appropiate cells
like
//declair global field variable
string personOld
string personNew

gridview_rowdatabinding(.................)handles......
if e.row.rowtype = datarow...
personNew = e.row.cell(0) //presuming you have a person column in the first grid cell
if (personNew = personOld){
//we have a relative - hide cell(0) presuming persons name is in first cell
e.row.cell(0).text = ""
}else{
// we have a new person - hide cell(1) presuming we have a relative in second cell
e.row.cell(1).text = ""
}
personOld = personNew
end grid_rowdata.........

this will produce

cell1 cell2
-----------------------
person
relative
relative
person
relative

OR------------------------------------

you could requery the database in the rowdatabing event for the list relatives of this person and put their names below the peron in the same cell
grid_rowdata....
person = e.row.cell(0).text
e.row.cell(0).text += "<br/>
reader = queryDB ..... select relative from table where personKey = @person
while reader.read
e.row.cell(0).text += reader.item("relative").tostring & "<br/>"
end while

BUT THAT makes a lot of DB calls if there are alot of records

OR-------------------------------------------

You could put the person records into a dataView and the relative records into a 2nd dataView and have a nested loop to get the relatives of the person
like
dataViewPerson = dataset filled from DB
datViewRelative = dataset filled from DB

datarowview dvp
datarowview dvr
for each dvp in datviewPerson
labelPerson.text += dvp.item("person")
for each dvr in dataviewRelative
if (dvr.item("personRelationKey") = dvp.item("personRelationKey")){
labelPerson += "<br/>" & dvr.item("relative")
}
next
next


-----------the end---------------

Please excuse mistakes in the code I normally write VB and I just typed it in this textbox without testing it but the theory is correct..... good luck

[gjon]

Thank you for these ideas.