Sporatic Insert Into error

[jdeline]

I have e-mailed Macromedia regarding this issue, but have always received good
advice here.

The SQL INSERT statement below causes the error shown when the value for
membership is Active, Retired or Spouse, but does not cause an error when the
value for membership is Student or Booster. This occurs on their server at
Rackspace.com, but runs OK for all memberships at
[url]http://www.texasbandmasters.org[/url] at Dantor.com

You can reproduce the error by going to
[url]http://72.3.133.220/pages/new_regFront.cfm[/url], selecting Active from Member Type,
leave the Member ID field blank and clicking Register. Complete the
registration form and click the Register button. The error will occur prior to
the credit card being processed.

Any ideas? (Hope the dump below does not blow the forums :-( )



Error Occurred While Processing Request
Error Executing Database Query.
Syntax error in INSERT INTO statement.

The error occurred in D:\inetpub\wwwroot\pages\processReg.cfm: line 402
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 389
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 224
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 214
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 1
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 402
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 389
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 224
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 214
Called from D:\inetpub\wwwroot\pages\processReg.cfm: line 1

400 : INSERT INTO members (regDate, #varList#)
401 : VALUES
402 : ('#regDate#', #PreserveSingleQuotes(insertVarValues)#)
403 : </CFQUERY>
404 : <CFELSE>




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

SQL INSERT INTO members (regDate,
membership,memberId,prefix,firstName,middleInitial ,lastName,suffix,comp,boardMem
ber,boardYear,pastPres,presYear,pastBandmaster,ban dYear,honorary,homeAddress,apt
No,homeCity,homeState,homeZip,homePhone,email,scho ol,isd,schoolAddress,schoolCit
y,schoolState,schoolZip,schoolPhone,schoolExt,scho olFax,memberPos,profAffiliatio
ns,noChildBadges,provideEmail) VALUES ('050326',
'Active','27748320','','Jim','','DeLine','','','', '','','','','','','5843
Spring Valley','','San Antonio','TX','','','','Madison','','','San
Antonio','TX','','','','','','','','')
DATASOURCE texasbandmaster871
VENDORERRORCODE 3092
SQLSTATE &nbsp;

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 6.0; Windows NT 5.0)
Remote Address 24.28.150.38
Referrer [url]https://72.3.133.220/pages/registration.cfm[/url]
Date/Time 26-Mar-05 09:42 AM

Stack Trace (click to expand)
at
cfprocessReg2ecfm1024621019._factor7(D:\inetpub\ww wroot\pages\processReg.cfm:402
) at
cfprocessReg2ecfm1024621019._factor12(D:\inetpub\w wwroot\pages\processReg.cfm:38
9) at
cfprocessReg2ecfm1024621019._factor13(D:\inetpub\w wwroot\pages\processReg.cfm:22
4) at
cfprocessReg2ecfm1024621019._factor14(D:\inetpub\w wwroot\pages\processReg.cfm:21
4) at
cfprocessReg2ecfm1024621019.runPage(D:\inetpub\www root\pages\processReg.cfm:1)
at
cfprocessReg2ecfm1024621019._factor7(D:\inetpub\ww wroot\pages\processReg.cfm:402
) at
cfprocessReg2ecfm1024621019._factor12(D:\inetpub\w wwroot\pages\processReg.cfm:38
9) at
cfprocessReg2ecfm1024621019._factor13(D:\inetpub\w wwroot\pages\processReg.cfm:22
4) at
cfprocessReg2ecfm1024621019._factor14(D:\inetpub\w wwroot\pages\processReg.cfm:21
4) at
cfprocessReg2ecfm1024621019.runPage(D:\inetpub\www root\pages\processReg.cfm:1)


com.inzoom.adojni.ComException: Syntax error in INSERT INTO statement. in
Microsoft JET Database Engine code=3092 Type=1
at com.inzoom.ado.Command.jniExecute(Native Method)
at com.inzoom.ado.Command.execute(Command.java:40)
at com.inzoom.jdbcado.Statement.exec(Statement.java:3 4)
at com.inzoom.jdbcado.Statement.execute(Statement.jav a:107)
at coldfusion.server.j2ee.sql.JRunStatement.execute(J RunStatement.java:212)
at coldfusion.sql.Executive.executeQuery(Executive.ja va:974)
at coldfusion.sql.Executive.executeQuery(Executive.ja va:886)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:229)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.j ava:447)
at
cfprocessReg2ecfm1024621019._factor7(D:\inetpub\ww wroot\pages\processReg.cfm:402
)
at
cfprocessReg2ecfm1024621019._factor12(D:\inetpub\w wwroot\pages\processReg.cfm:38
9)
at
cfprocessReg2ecfm1024621019._factor13(D:\inetpub\w wwroot\pages\processReg.cfm:22
4)
at
cfprocessReg2ecfm1024621019._factor14(D:\inetpub\w wwroot\pages\processReg.cfm:21
4)
at
cfprocessReg2ecfm1024621019.runPage(D:\inetpub\www root\pages\processReg.cfm:1)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java :147)
at coldfusion.tagext.lang.IncludeTag.doStartTag(Inclu deTag.java:357)
at coldfusion.filter.CfincludeFilter.invoke(Cfinclude Filter.java:62)
at coldfusion.filter.ApplicationFilter.invoke(Applica tionFilter.java:107)
at coldfusion.filter.PathFilter.invoke(PathFilter.jav a:80)
at coldfusion.filter.ExceptionFilter.invoke(Exception Filter.java:47)
at coldfusion.filter.BrowserDebugFilter.invoke(Browse rDebugFilter.java:52)
at
coldfusion.filter.ClientScopePersistenceFilter.inv oke(ClientScopePersistenceFilt
er.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilt er.java:35)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilt er.java:43)
at coldfusion.filter.DatasourceFilter.invoke(Datasour ceFilter.java:22)
at coldfusion.CfmServlet.service(CfmServlet.java:105)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker. java:91)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvok erChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequ estDispatcher.java:252)
at jrun.servlet.ServletEngineService.dispatch(Servlet EngineService.java:527)
at
jrun.servlet.jrpp.JRunProxyService.invokeRunnable( JRunProxyService.java:192)
at
jrunx.scheduler.ThreadPool$DownstreamMetrics.invok eRunnable(ThreadPool.java:348)
at
jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRu nnable(ThreadPool.java:451)
at
jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeR unnable(ThreadPool.java:294)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java :66)

[Steve Sommers]

Nothing obvious with the SQL statement. Try posting the schema for the statement table -- it may be a type mismatch or a length problem.

[jorgepino]

('#regDate#', #PreserveSingleQuotes(insertVarValues)#)

Have to try adding single quotes to '#PreserveSingleQuotes(insertVarValues)#'

[webdr7]

Did this issue ever get resolved? i just migrated our applications from 5.0 to
MX 7 and am having the same issues spuradically. Most inserts work fine,
however there are a few that do not and the syntax appears right on.

Any help would be muchly appreciated