Database ADO exception on adding new record

Peter Mortier

Hi, when writing in Access database using ADO and record binding, I run into an exception after some time. I wrote a small console program to demonstrate the problem :

#include #include #import "c:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "EndOfFile")
#include "icrsint.h"
_COM_SMARTPTR_TYPEDEF(IADORecordBinding, __uuidof(IADORecordBinding));

void OpenDatabase();
void WriteToDatabase(int i);
void CloseDatabase();

_ConnectionPtr m_pConnectionPtr = 0;
DWORD64 cnt_recordset;

int main()
{
HRESULT hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);
m_pConnectionPtr = NULL;
m_pConnectionPtr.CreateInstance(__uuidof(Connection));
int cnt = 0;
cnt_recordset = 0;

OpenDatabase();
while (1)
{
std::cout << cnt++ << " : " << cnt_recordset << std::endl;
for (int i = 0; i < 1000; i++)
WriteToDatabase(i);
}
CloseDatabase();
CoUninitialize();
}

void OpenDatabase()
{
bstr_t strCnn("Provider=MSDASQL;DSN=TestDB;User ID=sa;");
m_pConnectionPtr->Open(strCnn, "", "", NULL);
}

void CloseDatabase()
{
if ((m_pConnectionPtr->State == adStateOpen))
m_pConnectionPtr->Close();
}

void WriteToDatabase(int i)
{
cnt_recordset++;
class CMyRecordSet : public CADORecordBinding
{
BEGIN_ADO_BINDING(CMyRecordSet)

  // Column m\_nID is the 1st field in the table.
  ADO\_VARIABLE\_LENGTH\_ENTRY2(1, adInteger, m\_nID, sizeof(m\_nID), m\_IDStatus, FALSE)

  // Column m\_bCritical is the 2nd field in the table.
  ADO\_FIXED\_LENGTH\_ENTRY(2, adInteger, m\_value, m\_valueStatus, TRUE)

  END\_ADO\_BINDING()

public:
int m_nID;
int m_value;
ULONG m_IDStatus;
ULONG m_valueStatus;
};

HRESULT hr = true;
// open recordset
//_RecordsetPtr pRs = NULL;
//pRst.CreateInstance(__uuidof(Recordset));

_RecordsetPtr pRs("ADODB.Recordset");
CMyRecordSet rs;
IADORecordBindingPtr picRs(pRs);
hr = pRs->Open("TTagData",
_variant_t((IDispatch*)m_pConnectionPtr, true),
adOpenKeyset, adLockOptimistic, adCmdTable);
//Bind the Recordset to a C++ Class here.
hr = picRs->BindToRecordset(&rs);
// fill in data
rs.m_value = i;
// add new record to the table
picRs->AddNew(&rs);
// picRs->Release(); tried this but no result

pRs->Close();
}

The exception occurs on closing the recordset after the AddNew :

inline HRESULT Recordset15::Close ( ) {
HRESULT

Lost User

From ErrorValueEnum - SQL Server | Microsoft Docs[^]

adErrIllegalOperation 3219 -2146825069 0x800A0C93 Operation is not allowed in this context.

Which, like so many error codes, does not tell you much. You could try one of the Microsoft forums.

CHill60

Quote:

This happens after a couple of hundreds of thousends writes.

Access is probably not the best database of choice for that many records to be honest. How big (size on disk) is your .accdb file? There is an absolute maximum size for an Access database of 2GB - you may need to consider linked tables to bring the size down

Peter Mortier

Database is only 126MB big at that stage so this could not be the cause. I have rewritten the code without datarecord binding :

void WriteToDatabase2(int i)
{
_RecordsetPtr pRs("ADODB.Recordset");
HRESULT hr;
char sql[128];
sprintf(sql, "insert into TTagData([Value]) values (%d);", i);
hr=pRs->Open(_variant_t(sql),
_variant_t((IDispatch*)m_pConnectionPtr, true),
adOpenStatic, adLockReadOnly, adCmdUnknown);
}

Have written a couple of million of records and now I don't get the exception! Is there something wrong with the datarecord binding or the way I use it??

Peter Mortier

I have rewritten the code without datarecord binding :

void WriteToDatabase2(int i)
{
_RecordsetPtr pRs("ADODB.Recordset");
HRESULT hr;
char sql[128];
sprintf(sql, "insert into TTagData([Value]) values (%d);", i);
hr=pRs->Open(_variant_t(sql),
_variant_t((IDispatch*)m_pConnectionPtr, true),
adOpenStatic, adLockReadOnly, adCmdUnknown);
}

Have now written a couple of million of records and now I don't get the exception! Is there something wrong with the datarecord binding or the way I use it??

Victor Nijegorodov

According to [Open Method (ADO Recordset) - SQL Server | Microsoft Docs](https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/open-method-ado-recordset?view=sql-server-ver15): >It is not a good idea to use the Source argument of the Open method to perform an action query that does not return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead.

Peter Mortier

OK, so I changed my code to something like this :

_CommandPtr pCmd;
pCmd.CreateInstance(__uuidof(Command));
pCmd->ActiveConnection = pConn;
pCmd->CommandText = "Insert into [table] ([value]) values (1)";
pCmd->Execute();

Now in the table is an autoincrement ID which I need for use in a linked table. Does the pCmd->Execute() return a recordset and if so what's in it? Or is there another way to get the data of the just added record? I find very few information on this topic.

Victor Nijegorodov

I usually divided the INSERT into DB and then ReQuery for the opened recordset. However, did you check the returned recordset from

pCmd->Execute();