Error executing database query when inserting record

Page 1 of 1

2 Replies - 8032 Views - Last Post: 07 September 2007 - 02:02 PM Rate Topic: -----

#1 lori0502  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 04-September 07

Error executing database query when inserting record

Posted 04 September 2007 - 01:44 PM

I have inherited code that I thought was working. I now have a user who reported a problem using the insert record page. Sure enough, when you use the insert record, the error message states:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

The error occurred in D:\WebSites\Intranet\updates\sunterra\fsubcats\insert.cfm: line 48

46 : <cfif IsDefined("FORM.SUBHEADIMG") AND #FORM.SUBHEADIMG# NEQ "">
47 : , SUBHEADIMG='#filename_SUBHEADIMG_path#'
48 : </cfif>
49 :
50 : )

--------------------------------------------------------------------------------

SQL INSERT INTO FOUNTAINSSUBCATS (SUBCAT, BLURB, SORDER, SHOW, IMG, SUBHEADIMG) VALUES ( 'NewCategory' , 'Blurb' , '9' , 'NO' , IMG='retailOn.gif' )
DATASOURCE Sunterra
VENDORERRORCODE -3520
SQLSTATE 21S01

Please try the following:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.


Browser Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 1.1.4322)
Remote Address 10.10.45.52
Referrer http://intranet.lrne...cats/insert.cfm
Date/Time 04-Sep-07 03:41 PM

Stack Trace (click to expand)
at cfinsert2ecfm2020229437.runPage(D:\WebSites\Intranet\updates\sunterra\fsubcats\insert.cfm:48) at cfinsert2ecfm2020229437.runPage(D:\WebSites\Intranet\updates\sunterra\fsubcats\insert.cfm:48)


java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
at macromedia.sequelink.ssp.Diagnostic.toSQLException(Unknown Source)
at macromedia.sequelink.ssp.Chain.cnvDiagnostics(Unknown Source)
at macromedia.sequelink.ssp.Chain.decodeDiagnostic(Unknown Source)
at macromedia.sequelink.ssp.Chain.decodeBody(Unknown Source)
at macromedia.sequelink.ssp.Chain.decode(Unknown Source)
at macromedia.sequelink.ssp.Chain.send(Unknown Source)
at macromedia.sequelink.ctxt.stmt.StatementContext.execDirect(Unknown Source)
at macromedia.jdbc.sequelink.SequeLinkImplStatement.execute(Unknown Source)
at macromedia.jdbc.slbase.BaseStatement.commonExecute(Unknown Source)
at macromedia.jdbc.slbase.BaseStatement.executeInternal(Unknown Source)
at macromedia.jdbc.slbase.BaseStatement.execute(Unknown Source)
at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:212)
at coldfusion.sql.Executive.executeQuery(Executive.java:974)
at coldfusion.sql.Executive.executeQuery(Executive.java:886)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:233)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:447)
at cfinsert2ecfm2020229437.runPage(D:\WebSites\Intranet\updates\sunterra\fsubcats\insert.cfm:48)
at coldfusion.runtime.CfJspPage.invoke(Unknown Source)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:357)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:62)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:107)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:80)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:47)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.CfmServlet.service(CfmServlet.java:105)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:252)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192)
at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348)
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451)
at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

Here is the code for the page if anyone has time to review it.

<!-- KEYS: Sunterra, FOUNTAINSSUBCATS, SUBCAT, BLURB, SUBCATID, s_fsubcats -->
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfif isdefined("FORM.IMG") AND #FORM.IMG# NEQ "">
<cffile action="upload" filefield="IMG" destination= "d:\websites\sunterra\fountains\products\images\"  nameconflict="overwrite">
The file was successfully uploaded to <cfoutput>#cffile.serverDirectory#</cfoutput> 
<cfset filename_IMG_path=#cffile.serverFile#>
</cfif>

<cfif isdefined("FORM.SUBHEADIMG") AND #FORM.SUBHEADIMG# NEQ "">
<cffile action="upload" filefield="SUBHEADIMG" destination= "d:\websites\sunterra\fountains\products\images\"  nameconflict="overwrite">
The file was successfully uploaded to <cfoutput>#cffile.serverDirectory#</cfoutput> 
<cfset filename_SUBHEADIMG_path=#cffile.serverFile#>
</cfif>

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="Sunterra">
  INSERT INTO FOUNTAINSSUBCATS (SUBCAT, BLURB, SORDER, SHOW, IMG, SUBHEADIMG)
VALUES (
  <cfif IsDefined("FORM.SUBCAT") AND #FORM.SUBCAT# NEQ "">
	'#FORM.SUBCAT#'
	  <cfelse>
	  NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.BLURB") AND #FORM.BLURB# NEQ "">
	'#FORM.BLURB#'
	  <cfelse>
	  NULL
  </cfif>
	  ,
  <cfif IsDefined("FORM.SORDER") AND #FORM.SORDER# NEQ "">
	'#FORM.SORDER#'
	  <cfelse>
	  NULL
  </cfif>
	,
  <cfif IsDefined("FORM.SHOW") AND #FORM.SHOW# NEQ "">
	'#FORM.SHOW#'
	  <cfelse>
	  NULL
  </cfif>
		<cfif IsDefined("FORM.IMG") AND #FORM.IMG# NEQ "">
	, IMG='#filename_IMG_path#'
  </cfif>
  <cfif IsDefined("FORM.SUBHEADIMG") AND #FORM.SUBHEADIMG# NEQ "">
	, SUBHEADIMG='#filename_SUBHEADIMG_path#'
  </cfif>

  )
  </cfquery>
  <cflocation url="index.cfm">
