0

Hiho,

I have a small VET app with an owner table and a pet table, every owner has 4 pets.

I managed to write the join statement:

string getOwner = "SELECT Owner.name AS owner_name, Pet.name AS pet_name FROM Owner JOIN Pet ON owner_id = f_owner_id ";

and now I'm looping though reader

        SqlCommand select = new SqlCommand(getOwner, conn);

        int i = 0;
        SqlDataReader reader = select.ExecuteReader();
        while (reader.Read())
        {

            Owner owner = new Owner(reader[0].ToString());
            owner.pets = new Pet[4];
            owner.pets[0] = new Pet(reader["pet_name"].ToString());
            owner.pets[1] = new Pet(reader.GetValue(1).ToString());
            owner.pets[2] = new Pet(reader["pet_name"].ToString());
            owner.pets[3] = new Pet(reader["pet_name"].ToString());
            try
            {
                owners[i] = owner;
            }
            catch { }                
            i++;
        }

but it only gives me the first match of the join, meaning I only get the first pet of each owner, but i can't access the 2-4th pet. the reader array has only a size of 2 first is the owner name and second the name of the first pet.

how can i access the other pets? I was not able to find an answer yet, i would really appreciate a hint ;)

Jonny Rimek
  • 1,061
  • 11
  • 20
  • Since there are multiple records, you will have to use 1 more loop to scan through the records for each owner. – Techie Dec 01 '15 at 17:25
  • You have a one to many relationship here, have a look at these two questions for tips : http://stackoverflow.com/questions/6918001/populating-an-object-based-on-a-one-to-many-table-relationship-in-sql and http://stackoverflow.com/questions/19448899/how-to-read-multiple-resultset-from-sqldatareader – Alicia Dec 02 '15 at 09:56

0 Answers0