preventing duplicate keys?

[jim enright]

using a vc++ gui to interact with an MSAccess db. uisng try/catch
blocks to catch exceptions like inserting duplicate yr,mo,day combos
into the table.

the db squawks but the bogus record is entered into the db, thus requiring,
manual deletion.

attempts to set a flag that is tripped should the catch block be called
and the go back to delete the record also fail since the
db wont allow operations on the bad record that i am trying to
prevent getting in in the first place.


how can i prevent records with detectable errors from getting in?

using ADO.

thanks in advance.

j

[DanielaTm]

Why don't you create an unique index on database? it certainly prevents duplicate keys

[jim enright]

the table is indexed no dupes by yr,mo,day. the problem is that when i programattically attempt to add a new record with the same
yr, mo, day Access supplants the day with 0 and adds a record known by
Access to be incorrect.

additionally, i kknow beforehand from the try/catch block that the
Access table knows this is record violates the table rules but then
changes my data so it can insert the record. This is whacked.

if the record violates the rules it should not be entered and Access should
not be making any attempts to change data and enter it.

my attempts to programaticaly undo the damage after the fact are then
frustrated by MSAccess saying it will not allow any operations (ie delete)
on a record that violates the rules.

i need a methodology to prevent the record getting in in the first place.


thanks - j

[TheCPUWizard]

This should NOT be happening.

1) Can you post a minimal yet complete sample that illustrates this issue?

2) What versions of VC and Access are you using? Have you applied all appropriate updates?

[davide++]

Hi all.

If you add an unique index on table (as Daniela said) Access won't be able to insert a record with same value, and will throw an exception that you can manage in try/catch block.

Another simple way is execute a SELECT COUNT(*) query to check if record exists in table before to perform INSERT command: in this case you won't execute the INSERT.

[jim enright]

thanks for the repsonses - will try to replicate problem outside of app.

trying like hell to avoid running an sql prequery simply because
the point is that the try/catch clocks should be able to detect and correct.

talk to you in a bit.

thanks - j

[TheCPUWizard]

Another simple way is execute a SELECT COUNT(*) query to check if record exists in table before to perform INSERT command: in this case you won't execute the INSERT.


