EasyCFM.COM ColdFusion Forums / MS Access Related Issues / SYNTAX ERROR FOR INSERT TO Statement

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: SYNTAX ERROR FOR INSERT TO Statement

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

mel77
03-11-2009 @ 3:02 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 4
Joined: Mar 2009

I am hosting on Go daddy. I have been having problem with my hosting for the past five weeks. I really need help with Inserting Record to my MS access driver database.

The table contains the following:INSERT INTO Users (UserID, Username, Password, FirstName, LastName, Telephone, EmailAddress, RegisterDate, UserTypeID)

I can only log on from the database eg "login". My reviews table doesn't display when post a review.

It appears on the Admin index page and i check the Online box for it to display for members to view. The update doesn't work too.

I have allowed the Permission to be Read and Write into database.

Go daddy people said they are not seeing any problem with setup, that is everything is working fine, yet new members can not add record. Please can anyone help me.

TABLES ARE:

Users Table columns (USERID "PK", Username, Password, Firstname, Lastname, Telephone, EmailAddress, Phone, RegisteredDate, UserTypeID "foreign Key")

BookApp Table columns (BookingID "PK", UserID "foreign key", Appointment, When, Period)

note the when is "Date" and the period is "Time"

Reviews Table columns ( ReviewID"PK", UserID "FK", ReviewDate, ReviewText, Online "YesNoFormat")

The ERROR MESSAGE FROM THE CF IS BELOW

Error Executing Database Query.  
Syntax error in INSERT INTO statement.


THE CODE USED IS THIS.... the database relationship is built in MS Access

This section of the code below is checking username before processing them

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.Username")>
  <cfquery name="MM_search" datasource="#Request.DSN#">
    SELECT Username FROM Users WHERE Username='#FORM.Username#'
  </cfquery>
  <cfif MM_search.RecordCount GTE 1>
    <cflocation url="/userexist.cfm?requsername=#FORM.Username#" addtoken="no">
  </cfif>
