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
981 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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