SQL Server CE query having no effect?

Question

I'm attempting to insert rows into a SQL Server CE database, and it's returning that 1 row is affected, there's no exception, and no syntax error in the query as far as I can see - but it's having no effect, when I look in the table from the Database Explorer.

If I run a query through VS, everything works fine. There's no connection problem as far as I can tell... what am I doing wrong here?

Here's the code, though it probably doesn't make a difference:

    Using conn As New SqlCeConnection(My.Settings.DietSafetyCheckerReportsConnectionString)
        conn.Open()

        Using cmd As SqlCeCommand = conn.CreateCommand()
            cmd.CommandText = "INSERT INTO Reports(PatientID, PreparedBy, PreparedFor, WeightInKilos, HeightInMeters, Age, PercentBodyFat, ElbowMeasurementInCentimeters, ReportDate, Gender) " &
                              "VALUES(@pid, @pby, @pfor, @weight, @height, @age, @bodyfat, @elbow, @rdate, @gender);"

            cmd.Parameters.Add("@pid", SqlDbType.NVarChar, 100).Value = Me.PatientID
            cmd.Parameters.Add("@pby", SqlDbType.NVarChar, 100).Value = Me.PreparedBy
            cmd.Parameters.Add("@pfor", SqlDbType.NVarChar, 100).Value = Me.PreparedFor
            cmd.Parameters.Add("@weight", SqlDbType.Float).Value = Me.WeightInKilos
            cmd.Parameters.Add("@height", SqlDbType.Float).Value = Me.HeightInMeters
            cmd.Parameters.Add("@age", SqlDbType.TinyInt).Value = Me.Age
            cmd.Parameters.Add("@bodyfat", SqlDbType.Float, 100).Value = Me.PercentBodyFat
            cmd.Parameters.Add("@elbow", SqlDbType.TinyInt, 100).Value = Me.ElbowMeasurementInCentimeters
            cmd.Parameters.Add("@rdate", SqlDbType.DateTime).Value = Me.ReportDate
            cmd.Parameters.Add("@gender", SqlDbType.TinyInt, 100).Value = Me.Gender

            If cmd.ExecuteNonQuery() <> 1 Then Throw New ApplicationException("Failed to insert row into databse.")
        End Using

        conn.Close()
    End Using

(By the way, this also doesn't work:

    Using da As New SqlCeDataAdapter("SELECT * FROM Reports", conn)
            Dim ds As New DietSafetyCheckerReportsDataSet()
            Dim dt As DietSafetyCheckerReportsDataSet.ReportsDataTable
            da.Fill(ds)
            dt = DirectCast(ds.Tables("Reports"), DietSafetyCheckerReportsDataSet.ReportsDataTable)
            Dim dr As DietSafetyCheckerReportsDataSet.ReportsRow = dt.NewReportsRow()
            dr.Age = Me.Age
            dr.ElbowMeasurementInCentimeters = Me.ElbowMeasurementInCentimeters
            dr.Gender = Me.Gender
            dr.HeightInMeters = Me.HeightInMeters
            dr.PatientID = Me.PatientID
            dr.PercentBodyFat = Me.PercentBodyFat
            dr.PreparedBy = Me.PreparedBy
            dr.PreparedFor = Me.PreparedFor
            dr.ReportDate = Me.ReportDate
            dr.WeightInKilos = Me.WeightInKilos
            dt.Rows.Add(dr)
            da.Update(ds)
        End Using
)

A comment about the reason of the downvote would be much appreciated. — Ry-, May 05 '12 at 20:05

score 2 · Accepted Answer · edited Oct 20 '11 at 19:37

2

Look in your bin/debug folder, you probably have more copies of the same database file

edited Oct 20 '11 at 19:37

Ry-

218,210
55
464
476

answered Aug 28 '11 at 19:33

ErikEJ

40,951
5
75
115

I tried changing the copy options but that still didn't work, so I created a new database connection and pressed "Cancel" before the wizard finished, then added everything manually. Thanks! – Ry- Aug 28 '11 at 19:41

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

My suggestion is that you turned off autocommit mode on server (which is on by default) or on connection settings, so you need to commit your transaction manually. Take a look here for more info.

edited May 23 '17 at 11:43

Community

1
1

answered Aug 28 '11 at 19:24

a1ex07

36,826
12
90
103

Thanks, is there a way I can set it without using SQL code every time? A database-wide setting? – Ry- Aug 28 '11 at 19:27
I also get an `SqlCeException` with "Invalid set option. [ Set option = IMPLICIT_TRANSACTIONS ]" trying to fix it like that. – Ry- Aug 28 '11 at 19:29
That was just a guess. Anyway, it seems that you found the problem already. – a1ex07 Aug 28 '11 at 19:53

SQL Server CE query having no effect?

2 Answers2

Linked