Solved

ColdFusion - problem creating new entries.

Posted on 2004-08-11
2
195 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to Eclipse 16 132
Summernote required 3 191
change time in cron 4 89
DW 2017 background color change 5 9
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

820 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