sql-server-ce

Problems with executing SqlCeCommand

So I have .sdf database file and such query: SELECT id, name, url, lastSynchronized, synchronizationInterval, fileExtensions FROM pathconfiguration WHERE remote = 1 and (manualSynchronizationRequest = 1 OR (synchronizationInterval IS NOT NULL AND dateadd(minute, synchronizationInterval, COALESCE(lastSynchronized, '1900-01-01')) < GETDAT...

Size limit problem with ntext in Sql Server CE 3.5 and Visual Studio

I am experiencing some strange behavior with SQL Server CE 3.5 SP2. I have a table with 2 columns; one of type int named ID, which is the primary key, and one of type ntext named 'Value'. The 'Value' column is supposed to contain rather long string values. However, when I try to store a string that is longer than 4000 characters, the v...

Best Practice re SDF file location?

I am writing a SQL Compact database app with an SDF database that I need to install on the user's machine. The user needs to be able to read and write to the database, and the database needs to be available to all users. The app has to run with normal user rights, and UAC can't be disabled. Where is the best place to place the database ...

Problem when inserting data into SQL Compact by ADO.Net Entity Framework

DatabaseEntities de = new DatabaseEntities(); Income income = de.Incomes.CreateObject(); income.Id = de.Incomes.Max(f => f.Id) + 1; income.Person = Users.SelectedValue.ToString(); income.Value = value; income.Unit = Unit.SelectedValue.ToString(); income.Descr...

The Entity Framework takes the next data page automatically

//Bind data this.IncomeGridView.DataSource = incomeData; //If incomeData is not empty then Taking the value of the Id field of the first and the last row in data page if (incomeData.Count() > 0) { this.incomePaging_IdAtTheEndOfCurrentPage = incomeData.ToList()[incomeData.Count() - 1].Id; **this.incomePaging_IdAt...

How to open SQL Compact database read only

There is a SQL Compact v3.1 database that I want to quickly read. I'm doing this in python so I don't have access to managed code. I've noticed that if I use adodbapi the database file actually gets modified just by opening it. And sadly when I add 'File mode=Read Only' to the connection string I get a weird error. Here is the code I...

No mapping exists from DbType System.DateTimeOffset to a known SqlCeType.

I have a SQL Server 2008 database that contains DateTimeOffset objects. As per this page, SQL Server Compact provides support for replicating the new data types in SQL Server 2008 such as date, time, datetime2, datetimeoffset, geography, and geometry. The new data types in SQL Server 2008 are mapped to nchar, nvarchar, image, etc. F...

sql ce escape '(single quote) C#

I am writing some code to store names in a database. I limit the names to only certain characters, but last names are a challenge. Since some people have single quotes in their name (example O'Brian) I need to allow this. So I wrote a regex replace to replace the ' with a \' which I assumed should make the ' a literal. It works as far...

SQL Server CE Schema Compare Tool

Is there any tool which schema compares directly 2 SQL CE databases. VS2010 not working for CE as well as every commercial tool i have seen. How would you approach this problem? ...

Execute/Query against SQL Compact 4.0 in ASP.NET

I'm writing a small little utility MVC app and I need to have the ability to execute ad-hoc queries against my one-table SQL Compact 4.0 .sdf file for management (Web Matrix isn't working right for development, and it won't be available on the PC this will ultimately be running on). Using Entity Framework code-first, everything is worki...

Switching from Oracle 10g Express to SQL Server Compact (NHibernate)

Hey, I am switching my application from Oracle 10g to SQL Server Compact. Currently I have this in the mapping file: <id name="Id" column="MY_ID"> <generator class="sequence"> <param name="sequence">MY_SEQ</param> </generator> </id> and I have been informed that sequence does not exist in SQL Server Compact, I was wondering i...

Why is my table name not valid?

Here is the create statement: create table dbmonitor.DBMON_DATABASE_TYPE ( DATABASE_TYPE_ID BIGINT IDENTITY NOT NULL, DispName NVARCHAR(255) null, primary key (DATABASE_TYPE_ID) ) and this is the error I get: 13:40:57,685 ERROR [TestRunnerThread] SchemaExport [(null)]- The table name is not valid. [ Token li...

SQL Compact Edition 4.0 CTP1 needs Visual C++ 2008 Runtime Libraries SP1

SQL Compact Edition 4.0 CTP1 needs Visual C++ 2008 Runtime Libraries SP1 and if SQL CE is deployed privately in the application’s BIN folder the following have to be present on the machine for it to function properly: a. Installing the .NET Framework 3.5 SP1 also installs the Visual C++ 2008 Runtime Libraries SP1. b. Or insta...

Using GUID with SQL Server and NHibernate.

I'm running NHibernate and SQL Server CE I am trying to use GUIDs as my ID column. This is the code I already have: Mapping: <class name="DatabaseType" table="DBMON_DATABASE_TYPE"> <id name="Id" column="DATABASE_TYPE_ID"> <generator class="guid" /> </id> <property name="DispName" /> </class> And this is the cre...

Access-like editor for SQL Compact Edition

I'd like to find the best editor for flat SQL Server Compact edition databases. Essentially we want to push around arbitrary flat typed datasets of under 4 Gb each and have a good editing experience for manipulating this data. Access isn't the first thing that comes to mind, but I'm looking for a more "de facto" approach rather than the ...

Why can't I insert a record into my SQL Compact 3.5 database?

Hello, I just made a very simple test app using documentation from MSDN. All I want to do is insert a record into my table which is in a SQL Server Compact database in my VS 2010 app. But when I run it, it acts like it executes fine (no errors), but a record is never inserted into my SQL Compact 3.5 database when I go to view the tabl...

How to write a query that calculates a value using a previous records value?

I'm trying to figure out the best way to calculate a value that is based on the previous records values. I'm sure it's possible, I just can't figure it out. Consider a table that has StartTime & EndTime. Along with these values is are 2 different types of wages: OnDuty & OffDuty. OnDuty = All time between StartTime and EndTime. ...

Reading a datetime column with a null data SqlCeDataReader ?

What's the way to read a column that might have a null datetime value in SQLCe? Right now i have this SqlCeDataReader reader = cmd.ExecuteReader(); DateTime? shapeFileSQLDateTime = (DateTime?)reader["ShapeFileTimestamp"];//ok b/c has data DateTime? mdbSQLDateTime = (DateTime?)reader["CreatedTimestamp"]; //throws exception b/c is nul...

SQL CE vs Jet in winforms application

I have a legacy desktop application using JET for the database. The application needs to be able to access the database file from a network drive. Should I migrate the data access to SQL CE (or SQL Lite), or leave it in JET? Maximum table row size is around 50000. ...

How to get the next available primary key from a SqlCe DB ?

How can I get the next available primary key from my table? The primary key is a numeric. I'm using C#. ...