error on insert record form

Forum|Forum|15 years ago
August 16, 2010
1 reply
962 views

Hi,

I've created about a million insert record forms without any problems, but for some reason I keep getting the same error when I've tried creating the form on different pages, with different recordsets and whatever else I can think of, but I keep getting this error (here's the error and my code):

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where, when, whotocontact) VALUES ( 2 , 'stay' , 'mikew' , 'd' , 'h' ' at line 1

The error occurred in C:\ColdFusion9\wwwroot\mymindsnotrightcopy\supportgroupFormNeedsInclude.cfm: line 47

45 : , <cfif IsDefined("FORM.whotocontact") AND #FORM.whotocontact# NEQ "">
46 : <cfqueryparam value="#FORM.whotocontact#" cfsqltype="cf_sql_clob" maxlength="2147483647">
47 : <cfelse>
48 : ''
49 : </cfif>

<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery datasource="mymindsnotrighttest">
INSERT INTO supportgroups (supportgroup_id, supportgroup_name, user_id, username, description, howlong, state, where, when, whotocontact)
VALUES (<cfif IsDefined("FORM.supportgroup_id") AND #FORM.supportgroup_id# NEQ "">
<cfqueryparam value="#FORM.supportgroup_id#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, <cfif IsDefined("FORM.supportgroup_name") AND #FORM.supportgroup_name# NEQ "">
<cfqueryparam value="#FORM.supportgroup_name#" cfsqltype="cf_sql_clob" maxlength="65535">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.user_id") AND #FORM.user_id# NEQ "">
<cfqueryparam value="#FORM.user_id#" cfsqltype="cf_sql_numeric">
<cfelse>
NULL
</cfif>
, <cfif IsDefined("FORM.username") AND #FORM.username# NEQ "">
<cfqueryparam value="#FORM.username#" cfsqltype="cf_sql_clob" maxlength="65535">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.description") AND #FORM.description# NEQ "">
<cfqueryparam value="#FORM.description#" cfsqltype="cf_sql_clob" maxlength="9999">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.howlong") AND #FORM.howlong# NEQ "">
<cfqueryparam value="#FORM.howlong#" cfsqltype="cf_sql_clob" maxlength="65535">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.state") AND #FORM.state# NEQ "">
<cfqueryparam value="#FORM.state#" cfsqltype="cf_sql_clob" maxlength="65535">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.where") AND #FORM.where# NEQ "">
<cfqueryparam value="#FORM.where#" cfsqltype="cf_sql_clob" maxlength="2147483647">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.when") AND #FORM.when# NEQ "">
<cfqueryparam value="#FORM.when#" cfsqltype="cf_sql_clob" maxlength="2147483647">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.whotocontact") AND #FORM.whotocontact# NEQ "">
<cfqueryparam value="#FORM.whotocontact#" cfsqltype="cf_sql_clob" maxlength="9999">
<cfelse>
''
</cfif>
)
</cfquery>
<cflocation url="supportgroup_details.cfm?supportgroup_id=#FORM.supportgroup_id#">
</cfif>
<cfquery name="rsSupportGroups" datasource="mymindsnotrighttest">
SELECT *
FROM supportgroups
</cfquery>
<cfquery name="rsStates" datasource="mymindsnotrighttest">
SELECT *
FROM states
ORDER BY "state" ASC
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>My Minds Not Right -- An Online Support Group</title>
    <link rel="stylesheet" type="text/css" href="HTML/style.css">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css">
.blogcontent {
    font-size: 11pt;
    font-family: Tahoma, Geneva, sans-serif;
}

.comments {
    font-family: Tahoma, Geneva, sans-serif;
    font-size: 12px;
    font-weight: bold;
    text-decoration: underline;
    text-align: right;
    color: #246494;
}

.FeaturedBlogList {
font-family: Tahoma, Geneva, sans-serif;
font-size: 11px;
}

.FeaturedBlogListHeader {
    font-family: Tahoma, Geneva, sans-serif;
    font-size: 12px;
    font-weight: bold;
    color: #246494;
    /* [disabled]border-bottom-style: solid; */
}

.blogdate {
    font-size: 9pt;
    font-style: italic;
    font-family: Tahoma, Geneva, sans-serif;
}

.bloguserandwho {
    font-size: 10pt;
    font-style: normal;
    font-family: Tahoma, Geneva, sans-serif;
}
.redlink {
    font-family: Tahoma, Geneva, sans-serif;
    color: #F00;
    font-size: 10pt;
}

.blogtitle {
    font-size: 16px;
    font-weight: bold;
    font-family: Tahoma, Geneva, sans-serif;
    color: #246494;
}
.quoteStyle {
    font-family: Verdana, Geneva, sans-serif;
    font-size: 11pt;
    font-style: italic;
    color: #000;
}

.justified {
    text-align: justify;
}
</style>
<script type="text/javascript">
function MM_swapImgRestore() { //v3.0
var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a)&&x.oSrc;i++) x.src=x.oSrc;
}
function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a.indexOf("#")!=0){ d.MM_p=new Image; d.MM_p[j++].src=a;}}
}

