Solved

sql cf  error - There are fewer columns in the INSERT statement than values specified in the VALUES clause.

Posted on 2008-10-02
22
971 Views
Last Modified: 2012-06-27
This HAS to be one of those simple things I just can't see;

<cfquery name="team" datasource="#request.dsn#">
      insert into Bird_Dog_Notes (Individual_Number,AdminID,Date_Entered,Note_Line)
      values (#indivnum#,#AdminID#,'#today#','#newnote#')
</cfquery>


 There are fewer columns in the INSERT statement than values specified in the VALUES clause.
0
Comment
Question by:larksys
  • 10
  • 8
  • 3
  • +1
22 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22631001
what is the structure of your table. You obviously have fewer columns in the query then you specified for the insert. Probably some primary key issue
0
 
LVL 1

Author Comment

by:larksys
ID: 22631052
The only other column in the table is an auto-increment id column. I don't understand how that has anything to do with the error message.
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 22631059
you should print out sql
<cfoutput>

insert into Bird_Dog_Notes (Individual_Number,AdminID,Date_Entered,Note_Line)

      values (#indivnum#,#AdminID#,'#today#','#newnote#')

</cfoutput>

<cfabort>

<cfquery name="team" datasource="#request.dsn#">

      insert into Bird_Dog_Notes (Individual_Number,AdminID,Date_Entered,Note_Line)

      values (#indivnum#,#AdminID#,'#today#','#newnote#')

</cfquery>

Open in new window

0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22631062
that exactly why is error in out.
For testing try to do this
  insert into Bird_Dog_Notes values (#indivnum#,#AdminID#,'#today#','#newnote#')
0
 
LVL 1

Author Comment

by:larksys
ID: 22631103
I don't understand why I would test by eliminating the column names, but I did and got the following message;

An explicit value for the identity column in table 'Bird_Dog_Notes' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I don't think generating my own auto-increment id to make the test work would solve my problem.

0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22631114
so then do this
<cfquery name="team" datasource="#request.dsn#">
set IDENTITY_INSERT Bird_Dog_Notes  ON
      insert into Bird_Dog_Notes
      values (#indivnum#,#AdminID#,'#today#','#newnote#')
set IDENTITY_INSERT Bird_Dog_Notes  OFF
</cfquery>
0
 
LVL 1

Author Comment

by:larksys
ID: 22631142
Same error message;

An explicit value for the identity column in table 'Bird_Dog_Notes' can only be specified when a column list is used and IDENTITY_INSERT is ON.

<cfquery name="team" datasource="#request.dsn#">
      set IDENTITY_INSERT Bird_Dog_Notes  ON
      insert into Bird_Dog_Notes
      values (5,#indivnum#,#AdminID#,'#today#','#newnote#')
      set IDENTITY_INSERT Bird_Dog_Notes Off                              
                        </cfquery>
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22631151
do not specify 5
<cfquery name="team" datasource="#request.dsn#">
      set IDENTITY_INSERT Bird_Dog_Notes  ON
      insert into Bird_Dog_Notes (Individual_Number,AdminID,Date_Entered,Note_Line)
      values (#indivnum#,#AdminID#,'#today#','#newnote#')
      set IDENTITY_INSERT Bird_Dog_Notes Off                              
                        </cfquery>
0
 
LVL 1

Author Comment

by:larksys
ID: 22631161
An explicit value for the identity column in table 'Bird_Dog_Notes' can only be specified when a column list is used and IDENTITY_INSERT is ON

<cfquery name="team" datasource="#request.dsn#">
      set IDENTITY_INSERT Bird_Dog_Notes  ON
                              insert into Bird_Dog_Notes
                              values (#indivnum#,#AdminID#,'#today#','#newnote#')
      set IDENTITY_INSERT Bird_Dog_Notes Off                              
                        </cfquery>
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22631207
try this
<cfquery name="team" datasource="#request.dsn#">
      set IDENTITY_INSERT Bird_Dog_Notes  ON
                              insert into Bird_Dog_Notes
                              values (#indivnum#,#AdminID#,'#today#','#newnote#')
                               
                        </cfquery>
0
 
LVL 1

Author Comment

by:larksys
ID: 22631238
Again;
An explicit value for the identity column in table 'Bird_Dog_Notes' can only be specified when a column list is used and IDENTITY_INSERT is ON

<cfquery name="team" datasource="#request.dsn#">
      set IDENTITY_INSERT Bird_Dog_Notes  ON
                              insert into Bird_Dog_Notes
                              values (#indivnum#,#AdminID#,'#today#','#newnote#')
                                    
                        </cfquery>

Is this leading somewhere? I'm not going to leave it at trying to insert an identity column. SQL will do that for me. The original error is telling me I have a mismatch between column names and values, which isn't true.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 19

Expert Comment

by:erikTsomik
ID: 22631246
i think that this is the identity column problem. Can you try to do it on the sql backend
0
 
LVL 1

Author Comment

by:larksys
ID: 22631264
INSERT INTO Bird_Dog_Notes
                      (Individual_Number, AdminID, Date_Entered, Note_Line)
VALUES     (1830, 24, '2008/10/03', 'test note line')

1      1830      24      2008/10/02      zzzzzzzz
2      1830      24      2008/10/02      tttttttttt
3      1830      24      2008/10/03      test note line

The last line is the one that was inserted (perfectly, including the id column).
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22631269
good then
0
 
LVL 1

Author Comment

by:larksys
ID: 22631334
huyBD: I get nothing by trying to output the query. I will try to create a test form with the query on it.

When I put the query on the same page as the form that normally would submit to the page that does the query, it works.
0
 
LVL 1

Author Comment

by:larksys
ID: 22631346
It's not making much sense. This is way too simple to fail.


The form;

<cfset attributes.suppresslayout2 = "true">

<link rel="stylesheet" type="text/css" href="/control/individuals/indiv.css" title="Global" />
 

<cfform name="BirdDog">

<div class="PanelContent">

                 <div id="BirdDogBox">

				<cfquery name="QGetAdminName" datasource="#request.dsn#">

				select *

				From admin_users

				where adminid = #adminid#

				</cfquery>

    <div class="PanelTab" tabindex="0">Bird Dog Notes for <cfoutput>#firstname# #lastname# by #QGetAdminName.UserName#</cfoutput></div> 

                    <cfgrid format="html" name="BirdDogGrid"  bind="url:panels/panelBirdDog_2.cfm?Indivnum=#indivnum#&page={cfgridpage}&pageSize={cfgridpagesize}&sortCol={cfgridsortcolumn}&sortDir={cfgridsortdirection}" bindonload="yes" width="450" selectonload="no" pagesize="8" selectmode="edit" onchange="cfc:panelBirdDog.editBirddogData({cfgridaction},{cfgridrow},{cfgridchanged},#indivnum#,#adminID#,{birddoggrid.note})" selectcolor="orange" insert="true">

                        <cfgridcolumn name="ID" display="no" />

                        <cfgridcolumn name="AdminID" display="no"/>

                        <cfgridcolumn name="Individual_number" display="no"/>

						<cfgridcolumn name="UserName" display=true header="Cons/Rec" width="70"  select="yes" />

                        <cfgridcolumn name="Date_Entered" display=true header="Date" width="90"  select="yes" />

                        <cfgridcolumn name="Note_Line" display=true header="Note" width="110" select="yes"/>

                    </cfgrid>

                    

                </div>

				<br/>

				New Note<br/>

				<cfinput name="indivnum" value="#indivnum#" type="text">

				<cfinput name="indivnum" value="#adminid#" type="text">

				<cfinput name="newnote" type="text" >

		<cfinput type="button" name="submit" value="Submit Changes" onclick="javascript:submitBirdDogFrm();">	

		

		

			

</cfform>	
 
 

The code that opens the window;

<cfwindow x="120" y="100" width="825" height="600" 

			name="BirdDog" minHeight="400" minWidth="400" 

			title="Bird Dog Notes" initshow="false" 

			source="panels/panelBirdDog.cfm?indivNum=#indivNum#&firstname=#users_Info.firstname#&lastname=#users_info.lastname#" 

			headerstyle="background-color:##ff0000; border-color:##FF0000;" bodystyle="border-color:##FF0000" />
 
 

The code that submits the form;
 

function submitBirdDogFrm(){

		ColdFusion.Ajax.submitForm('BirdDog','panels/panelBirdDog_db.cfm', birddogcallback, errorhandler);

	}

function birddogcallback(text)	{

		ColdFusion.Window.hide('BirdDog');
 

The action page panelBirdDog_db

 

<cfset attributes.suppresslayout2 = "true">

<cfparam name="attributes.newnote" default="" />

<cfparam name="attributes.indivnum" default="" />

<cfparam name="attributes.adminid" default="" />

<cfset today = "#dateformat(now(),"yyyy/mm/dd")#" />
 
 

<cfif attributes.newnote gt "">

<cfoutput>

	#indivnum#,#AdminID#,'#today#','#newnote#'<br/>

	

<cfquery name="team" datasource="#request.dsn#">

					insert into Bird_Dog_Notes (Individual_Number,AdminID,Date_Entered,Note_Line)

					values (#indivnum#,#AdminID#,'#today#','#newnote#')

					

				</cfquery>

</cfoutput>
 
 

</cfif>

Open in new window

0
 
LVL 1

Author Comment

by:larksys
ID: 22633650
what happened? Give up?
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22633716
ok just for testing purposes to prove that i am correct , change the dataabse and do not make you ID column as a identiry column will see if it is working this way
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 22634828
This may be your problem... in your form, you have two fields named the same..

<cfinput name="indivnum" value="#indivnum#" type="text">
<cfinput name="indivnum" value="#adminid#" type="text">

The name for both fields is "indivnum"
If you enter a value for each one, let's say AAAA for the first field and BBBB for the second, then the value of form.indivnum will be AAAA,BBBB   and when it goes into the sql statement it will have an extra comma which will make SQL think you have entered two many values.

Moving forward,
You should probably put some safe guards around the insert statement such as...

  values (#indivnum#,#AdminID#,'#today#','#newnote#')

could be...
 values (
     <cfif indivnum is "">NULL<cfelse>#val(indivnum)#</cfif>
     ..etc...

That will protect it if the variable is empty or not a number.



0
 
LVL 39

Expert Comment

by:gdemaria
ID: 22634865
>  I don't understand why I would test by eliminating the column names, but I did and got the following message;

> An explicit value for the identity column in table 'Bird_Dog_Notes' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The reason you got this error after removing the column names is because when you don't specify the column names, the columns are lined up in order.   The first field of your table is the identity column, so now the first value of your insert is trying to insert into the identity column, which is obviously not what you wanted to do..

Once you successfully turned off identity input, you would just be inserting the wrong value into the wrong fields.


0
 
LVL 39

Expert Comment

by:gdemaria
ID: 22634917
Erik, you should always specify your column names when doing an insert.  If you don't, then any change to the database table (whether changing the order of the fields or just adding a field) will immediately break all your insert statements.
0
 
LVL 1

Author Closing Comment

by:larksys
ID: 31502655
I found that too in the wee hours of the morning. Thanks so much.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 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

11 Experts available now in Live!

Get 1:1 Help Now