SQL query: foreach row?

[MaryAnne19]

Hi all!

I have a program that runs as a large form (Form1) with smaller forms that open inside of it (PeopleBox). It's for friends/contacts - each person gets their own row in the database (PeopleDB). At runtime I'd like it to open a PeopleBox for each person already existing inside the DB.

I've managed to get it to open a PeopleBox for each row in the DB, but each box is showing the information for the first row only (rather than cycling through the rows). I'm sure I need a foreach statement, but I'm unsure how to phrase it. Any help would be greatly appreciated!

Code below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;


namespace MaryAnne
{
    public partial class Form1 : Form
    {

        public Form1() 
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            
            //  DB connection
            string fileName = "PeopleDB.sdf";
            string connectionString = string.Format("DataSource=\"{0}\";", fileName);

            //  SQL Command
            string sql = "select * from PeopleTable";

            SqlCeConnection cn = null;
            try
            {
                cn = new SqlCeConnection(connectionString);

                SqlCeCommand cmd = new SqlCeCommand(sql, cn);

                //  Checking to make sure no concurrent connections exist
                if (cn.State == ConnectionState.Open)  
                    cn.Close();

                //  Opening the connection
                cn.Open();

                SqlCeDataReader scdr = cmd.ExecuteReader();

                while (scdr.Read())
                {
                    //  Opening a PeopleBox for each row in the DB
                    PeopleBox childForm = new PeopleBox();
                    childForm.MdiParent = this;
                    childForm.Show();
                }
                scdr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (cn != null)
                    cn.Close();
            }
                        
        }

    }
}

The relevant bit of code is:

while (scdr.Read())
                {
                    //  Opening a PeopleBox for each row in the DB
                    PeopleBox childForm = new PeopleBox();
                    childForm.MdiParent = this;
                    childForm.Show();
                }

I know that the while statement is where I'm going wrong. I'm just not having any luck with foreach statements - everything I've tried has thrown up a series of errors.

Thanks for reading!
-MaryAnne

[Momerath]

You can't use foreach in this situation (You can only use foreach on object collections that implement IEnumerable).

You are reading data into the scdr object, but you never use it. Everything about the row is there, and that's what you need to be looking at.

[MaryAnne19]

Hi Momerath!

How would I go about pulling the row information from scdr and passing it to each instance of PeopleBox? With a DataRow construct?

I'm not asking you to write it up for me, but if you could point me to a tutorial that'll explain what needs to be done in newbie terms, I'd be eternally greatful!

Thanks!
-MaryAnne

You can't use foreach in this situation (You can only use foreach on object collections that implement IEnumerable).

You are reading data into the scdr object, but you never use it. Everything about the row is there, and that's what you need to be looking at.

[N4JRY]

Hi,

scdr when it is in the while loop contains the data you need. Just access it like:
scdr("ID") (although you might want to test it for null if it is nullable).

I suggest you then pass the information via a custom constuctor to the PeopleBox. In this example the constructor is expecting an int but it could be a data object.

Inside the loop the code could be:

PeopleBox childForm = new PeopleBox(scdr("ID"));

Then the new PeopleBox might look utilise something like:

    private readonly int _id;
    public Form2(int ID)
    {
        // New constructor
        InitializeComponent();
        _id = ID;
    }
    private void Form2_Load(object sender, EventArgs e)
    {
        // Do what you need with the ID
        this.Text = _id.ToString();
    }

Hope this helps.

Nicholas

[MaryAnne19]

Hi Nicholas!

So let me make sure I'm understanding this: I don't just need to create the relevant code in Form1, but I also need to write some code into PeopleBox that will listen for the information being passed from Form1?

-MaryAnne

[MaryAnne19]

Hi Nicholas!

So let me make sure I'm understanding this: I don't just need to create the relevant code in Form1, but I also need to write some code into PeopleBox that will listen for the information being passed from Form1?

-MaryAnne

[N4JRY]

Yes, in some fashion or form PeopleBox has to do something with what it gets given.

Using the constructor keeps everything in one place but you could use properties:

private int _id;
        public int UserID
        {
            get { return _id; }
            set { _id = value; this.Text = _id.ToString();}
        }

That gets accessed like:

PeopleBox childForm = new PeopleBox();
childForm.MdiParent = this;
childForm.UserID = scdr("ID");
childForm.Show();