This article describes how to use SQL CE using VB. NET and how to transfer data from SQLCE (Pocket PC) to SQL Server 2000 which is running in your desktop system.
- Pocket PC with Microsoft Windows powered
- Microsoft .Net compact Framework 1.0 (Default in PocketPC)
- MS SQL server 2000 with SP3
- MS SQL CE 2000 with SP3
- Create DataBase named D1Temp and execute script "D1temp SQLScript.sql"
The basic idea behind this work is to update any information on the fly. i.e. If I am traveling from one location to another location if I seen
or knowing some interesting information. If I want those information to my data storage then just need a pocket PC and Internet connection.
On the flight I can note all the information and transfer the same to my desktop, which is running in some part of the world. I won't worry about the memory size of my Pocket PC because all the information is posted to the remote database.
You can make this work as a template and do your own mobile device application.
Using the code
Download the source code in that you can find a SQL script named SQLSCRIPT.sql. Create a Database (I used D1Temp) and run the script. Open Config.xml and specify correctly
If you are not properly configure this XML file then you will not get the expected result. Note: Make sure you are properly configured your SQLCE virtual folder and the IIS. The Database.vb files holds all the database related connections and query handling. The below code is used to connect to the Host database and populate the data to SQL CE
Dim rdaOleDbConnectString As String = _
"Provider=sqloledb; Data Source=" & _
oDBConfig.DatabaseServer & ";Initial" & _
" Catalog=" & oDBConfig.PublisherDB & ";User Id=" & _
oDBConfig.PublisherLogin & ";Password=" & _
rda = New SqlCeRemoteDataAccess
rda.InternetUrl = oDBConfig.SqlCeUrl
rda.LocalConnectionString = _
"Provider=Microsoft.SQLSERVER." & _
"OLEDB.CE.2.0;Data Source=" & oDBConfig.LocalDBLocation & _
rda.InternetLogin = oDBConfig.IISLogin
rda.InternetPassword = oDBConfig.IISPassword
rda.Pull("MyFavourite", "Select * from MyFavourite", _
Getting back the details from PDA to SQL 2000 also same as above code except
rda.Pull. Here we have to use
rda.Push("MyFavourite", rdaOleDbConnectString, _
And also I added some of functions for getting data into DataReader and executing SQL statements.
Points of Interest
When I tried using the emulator most of time it shows error "80072EFD request send data to the computer running IIS has failed for more information see HRESULT" even everything is configured properly. If I use my pocket PC instead of Emulator it works good. After that I used to run the SQL CE virtual folder path "http://sys1/sqlce/sscesa20.dll" often through my IE . Some times it works but most of time it does not. If this trick fails then I used this logic i.e. I will change IISLogin from "everest/rasheed" to "everest\rasheed" (Just changed the slash) if it fails next time then I will change IISLogin from "everest\rasheed" to "everest/rasheed" You won't believe it but this worked perfectly. I didn't know the secret of "slash" ;-)