But will totally fall apart in a multi user scenario (which should almost always be designed for), unless you start using transactions (which are not "really" supported by Access, and even in that case will incude significant overhead.

Much better to debug the existing problem than to try to hide it....

[davide++]

But will totally fall apart in a multi user scenario (which should almost always be designed for), unless you start using transactions (which are not "really" supported by Access, and even in that case will incude significant overhead.

Much better to debug the existing problem than to try to hide it....

Hi all
You don't hide a problem using COUNT(*), simply check if there's an inconsistent situation and avoid it.
An alternative (and valid) way is using the exception mechanism, but I think both techiques have good points and bad points.
In my opinion query makes code more readable, because one can do control and action "in the same place": using exception, one "breaks" control and action in two points in the code. Well, this is a simple situation, and when INSERT fails probably one wants show an error message and stop process; but in more complex cases one would perform other operations on db (for example, to log the error into another table), and what happens if these operations fail? One must manage the exception within an exception...
Exceptions are very useful - are the best thing - to catch and manage unexpected errors (of course, they're very rare using Access).

[TheCPUWizard]

You don't hide a problem using COUNT(*), simply check if there's an inconsistent situation and avoid it.


The problem is that if you do the COUNT(*) before attempting the insert, then two people can still overlap, and you will end up with duplicate data (although it will be rarer, which is why I refer to it as hiding the problem, it may be weeks or months before it occurs.

If you do the COUNT(*) after then the data it still corrupt, and there is no repair (according to the OP).

IIRC, there was a bug of this nature in older versions of Access, and a HotFix was published. This should have been fixed in current versions. (I still have not seen what version is in use....).

[davide++]

The problem is that if you do the COUNT(*) before attempting the insert, then two people can still overlap, and you will end up with duplicate data (although it will be rarer, which is why I refer to it as hiding the problem, it may be weeks or months before it occurs.

If you do the COUNT(*) after then the data it still corrupt, and there is no repair (according to the OP).

IIRC, there was a bug of this nature in older versions of Access, and a HotFix was published. This should have been fixed in current versions. (I still have not seen what version is in use....).

Well.

Obviusoly perform the query after INSERT insn't very usefull... it's definitely unnecessary.

OK, it's true, but you won't solve this problem using exception instead of SELECT COUNT(*): in both cases the first wins and the second crashes. As you said, this situation is rarely, extremely rarely in my opinion if the two operations will executed in sequence (this isn't true for very complex systems, with hundred of user), so in general I can consider this type of error as an unexpected error, and manage it with exceptions: the important thing is that the unique index must be defined, because it assures database integrity, in any case.
I didn't say that exceptions are unnecessary, they should be always defined in good code.

The safiest way to avoid this problem is to generate the keys automatically, using specific database objects, such as auto-incremented fields or sequences.

[jim enright]

i have isolated and replicated the problem in a new Dialog based app named TestAccess

i have modified the TestAccessDlg.h file as follows:

// AccessTestDlg.h : header file
//

#define INITGUID
#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF", "ADOEOF")
#include "icrsint.h"

// Function declarations
inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};

class CAddRecord : public CADORecordBinding{
BEGIN_ADO_BINDING(CAddRecord)
ADO_VARIABLE_LENGTH_ENTRY2(4, adVarChar, Key1,sizeof(Key1), Key1Status, TRUE)
ADO_VARIABLE_LENGTH_ENTRY2(4, adVarChar, Key2,sizeof(Key2), Key2Status, TRUE)
END_ADO_BINDING()

public:
CHAR Key1[2];
ULONG Key1Status;
CHAR Key2[2];
ULONG Key2Status;
};

// CAccessTestDlg dialog
class CAccessTestDlg : public CDialog
{ ....


i have modified the TestAccessDlg::OnInitDialog() fn :

// TODO: Add extra initialization here

_variant_t vtEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR);
_bstr_t bstrEmpty(L"");
_bstr_t bstrConnect(L"");

CoInitialize(NULL);

try{
//bstrConnect = L"dsn=OPTIMPricingProd;uid=Admin;";
//_bstr_t bstrConnect( L"dsn=OPTIMPricingProd;uid=Admin;" );
//_bstr_t bstrConnect( L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=res\\Test.mdb;User Id=Admin;");
bstrConnect = L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\CDS_OPTIM_4_0\\PricingFiles\\PricingFile.mdb;User Id=Admin;";

TESTHR(hr = DBConn.CreateInstance( __uuidof( Connection ) ));

DBConn->ConnectionString = bstrConnect;
hr = DBConn->Open( bstrEmpty, bstrEmpty, bstrEmpty,-1 );

TESTHR(hr = DBCmd.CreateInstance( __uuidof( Command ) ));
DBCmd->ActiveConnection = DBConn;

TESTHR(hr = pRecordSet.CreateInstance( __uuidof( Recordset ) ));

pAddRecord = NULL;
if (FAILED(pRecordSet->QueryInterface(__uuidof(IADORecordBinding),(LPVOID*)&pAddRecord))){
//if (FAILED(AccessDBManager.RecordsetPtr->QueryInterface(__uuidof(IADORecordBinding),(LPVOID*)&pAddRecord))){
_com_issue_error(E_NOINTERFACE);
}
pAddRecord->BindToRecordset(&AddRecord);

// Set up the Command.
DBCmd->CommandText =
(_bstr_t)"Select * from PostalRate";
DBCmd->CommandType = adCmdText;

pRecordSet->putref_ActiveConnection(DBConn);
pRecordSet->putref_Source(DBCmd);
pRecordSet->Open(vtEmpty,vtEmpty,adOpenStatic,adLockOptimistic,adCmdText);

// now demo a few basic operations
pRecords = &Records;
pRecordSet->get_RecordCount(pRecords);

pRecordSet->AddNew();
pRecordSet->MoveLast();

vName.SetString("Use");
s = "-1";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

vName.SetString("RateY");
s = "2007";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

vName.SetString("RateM");
s = " 3";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

vName.SetString("RateD");
s = "22";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

vName.SetString("RateInit");
s = "0.41";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

vName.SetString("WeightInit");
s = "28.00";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

vName.SetString("RateIncr");
s = "0.41";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

vName.SetString("WeightIncr");
s = "28.00";
charstar = s;
vValue.SetString(charstar);
pRecordSet->Update(vName,vValue);

}
catch( _com_error &e ){
// basic error handling
_tprintf(_T("Exception!\n"));
_tprintf(_T("\a\tCode = %08lx\n"), e.Error());
_tprintf(_T("\a\tCode meaning = %s\n"), e.ErrorMessage());
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
_tprintf(_T("\a\tSource = %s\n"), (LPCTSTR) bstrSource);
_tprintf(_T("\a\tDescription = %s\n"), (LPCTSTR) bstrDescription);
CString s("?");
MessageBox(bstrDescription,s,MB_OK);
}

return TRUE; // return TRUE unless you set the focus to a control

the problem is replicated in that there is a single record in the table
tos start with ratey = 2007, ratem = 3, rated = 22.

exceptions are thrown from the update function the starting from
where the attempt is made to set rated to 22. all fields thereafter
are set to 0. but the record is added with rated changed to 0.

if i attempt to manually attempt to cut and paste the single original
record, Access will not allow me to do anything. if i close the table
it will force the deletion of the record.

appreciate any insight - i need access to behave as if i were interactively using it. it should not change data i am attempting to insert.

thanks - j

[TheCPUWizard]

Can you do a "Clean" on the project directory (get rid of .obj,.exe,.sbr, etc) and zip up the results??? Also zip and post the mdb file (a small one)

Also I still have note seen the versions (but I might have missed a post).

[jim enright]

my bad - using vc++2005 and MSAccess 2003.

not sure i can zip anything but the .h and .cpp files for
the diagnostic app are very small - not much more than what
i posted.

will copy the access db and strip out all tables except postal
rate that would be very small also.

how would i attach these?

thanks - j

[TheCPUWizard]

Go down below the Reply to Thread box to Additional Options, there is an area for "Manage Attachments"

Just having the project/solution files etc will save some time in setting up the repro.... (trying to help as many people as possible...)

[jim enright]

attached the AccessTestDlg.h and .cpp files - the single table, single record
access db somehow takes up 192K > 100K limit.

will get a hold of some zip utility and get that loaded.

thanks for your help. let me know if there are other c++ files you need.

j

[TheCPUWizard]

Either that or e-mail it to me:

dvcorbin "at" optonline "dot" net

I will take a look at it later tonight, getting ready to go out to dinner and a show.....

[jim enright]

okay have zipped both the project (misppelled as procect.zip) and the access db (MSAccess.zip).

there is an AccessDBManager.cpp file that is in there but not needed

hope this helps

[jim enright]

MS had a great suggestion - give them $245 dollars and hope they
agree its a bug and so they will give it back.

on a more serious note, installing a validation rule on RateD to
be > 0 prevents the inclusion of an erroneous record but
the designation of y,m,d as a unique key ought to be sufficient.

if its sufficient in interactive mode it has to be sufficient for
transactional purposes.

[jim enright]

still looking for answer to this and more general problem stated below:

using vc++2005/ADO and msaccess 2003.

whenver i attempt to move data from a GUI and add a new record to a table
i use a try/catch block. i found this to be excellent in getting explanations
back from msaccess as to why a candidate record will not be allowed in: eg:
duplicate keys, wrong data type, violation of table rules, etc.

the problem is that attempts to correct the candidate record fail. my
uninformed guess is that the following takes place:

access handles the first attempt to add a new record by placing the
info in an edit buffer, runs its integrity checks, and squawks if there are
problems - but does not remove the record from the edit buffer.

further attempts are frustrated because they are viewed by msaccess
as attempts to do other things without rectifying the first problem - the bad data in the edit buffer.

is there a way to remove the edit buffer record? if so i could use the
the try/catch block to undo the attempted load and then
attempt new data.

thanks - j