function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d)&&d.all) x=d.all; for (i=0;!x&&i<d.forms.length;i++) x=d.forms;
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers.document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
if ((x=MM_findObj(a))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
</script>
</head>

<table border="0" cellspacing="0" cellpadding="0" width="100%" height="100%">
<tr>
    <td width="50%" background="images/bg.gif" valign="top"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
    <td valign="top" background="images/bg_left.gif"><img src="images/bg_left.gif" alt="" width="17" height="16" border="0">
    </td>
    <td valign="top">
      <table width="100%" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td width="780"></td>
          <td width="100%">
            <table width="780" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td><img src="images/logo01new.jpg" width="510" height="126"><img src="images/logo02clean.jpg" width="270" height="126" border="0"></td>
              </tr>
              <tr>
                <cfinclude template="topBar.cfm">
              </tr>
            </table>
          </td>
        </tr>
      </table>
      <cfinclude template="sideBar.cfm">
<p class="left">
<cfinclude template="includes/sidebarLinks.cfm">
<br>
</p>

          </td>
          <td rowspan="2" width="100%">
            <table border="0" cellspacing="0" cellpadding="0" width="100%">
              <tr>
                <td background="images/fon_right.gif"><img src="images/featuredblogs.jpg" width="322" height="35" alt="" border="0"></td>
                <td bgcolor="#B9B9B9"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
                <td background="images/fon_right.gif"> </td>
                <td bgcolor="#B9B9B9"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
                <td valign="top" rowspan="2" width="100%">
                  <table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                      <td width="2"><img src="images/2_f_2.gif" width="2" height="35"></td>
                      <td background="images/2_f_2_fon.gif" width="100%"><img src="images/topend.jpg" width="28" height="36"></td>
                    </tr>
                  </table></td>
              </tr>
              <tr valign="top">
                <td background="images/fon01.gif" style="background-position: top; background-repeat: repeat-x; font-size: 9px; font-family: Tahoma, Geneva, sans-serif; color: #000;"><table border="0" cellspacing="0" cellpadding="0" width="90%" background="" align="center">
                    <tr valign="top">
                      <td align="left"><h6><br>
                      </h6>
                        <table width="97%" border="0" cellspacing="0" cellpadding="0">
                          <tr>
                            <td width="44%"><span class="blogtitle">Support Groups:</span></td>
                            <td width="56%" align="right" class="redlink"><strong>start a group/add your group to list</strong></td>
                          </tr>
                        </table>
                        <p class="blogtitle"> </p>
<p class="blogcontent"> </p>
<p class="blogtitle"> </p>
<p class="blogtitle"> </p></td>
                    </tr>
                  </table>
                </td>
                <td bgcolor="#B9B9B9"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
                <td background="images/fon01.gif" style="background-position: top; background-repeat: repeat-x;">
                  <br></td>
                <td bgcolor="#B9B9B9"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
              </tr>
              <tr>
                <td background="images/fon02.gif" colspan="3"><img src="images/blogoftheweek.jpg" width="563" height="43" alt="" border="0"></td>
                <td bgcolor="#B9B9B9"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
                <td background="images/fon_right.gif" width="100%"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
              </tr>
              <tr>
                <td colspan="3" align="left" valign="top"><blockquote>
                  <p> </p>
                  <p> </p>
                  <p> </p>
                  <form method="post" name="form1" action="<cfoutput>#CurrentPage#</cfoutput>">
                    <table align="center">
                      <tr valign="baseline">
                        <td nowrap align="right">Supportgroup_name:</td>
                        <td><input type="text" name="supportgroup_name" value="" size="32"></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap align="right" valign="top">Description:</td>
                        <td><textarea name="description" cols="50" rows="5"></textarea></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap align="right" valign="top">Howlong:</td>
                        <td><textarea name="howlong" cols="50" rows="5"></textarea></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap align="right">State:</td>
                        <td><input type="text" name="state" value="" size="32"></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap align="right" valign="top">Where:</td>
                        <td><textarea name="where" cols="50" rows="5"></textarea></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap align="right" valign="top">When:</td>
                        <td><textarea name="when" cols="50" rows="5"></textarea></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap align="right" valign="top">Whotocontact:</td>
                        <td><textarea name="whotocontact" cols="50" rows="5"></textarea></td>
                      </tr>
                      <tr valign="baseline">
                        <td nowrap align="right"> </td>
                        <td><input type="submit" value="Insert record"></td>
                      </tr>
                    </table>
                    <input type="hidden" name="supportgroup_id" value="<cfoutput>#IncrementValue(rsSupportGroups.RecordCount)#</cfoutput>">
                    <input type="hidden" name="user_id" value="<cfoutput>#rsGetUserID.user_id#</cfoutput>">
                    <input type="hidden" name="username" value="<cfoutput>#Session.MM_Username#</cfoutput>">
                    <input type="hidden" name="MM_InsertRecord" value="form1">
                  </form>
                  <p> </p>
<p> </p>
                  <p> </p>
                  <p> </p>
                  <p> </p>
                  <p> </p>
                  <p> </p>
                  <p> </p>
                </blockquote>
                <p> </p>
                <p> </p>
                <p> </p>
                <p> </p></td>
                <td bgcolor="#B9B9B9"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
                <td>
                  <div align="left"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></div>
                </td>
              </tr>
            </table>
          </td>
        </tr>
        <tr bgcolor="#246494">
          <td valign="bottom">
            <table border="0" cellspacing="0" cellpadding="0" width="188" height="67" background="images/left_bot.gif">
              <tr>
                <td valign="bottom">
                  <p class="left" style="margin-bottom: 0px; margin-left: 20px;">Copyright
                    ©2010<br>
                    MyMindsNotRight.com<br>
                    All rights reserved</p>
                </td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
      <table border="0" cellspacing="0" cellpadding="0" width="100%" height="77" background="images/fon_bot.gif">
        <tr>
          <td valign="top" width="780">
            <cfinclude template="bottomBar.cfm"></td>
          <td><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
        </tr>
        <tr>
          <td><img src="images/px1.gif" width="780" height="1" alt="" border="0"></td>
          <td><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
        </tr>
      </table>
    </td>
    <td valign="bottom" background="images/bg_right.gif"><img src="images/bg_right.gif" alt="" width="17" height="16" border="0"></td>
    <td width="50%" background="images/bg.gif" valign="top"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td>
</tr>
</table>
</body>
</html>

This topic has been closed for replies.

Correct answer Dan_Bracuk

Once you get past the keywords, your next problem is that you have if/else logic in the values part of the query, but not the fields part. If anything returns false, you'll have more fields than values.

ilssac

Inspiring

wycks wrote:
INSERT INTO supportgroups (supportgroup_id, supportgroup_name, user_id,
username, description, howlong, state, where, when, whotocontact)

I stopped looking at this point.... but I can already see an error. "where" is a reserved word in SQL. You can't use it as a column name, at least not without escaping it. Your database is trying to start a where clause when it sees that command there, and goes this is not right.

Either escape the column name or change the name of the column to not be a SQL command word.

-

-__cfSearching__-

Inspiring

The same goes for the keyword "when".

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

D

Dan_BracukCorrect answer

Inspiring

Once you get past the keywords, your next problem is that you have if/else logic in the values part of the query, but not the fields part. If anything returns false, you'll have more fields than values.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded