Solved

ColdFusion - problem creating new entries.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

786 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