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
989 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 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