Pocket PC with SQL CE

• Download source - 15.2 Kb

Introduction

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.

Client Requirement

1. Pocket PC with Microsoft Windows powered
2. Microsoft .Net compact Framework 1.0 (Default in PocketPC)

Server Requirement

1. MS SQL server 2000 with SP3
2. MS SQL CE 2000 with SP3
3. Create DataBase named D1Temp and execute script "D1temp SQLScript.sql"

Background

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

• DatabaseServer
• DatabaseName
• DatabaseLogin
• DatabasePassword
• SQLCEURL
• IISLogin
• IISPassword

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

'Connect to SQL 2000

Dim rdaOleDbConnectString As String = _
  "Provider=sqloledb; Data Source=" & _
  oDBConfig.DatabaseServer & ";Initial" & _
  " Catalog=" & oDBConfig.PublisherDB & ";User Id=" & _
  oDBConfig.PublisherLogin & ";Password=" & _
  oDBConfig.PublisherPassword

  'create an RDA object to connect to the SQL Server CE 

  'database on the mobile device: 

  'Initialize the RDA object.

  Try
    rda = New SqlCeRemoteDataAccess

    rda.InternetUrl = oDBConfig.SqlCeUrl
    rda.LocalConnectionString = _
    "Provider=Microsoft.SQLSERVER." & _
    "OLEDB.CE.2.0;Data Source=" & oDBConfig.LocalDBLocation & _
    oDBConfig.LocalDBName

    rda.InternetLogin = oDBConfig.IISLogin
    rda.InternetPassword = oDBConfig.IISPassword

    'Pull from SQL 2000

    rda.Pull("MyFavourite", "Select * from MyFavourite", _
    rdaOleDbConnectString, _
    RdaTrackOption.TrackingOnWithIndexes)
    .....        

Getting back the details from PDA to SQL 2000 also same as above code except rda.Pull. Here we have to use rda.push :-

   rda.Push("MyFavourite", rdaOleDbConnectString, _
   RdaBatchOption.BatchingOn)        

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" ;-)

History

• First release 1.0

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 25 of 74 (Total in Forum: 74) (Refresh) FirstPrevNext

