Thursday, February 9, 2012

(SQL Mobile)Can SELECT, but cannot INSERT/UPDATE. Why not?

I'm just getting started with using SQL Server Mobile while creating a handheld app using Visual Studio 2005. So far, I have succeeded in RETRIEVING data from the mobile database, but I don't understand why my INSERT nor UPDATE statements will not work. (I'm assuming they don't work, because after I run my code via the debugger, I oddly get no error message, but when I "open" the table in the Server Explorer, I do not see my expected results. Do I need to do some kind of "commit" or something? Here's my code. Thanks for any help! - Sue

-

'Create SQL statement:
Dim strSQL As String
strSQL = "INSERT INTO MyTable (col1, col2) VALUES ('yucky', 'poo')"

'Create DB connection:
Dim connDB As New System.Data.SqlServerCe.SqlCeConnection
connDB.ConnectionString = ("Data Source =" _
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\SueUWM.sdf;"))

'Create DB command:
Dim cmndDB As New System.Data.SqlServerCe.SqlCeCommand(strSQL, connDB)

'Open the connection:
connDB.Open()

'Execute the command:
Dim intRecordsAffected As Integer
Try
intRecordsAffected = cmndDB.ExecuteNonQuery()

If intRecordsAffected <> 1 Then
MessageBox.Show("Unable to insert into database.")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString, "Insert Error")
End Try

'close the DB connection:

connDB.Close()

-

I am so dumb! I just learned that the insert & update modifications are made on the .sdf file on the emulator, and therefore will *not* be visible to me in Visual Studio's Server Explorer (on the desktop)! - Sue

No comments:

Post a Comment