Problem inserting record using INSERT INTO

Report · Nov 03, 2011

I am an amateur web builder using some ColdFusion functionality to access information on an Access database. I know very little about ColdFusion syntax, but I'm using Dreamweaver CS3 to help generate most of the code. I'm working on an insert record page to create a user database with login information. I'm not sure what the problem is, but I'm getting a syntax error referencing this particular portion of the code:

Syntax error in INSERT INTO statement.

The error occurred in C:\ColdFusion9\wwwroot\Everett\register.cfm: line 22

Below is the entire page with line 22 (referenced in the error message) in red. Any ideas?

<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "register">
<cfquery datasource="everettweb">
    INSERT INTO Users ([First Name], [Last Name], [Email Address], Password)
VALUES (<cfif IsDefined("FORM.first_name") AND #FORM.first_name# NEQ "">
<cfqueryparam value="#FORM.first_name#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.last_name") AND #FORM.last_name# NEQ "">
<cfqueryparam value="#FORM.last_name#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.email") AND #FORM.email# NEQ "">
<cfqueryparam value="#FORM.email#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.password") AND #FORM.password# NEQ "">
<cfqueryparam value="#FORM.password#" cfsqltype="cf_sql_clob" maxlength="255">
<cfelse>
''
</cfif>
)
</cfquery>
<cflocation url="register_success.cfm">
</cfif>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link href="main.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Everett Music Department, Everett, MA</title>

<style type="text/css">

</style>
<script src="SpryAssets/SpryMenuBar.js" type="text/javascript"></script>
<script type="text/javascript">
<!--
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_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_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>

<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />
<script src="SpryAssets/SpryValidationConfirm.js" type="text/javascript"></script>
<link href="SpryAssets/SpryValidationConfirm.css" rel="stylesheet" type="text/css" />

<table width="960" align="center" border="0" cellpadding="0" cellspacing="0">
<tr>
    <td colspan="3"><img src="top_border.jpg" width="960" height="20" align="top" /></td>
</tr>
<tr align="center">
    <td colspan="3"><a href="index.php"><img src="e_oval_top.jpg" height="100" width="270" border="0" /></a><a href="index.php"><img src="header.jpg" height="100" width="690" border="0" /></a></td>
</tr>
<tr>
    <td height="35" width="301"><a href="index.php"><img src="e_oval_bottom.jpg" height="35" width="234" border="0" /></a><a href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('home','','menu_home_over.jpg',1)"><img src="menu_home.jpg" width="67" height="35" name="home" border="0" id="home" /></a></td>
    <td width="251"><ul id="MenuBar1" class="MenuBarHorizontal">
      <li><a class="MenuBarItemSubmenu" href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('about','','menu_about_over.jpg',1)"><img src="menu_about.jpg" width="71" height="35" name="about" border="0" id="about" /></a>
        <ul>
          <li><a href="#">News</a></li>
          <li><a href="#">History</a></li>
          <li><a href="#">Media</a></li>
        </ul>
      </li>
      <li><a class="MenuBarHorizontal" href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('ensembles','','menu_ensembles_over.jpg',1)"><img src="menu_ensembles.jpg" width="98" height="35" name="ensembles" border="0" id="ensembles" /></a>
        <ul>
          <li><a href="#">Band</a></li>
          <li><a href="#">Chorus</a></li>
          <li><a href="#">Strings</a></li>
        </ul>
      </li>
      <li><a class="MenuBarItemSubmenu" href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('schools','','menu_schools_over.jpg',1)"><img src="menu_schools.jpg" width="82" height="35" name="schools" border="0" id="schools" /></a>
        <ul>
          <li><a href="#">Everett High School</a></li>
          <li><a href="#">English School</a></li>
          <li><a href="#">Keverian School</a></li>
          <li><a href="#">Lafayette School</a></li>
          <li><a href="#">Parlin School</a></li>
          <li><a href="#">Webster School</a></li>
          <li><a href="#">Whittier School</a></li>
        </ul>
      </li>
    </ul>
    </td>
           <td width="408"><a href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('events','','menu_events_over.jpg',1)"><img src="menu_events.jpg" width="74" height="35" name="events" border="0" id="events" /></a><a href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('faculty','','menu_faculty_over.jpg',1)"><img src="menu_faculty.jpg" width="79" height="35" name="faculty" border="0" id="faculty" /></a><a href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('links','','menu_links_over.jpg',1)"><img src="menu_links.jpg" width="66" height="35" name="links" border="0" id="links" /></a><a href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('login','','menu_login_over.jpg',1)"><img src="menu_login.jpg" name="login" width="69" height="35" border="0" id="login" /></a><a href="#" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('contact','','menu_contact_over.jpg',1)"><img src="menu_contact.jpg" width="100" height="35" name="contact" border="0" id="contact" /></a><img src="menu_spacer_end.jpg" width="20" height="35" /></td>
</tr>
<tr height="10">
    <td colspan="3"><img src="menu_bottom_spacer.jpg" height="10" width="960" /></td>
