Solved

ColdFusion - problem creating new entries.

Posted on 2004-08-11
2
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The purpose of this video is to demonstrate how to insert an Iframe into 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 : Open Page or Post…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

752 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