asked on

SQLCE table not updating

Below is my code. It is a simple update of one record in a table. It does not process. I can use a datareader to pull data from the database with no issues.

Dim cn As Data.SqlServerCe.SqlCeConnection
            Dim cm As Data.SqlServerCe.SqlCeCommand

            cn = New Data.SqlServerCe.SqlCeConnection
            cn.ConnectionString = "data source=" & System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) & "\mobile.sdf;Persist Security Info=False;"

            cn.Open()
            cm = New Data.SqlServerCe.SqlCeCommand
            cm.Connection = cn

            cm.CommandType = Data.CommandType.Text

            cm.CommandText = "UPDATE MSYSTEM SET servadd = '" & txtseradd.Text & "', " & _
            "db = '" & txtdatabase.Text & "';"

            cm.ExecuteNonQuery()

            cn.Close()
            cn = Nothing

Open in new window

Nasir Razzaq

Do you have a where clause in actual query? If you copy the query and execute it directly, does it work? Are you sure you are looking at the right file?

HainKurt

line 1: response.write(cm.CommandText)

and post the query...

Barry Cunney

Check the length of the text string coming from txtseradd.Text if it is is longer than the DB field length of servadd trim it and left string to make same size

Barry Cunney

Maybe even cast the value coming from txtseradd.Text to a simialr type to the underlying DB field just to be sure

jdonaghe

ASKER

I have run the same statement in the SQL Server studio and had no issues. I will trim all the fields when updating and see if that works. If that does not what would cause it not to update? Is there permissions on the SQLCE databases that I am missing? I am testing this on the Windows 5.0 R2 emulator.

Barry Cunney

As a test you could also try an INSERT statement

Also double-check the connection string - If possible as a test fully harcode the literal path to the .sdf file and make sure that you are definitely looking at the same sdf file when checking for the updated data

Nasir Razzaq

How are you verifying whether the update has run or not? Are you doing a select afterwards? If not, try doing a select after update and see what you get back.

jdonaghe

ASKER

I tried running the insert statement and it ran with no problem if i tried to pull the data back out in the same subroutine/connection. But if I ran another subroutine with the same code to pull the data out it wasn't there. Is there a bug with the emulator? Does it matter that I am putting the program on a "Share Folder" (acts like an SD card)? It is too large to fit on the device because of the .NET framework.

Nasir Razzaq

>It is too large to fit on the device

You said you are running it on emulator. You created shared folder for virtual SD card?

>But if I ran another subroutine with the same code to pull the data out it wasn't there

In the same session? The data file gets deleted and reloaded between each run.

jdonaghe

ASKER

Yes I did use the share folder in the emulator setup.

It is done in the same session.

ASKER CERTIFIED SOLUTION

jdonaghe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdonaghe

ASKER

It was a bug in the emulator.