I'm using VS2010 with .NET4

Hi all, I'm working on a program that spawns a childform for each row in a database at runtime. This required some custom solutions and prevented me from using the built-in database interactions. All of that is working well, but I'm at the point where I'd like to have a button on each childform that will spawn new forms if I need them.

My problem: I can make the button create a new instance of the form, but I'm unable to link that new form to the database. I have tried multiple solutions and I've just made a complete mess of it, so I've reverted to the last known 'working' version of my code in the hopes that someone here can help by showing me an easy way to make it happen within the existing framework.

At runtime, Form1 acts as the parent and creates a new instance of DisplayForm for each row in the database. Each DisplayForm has a button called "buttonNew" that creates a new instance of DisplayForm that needs to be connected to the db.

Db name = MyDatabase.sdf
Table name = MyTable

Form1:

public partial class Form1 : Form
    {
        // Adds these private fields, all null by default
        DataSet ds;
        DataTable dt;
        SqlCeConnection cn;
        SqlCeDataAdapter adp;
        SqlCeCommandBuilder cb;

        public Form1()
        {
            InitializeComponent();
        }

        //  This bit helps format the coordinate locations for the childform
        public Point StringToPoint(string str)
        {
            string[] s = str.Split(',');
            int x = Int32.Parse(s[0]);
            int y = Int32.Parse(s[1]);
            return new Point(x, y);
        }

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

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

            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();

                // create data adapter
                adp = new SqlCeDataAdapter(cmd);

                // create command builder for inserts, updates and deletes 
                cb = new SqlCeCommandBuilder();
                cb.DataAdapter = adp;

                // create new dataset and fill it from database
                ds = new DataSet("test");
                adp.Fill(ds);

                // get datatable which is first and only table in dataset
                dt = ds.Tables[0];

                // create MDI child forms and populate their textboxes
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    DisplayForm childForm = new DisplayForm();
                    childForm.Name = "DisplayForm" + i.ToString(); // need a name to distinguish them
                    childForm.MdiParent = this;
                    childForm.Id = (int)row["id"];
                    childForm.Title = (string)row["title"];
                    childForm.Content = (string)row["content"];
                    childForm.Show();
                    childForm.Location = StringToPoint((string)row["location"]);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                // close connection in the event of an exception, otherwise leave it open
                if (cn != null) cn.Close();
            }
        }

        //  The method that updates the DB with the new values
        public void NotifyChanges(DisplayForm childForm)
        {
            int rowIndex = int.Parse(childForm.Name.Substring(11));
            
            // update the appropriate row of the datatable 
            dt.Rows[rowIndex]["title"] = childForm.Title;
            dt.Rows[rowIndex]["content"] = childForm.Content;
            
            //  Making sure no NULL location values end up in the database
            if (childForm.DSLocation == null)
            {
                // If NULL, nothing happens...
            }
            else
            {
                dt.Rows[rowIndex]["location"] = childForm.DSLocation;
            }

            // update the datatable
            adp.Update(ds);
        }

        public void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            // close the connection if necessary
            if (cn.State == ConnectionState.Open) cn.Close();
        }
    }

DisplayForm:

public partial class DisplayForm : Form
    {
        // properties to hold the three fields
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public string DSLocation { get; set; }

        public DisplayForm()
        {
            InitializeComponent();
        }

        public void DisplayForm_Load(object sender, EventArgs e)
        {
            // set initial values 
            idTextBox.Text = Id.ToString();
            titleTextBox.Text = Title;
            contentTextBox.Text = Content;
            DSLocation = "1, 1";  //  Needed to set a value for DSLocation - the 1,1 doesn't seem to cause any problems.  Should probably make it a null.

        }

        public void Notebox_LocationChanged(object sender, EventArgs e)
        {
            //  Need to remove LocationChanged - it was a failed attempt that ResizeEnd fixed.
        }

        //  Updates DB when position changes
        public void Notebox_ResizeEnd(object sender, EventArgs e)  //  ResizeEnd fires at the end of a form move, this was the only way I discovered to make this update work properly.
        {
            DSLocation = String.Format("{0},{1}", this.Location.X, this.Location.Y);
            ((Form1)this.MdiParent).NotifyChanges(this);  //  notify parent form that there are changes on this child
        }

        //  Updates DB when title changes
        public void titleTextBox_TextChanged(object sender, EventArgs e)
        {
            // reset properties to new values         
            Title = titleTextBox.Text;
            ((Form1)this.MdiParent).NotifyChanges(this); // notify parent form that there are changes on this child
         }

        //  Updates DB when content changes
        public void contentTextBox_TextChanged(object sender, EventArgs e)
        {
            // reset properties to new values         
            Content = contentTextBox.Text;
            ((Form1)this.MdiParent).NotifyChanges(this); // notify parent form that there are changes on this child
        }
        
        //  THIS IS WHERE THE BUTTON IS
        private void buttonNew_Click(object sender, EventArgs e)
        {
            DisplayForm childForm = new DisplayForm(); // Declare the childform as a new one.
            childForm.MdiParent = this.MdiParent; // Set the mainform as a parent form.
            childForm.Show();// Show the childform.
        }
    }

As always, thanks for reading this and lending a hand. I'm definitely learning, and it's thanks to all of you!
-Charlie

It might be better to pass the row to the display form instead of the data values.
That way any changes on the child form can be written directly to the relevant row.

In the main form write a method to add a new row and spawn a new form.

Add an event to your display form that is captured by the main form and triggered when a new child is required.

At first glance, it looks like since you are opening and closing the connection in the parent form and its a ".net dataset" it may not be in open state in the child form.

I think you should try passing the connection to the child form and update the DB in the child form unless you have any specific reason for doing the way you are doing:)

But in the sample code, you are notifying the parent form when there is a change in the child form's data.Are you able to view the "unsaved" changes in the grid in the parent form? if not, you might have to check why thats nt working first.

pls let know if this helps or if you were able to solve it yourself..

The connection string you are using is not correct. Take a look at here for your type of connection, but it should look something like that:

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SampleFolder\MyProject\MyDataBase2.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.