11-22-2008 06:09 AM
Hello, I'm trying to create a new Contact programmatically. The company i work for does all development in php. Ive been able to pull and update data but I have been getting this error when i attempt to insert a new contact into the contact table.
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting from a character string to uniqueidentifier.
Is there any way to have ACT create the 'createuserid' for me through an insert statement? I'm hoping that someone can shed some light on this for me.
11-22-2008 06:24 AM
I tried putting newid into some of the unique identifier fields and for the moment got passed some errors but this one has stumped me.
Here is my SQL so far
INSERT INTO TBL_CONTACT (CONTACTID,LASTNAME, FIRSTNAME, CATEGORY, CUST_Customer_070302640, COMPANYNAME, CONTACTWEBADDRESS, CREATEDATE, CREATEUSERID, MANAGEUSERID, ISIMPORTED, ISPRIVATE, TYPENUM) VALUES (newid(),'********','****','Customer - ******-****','99999','Silvia Pediatric Center', 'www.*******.com', CURRENT_TIMESTAMP,newid(),newid(),0,0,0 )
I get this error below:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'CONTACT_MANAGEUSERID_FK'. The conflict occurred in database 'Mednet_Database', table 'TBL_ACCESSOR', column 'ACCESSORID'.
Do i need to insert into TBL_ACCESSOR first before inserting into contact?
11-22-2008 07:55 PM
Direct insertion of data into the tables is not recommended or supported, it is quite possible you will insert enough data to satisfy the database's foreign key constraints yet not get the desired results in ACT!. I would suggest you try accessing the Act Framework via COM Interop so you can use it directly from PHP and go in via the front door. I'm not a PHP guy, there may be an easier way to consume .NET assemblies from PHP so look around. While you may have to learn a few things about ACT! SDK development you might prefer to not spend time learning, it will save you a bit of time over figuring out what values the database needs, not to mention isolating your code from ACT! schema changes going forward.
11-24-2008 09:59 AM
Alex - I strongly echo TFaust's comments about direct SQL insertion of data. You're in unwarranted waters and in EULA violation of connecting to the ACT7 SQL Server instance. ACT! has a published SDK for a reason, and the attempts you're making at writing to the database directly have you essentially swimming upstream.
Take a look at the ACT! SDK via: http://www.act.com/partners/addonpartners/sdk/index.cfm
ACT! Development Team