</cfif>   .................. //section one, checking username//

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="#Request.DSN#">
    INSERT INTO Users (UserID, Username, Password, FirstName, LastName, Telephone, EmailAddress, RegisterDate, BookingID, ReportID, ReviewID, UserTypeID)
    VALUES (
  <cfif IsDefined("FORM.UserID") AND #FORM.UserID# NEQ "">
    #FORM.UserID#
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.Username") AND #FORM.Username# NEQ "">
    '#FORM.Username#'
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.Password") AND #FORM.Password# NEQ "">
    '#FORM.Password#'
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ "">
    '#FORM.FirstName#'
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ "">
    '#FORM.LastName#'
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.Telephone") AND #FORM.Telephone# NEQ "">
    #FORM.Telephone#
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.EmailAddress") AND #FORM.EmailAddress# NEQ "">
    '#FORM.EmailAddress#'
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.RegisterDate") AND #FORM.RegisterDate# NEQ "">
    ###FORM.RegisterDate###
      <cfelse>
    NULL
  </cfif>
    ,
  <cfif IsDefined("FORM.UserTypeID") AND #FORM.UserTypeID# NEQ "">
    #FORM.UserTypeID#
      <cfelse>
    NULL
  </cfif>



<b>Also the Code for Booking Appointment</b>

<cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">
  <cfset MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>
  <cfset MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_UserAuthorization",DE(""))>
</cflock>
<cfif MM_Username EQ "">
  <cfset MM_referer=CGI.SCRIPT_NAME>
  <cfif CGI.QUERY_STRING NEQ "">
    <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>
  </cfif>
  <cfset MM_failureURL="/Members/loginfailed.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>
  <cflocation url="#MM_failureURL#" addtoken="no">
</cfif>
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form2">
  <cfquery datasource="#Request.DSN#">
  INSERT INTO BookApp (UserID, Username, EmailAddress, Phone, When, Period)
VALUES (
  <cfif IsDefined("FORM.UserID") AND #FORM.UserID# NEQ "">
    #FORM.UserID#
      <cfelse>
    NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.Username") AND #FORM.Username# NEQ "">
    '#FORM.Username#'
      <cfelse>
    NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.EmailAddress") AND #FORM.EmailAddress# NEQ "">
    '#FORM.EmailAddress#'
      <cfelse>
    NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.Phone") AND #FORM.Phone# NEQ "">
    #FORM.Phone#
      <cfelse>
    NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.When") AND #FORM.When# NEQ "">
    ###FORM.When###
      <cfelse>
    NULL
  </cfif>
  ,
  <cfif IsDefined("FORM.period") AND #FORM.period# NEQ "">
    ###FORM.period###
      <cfelse>
    NULL
  </cfif>
  )</cfquery>
  <cflocation url="/Members/msgsent.cfm">
</cfif>


THe Review is meant to appear also

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfparam name="URL.UserID" default="36">
<cfparam name="PageNum_rsReviews" default="1">
<cfquery name="rsReviews" datasource="#Request.DSN#">
SELECT Reviews.ReviewID, Reviews.UserID, Users.FirstName, Users.LastName, Reviews.ReviewDate, Reviews.ReviewText, Reviews.Online
FROM Reviews, Users
WHERE Reviews.UserID= #URL.UserID# AND Reviews.UserID = Users.UserID AND Reviews.Online = 1
</cfquery>
<cfset MaxRows_rsReviews=20>
<cfset StartRow_rsReviews=Min((PageNum_rsReviews-1)*MaxRows_rsReviews+1,Max(rsReviews.RecordCount,1))>
<cfset EndRow_rsReviews=Min(StartRow_rsReviews+MaxRows_rsReviews-1,rsReviews.RecordCount)>
<cfset TotalPages_rsReviews=Ceiling(rsReviews.RecordCount/MaxRows_rsReviews)>
<cfset QueryString_rsReviews=Iif(CGI.QUERY_STRING NEQ "",DE("&"&XMLFormat(CGI.QUERY_STRING)),DE(""))>
<cfset tempPos=ListContainsNoCase(QueryString_rsReviews,"PageNum_rsReviews=","&")>
<cfif tempPos NEQ 0>
  <cfset QueryString_rsReviews=ListDeleteAt(QueryString_rsReviews,tempPos,"&")>
</cfif>
<cfif IsDefined("FORM.username")>
  <cfset MM_redirectLoginSuccess="/Forum/inbox.cfm">
  <cfset MM_redirectLoginFailed="/forum.cfm">
  <cfquery  name="MM_rsUser" datasource="#Request.DSN#">
    SELECT Username,Password FROM Users WHERE Username='#FORM.username#' AND Password='#FORM.password#'
  </cfquery>
  <cfif MM_rsUser.RecordCount NEQ 0>
    <cftry>
    <cflock scope="Session" timeout="30" type="Exclusive">
      <cfset Session.MM_Username=FORM.username>
      <cfset Session.MM_UserAuthorization="">
    </cflock>
    <cfif IsDefined("URL.accessdenied") AND true>
      <cfset MM_redirectLoginSuccess=URL.accessdenied>
    </cfif>
    <cflocation url="#MM_redirectLoginSuccess#" addtoken="no">
    <cfcatch type="Lock"><!--- code for handling timeout of cflock --->
    </cfcatch>
    </cftry>
  </cfif>
  <cflocation url="#MM_redirectLoginFailed#" addtoken="no">
  <cfelse>
  <cfset MM_LoginAction=CGI.SCRIPT_NAME>
  <cfif CGI.QUERY_STRING NEQ "">
    <cfset MM_LoginAction=MM_LoginAction & "?" & XMLFormat(CGI.QUERY_STRING)>
  </cfif>
</cfif>



I love you all

Scarecrow
04-05-2009 @ 6:21 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

I'm going to assume that all the primary id fields are autonumbers, therefore they should not be in any of the insert statements.
If you still get errors, enable debugging if you can so we can see the actual query.  If not replace the cfquery tags with cfoutput and display the query so you can post it.

Ken


www.scarecrowapplications.com

mel77
04-12-2009 @ 12:47 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 4
Joined: Mar 2009

Thanks ken.

i want you to know that my debugging report is not indicating any error.

I am able to insert, update and delete from my pc.

I have also identify the error that made the reviews not to display on the forum page and i have fixed it.

But i still cant get through the insert error.
I was thinking it could be a reverse word but i have done all i know.

I will try the <cfoutput> you advised. I really need your help to put this to work.

Thanks once again ken for your help.
Happy Easter<if you believe the date is right>

I love you all

mel77
04-12-2009 @ 12:55 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 4
Joined: Mar 2009

Thanks ken.

i want you to know that my debugging report is not indicating any error.

I am able to insert, update and delete from my pc.

I have also identify the error that made the reviews not to display on the forum page and i have fixed it.

But i still cant get through the insert error.
I was thinking it could be a reverse word but i have done all i know.

I will try the <cfoutput> you advised. I really need your help to put this to work.

Thanks once again ken for your help.
Happy Easter<if you believe the date is right>

I love you all

I love you all


Website Designed and Developed by Pablo Varando.