i'm use this code ,in SQL2005 std and ACCESS database, it work
but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()
Exception : OleDbException
0x80040E30L
DB_E_BADTYPENAME
Code Snippet
OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");
od.Open();
OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@billno,@checkouttime,@finalprice,@handle,@ischeckout,@memo,@paymode,@trick)", od);
I may have stumbled upon the most obscure bug imaginable. I was noticing my parameterized queries were failing on our SBC (WinCE5 x86), but not on our WinCE5 x86 emulator, WinXP setup, or WinCE5 ARMIV emulator. After much grief, I discovered the source of the problem had to do with a call to ICommandWithParameters::GetParameterInfo().
On all platforms other than our SBC, the DBPARAMINFO::ulParamSize was (correctly) retrieved as 16B for type DBTYPE_DBTIMESTAMP. On the SBC this value was retrieved as 8B. Now I know that natively all platforms of SSCE store a datetime object in 8B, but a DBTIMESTAMP structure (16B) is what's always used to transfer those fields to and from the database. Needless to say, with the wrong ulParamSize being returned, my data buffer (which used DBTIMESTAMP's) was being overwritten.
The erroneous value can be seen in this image
Just to further the case that this is a bug, the column size of a datetime field is correctly retrieved as 16B on the same platform when GetColumnInfo is run, as shown here.
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
hi, this is my first post here, i am using Sql Server CE 2005 these days. in a project i found 2 problem that can't find any solution for them till now , maybe there are some bugs i founded them
here is : 1- when i have a SqlCeCommand Object and the ComandText for it is "Update [Term] SET Year=? WHERE _id=? AND name=? "
the problem is in here , i added parameters for this command, if value of parameter is in Unicode ( i tested it in Farsi[Persian] language value like 'زمستان' on 'name' parameter) , in ExcuteNonQuery() method , it throws FormatException with "Input String was not a correct format". although if i change the value of 'name' parameter to something non-unicode (for example i tested it with 'ABCDE' ) it works correctly,
i think SqlCeCommand in UPDATE query , have problem with unicode values in paramteres.
2- second one is so strange for me !! , i have a loop that update a column in a table. and then have another loop that update the next column of that table. for example first i update the column 3 in loop, then in another loop i update the column 4. after all, the value of column 4 and 3 is substituted, it means that values that i updated for column 3 is in column 4 , and values that i updated for column 4 is in column 3 !!!!!!! both columns is in 'int' type.
and the last point is i tested both of this situations in SQL Server 2000 Developer Edition and both worked correctly without any problem.
also i used SQL Server CE v3.1 with .Net framework 2.0 in Vs2005 and C#. is there any missing i'v done or is it some kind of bugs.
We are looking into developing our application using SQL Server Compact and have created a test database to become familiar with the API. Having read about ACID transactions in SSCE we would like to know how this is actually employed. What will happen if a power failure occurs during a transaction? Does this have to be recovered in the code?
I have read the following article: http://msdn2.microsoft.com/en-us/library/csz1c3h7.aspx, but is this the best way to deal with transaction failures?
I can't find any examples on how to recover from failures in transactions in SSCE.
Are there any pecularities of Transaction Logs in SSCE comparing to the same log in full scale SQL Server? How are they stored? And when they are considered to be expired in order to safely delete them?
VS 2008 supports targeting different versions of the .NET Framework. Will this apply to SSCE as well? I have some older apps that I want to keep in .NET 2 and SSCE 3.1. But I would also like to develop new stuff in 3.5. Will the targeting mechanism in VS 2008 support using SSCE 3.1 with .NET 2.0 and SSCE 3.5 with .NET 3.5?
I've just recently downloaded Visual Studio 2008 Express which comes with SQL Server 2005 Express and SQL Server Compact 3.5, both of which were installed when I installed the products.
I've been following the tutorial videos and trying some stuff of my own and I just can't seem to get a Compact 3.5 database to actually update! I'm doing EXACTLY what is shown in the tutorials and th dataset is being updated - changes and added records - and they will show up in a DataGrid control as expected, but when I accept the changes - nothing is changed and/or added to the actual .sdf file. When I open it again, it's the same as it was before. Using exactly the same code but with an SQL Server 2005 Express database (.mdf), updates are fine.
First I created a dataset that connects to the SSCE 3.5 database .SDF file. Then I simply drag the table from the dataset to the form which creates Dataset, Table Binding Source, Table Adapter Manager and BindingNavigator items, in addition to the toolbar and DataGrid controls. The code generated from this is below. I have added the Try/Catch block and messages to see if any errors are ocurring.
Code Snippet Public Class Form3
Private Sub TblMessageTypeBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblMessageTypeBindingNavigatorSaveItem.Click
When I run the app, the data in the database table is displayed correctly. If I make a change and add a record then click the Save button, I get the expected "Changes Saved" message indicating that there are no errors. When I look a the contents of the table, the changes and additions did not happen and the data is exactly the way it was before.
I had the full SQL Serer 7.0 installed as well so I thought there might be a conflict or something became corrupted. In uninstalled SQL Server 7.0, SQL Server 2005 Express and SQL Server Compact 3.5. I "repaired" VB 2008 Express which reinstalled SQL Server Compact 3.5 and separately reinstalled SQL Server 2005 Express. Same problem.
As I mentioned above, what gets me is that if I use the same setup for a SQL Server 2005 Express database, changes and inserts work just fine. (Used the Northwind.mdf (Express) and the Northwind.sdf (Compact) sample databases and created my own sample .mdf and .sdf databases - same results.)
I've looked at whatever I can to see if I can find the problem but just can't put my finger on it. Very frustrating.
Does anyone have any advice on what I might be doing wrong with Compact or if there is something I've missed or if there's a property somewhere that needs to be set, or perhaps a configuration issue??
I just installed VS2008 RTM and noticed that it really wants me to use 3.5 instead of 3.1. As I am doing most of my SSCE work through the SQL Server 2005 Management Studio (its query editor is still far superior to VS'), I found that I can still use 3.1 without much trouble (so long as I manually browse and add the 3.1 reference to my project).
That said, I'd like to use 3.5. My two questions are: - Is the SSCE 3.5 version included in VS2008 final? I noticed the dlls are all over a month old. Can I redistribute these with my app? - Will there be support in SQL 2005 or SQL 2008 for connecting to 3.5 databases? I know that a new SQL server 2008 CTP was released, but haven't seen mention of SSCE support. I'd really hate to lose the great query editor of the Management Studio when moving to 3.5.
We have plan to migrate our database from Access to SqlCe.
Here our situation:
We will never use a Pocket PC applications, only Windows desktop application. We want to migrate the .mdb file to a .sdf file (I've seen the ADS application, but since we don't use mobile devices, it's no use) The .sdf will be use on local client machine only. Here's my problems:
I've use a conversion to migrate from access (http://www.primeworks-mobile.com/Downloads/DPW.html) and seem to work fine. I updated the source code to use the SqlServerCe dll instead of OleDB. Some of my queries work fine, but others just won't work in VS2005 using C#.
To check if the query is really wrong, I take exactly the same query and put it Query Builder from the Server Explorer in VS2005. And the query just work fines!!! It's bring back all the records asked for, and I've got an error for the same query from my source code.
I'm using a datatable, here's the code used for filling the DataTable. I've got an error on the line adapter.FillSchema()
string connectionString = @"Data Source=" + fic_parent.CheminFichierComplet; using (SqlCeConnection connection = new SqlCeConnection(connectionString)) { SqlCeDataAdapter adapter = new SqlCeDataAdapter();
connection.Open();
SqlCeCommand command = new SqlCeCommand(requete, connection);
tableResultats = new DataTable(); adapter.FillSchema(tableResultats, SchemaType.Source); adapter.Fill(tableResultats);
// Close connection connection.Close(); }
Error is:
Message="The specified table does not exist. [ (...) ]" Source="SQL Server 2005 Everywhere Edition ADO.NET Data Provider" HResult=-2147217865 NativeError=0
Here my questions:
Is it possible to have an application that will convert my Access databases to an SSCe .sdf file ? Why I can query in Server Explorer and can't in my source code using the same sql statement ?
At which level my code isn't good, because I just replace all OleDB* --> SqlCe* in my code. I hope that I explain clearly my problem, do not hesitate to ask me questions about this problem.
I have a question about using SSCE with flash memory. Our CE components equally allocate the write access to the memory location. But write access is guaranteed only 10000 times per memory cell. So I need to know
1. what changes are made to the file system if I add an entry to a table? Is this entry the only information added to the file system? 2. What happens if I delete an entry? Will SSCE optimize the data in the filesystem?
I'm running VS2008 beta2. I've succefully added a SDF database file to my project and created a couple of tables in it from VS. I added a reference to the System.Data.SqlCeConnection. When I try to create a new instance of the SqlCeConnection class I get a nasty error that states I'm missing the 'sqlceme35.dll' file. I copy all of the files .dll into by bin folder. Now I'm getting a BadImageFormat error when I create an instance. Does anyone know what that means? or why I had to copy those files to begin with? This is very odd. Any help would be appreciated. I am new to SqlCe.
I don't know if it is by design or a bug. I have tried using the TableAdapterManager with remote SQL 2005, SQL 2005 Express (local and remote) with success. I have tried using the TableAdapterManager ,( which is included by default with the dataset), with SQL Compact 3.5 as a local (within project) and remote(not in the project) and it does not work.
I would think that if the intention is to start using SCE 3.5 as the local database for applications (which makes sense), then I would think that making use of the TableAdapterManager class would seem to be a no brainer unless I'm missing something. I've been known to do that.
I guess my question is, is this a bug or by design?
I've found a two different answers for this question:
one - on the http://support.microsoft.com/Default.aspx?kbid=920700 site where on the Performance improvements section there is a 128MB value in the Database size.
other is in the product datasheet there is a information that this version supports databases up to 4 GB.
What tool should I use to view SQL Server Compact Edition database?
I have VS2008 Professional, but I didn't find any tool in the installation. So I tried to install the SQL Server Developer Edition, which is included in the VS2008 package, but the installation has quit saying the that there is nothing to upgrade (there is probably newer version of SQL installed by VS2008 itself).
I also tried to download and install SQL Server Managment Studio Express. The installation went well, but then I wasn't able to select any SSCE database (there was only SQL Server Express or something like that selectable).
I just want to know if SSCE 3.0/3.5 can cater for Multi-user instances? I have a desktop application that I want to allow multiple Windows-user accounts to access. basically I need a datastore that can be lightweight (which SSCE is) and support "User Instances" (like in SQL Server Express)
hello,(sorry for my english language but im french :-) )
I have a lots of problems to convert the SQL language from MySQL to SSCE because the SSCE language is so poor...
If somebody have some URL with a lots of examples of request (select, update). for example, im not able to convert this update request :
Update reservation,lockers set reservation.ERS_ID='A' where lockers.LCK_CONF!='LW' AND lockers.LCK_PHYS_NO=reservation.LCK_PHYS_NO AND reservation.ERS_ID IN('V','M','E','S','F','D','P');
how to use 2 tables in the same update??? i saw the MSDN but there arent explicit and GOOD examples... http://msdn2.microsoft.com/fr-fr/library/ms174122.aspx
Hi, I replace the reference assembly System.Data.SqlServerCe with version 3.5 beta2 in Microsoft.Practices.EnterpriseLibrary.Data.SqlCe. When I used the Data.SqlCe block in application, an exception ("Cannot access a disposed object named SqlCeConnection") threw. This didn't occur in SSCE 3.5 beta1 and early versions. Finally I found the difference between beta1 and beta2. In the SqlCeConnection class, there is a RemoveWeakReference method. this is code snippet of beta1:
internal void RemoveWeakReference(object value) { if (this.weakReferenceCache != null) { this.weakReferenceCache.Remove(value); } } and this is for beta2:
internal void RemoveWeakReference(object value) { if (this.weakReferenceCache == null) { throw new ObjectDisposedException("SqlCeConnection"); } this.weakReferenceCache.Remove(value); }
So, if we dispose a connection before a command, such as: SqlCeConnection cn;SqlCeCommand cmd;... cn.Dispose();cmd.Dispose(); // Error, an exception will be threw. Is it a bug of Beta2?
I am writing a Desktop application accessing a SSCE database. I am running Windows XP SP2, VS2005 SP1, and have SQLServerCE3.1 installed. Debugging the application shows that ds.m_spInit->QueryInterface() returns E_NOINTERFACE. I have checked that SSCE runtime library is installed, IDBCreateSession uuid exists in the registry and m_spInit.CoCreateInstance(CLSID_SQLSERVERCE_3_0) return S_OK. Can you suggest me what need adding/installing to get the CSession.Open(datasource) working?
sorry to b a pest again! Before I made the decision to change the DB used in my app from SQL Server Express to SSCE, I had no problems with constructing a SELECT statement as laid out in the Title.
Basically, I have 2 tables with a one-many relationship between them. In the Parent table, I had a SQL Statement as follows:
SELECT DeptID, DeptName,
(SELECT DISTINCT COUNT(Active) FROM Documents WHERE (Documents.DeptID = Dept.DeptID) AND (Documents.Active = 'True') AS CountOfActive FROM Dept
Now in SSCE 3.1, I get an "Unable to parse query" error message when I construct the same SQL statement in my dataset designer.
I have had a heck of a time trying to get SQL Compact Edition 3.5 working in by Visual Studio 2008 environment. I (now) know it comes along with Visual Studio 2008 by default, however, I didn't realize that at first and went and installed one of the SQL Compact Edition MSI's that were available from MS download. Since then I am unable to create a new compact edition database (Add New Item...) in visual studio it says:
The data provider required to connect to the local data file could not be found. The file will be added to the project but the typed DataSet associated with the file will not be generated.
Also, When I try to create a new connection under server explorer, I notice the SQL Compact Edition is not a data source option.
I am running the relased build from the MSDN download iso: en_visual_studio_team_system_2008_development_x86_x64wow_dvd_X14-26382.iso
I have uninstalled the seperate install of the SQL Compact Edition and run repair on the visual studio 2008 installer and it does not fix the problem. I have seen a reference that says the design time installer should be under Program FilesMicrosoft Visual Studio 9.0Common7IDE, but it isn't in my install. I did find it on the source DVD under the folder WCUSSCESSCEDeviceRuntime-enu.msi.
After running that, I no longer get the original error message. Now I get a little further and then the Data Source Configuration Wizard comes up and says
An error occured while retrieving the information from the database:
SQL Server Compact is either not installed or the installation has been corrupted.
OK, so I look under C:Program FilesMicrosoft SQL Server Compact Editionv3.5 which install from the DVD (WCUSSCESSCEDeviceRuntime-enu.msi.) created and notice a readme file that inside has a link for downloading and installing the Runtime for Desktop computers.
Thinking that perhaps the runtime is a seperate install from the design tools I follow the link and setup starts. At this point I notice that the set says Compact Edition 3.5 BETA - hmm that doesn't seem right so I cancel. Not sure why the final product for the design tools would have a readme that points to a download link for the BETA of the runtime, but going back to the DVD I see the file WCUSSCESSCERuntime-enu.msi and run it. This time it says it will install Compact Edition 3.5 - looks good no BETA.
Finish the instal and viola! it works....
So what worked for me in the end:
1) Uninstall any other installs of Compact Edition 3.5 you might have 2) repair visual studio 3) install the design tools from the DVD WCUSSCESSCEDeviceRuntime-enu.msi. 4) install the runtime fgrom the DVD WCUSSCESSCEDeviceRuntime-enu.msi.
I'm receiving a bad error message when attempting to connect to SSCE .sdf files installed by Orcas Beta 1 (e.g., Northwind.sdf from the ...v3.5Samples folder) and created by the Orcas Beta 1 Sync Designer with SQL Management Studio Express SP2 (version 9.00.3042.00).
The message is:
TITLE: Connect to Server ------------------------------
Cannot connect to C:Program FilesMicrosoft SQL Server Compact Editionv3.5SamplesNorthwind.sdf.
You are trying to access an older version of a SQL Server Compact Edition database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact Edition 3.0 or later database, run Compact / Repair. [ Db version = 3505053,Requested version = 3004180,File name = C:Program FilesMicrosoft SQL Server Compact Editionv3.5SamplesNorthwind.sdf ] (SQL Server Compact Edition ADO.NET Data Provider)
------------------------------
Obviously 3505053 is a newer than SSCE 3004180, not earlier. I don't understand why SSMSX SP2 would request a specific version and refuse to open the latest version.
I don't recall having this problem with SSMSX SP2 and VS Orcas March CTP under Windows Server 2003 (March CTP wasn't certified for Vista).
VS Orcas Beta 1's Server Explorer can open the .sdf files and display their content but won't execute DDL commands against them.
There also is what I believe to be a related problem with the Sync Designer failing to add LastEdited and CreatedDate fields to the .sdf files (see Sync Designer/SSCE Version Problems with Orcas Beta 1 in the Sync Services forum.
Hi everyone - I'm getting myself into a right muddle and am looking for advice.
How do people deal with connection string matters when taking a dataset defined in one assembly (and by default using the connection strings defined within that assembly in the settings files) and then using that assembly in an app (which also has a requirement to see the same physical database).
I'm not sure I've explained that terribly well but what I'm trying to avoid is duplicate copies of my database which so far seems to be the only way that I've managed to make stuff work. I'm not very experienced with SSCE data access (I'm a serices/sockets/ip person) and this may just be ignorance. Most of the examples seem to assume that the data is in the same place as the app whereas I'm trying to collate a whole series of functions into a helper assembly that I'd like to re-use for other things.
Ideally the dataset designer would provide an easy way of choosing from centralised connection strings - perhaps this is what the Dataset Project implies - but again the docs are mostly focused on SQL Server? Otherwise the best I've been able to do is make the connection properties public and try to update them that way or use a post-build action to copy the database from my datalayer project directory to that of my application |DataDirectory|
I suppose the question might be if you have a dataset containing multiple tableadapters that assumes one connection string, is there any easy way to keep such strings co-ordinated between projects without hardcoding them? With a server resource, the same non-specific connection string resolves to the same server (if that make sense) and this seems to be were I can't make the logical shift.
Does anyone have any thoughts and can they please point this SSCE noob in the right direction?
I'm working on a program that does a 'compaction' or more literally, a replication, of one SSCE file to another. We couldn't use the built-in compaction routine due to some of its limitations. The basic steps of my program are as follows:
1. Create the full schema 2. Add constraints, indeces, etc. 3. Replicate each table by: a. Selecting all records from the old database b. Preparing a parameterized insert query with the number of records in the old database c. Insert each record sequentially via the parameterized query
My question is such: If I have an SSCE compacted database of 189.67MB and run my 'compaction' routine on it, I get a filesize of 216.01MB. Both files have the exact same data and schema. The latter file ONLY had insert statements run on it.
How can I get my program to give similar results to SSCE? I'm using VC++ and OLEDB code with SSCE 3.1.
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
I'am sure this is an old quesiton but I've spent the whole morning trying to find the answer
What are the string functions suported by Jet in OLEDB queries? In particular, I am desperately looking for a replace function to remove thousands separators from imported text file
