Question SQL Server CE not adding records

[bpeirson]

I am trying to use SQL server CE for a project I am currently working on and I'm having trouble getting records into my database. I built the database using VS 2010 and created two tables. Right now I'm just trying to add records to a table called Patients.

I have a seperate connection routine that handles connecting to the database, and as far as I can tell the connection is fine. After the connection to the database is made I'm using a button to execute the following code:

    Friend Sub New_Patient_SQL(ByVal First As String, ByVal Last As String, ByVal Address1 As String, ByVal Address2 As String, ByVal City As String, ByVal State As String, ByVal ZIP As String, ByVal DOB As String)

        If SQL_Con.State = ConnectionState.Closed Then

            SQL_Con.Open()

        End If

        Dim cmd As SqlServerCe.SqlCeCommand

        Dim InsertString As String = "INSERT INTO Patients (FirstName, LastName, Address1, Address2, City, State, ZIP, DOB) VALUES (@FirstName, @LastName, @Address1, @Address2, @City, @State, @ZIP, @DOB)"

        'Dim sql As String = "INSERT INTO Patients (FirstName, LastName, Address1, City, State, ZIP, DOB) VALUES (@FirstName, @LastName, @Address1, @City, @State, @ZIP, @DOB)"

        Try

            cmd = New SqlServerCe.SqlCeCommand(InsertString, SQL_Con)

            cmd.Parameters.AddWithValue("@FirstName", First)
            cmd.Parameters.AddWithValue("@LastName", Last)
            cmd.Parameters.AddWithValue("@Address1", Address1)
            cmd.Parameters.AddWithValue("@Address2", Address2)
            cmd.Parameters.AddWithValue("@City", City)
            cmd.Parameters.AddWithValue("@State", State)
            cmd.Parameters.AddWithValue("@ZIP", ZIP)
            cmd.Parameters.AddWithValue("@DOB", DOB)

            cmd.ExecuteNonQuery()

            frm2RxWizard.lblDebug.Text = "Done"

        Catch sqlexception As SqlCeException

            MessageBox.Show(sqlexception.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch ex As Exception

            MessageBox.Show(ex.Message, "Oh Crap.", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            SQL_Con.Close()

        End Try

    End Sub

I'm not getting any VB or SQL errors when this code executes, and the debug label I put in displays "Done" as it should. However when I open the database to check that the record has been written there is nothing there.

I haven't published the application yet, and was wondering if it may not be working because I am running it in Debug. I changed the connection string to point to the copy of the database in my debug folder but there are still no records being added.

Any suggestions would be greatly appreciated.

 

[jmcilhinney]

First, check the value returned by ExecuteNonQuery. If it's not zero then the data is being saved. In that case, follow the first link in my signature to understand how local data files are managed.

 

[bpeirson]

Thank you, that link nailed it. I did have the database set to copy if newer. It occurs to me that for those just learning this stuff that MS wouldn't make an option they don't recommend the default option...

I was able to open the copy saved in bin and the records are there. Thanks again for the info.