Savva, Annabelle
2008-01-24T17:14:00Z
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
Type=Primary
SQL Statement=Select max(CON_ContactID)+1 from Contacts
Identity=Yes

The statement in the SQL Commands for
Insert
{call usp_Out_AddContact (
'@CON_ContactID',1,'@CON_FName','@CON_LName','@CON_JobTitle',
'@CON_Depart','@CON_Addr1','@CON_Addr2','@CON_City','@CON_County',48,'@CON_Postcode','@CON_TeleNo','@CON_FaxNo','@CON_Email',
'@CON_Mobile','@CON_Comments')}

Update
{call usp_Out_UpdContact (
1,'@CON_FName','@CON_LName','@CON_JobTitle','@CON_Depart',
'@CON_Addr1','@CON_Addr2','@CON_City','@CON_County',48,
'@CON_Postcode','@CON_TeleNo','@CON_FaxNo','@CON_Email',
'@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
2008-01-24T17:49:00Z
Just clicked - the Mandatory Key was wrong...
the Identity was set to Yes instead of NO!
Administrator
2008-01-24T17:51:00Z
Hello,

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