Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1032
  • Last Modified:

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

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
larksys
Asked:
larksys
  • 10
  • 8
  • 3
  • +1
1 Solution
 
erikTsomikSystem Architect, CF programmer Commented:
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
 
larksysAuthor Commented:
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
 
HuyBDCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
erikTsomikSystem Architect, CF programmer Commented:
that exactly why is error in out.
For testing try to do this
  insert into Bird_Dog_Notes values (#indivnum#,#AdminID#,'#today#','#newnote#')
0
 
larksysAuthor Commented:
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
 
erikTsomikSystem Architect, CF programmer Commented:
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
 
larksysAuthor Commented:
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
 
erikTsomikSystem Architect, CF programmer Commented:
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
 
larksysAuthor Commented:
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
 
erikTsomikSystem Architect, CF programmer Commented:
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
 
larksysAuthor Commented:
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
 
erikTsomikSystem Architect, CF programmer Commented:
i think that this is the identity column problem. Can you try to do it on the sql backend
0
 
larksysAuthor Commented:
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
 
erikTsomikSystem Architect, CF programmer Commented:
good then
0
 
larksysAuthor Commented:
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
 
larksysAuthor Commented:
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
 
larksysAuthor Commented:
what happened? Give up?
0
 
erikTsomikSystem Architect, CF programmer Commented:
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
 
gdemariaCommented:
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
 
gdemariaCommented:
>  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
 
gdemariaCommented:
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
 
larksysAuthor Commented:
I found that too in the wee hours of the morning. Thanks so much.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now