TUniTable ACCESS update error

[RichTat]

C++ Builder 2010 with Microsoft Access ODBC driver ACEODBC.DLL and AccessUniProvider1 and TUniTable : the following code gives an error:
'raised exception EDatabaseError with message'Update failed found 0 records' at the last Post() line.
The table is empty with the field "ID" as the primary key and type NUMBER not AUTONUMBER.
The record is actually added to the table.
Where am I going wrong - all this type of code worked with the TADOTable components.

void __fastcall TForm1::Button3Click(TObject *Sender)
{
int n;

tabTest->Insert();
tabTest->FieldByName("ID")->AsInteger = 1;
tabTest->Post();
tabTest->First();
n = tabTest->RecordCount;
if (n == 1)
{
tabTest->Edit();
tabTest->FieldByName("CID")->AsInteger = 1;
tabTest->Post();
}
}

[bork]

Hello

Please provide us the DDL script for creating the tables that are used in your sample.

[RichTat]

Hello bork

I use Microsoft Access 2003 to create the tables and to manipulate them eg deleting records for repeated testing, adding new fields and deleting unwanted fields etc. during development.

Should I use a script instead and try again ?

[bork]

The LockMode property is set to lmOptimistic by default and before editing TUniTable try to lock the record. Your field CID has default value 0 and TuniTable.Options.DefaultValues is False. So the default value was inserted into the database, but TUniTable doesn't know about it. As a result TUniTable cannot create the WHERE clause correctly. I can offer the following ways to resolve this issue:
- You can set the LockMode property to lmNone
- You can change the default value of the CID field to Null in the MS Access database
- You can set Options.UpdateAllFields to True
- You can set RefreshOption.roBeforeEdit to True
You can use any of these ways to resolve the issue.

[RichTat]

Hello bork

Yes - thank you - the RefreshOption.roBeforeEdit to True seemed to be the logical one to use and that worked.
I have other problems with ODBC syntax errors which I tracked down to illegal field names (Date, etc. - do I ever learn !)
The ODBC trace mechanism is not very helpful in determining the exact causes of problems.
Is there a way of monitoring in the client program the SQL statements submitted by the UniDAC component to the database servers and/or ODBC channel ? This would give an quick and easy method of analysing errors.

[bork]

Hello

You can use TUniSQLMonitor to get the list of SQL queries that were sent to a database. You should add the OnSQL event handler:

Code: Select all

procedure TForm1.UniSQLMonitor1SQL(Sender: TObject; Text: String;
  Flag: TDATraceFlag);
begin
  Memo1.Lines.Add(Text);
  Memo1.Lines.Add('---');
end;

You should quote field names like "Date" in the queries and set the Optopns.QuoteNames option to True to generate correct update queries.