[flexin1981]

Hi guys i have been trying to get my form to add data to a .sdf SQL Db and although the code runs ok i cant seem to verify that the code has done the job. I have tried using the second snippet to display what has been written but i come back with an earlier record. How can i modify this so that i can verify each record in the DB @ present.

try
            {
                // Retrieve the connection string from the settings file.
                string conString = Properties.Settings.Default.Database1ConnectionString;

                // Open the connection using the connection string.
                using (SqlCeConnection con = new SqlCeConnection(conString))
                {
                    con.Open();

                    using (SqlCeCommand com = new SqlCeCommand("INSERT INTO Employee_Records VALUES(@First_Name, @Surname, @Telephone, @Address1, @Address2, @County, @Postcode, @Start_Date, @Mobile, @Email)", con))
                    {
                        com.Parameters.AddWithValue("@First_Name", textBox1.Text);
                        com.Parameters.AddWithValue("@Surname", textBox3.Text);
                        com.Parameters.AddWithValue("@Telephone", textBox8.Text);
                        com.Parameters.AddWithValue("@Address1", textBox4.Text);
                        com.Parameters.AddWithValue("@Address2", textBox5.Text);
                        com.Parameters.AddWithValue("@County", textBox6.Text);
                        com.Parameters.AddWithValue("@Postcode", textBox7.Text);
                        com.Parameters.AddWithValue("@Start_Date", textBox11.Text);
                        com.Parameters.AddWithValue("@Mobile", textBox9.Text);
                        com.Parameters.AddWithValue("@Email", textBox10.Text);
                        com.ExecuteNonQuery();
                    }
                    
                }
                this.Close();
            }
            catch (Exception F)
            {
                MessageBox.Show (F.Message.ToString());
                this.Close();
            }

            string conString = Properties.Settings.Default.Database1ConnectionString;
            using (SqlCeConnection con = new SqlCeConnection(conString))
            {
                con.Open();
                // Read in all values in the table.
                using (SqlCeCommand com = new SqlCeCommand("SELECT First_Name FROM Employee_Records", con))
                {
	            SqlCeDataReader reader = com.ExecuteReader();
	            while (reader.Read())
	            {
                    String num = reader.GetString(0);
	                MessageBox.Show(num);
	            }
                }
            }

[DarenR]

did you try using order by desc

[flexin1981]

Hi Guys,

Thanks for the help, it seems i was forgetting to close the SQL connection which was not finishing the SQL transaction.

added the below after the com.ExecuteNonQuery(); command at the data is now in the DB.

[code]
con.Close();
[\code]

on a side note i find it interesting that you have to open and close the connection to the DB to finalize the transaction,this could be rather inefficient if you are creating a lot of transactions. Is this done so that the DB is only locked for a small amount of time ? or am i missing something ?.

[DarenR]

yes if you leave the con open all the time it will eat up the resources in the sql data base. Also what if someone else was trying to access the same tables fromt he back end? Hard to modify tables when they are being accessed at the same time by another program.