SQL Server CE 3.5 update row error DB_E_ERRORSOCCURRED column error is DBSTATUS_E_SCHEMAVIOLATION

I am investigating moving a small and simple SQL Server database to SQL Server CE and am currently using a small prototype to investigate basic operations with SQL Server CE with the following operations in mind: (1) programmatically create a table, (2) insert new records, (3) read existing records, and (4) update existing records.

The prototype is having a problem with updating existing records when using the Accessor and the bound members of the Accessor struct. The select statement works correctly returning the row along with the data. I can update the Accessor bound members however when I use the SetData() method to update the row, the HRESULT value returned is DB_E_ERRORSOCCURRED. I then examine the DBSTATUS variables and I can see the error code of DBSTATUS_E_SCHEMAVIOLATION.

What does DBSTATUS_E_SCHEMAVIOLATION mean and what do I need to change so that SetData() works?

If I modify the SQL query used in the OLEDB so that rather than doing a SELECT I instead do an UPDATE the row selected by the WHERE clause of the UPDATE is modified correctly. The problem appears to be with the SetData() functionality and the binding logic. When I have done the same thing with SQL Server Express, I do not see an error. I see the same error with both SQL Server CE 3.5 and SQL Server Mobile for Visual Studio 2005.

In the output window of the Visual Studio 2005 IDE I see the following lines. Two of the lines marked with <<<<< ATLTRACE2 are output from ATLTRACE2 macros to show the individual column status values. From what I can find on the internet, the First-chance exception log is a warning that can be ignored.

First-chance exception at 0x7c812fd3 in dblist_ce.exe: Microsoft C++ exception: long at memory location 0x0012f698..
OLE DB Error Record dump for hr = 0x80040e21
The thread 'Win32 Thread' (0x16dc) has exited with code 0 (0x0).
Row #:    0 Source: "Microsoft Cursor Engine" Description: "Multiple-step operation generated errors. Check each status value." Help File: "(null)" Help Context:    0 GUID: {00000000-0000-0000-0000-000000000000}
OLE DB Error Record dump end
  myTable.m_dwIdNumberStatus = 8    <<<<< ATLTRACE2
  myTable.m_dwCountStatus = 11      <<<<< ATLTRACE2

The two status values (m_dwIdNumberStatus and m_dwCountStatus) have values from an enum and the above two values represent DBSTATUS_E_UNAVAILABLE = 8 and DBSTATUS_E_SCHEMAVIOLATION = 11. The status for IdNumber is DBSTATUS_E_UNAVAILABLE because I am setting it to be ignored before doing the SetData().

The source code for the prototype follows. What this does is to create the SQL Server CE database file if it does not exist and then fills it with a set of rows and then tries to do an update on one particular row.

// dblist_ce.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include <string>
#include <iostream>

#define SQLSERVER_MOBILE L"Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\\MyDatabase3.sdf"
#define SQLSERVER_CE_35  L"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\MyDatabase35.sdf"


#define SQL_SERVER_CONNECT_STRING   SQLSERVER_MOBILE
#define SQL_SERVER_CE_FILENAME     "C:\\MyDatabase3.sdf"

#if 0
#include "Table_1.h"
#else
//  contents of include file Table_1.h follow

// Table_1.h : Declaration of the CTable_1

// code generated on Saturday, April 26, 2014, 11:23 AM

class CTable_1Accessor
{
public:
    TCHAR m_IdNumber[11];
    LONG  m_Count;

    // The following wizard-generated data members contain status
    // values for the corresponding fields in the column map. You
    // can use these values to hold NULL values that the database
    // returns or to hold error information when the compiler returns
    // errors. See Field Status Data Members in Wizard-Generated
    // Accessors in the Visual C++ documentation for more information
    // on using these fields.
    // NOTE: You must initialize these fields before setting/inserting data!

    DBSTATUS m_dwIdNumberStatus;
    DBSTATUS m_dwCountStatus;

    // The following wizard-generated data members contain length
    // values for the corresponding fields in the column map.
    // NOTE: For variable-length columns, you must initialize these
    //       fields before setting/inserting data!

    DBLENGTH m_dwIdNumberLength;
    DBLENGTH m_dwCountLength;

    void GetRowsetProperties(CDBPropSet* pPropSet)
    {
        bool  bRet;
        bRet = pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
        bRet = pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
        bRet = pPropSet->AddProperty(DBPROP_IGetRow, true, DBPROPOPTIONS_OPTIONAL);
        bRet = pPropSet->AddProperty(DBPROP_IRowsetChange, true, DBPROPOPTIONS_OPTIONAL);
        bRet = pPropSet->AddProperty(DBPROP_IRowsetUpdate, true, DBPROPOPTIONS_OPTIONAL);
        bRet = pPropSet->AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
    }

