SQL Mobile 2005 – Editing Table Data

In SQL Server CE 2.0 it was always painful to create the mobile database, and populate the data. However, with SQL Mobile 2005 and VS.NET 2005 Microsoft has come a long way to making the SQL Mobile database much easier to manage. While creating a new database is pretty well document, editing the table records is more obscure.

While developing the database is handled easily in the Microsoft SQL Server Management Studio, editing the database records requires VS.NET 2005. The “trick” to editing the database table is to use the Server Explorer (View->Server Exploer) in VS.NET to open the table for editing.

The first set is to add a Data Connection to add your mobile database to the project. This can be done by selecting the Data Connections option in the Server Explorer and choosing on the “Add Connection…” option in the pop-up menu. This will launch the “Add Connection dialog which you can use to specify the location and password of the database. For this example I am using the NorthWinds.sdf mobile database.

Add Connection - Northwinds

Once the Data Connection is successfully added you can now use the Server Explorer window to examine the various properties of the database. In order to open a table to edit, you must first expand the Table tree to see a list of all of the tables that are in the current database.

Server Explorer - Northwinds Database

Finally by selecting the table and choosing “Open” for the pop-up menu (right mouse) you can open the table and start editing. In this case I chose the customer database table.

Edit the Customer Table

SQL Mobile 2005 – Error: Not Enough Storage is Available

Here’s another in a long list of cryptic error messages. In this case I am developing a local database component for our SNAPRetail applications and on opening the database for the first time I get a SQLCEException with the message “Not enough storage is available to complete this operation”. Initially, I thought it was a problem with my database, but it is after a little searching I found a post (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=895861&SiteID=17) on the Microsoft Technet forums that describes the problem as a “DLL Squeeze” and to open the connection to the database at the start of the appliation.

A little more research reveals that the DLLS that are being loaded are sqlceqp30.dll, sqlcese30.dll, sqlceer30en.dll, sqlceme30.dll which are the minium required for SQL Mobile to run.

Intermec Scan Configuration

I received a CN3 today to try out with our snapRetail applications and at first it seemed like things weren’t working correctly with scanning, but it soon became apparent that it was just a matter of scanner configuration.

Virtual Wedge

Virtual Wedge ConfigurationThe first problem I had was ironically the problem of scanning ocurring when I it should be disabled. By default Intermec has enabled a keyboard wedge, “Virtual Wedge”, that accepts barcode data from the scanner and generates key events to simulate that the data has come from the keybooard. This is easy enough to disable, just go in to Intermec Settings (Settings->System->Intermec Settings) and disabled the Virtual Wedge and after copying the Intermec Data Collection dll over to my program the terminal scanned only when I wanted.

While I guess it’s ok to enable this by default, though it has always been a pet peeve of mine to call applications barcode enabled, when all they are doing is using a keyboard wedge. While this “works”, it creates an incredibly clumsy interface for barcode scanning that keeps the scanner always enabled, and puts barcode data anywhere the keyboard focus is set. This may be “ok” for consumer devices, so I can put barcode data in my Word document, but for “real” applications it is really a bad idea.

What bothers me even more is when people claim that they have a barcode-enabled handheld program, when all they’ve done is turn the keyboard wedge on. Perhaps the worst case of this “mis-advertising” I’ve seen was during the keynote of the 2006 Microsoft Embedded Developers Convention where they demonstrated creating a “barcode enabled application” to a roomful of developers when all they had done was use a keyboard wedge. Perhaps if they would have attended my session they would have been able to do it the real way ;-).

Automatic Symbology Conversion

Once I got back to being able to control the scanner, and configure the Symbologies, I went in to the Item Lookup application to try scanning some barcodes. Everything seemed to be scanning fine, except that whenever I scanned a UPC code I was receiving thirteen digits (with a leading zero) instead of twelve as I was expecting from a UPC barcode.

UPC A xmit as EAN 13This gets in to the whole world of automatic barcode conversions. In order to support the requirements of different industries and governments there are a broad variety of similar, but slightly different symbologies. This means that for a US-based retail , the developer must take in to consideration EAN8, EAN13, UPCA, UPCE0, and UPCE1 barcode symbologies. In addition, there are also UPC Supplemental barcodes (extra information often used on books for ISBN numbers), check digits, and expansion of EAN8 barcodes to EAN13.

In this case, the default configuration that Intermec has selected is to convert everything to an EAN13 symbology which in my case caused the problem. This is also configurable through the Intermec Settings application, and disabling the conversion “UPC A xmit as EAN 13” solved my problem. Alternately, this can be configured programmatically through the Intermec Data Collection API but I like keeping these types of paramaters configurable by the end user.

However, the lesson is that even if the hardware vendors decide to make it easy for the application developer to use barcodes, its still important for them to understand what “magic” is happening under the covers.