Solved

ColdFusion - problem creating new entries.

Posted on 2004-08-11
2
190 Views
Last Modified: 2013-12-24
I am having trouble writing to a Microsoft SQL Server 2000 database with ColdFusion.  I am trying to INSERT a new entry into the database.  I have tried two alternate versions of this script.  Here's what I'm trying to do:

In both versions, there are three fields in this table: Category_ID, cName, and rep_id.  Category_ID is the primary key field, which is set to autonumber.

I have tried both of these with the autonumbering (and Primary Key setting) turned off and on.

VERSION A: Letting the auto-number do its thing

In this submission, I simply don't define the category_ID field and I submit the cName and rep_ID fields to the database, only updating those fields with the following script:

"<CFQUERY NAME="AddHelpDesk"
    DATASOURCE="testhelpdesk">
    INSERT INTO categories (cname, rep_id)
    VALUES ('#Form.cName#', '#Form.rep_id#')
</CFQUERY>"

That gives me this ERROR:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'category_id', table 'master.dbo.categories'; column does not allow nulls. INSERT fails.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (21:1) to (22:30)."



VERSION B: Coding a new ID number

In the form, I have a hidden field "category_ID" which is set to "0".  When the form submits to the database, I then grab the number of existing entries in the category_ID field, then add +2 to the category_ID field from the form (<cfset #form.category_id# = #getHelpDeskIDs.recordcount# + 2>).

I then insert it to the database with this script:

"<CFQUERY NAME="AddHelpDesk"
    DATASOURCE="testhelpdesk">
    INSERT INTO categories (category_id, cname, rep_id) VALUES ('#form.category_id#',
    '#Form.cName#', '#Form.rep_id#')
    </CFQUERY>"

That gives me this ERROR:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__categories__009FF5AC'. Cannot insert duplicate key in object 'categories'.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (21:1) to (22:30)."

This happens, whether I have the Primary ID Key set to the "category_ID" field or not.

0
Comment
Question by:the-lan-man
2 Comments
 
LVL 15

Accepted Solution

by:
danrosenthal earned 125 total points
ID: 11777639
You need to isolate the problem, so start with a simple insert where you have no variables:

INSERT INTO categories (category_id, cname, rep_id) VALUES (1000,'this is my cname',123)

and see if that works. One guess is that you are inserting incorrect values (your cfset of the category_id looks wrong to me)  or trying to insert numeric values into non-numeric fields (Don't use single quotes around numeric field values)

0
 

Author Comment

by:the-lan-man
ID: 11777979
Thanks!

I think I got it.  That was part of the problem.  The other part was I kept trying to insert a duplicate key (as the error said) and it wasn't adding correctly.  It seems to be going smoothly at the moment.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now