    HRESULT OpenDataSource()
    {
        CDataSource _db;
        HRESULT hr;
        hr = _db.OpenFromInitializationString(SQL_SERVER_CONNECT_STRING);
        if (FAILED(hr))
        {
#ifdef _DEBUG
            AtlTraceErrorRecords(hr);
#endif
            return hr;
        }
        return m_session.Open(_db);
    }

    void CloseDataSource()
    {
        m_session.Close();
    }

    operator const CSession&()
    {
        return m_session;
    }

    CSession m_session;

    DEFINE_COMMAND_EX(CTable_1Accessor, L" \
    SELECT \
        IdNumber, \
        Count \
        FROM Table_1")


    // In order to fix several issues with some providers, the code below may bind
    // columns in a different order than reported by the provider

    BEGIN_COLUMN_MAP(CTable_1Accessor)
        COLUMN_ENTRY_LENGTH_STATUS(1, m_IdNumber, m_dwIdNumberLength, m_dwIdNumberStatus)
        COLUMN_ENTRY_LENGTH_STATUS(2, m_Count, m_dwCountLength, m_dwCountStatus)
    END_COLUMN_MAP()
};

class CTable_1 : public CCommand<CAccessor<CTable_1Accessor> >
{
public:
    HRESULT OpenAll()
    {
        HRESULT hr;
        hr = OpenDataSource();
        if (FAILED(hr))
            return hr;
        __if_exists(GetRowsetProperties)
        {
            CDBPropSet propset(DBPROPSET_ROWSET);
            __if_exists(HasBookmark)
            {
                if( HasBookmark() )
                    propset.AddProperty(DBPROP_IRowsetLocate, true);
            }
            GetRowsetProperties(&propset);
            return OpenRowset(&propset);
        }
        __if_not_exists(GetRowsetProperties)
        {
            __if_exists(HasBookmark)
            {
                if( HasBookmark() )
                {
                    CDBPropSet propset(DBPROPSET_ROWSET);
                    propset.AddProperty(DBPROP_IRowsetLocate, true);
                    return OpenRowset(&propset);
                }
            }
        }
        return OpenRowset();
    }

    HRESULT OpenRowset(DBPROPSET *pPropSet = NULL)
    {
        HRESULT hr = Open(m_session, NULL, pPropSet);
#ifdef _DEBUG
        if(FAILED(hr))
            AtlTraceErrorRecords(hr);
#endif
        return hr;
    }

    void CloseAll()
    {
        Close();
        ReleaseCommand();
        CloseDataSource();
    }
};
// ------     End of the content from include file Table_1.h
#endif

int _tmain(int argc, _TCHAR* argv[])
{
    HRESULT hrResult = OleInitialize(NULL);
    switch (hrResult)
    {
        case S_OK:
            break;
        default:
            std::cout << "Ole Initialization Failed " << hrResult << std::endl;
            return 1;
    }

    HRESULT   hr;

    CTable_1  myTable;

    bool      myTableNew = false;

    hr = myTable.OpenAll ();
    AtlTraceErrorRecords(hr);
    if (hr == S_OK) {
        int  nItem = 0;

        for (nItem = 0, hr = myTable.MoveFirst(); hr == S_OK; hr = myTable.MoveNext())
        {
            char szValueChar[12] = {0};
            for (int i = 0; i < 10; i++) szValueChar[i] = (char)myTable.m_IdNumber[i];
            std::string sTemp (szValueChar);
            std::cout << nItem << "  -> " << sTemp << " : " << myTable.m_Count << std::endl;
            nItem++;
        }
        myTable.Close();    // close this row set.
    } else if (hr == E_FAIL) {
        FILE *hFile = fopen (SQL_SERVER_CE_FILENAME, "w");
        if (hFile) {
            fclose(hFile);
            hr = myTable.OpenAll ();
            if (hr == E_FAIL)
                return 0;
        }
    }

    if (hr == DB_E_NOTABLE) {
        // The database file is empty meaning that there are no tables defined
        // so we will create the table that we want to use.
        myTable.Close();    // close this row set.

        CDBPropSet m_pPropSet(DBPROPSET_ROWSET);
        myTable.GetRowsetProperties (&m_pPropSet);

        TCHAR *tcsQuery = L"create table Table_1 ([IdNumber] nchar(10) not null, [Count] int not null)";

        hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, false);
        myTable.Close();    // close this row set.
        myTableNew = true;
    }

    CDBPropSet m_pPropSet(DBPROPSET_ROWSET);
    myTable.GetRowsetProperties (&m_pPropSet);

    TCHAR tcsQuery[256];

    if (myTableNew) {
        struct {
            TCHAR IdNumber[11];
            int   iCount;
        } myInsertData[] = {
            {L"0000000101", 1001},
            {L"0000000102", 1002},
            {L"0000000103", 1003},
            {L"0000000104", 1004},
            {L"0000000105", 1005},
            {L"0000000106", 1006},
            {L"0000000107", 1007},
            {L"0000000108", 1008},
            {L"0000000109", 1009},
            {L"0000000120", 1010}
        };

        std::cout << "--  New table so insert standard rows " << std::endl;

        for (int i = 0; i < sizeof(myInsertData)/sizeof(myInsertData[0]); i++) {
            _swprintf (tcsQuery, L"INSERT INTO Table_1 ( [IdNumber], [Count] ) VALUES ('%s', %d)", myInsertData[i].IdNumber, myInsertData[i].iCount);
            hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, false);
            myTable.Close();    // close this row set.
        }
    }

    // Lets print out a list of the rows that we currently have in the database
    wcscpy (tcsQuery, L"SELECT [IdNumber], [Count] from Table_1");
    hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, true);
    if (hr == S_OK) {
        int  nItem = 0;

        for (nItem = 0, hr = myTable.MoveFirst(); hr == S_OK; hr = myTable.MoveNext())
        {
            char szValueChar[12] = {0};
            for (int i = 0; i < 10; i++) szValueChar[i] = (char)myTable.m_IdNumber[i];
            std::string sTemp (szValueChar);
            std::cout << nItem << "  -> " << sTemp << " : " << myTable.m_Count << std::endl;
            nItem++;
        }
        myTable.Close();    // close this row set.
    }

    std::cout << " --  After insert now list the rows we have inserted" << std::endl;

    wcscpy (tcsQuery, L"SELECT [IdNumber], [Count] from Table_1 where [IdNumber] = '0000000103'");
