Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 884
  • Last Modified:

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

0
jdonaghe
Asked:
jdonaghe
  • 5
  • 3
  • 3
  • +1
1 Solution
 
CodeCruiserCommented:
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?
0
 
HainKurtSr. System AnalystCommented:
line 1: response.write(cm.CommandText)

and post the query...
0
 
Barry CunneyCommented:
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
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Barry CunneyCommented:
Maybe even cast the value coming from txtseradd.Text to a simialr type to the underlying DB field just to be sure
0
 
jdonagheAuthor Commented:
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.
0
 
Barry CunneyCommented:
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
0
 
CodeCruiserCommented:
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.
0
 
jdonagheAuthor Commented:
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.
0
 
CodeCruiserCommented:
>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.
0
 
jdonagheAuthor Commented:
Yes I did use the share folder in the emulator setup.

It is done in the same session.
0
 
jdonagheAuthor Commented:
After searching the internet I ran a couple articles about sql server 2005 having bugs when using sqlce on the Share folder in the emulator.  I copied my program and database to the device and ran it from there and it functioned correctly.  Thanks for all the help I learned more debugging techniques trying to figure out this problem.
0
 
jdonagheAuthor Commented:
It was a bug in the emulator.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now