3 Replies - 768 Views - Last Post: 18 December 2012 - 10:28 AM Rate Topic: -----

#1 flexin1981  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 11-September 12

Sql DB can't see if data is actually being written

Posted 18 December 2012 - 07:19 AM

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




Is This A Good Question/Topic? 0
  • +

Replies To: Sql DB can't see if data is actually being written

#2 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 2,999
  • Joined: 12-January 10

Re: Sql DB can't see if data is actually being written

Posted 18 December 2012 - 07:57 AM

did you try using order by desc
Was This Post Helpful? 0
  • +
  • -

#3 flexin1981  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 11-September 12

Re: Sql DB can't see if data is actually being written

Posted 18 December 2012 - 09:38 AM

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 ?.
Was This Post Helpful? 0
  • +
  • -

#4 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 2,999
  • Joined: 12-January 10

Re: Sql DB can't see if data is actually being written

Posted 18 December 2012 - 10:28 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1