</cfif>
<html><!-- InstanceBegin template="/Templates/intranet.dwt.cfm" codeOutsideHTMLIsLocked="false" -->
<head>
<!-- InstanceBeginEditable name="doctitle" -->
<title>Website Updates</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!-- InstanceBeginEditable name="head" -->
<!-- InstanceEndEditable -->
<link href="/intranet.css" rel="stylesheet" type="text/css">
</head>

<body bgcolor="#CCCCCC" leftmargin="0" topmargin="0">
<table width="700" border="0" cellspacing="0" cellpadding="0" align="center" class="body">
  <tr>
	<td width="700"  height="64" background="/images/intranetHeader.jpg" align="right" valign="top">	<cfif MM_Username neq ""><font color="#FFFFFF"><a href="/home/profile.cfm"  class="whiteLink"><cfoutput>#MM_Userfullname#</cfoutput></a> | <A href="/login.cfm?MM_logout=1" class="whiteLink">Logout</A>  </font></cfif><br><br><br>
</td>
  </tr>
  <tr valign="top">
	<td>
	<table width="699" border="0" cellspacing="0" cellpadding="0">
	  <tr valign="top">
		<td  height="450" width="140" bgcolor="#E0E0E0" align="left">
<!-- InstanceBeginEditable name="Nav" --><cfmodule template="../../../_tags/mainnav.cfm" maintopic="updates"><!-- InstanceEndEditable -->
		<table cellspacing="0" width="140">
		  <tr>
			<td class="navb script:document.form1.submit();"><u>Save</u></a></p>
  <table align="left">
	<tr valign="baseline">
	  <td align="right" class="colheader">SUBCAT:</td>
	  <td class="row"><input type="text" name="SUBCAT" value="" size="32"></td>
	</tr>
	<tr bordercolor="#000000" bgcolor="#EBEBEB" class="body">
	  <td class="colheader"><strong>BLURB:</strong></td>
	  <td class="row"><textarea name="BLURB" cols="49" rows="5"></textarea>
	  </td>
	</tr>
	<tr bordercolor="#000000" bgcolor="#EBEBEB" class="body">
	  <td class="colheader">IMG:</td>
	  <td class="row"><input name="IMG" type="file" id="IMG">
	  </td>
	</tr>
	<tr bordercolor="#000000" bgcolor="#EBEBEB" class="body">
	  <td class="colheader">SUBHEADIMG:</td>
	  <td class="row"><input name="SUBHEADIMGS" type="file" id="SUBHEADIMG">
	  </td>
	</tr>
	<tr bordercolor="#000000" bgcolor="#EBEBEB" class="body">
	  <td class="colheader">SORDER:</td>
	  <td class="row"><input name="SORDER" type="text" id="SORDER" size="32">
	  </td>
	</tr>
	<tr bordercolor="#000000" bgcolor="#EBEBEB" class="body">
	  <td class="colheader">SHOW:</td>
	  <td class="row"><select name="SHOW" id="SHOW">
	  <option value="YES">YES</option>
	  <option value="NO">NO</option>
	  </select>
</td>
	</tr>
  </table>
  <input type="hidden" name="MM_InsertRecord" value="form1">
</form>
<p> </p>

<!-- InstanceEndEditable -->
		</td></tr></table>
		</td>
	  </tr>
	</table></td>
  </tr>
  <tr>
	<td bgcolor="#000000"> </td>
  </tr>
</table>
</body>
<!-- InstanceEnd --></html>


Thank you for taking the time to review this. I have been tweaking and un-tweaking all afternoon. I can usually find the problem, but this one is working against me.

Is This A Good Question/Topic? 0
  • +

Replies To: Error executing database query when inserting record

#2 supersssweety  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 373
  • Joined: 16-March 07

Re: Error executing database query when inserting record

Posted 05 September 2007 - 11:43 AM

INSERT INTO FOUNTAINSSUBCATS
(SUBCAT, BLURB, SORDER, SHOW, IMG, SUBHEADIMG)
VALUES
( 'NewCategory' , 'Blurb' , '9' , 'NO' , IMG='retailOn.gif' )

You tell it to insert 6 things but only give it 5 values, this is due to your if statement. You need to make it insert SOMETHING if the value is not found. Like this:

<cfif IsDefined("FORM.SUBHEADIMG") AND #FORM.SUBHEADIMG# NEQ "">
	, SUBHEADIMG='#filename_SUBHEADIMG_path#'
<cfelse>
, ''
  </cfif>


or you could say '0' or 'NULL' or whatever you would like to display, if you want it to display nothing when it is quried later then just '' will work fine. Whatever you put in there make sure that the field is allowed to take that type of value, enuse that it can be null, or isn't an INT etc...

you should make an else like that for every if statement. The values have to add up with what you are trying to insert...let me know how it goes

This post has been edited by supersssweety: 05 September 2007 - 11:49 AM

Was This Post Helpful? 0
  • +
  • -

#3 lori0502  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 04-September 07

Re: Error executing database query when inserting record

Posted 07 September 2007 - 02:02 PM

Thank you for your response. Your answer showed me something I had been missing each time I had looked at the error. You mentioned that I tell it to insert 6 things but only enter 5. I did enter 6, but it only seems to take and refer to 5. So, I decided that I might have a table problem. The information that I was trying to add matched the table, but it wouldn't take it. I decided that maybe its column had grown weary (the tables are Access, a little weak for our needs). So, I retyped the info in the table with the exact same column header and saved it. The insert then took the info. Like there was a phantom character or something iimpeading my progress.

I did take your advice and added the 'NULL' info in case those that add records leave the image information blank. I am not sure why that was missing the first time but it is always good to have that.

Thank you so much for reviewing what my eyes were missing.
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1