I wasn't trying to insert into an identity field. SQL was complaining that I was and I have no idea why.
Turns out the Sysadmin moved the database to a different ip address. Didn't tell me yet. He assures me he was GOING to! So I was accessing the old database with my Query Analyser, fixing problems, setting up tables etc, but I wasn't making any changes on the database that ColdFusion was accessing. So no matter what I did, thinking I was fixing the problem, I did nothing of the kind. No wonder everything I did made no difference. I'd set up the table in this example with a teensy error in it, and just at that moment the Sysadmin moved the ip addresses to the new location. So I couldn't correct the error, even though I thought I was. Thanks for the helpful suggestions, but I bet noone had anything close to that in mind! Cheers, Mike Kear Windsor, NSW, Australia AFP WebWorks -----Original Message----- From: Christopher Olive [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 02, 2002 1:27 AM To: CF-Talk Subject: RE: CF Can't insertinto SQL2K ... any ideas? you shouldn't be inserting into an IDENTITY key, really. let the DB generate the ID. what's the SQL you're using? christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -----Original Message----- From: Mike Kear [mailto:[EMAIL PROTECTED]] Sent: Saturday, March 30, 2002 11:07 AM To: CF-Talk Subject: CF Can't insertinto SQL2K ... any ideas? My server's using CF5 and SQL2K. I can insert into a table using the query analyser, and I can have CF query the datasource no problem. But when it goes to insert into the database, using the same SQL I used in the query analyser, it hiccups. I get a error messages as follows: [quote] ODBC Error Code = 23000 (Integrity constraint violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'memberid', table 'btcmsa.dbo.Newsletter'; column does not allow nulls. INSERT fails. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (9:1) to (9:99). [/quote] Here's the SQL I used to set up the table: [quote] CREATE TABLE [Newsletter] ( [memberid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) NULL , [email] [varchar] (255) NULL , [emailtype] [int] NULL , [messagesentwhen] [datetime] NULL , [messagesent] [bit] NOT NULL ) ON [PRIMARY] GO [/quote] The field it's complaining about, "memberid" is a primary key. The SQL in the CFQUERY tag works fine in query analyser. I have tried it with and without username, password, dbtype in the CFQUERY tag. IS this the problem?? I'm thinking the problem is the SERVER doesn't have permissions to insert into the database - it needs to be set up as database owner and hasn't been. Which means I have to focus the SysAdmin's eye on it and that's not easy at Easter time. IS there anything else I should look for? Something I might have overlooked? Cheers, Mike Kear Windsor, NSW, Australia AFP WebWorks ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
