Savva, Annabelle
Hi, I hope someone might be able to help me...

I am having problems using stored procedures.

My aim is to be able synchronised data from our CRM System with Outlook. I decided to try contacts & companies details first using a view to populate Outlook and 2 stored procedures, 1 for inserts & 1 for updates. As I was not having much luck I have broken it down so my stored procedure is currently only looking to insert/update contacts records. Which unfortunately does not appear to be working how I would like. I am currently using the trial version, version 4-2000(40030).

Set-up is;
Mandatory Key is set to
DB Field=CON_ContactID
SQL Statement=Select max(CON_ContactID)+1 from Contacts

The statement in the SQL Commands for
{call usp_Out_AddContact (

{call usp_Out_UpdContact (
'@CON_Mobile','@CON_Comments', '@CON_EditedDte')}

The insert works fine when I exclude the CON_ContactID from both the stored proc and the SQL Command box, however when I then try to update the record, it insert a duplicate into the contacts table. If I leave the CON_ContactID field in then I get this error
Cannot Insert the Value NULL into a column CON_ContactID

Please can some point in the right direction...? Should I be including the CON_ContactID in the insert stored procedure? If not how do I prevent a duplicate record from being inserted?

Savva, Annabelle
Just clicked - the Mandatory Key was wrong...
the Identity was set to Yes instead of NO!

Please set Identity=Yes to Identity=No!

Identity=Yes means Database will generate a new key value, but, you are generating the value in your SQL statement
Select max(CON_ContactID)+1 from Contacts

IF you need database generated value:
-change your table column defintion to auto generate value
-set Identity=Yes but use for example, Select @@IDENTITY, to retrieve the value after insert
-remove also @CON_ContactID from your usp_Out_AddContact stored procedure when using DB generated values!
Similar Topics