//  wcscpy (tcsQuery, L"UPDATE Table_1 SET [Count]=[Count] + 1 where [IdNumber] = '0000000103'");
    hr = myTable.Open (myTable.m_session, tcsQuery, &m_pPropSet, NULL, DBGUID_DEFAULT, true);
    AtlTraceErrorRecords(hr);
    if (hr == S_OK) {
        if ((hr = myTable.MoveFirst()) == S_OK)
        {
            char szValueChar[12] = {0};
            for (int i = 0; i < 10; i++) szValueChar[i] = (char)myTable.m_IdNumber[i];
            std::string sTemp (szValueChar);
            std::cout << "  -> " << sTemp << " : " << myTable.m_Count << std::endl;
            int iCountTemp = myTable.m_Count++;
            std::cout << "          increment count from " << iCountTemp << " to " << myTable.m_Count << std::endl;
            myTable.m_dwIdNumberStatus = DBSTATUS_S_IGNORE;
            myTable.m_dwCountStatus = DBSTATUS_S_OK;
            hr = myTable.SetData ();
            AtlTraceErrorRecords(hr);
            if (hr != S_OK) {
                ATLTRACE2("  myTable.m_dwIdNumberStatus = %d\n", myTable.m_dwIdNumberStatus);
                ATLTRACE2("  myTable.m_dwCountStatus = %d\n", myTable.m_dwCountStatus);
                if (myTable.m_dwIdNumberStatus != DBSTATUS_S_OK) {
                    std::cout << "                error: m_dwIdNumberStatus = "<< myTable.m_dwIdNumberStatus << std::endl;
                }
                if (myTable.m_dwCountStatus != DBSTATUS_S_OK) {
                    std::cout << "                error: m_dwCountStatus = "<< myTable.m_dwCountStatus << std::endl;
                }
            }
        } else {
            AtlTraceErrorRecords(hr);
        }
    }
    myTable.Close();    // close this row set.

    OleUninitialize ();
    return 0;
}

MSDN says that DBSTATUS_E_SCHEMAVIOLATION means that

The data value violated the schema's constraint for the column.

Just search for DBSTATUS_E_SCHEMAVIOLATION on that page.

It is strange because the only constraint that you have for the Count column is "not null".

Personally, I never used OLE DB with Compact Edition of SQL Server, and I never tried to update rows like you do. I put all my T-SQL code in stored procedures and call them using "call" or "exec". Inside stored procedures I use standard UPDATE statements.

In your case it is very likely that you are right about the type of the cursor. When you call myTable.Open to run the query SELECT [IdNumber], [Count] from Table_1 where [IdNumber] = '0000000103' it is very likely that you are getting a read-only cursor. I don't know how to check it, but in your place I would try to find a way to confirm whether the cursor is updatable in the first place.