SQL Server CE 3.5 更新行错误 DB_E_ERRORSOCCURRED 列错误是 DBSTATUS_E_SCHEMAVIOLATION

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
", myTable.m_dwIdNumberStatus);
                ATLTRACE2("  myTable.m_dwCountStatus = %d
", 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 说 DBSTATUS_E_SCHEMAVIOLATION 意味着