My vote of 2 Rob Graham 6:01 5 May '09   Disappointing article. There is almost no discussion of the code, implementation issues, or lessons learned. Sign In·View Thread·PermaLink db connection error ashuangrish 22:44 27 Nov '08   Requet to send data to the computer running iis has failed. Error:28037 Microsoft sql server ce I have VS2008 and sql server ce 3.5 and want to connect to remote Sql server2005 Below is my xml file: PALINFOC-55F8BC\SQLEXPRESS D1Temp http://192.168.11.217/sqlcesa35.dll Provider=Microsoft.SQLServer.OLEDB.CE.3.5; Data Source=\Program Files\MyFavourite\my.sdf; \Program Files\MyFavourite\ my.sdf IUSR_PALINFOC-55F8BC pal here: 1)PALINFOC-55F8BC\SQLEXPRESS is my pc name 2)D1Temp is the remote db in sql server 2005 3)\Program Files\MyFavourite\my.sdf = path to local sqlserver ce db Plz help me as i am spending sleepless nights to resolve this? Sign In·View Thread·PermaLink Re: db connection error rasheed1979 19:38 30 Nov '08   Hi, I havent tested this application with sql server 2005. Let me give a try. Thanks, Rasheed. If u know do it... else learn it! Sign In·View Thread·PermaLink Re: db connection error ashuangrish 1:53 1 Dec '08   Hello Rasheed I hav tried with sql server 2000 also and getting the same error 28037. A request to send data to the computer running IIS has failed. For more information, see hr result Plz help???????????// Sign In·View Thread·PermaLink 2.00/5 (1 vote) Re: db connection error ashuangrish 1:28 5 Dec '08   I hav done that.Anyways thanx for trying. Sign In·View Thread·PermaLink Re: db connection error omid2002 9:05 23 Feb '09   Hi, I have the same problem on SQL2005. I'm using VS.NET 2005 and the code "If Not File.Exists(oDBConfig.LocalDBLocation & oDBConfig.LocalDBName) Then ...." in frmMain.vb return "Database Dose not exist..." message. If I use the "Data Source=\My Documents\MyFav.sdf;" for LocalDBConnect parameter into Config.Xml, Where the DB file muse be located? My project directory is: "E:\Omid\Download\PocketPCwithSQLCE_Src" By the way, I cann't understand why the "Path.GetDirectoryName([Assembly].GetExecutingAssembly.GetName.CodeBase)" return "\Program Files\MyFavourite"?! How can I explore this path? Thanks Omid Sign In·View Thread·PermaLink help Rasheed :( three.leaf 13:46 12 Sep '08   Hi, i was working fine with my project until i reinstall windows xp. Here is the deal, i'm getting the native error 28017. According to this http://msdn.microsoft.com/en-us/library/aa224612(SQL.80).aspx#sqlce_troubleconnect_topic04[^], it's a error on the internet url but i have no idea how to fix this. When i try to open in the device http://server ip/share/sqlcesa30.dll, i get the message of i don't have the authorization to view this page. Or i get the message box of asking for user & password. Any idea of how to fix this? I set in the share folder configuring to the clients will log on anonymously. Sign In·View Thread·PermaLink phone incoming call information prats1 1:28 15 Jun '07   can u tell me how to capture phone incoming call information and transfer it onto my pocket pc database. is it possible to do it? thanks Sign In·View Thread·PermaLink 1.00/5 (1 vote) E r r o r Pavan_pareta 1:13 2 Jun '07   Hello , I have got this Error: Header information is either corrupted or missing. [,,,Header name,,]. Pavan Pareta Sign In·View Thread·PermaLink missing method exception mokhabarat 13:21 3 May '07   hi i downloaded your code but i'm having tons of trouble running it...this is my first time running sqlce and trying to access a database over a pda so i have zero experience in the matter. anyways, i cant seems to get the parameters right in the config file, the program compiles and runs gr8 but when i try to update the database or submit a new query to the database i get a missing method exception for the internet.url, i've looked at the msdn website to try to figure out the parameters but nothing worked.. i'm using sql server 2005 with windows mobile 2005 and running the app with the ppc on the cradle...plz let me know if there's anything wrong with wat i'm doing...any input will help thx Ghassan. Sign In·View Thread·PermaLink sql express rajeshatsrin 6:01 20 Apr '07   Hi Can you even use SQL Express 2005 as a host for running RDA applications i thought only sql server 2005 supported this. can anyone pls confirm. rajesh Rajesh Sign In·View Thread·PermaLink Error when synchronize data from Pocket PC to Pc and opposite tommyitvn 20:21 5 Dec '06   Dear Rasheed, I have a problem with synchonize data from Pocket PC to PC and opposite. It raise error Message="A request to send data to the computer running IIS has failed. For more information, see HRESULT." HResult=-2147012867 NativeError=28037 Now I use SQL Server Mobile 2005 and SQL Server Express 2005 I test SQL Server Mobile Agent, it runs well in PC: http://192.168.2.12/SQLServerMobile/sqlcesa30.dll I use Emulator in VS2005(not actual Pocket PC). I start IE in my Emulator with http://192.168.2.12/SQLServerMobile/sqlcesa30.dll but It can not run. It message "Cannot connect for an unknow reason". To check your connection setting and change them if needed, tap Settings ..." I can not execute Pull, also Push method! This is source code: ------------------------------------------------------------- SqlCeConnection cn = null; string rdaOleDbConnectString = "Provider=sqloledb; Data Source=VANTINH\\SQLEXPRESS;" + "Initial Catalog=" + "QLSV; " + "User Id=" + ";Password="; SqlCeRemoteDataAccess rda = null; try { rda = new SqlCeRemoteDataAccess(); rda.InternetLogin = "username"; rda.InternetPassword = "password"; rda.InternetUrl = "http://192.168.2.12/SQLServerMobile/sqlcesa30.dll"; rda.LocalConnectionString = "Data Source=\\Program Files\\" + "Test.sdf"; if (this.chkDropTable.Checked) { cn = new SqlCeConnection(rda.LocalConnectionString); cn.Open(); SqlCeCommand cmd = cn.CreateCommand(); cmd.CommandText = "DROP TABLE " + "HocSinh"; cmd.ExecuteNonQuery(); if (cn.State != ConnectionState.Closed) cn.Close(); } if (this.cmbTrackOption.Text == "TrackingOff") rda.Pull("HocSinh", "select * from HocSinh", rdaOleDbConnectString, RdaTrackOption.TrackingOff, "ErrorTable"); if (this.cmbTrackOption.Text == "TrackingOn") rda.Pull("HocSinh", "select * from HocSinh", rdaOleDbConnectString, RdaTrackOption.TrackingOn, "ErrorTable"); if (this.cmbTrackOption.Text == "TrackingOffWithIndexes") rda.Pull("HocSinh", "select * from HocSinh", rdaOleDbConnectString, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable"); if (this.cmbTrackOption.Text == "TrackingOnWithIndexes") rda.Pull("HocSinh", "select * from HocSinh", rdaOleDbConnectString, RdaTrackOption.TrackingOnWithIndexes, "ErrorTable"); } catch (SqlCeException exc) { MessageBox.Show(exc.Message); return; } finally { //MessageBox.Show("Table is Pulled Successfully"); rda.Dispose(); this.Close(); } ----------------------------------------------------------------------------------- Please help me to solve this problem! ------------------------------------------------------------- Could you help me to solve this problem? Thanks a lot! Tommyitvn Sign In·View Thread·PermaLink 3.50/5 (2 votes) Re: Error when synchronize data from Pocket PC to Pc and opposite rasheed1979 2:20 22 Dec '06   Tommyitvn, I havent tryed in VS2005 with SQL 2005. I am sorry that i am unable to provide answer for this. Rasheed. Try try until achive your GOAL Sign In·View Thread·PermaLink Missing Method Exception victorarce 7:56 7 Jul '06   Hi, I get the MissingMethod Exception when trying to create a SqlCeRemoteDataAccess object when I run the application on my handheld. It works fine in the emulator. Im running Windows CE with CF 1.0 and VS.Net2003 with SQL2000 server. any help would be great the only thing for sure is that nothing's for sure Sign In·View Thread·PermaLink Re: Missing Method Exception storystar 9:57 31 Oct '06   Make sure your have added all reference required. storynana Sign In·View Thread·PermaLink rasheed..plz help sudhacnaidu 19:47 8 Jun '06   Hi rasheed help me solve the problem posted a few min back...plz..plz..plz Sign In·View Thread·PermaLink Re: rasheed..plz help sudhacnaidu 1:21 9 Jun '06   why isnt there anyone who can help me solve the problem I have the detailed description of the error here: Source: Microsoft SQL Server 2000 Windows CE Edition Native Error: 28507 HR: 80004005 Description: some distributor properties are not specified Param = 0 Param = 0 Param = 0 Param = Param = Param = With reference to books online, the error is SSCE_M_MISSINGDISTRIBUTORPROPERTIES I tried giving sa as the user name and password for distributor with no avail. ~Sudha Sign In·View Thread·PermaLink Re: rasheed..plz help rasheed1979 17:55 11 Jun '06   Sudha, Let me check the problem... Rasheed. Try try until achive your GOAL Sign In·View Thread·PermaLink error with initialize SQL sudhacnaidu 19:41 8 Jun '06   Initializing replication, gives error 80004005... *I have configured the server to have SQL Authentication on my SQL Server. also tried using domain account(with permissions to acess database) with no avail *Executing http://10.0.0.10/sql/sscesa20.dl displays "SQL Server CE Server Agent" on my hand held device *Virtual dir is configured to use annonymous access. where am i wrong in my code? are there any additional steps to be followed to My instinct says there is something else i got to do with sql server configuration....but i am not able to figure that out void InitializeReplicationRDA() { HRESULT hr; hr = CoInitializeEx(NULL, COINIT_MULTITHREADED); hr = CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER, IID_ISSCEMerge, (LPVOID *)&CEMerge); if(FAILED(hr)) { MessageBox (NULL, TEXT("THE DATABASE OBJECT COULD NOT BE CREATED"), TEXT("NO DATABASE CONNECTIVITY"), MB_OK); return; } else MessageBox (NULL, TEXT("INSTANCE SUCCESS"), TEXT("SUCCESS!!!"), MB_OK); CEMerge->put_InternetURL(L"http://10.0.0.10/sql/sscesa20.dll"); CEMerge->put_InternetLogin(L""); CEMerge->put_InternetPassword(L""); CEMerge->put_Publisher(L"SQLSERVER"); CEMerge->put_PublisherDatabase(L"southwind"); CEMerge->put_Publication(L"southwind"); CEMerge->put_PublisherSecurityMode( (REPL_SECURITY_TYPE)DB_AUTHENTICATION); // CEMerge->put_PublisherSecurityMode (NT_AUTHENTICATION); CEMerge->put_PublisherLogin(L"sa"); CEMerge->put_PublisherPassword(L""); CEMerge->put_PublisherNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK); CEMerge->put_Subscriber(L"southwind"); CEMerge->put_SubscriberConnectionString (L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=southwind.sdf"); CEMerge->put_DistributorNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK); CEMerge->put_DistributorSecurityMode ((REPL_SECURITY_TYPE)DB_AUTHENTICATION); CEMerge->put_DistributorLogin(L""); CEMerge->put_DistributorPassword(L""); CEMerge->put_ExchangeType((REPL_EXCHANGE_TYPE)BIDIRECTIONAL); CEMerge->put_Validate(NO_VALIDATION); //ADD SUBSCRIPTION GetLocalTime(&systemtime); SystemTimeToFileTime(&systemtime, (FILETIME*)&timeStart); // hr = CEMerge->AddSubscription(CREATE_DATABASE); hr = CEMerge->AddSubscription(EXISTING_DATABASE); if (SUCCEEDED(hr)) { GetLocalTime(&systemtime); SystemTimeToFileTime(&systemtime, (FILETIME*)&timeStop); timeTotal = ((double)(timeStop - timeStart) / 10000000); swprintf(wszBuffer, L"Subscription added\r\n\r\nElapsed time in seconds = %8.3f", timeTotal); MessageBox(NULL, wszBuffer, L" A D D S U B S C R I P T I O N ", MB_OK); } else { printf("%x",hr); MessageBox (NULL, TEXT("S U B S C R I P T I O N F A I L E D"), TEXT("error"), MB_OK); } //SYNCHRONIZE hr = CEMerge->Initialize(); if (SUCCEEDED(hr)) { MessageBox (NULL, TEXT("I N I T I A L I Z E SUCCESS"), TEXT("SUCCESS!!!"), MB_OK); fInitialized = TRUE; hr = CEMerge->Run(); } else { printf("%x",hr); MessageBox (NULL, TEXT(" I N I T I A L I Z E F A I L E D"), TEXT("error"), MB_OK); } if (SUCCEEDED(hr)) { MessageBox (NULL, TEXT("Run SUCCESS"), TEXT("SUCCESS!!!"), MB_OK); LONG lPubChanges; LONG lPubConflicts; LONG lSubChanges; GetLocalTime(&systemtime); SystemTimeToFileTime(&systemtime, (FILETIME*)&timeStop); timeTotal = ((double)(timeStop - timeStart) / 10000000); CEMerge->get_PublisherChanges(&lPubChanges); CEMerge->get_PublisherConflicts(&lPubConflicts); CEMerge->get_SubscriberChanges(&lSubChanges); swprintf(wszBuffer, L"Synchronization Complete\r\n\r\nElapsed time in seconds = %8.3f\r\nPublisher Changes = %d\r\nPublisher Conflicts = %d\r\nSubscriber Changes = %d", timeTotal, lPubChanges, lPubConflicts, lSubChanges); MessageBox(g_hwndMain, wszBuffer, L" S Y N C H R O N I Z E ", MB_OK); } else { printf("%x",hr); MessageBox (NULL, TEXT(" S Y N C H R O N I Z E F A I L E D"), TEXT("error"), MB_OK); } if (fInitialized) (void)CEMerge->Terminate(); // Create and initialize the RDA object hr = CoCreateInstance(CLSID_RemoteDataAccess, NULL, CLSCTX_INPROC_SERVER, IID_ISSCERDA, (LPVOID *)&CERDA); CERDA->put_InternetURL(L"http://10.0.0.10/sql/sscesa20.dll"); CERDA->put_InternetLogin(L""); CERDA->put_InternetPassword(L""); CERDA->put_LocalConnectionString(L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=southwind.sdf"); wcscpy(wszConnectString, L"provider=sqloledb; data source=SQLSERVER; Initial Catalog=southwind; user id=sa; password=sa"); wcscpy(wszLocalTable, L"southwind"); wcscpy(wszErrorTable, L"southwinderr"); wcscpy(wszQuery, L"select * from T1"); //PULL // Invoke the RemoteDataAccess Object bstrConnectStr = SysAllocString(wszConnectString); bstrLocalTable = SysAllocString(wszLocalTable); bstrErrorTable = SysAllocString(wszErrorTable); bstrQuery = SysAllocString(wszQuery); hr = CERDA->Pull((WCHAR*)bstrLocalTable, (WCHAR*)bstrQuery, (WCHAR*)bstrConnectStr, TRACKINGON, (WCHAR*)bstrErrorTable); SysFreeString(bstrConnectStr); SysFreeString(bstrLocalTable); SysFreeString(bstrErrorTable); SysFreeString(bstrQuery); // Handle Errors if (SUCCEEDED(hr)) { GetLocalTime(&systemtime); SystemTimeToFileTime(&systemtime, (FILETIME*)&timeStop); timeTotal = ((double)(timeStop - timeStart) / 10000000); swprintf(wszBuffer, L"Pull succeeded\r\n\r\nElapsed time in seconds = %8.3f", timeTotal); MessageBox(g_hwndMain, wszBuffer,L" R D A P U L L", MB_OK); } // else // ShowRDAErrors(L"Pull failed"); TerminateReplicationRDA(); return; } Any help is greatly appreciated... Sudha Sign In·View Thread·PermaLink Book Baskras121 3:58 4 May '06   Does somebody nows a good book for pda applications in .net? Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: Book acc1234567890 3:51 7 Mar '07   Microsoft .NET Compact Framework / Andy Wigley ISBN 0-7356-1725-2 Sign In·View Thread·PermaLink Not able to Sync Arathi Vasupal 0:45 27 Mar '06   Started with RDA and facing a lot of problems.I installed SQL SERVER CE 2000 tools and created a virtual folder containing sscesa20.dll.When i type http:////sscesa20.dll, it shows 'SQL SERVER CE Server Agent'.When i put the pocket pc in the cradle(which is connected to my desktop)and type the same url in PIE, it also shows 'SQL SERVER CE Server Agent'. Now I created a database by name 'D1temp' in the SQL SERVER and created the table 'myfavourite' using the script file provided.It got created but the table is empty. On the Pocket PC,i created a database by name 'myfav.sdf' in '\My Documents' folder and added the fields given in the script file and added some records also. In the Config.xml file,I have used the following details: DatabaseServer: The Server name under which the D1Temp database gets listed. DatabaseName: D1Temp DatabaseLogin:sa DatabasePassword: SqlCEURL:http:////sscesa20.dll IISLogin: IISPassword: When i run the program,The records get displayed and when i say 'Sync from Host', It comes till rda.InternetLogin = odbConfig.IISLOgin and jumps to error and nothing is displayed in the error. Am i in the right track towards RDA? Or else guide me the right steps. Please help me to find the error and rectify it. Sign In·View Thread·PermaLink 2.00/5 (2 votes) Is there away to send to Desktop Ms Access silkkeng 17:19 7 Mar '06   Hi, I just wondering if it is possible to send to Desktop Ms Access? Sign In·View Thread·PermaLink Re: Is there away to send to Desktop Ms Access rasheed1979 16:05 13 Mar '06   I guess you can. Try try until achive your GOAL Sign In·View Thread·PermaLink Problem installing SQL server CE ronithefrnd 18:45 1 Jan '06   Hie all, I'm using SQL server 2000 with sp2 and IIS 5 running on the same machine.But whenever I try to install SQL server ce 2.0 server components I get an error msg telleing me tht the SQL SE version is only for higher than version sp1.I'm a bit confused!! Can anybody tell me what wrong I'm doing ?? I need another help,Can anybody tell me if this type of Data sync will work on Bluetooth and if then what else shud I do to get access to Desktop SQL server 2000 ?? Thanks in advance,Specially thnks to Rasheed for this cool article BR Arif Sun2Moon Sign In·View Thread·PermaLink 2.00/5 (1 vote)

Last Visit: 4:10 9 Nov '09     Last Update: 4:10 9 Nov '09 123 Next »