</tr>
</table>
<table width="960" cellpadding="0" cellspacing="0" align="center">
<tr height="50">
    <td width="30" background="left_border.jpg"><img src="clear.gif" width="30" height="50" /></td>
    <td width="900" bgcolor="#FFFFFF">
      <table width="900" cellpadding="0" cellspacing="0">
        <tr>
          <td width="900" height="350" valign="top">
      <form action="<cfoutput>#CurrentPage#</cfoutput>" method="POST" name="register" preloader="no" id="register">
            <table width="100%">
              <tr>
                <td colspan="2" class="heading1">Fill in the information below to register for this site:</td>
              </tr>
              <tr>
                <td colspan="2"><img src="clear.gif" height="15" /></td>
              </tr>
              <tr>
                <td width="50%" class="form" align="right">First Name:</td>
                <td width="50%"><span id="sprytextfield1">
                  <input type="text" name="first_name" required="yes" id="first_name" width="150" typeahead="no" showautosuggestloadingicon="true" />
                  <span class="textfieldRequiredMsg">A value is required.</span></span></td>
              </tr>
              <tr>
                <td class="form" align="right">Last Name:</td>
                <td><span id="sprytextfield2">
                  <input type="text" name="last_name" required="yes" id="last_name" width="150" typeahead="no" showautosuggestloadingicon="true" />
                  <span class="textfieldRequiredMsg">A value is required.</span></span></td>
              </tr>
              <tr>
                <td class="form" align="right">Email Address:</td>
                <td><span id="sprytextfield3">
                <input type="text" name="email" validate="email" required="yes" id="email" width="150" typeahead="no" showautosuggestloadingicon="true" />
                <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldInvalidFormatMsg">Invalid format.</span></span></td>
              </tr>
              <tr>
                <td class="form" align="right">Confirm Email Address:</td>
                <td><span id="sprytextfield4"><span id="ConfirmWidget">
                <input type="text" name="email_confirm" validate="email" required="yes" id="email_confirm" width="150" typeahead="no" showautosuggestloadingicon="true" />
                <span class="confirmInvalidMsg">The values do not match</span></span></span></td>
              </tr>
              <tr>
                <td class="form" align="right">Password:</td>
                <td><span id="sprytextfield5">
                  <input type="password" name="password" required="yes" id="password" width="150" />
                  <span class="textfieldRequiredMsg">A value is required.</span></span></td>
              </tr>
              <tr>
                <td class="form" align="right">Confirm Password:</td>
                <td><span id="sprytextfield6"><span id="ConfirmWidget">
                  <input type="password" name="password_confirm" required="yes" id="password_confirm" width="150" />
                  <span class="confirmInvalidMsg">The values do not match</span></span></span></td>
              </tr>
              <tr>
                <td> </td>
                <td><input name="submit" type="submit" id="submit" value="Register" /></td>
              </tr>
            </table>

      <input type="hidden" name="MM_InsertRecord" value="register" />
      </form>
      <script type="text/javascript">

</script>
<script type="text/javascript">
var ConfirmWidgetObject = new Spry.Widget.ValidationConfirm("sprytextfield4", "email");
var ConfirmWidgetObject = new Spry.Widget.ValidationConfirm("sprytextfield6", "password");
</script>
          </td>
        </tr>
      </table>
    </td>
    <td width="30" background="right_border.jpg"><img src="clear.gif" width="30" height="50" /></td>
</tr>
<tr>
    <td colspan="3" background="footer.jpg" class="footer" height="80"/>This website best viewed using:<br /><a href="http://www.firefox.com"><img src="firefox_logo.gif" width="110" height="40" border="0" /></a></td>
</tr>
</table>

Report · Nov 03, 2011

Syntax error in INSERT INTO statement.
  INSERT INTO Users ([First Name], [Last Name], [Email Address], Password)

That oh-so-uninformative error is because "Password" is a reserved word with your database driver. Either escape it by putting square brackets around it too, or rename the column permanently. It is best to avoid using reserved words whenever possible. So renaming the column is the better option. Also, I would try and avoid using invalid characters like spaces in column names ie "First Name". It is technically allowed, but it requires special handling everywhere which adds unecessary complexity.

I'm not sure what the problem is, but I'm getting a syntax error referencing this particular portion of the code:

Do not take the error line numbers as gospel. Sometimes they just indicate that the error is within the vincinty of that line.

I'm using Dreamweaver CS3 to help generate most of the code

Unforutnately, DW wizards generate some truly awful and verbose code. To give you an idea, here is what the query should look like, without all the wizard nonsense.

<cfparam name="FORM.first_name" default="">

<cfparam name="FORM.last_name" default="">

<cfparam name="FORM.email" default="">

<cfparam name="FORM.FORM.password" default="">

<cfquery datasource="YourDSNName">

INSERT INTO Users ([First Name], [Last Name], [Email Address], [Password])

VALUES (

<cfqueryparam value="#FORM.first_name#" cfsqltype="cf_sql_varchar">

, <cfqueryparam value="#FORM.last_name#" cfsqltype="cf_sql_varchar">

, <cfqueryparam value="#FORM.email#" cfsqltype="cf_sql_varchar">

, <cfqueryparam value="#FORM.password#" cfsqltype="cf_sql_varchar">

)

</cfquery>

CF is pretty easy to learn. You might want to begin perusing the CF documentation and a few tutorials to get more familiar with the language. Since you are working with a database, I would also recommend a SQL tutorial.

Report · Nov 04, 2011

Thanks so much for your help! Brackets did the trick! I do plan on learning more about CF and SQL over time, and I do know that Dreamweaver puts a lot of unnecessary stuff in its code, but for the time being I'm hoping to just get this up and running and then refine it down the road as I learn more. Thanks again!

Connected by DROID on Verizon Wireless

Problem inserting record using INSERT INTO

1 Correct answer