ColdFusion - problem creating new entries.

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.

the-lan-manAsked:
Who is Participating?
 
danrosenthalConnect With a Mentor Commented:
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
 
the-lan-manAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.