9 Replies - 3693 Views - Last Post: 01 June 2007 - 06:28 AM Rate Topic: -----

#1 BenniMartin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 19-May 07

cfquery syntax error

Posted 19 May 2007 - 11:21 PM

Hey,
I'm getting an error when i try and information to my access databse from my form. I'm knew to Coldfusion and SQL but i have searched high and low for the cause of my syntax error but no matter what i try i cant get it to work, so any help would be greatly appreciated.

Here is the code:
 <cfquery name="qInsert" datasource="A13060_13398620">

INSERT INTO Listings

(Price, Address, Status_ID, Suburb_ID, Bathrooms, Bedrooms, Comments,
Pool, Fireplace, Garage, Laundry, )

VALUES

(#form.Price#, '#form.Address#', #form.Status_ID#, #form.Suburb_ID#,
#form.Bathrooms#, #form.Bedrooms#, '#form.Comments#', 

<cfif isDefined("form.Pool") >
		 'Yes'
<cfelse>
		  'No'
</cfif>,

<cfif isDefined("form.Fireplace") >
		 'Yes'
<cfelse>
		  'No'
</cfif>,

<cfif isDefined("form.Garage") >
		 'Yes'
<cfelse>
		  'No'
</cfif>,

<cfif isDefined("form.Laundry") >
		 'Yes'
<cfelse>
		  'No'
</cfif> )


</cfquery>



Basically the last four were checkboxes and i just want it to insert yes or no into the related record.

Here are the datatypes in access:
Price = Currency
Address = Memo
Status_ID = Number
Suburb_ID = Number
Bathrooms = Number
Bedrooms = Number
Comments = Memo
Pool = Text
Fireplace = Text
Garage = Text
Laundry = text


Once again thanks

Ben

Is This A Good Question/Topic? 0
  • +

Replies To: cfquery syntax error

#2 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: cfquery syntax error

Posted 20 May 2007 - 07:51 AM

What's the error?
Was This Post Helpful? 0
  • +
  • -

#3 BenniMartin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 19-May 07

Re: cfquery syntax error

Post icon  Posted 20 May 2007 - 08:11 AM

 [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
 
The error occurred in O:\Hosted Web Sites\Joel.Day1\bead-mm_com\www\ided\13398620\addlisting.cfm: line 33

31 : <cfelse>
32 :		  'No')
33 : </cfif>
34 : 
35 : 

L		 INSERT INTO Listings (Price, Address, Status_ID, Suburb_ID, Bathrooms, Bedrooms, Comments, Pool, Fireplace, Garage, Laundry, ) VALUES (6, '31 j street', 21, 21, 6, 6, 'Comments', 'No', 'No', 'No', 'No')
DATASOURCE 	  A13060_13398620
VENDORERRORCODE 	  -3502
SQLSTATE 	  42000




Is the error, any ideas ?
Was This Post Helpful? 0
  • +
  • -

#4 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: cfquery syntax error

Posted 20 May 2007 - 08:16 AM

Man, I hate to do this to ya cause you're gonna kick yourself, but your error is right here:

(Price, Address, Status_ID, Suburb_ID, Bathrooms, Bedrooms, Comments,
Pool, Fireplace, Garage, Laundry, )


Notice after Laundry you just have a comma and nothing after it... that's where it's erroring. Take the comma out.
Was This Post Helpful? 0
  • +
  • -

#5 BenniMartin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 19-May 07

Re: cfquery syntax error

Posted 20 May 2007 - 05:57 PM

I have taken that out and still get an error when i submit the form : (


Here is the form if it helps

<CFFORM ACTION="addlisting.cfm" method = "POST">
		
		<p>Address Details:<br />
		Address:<cfinput type="text" name="Address" value="Address" required="no" message="An Address is required!" validateAt="onServer"><br>
		SuburbID:<cfinput type="text" name="Suburb_ID" value="Address" required="no" message="An Address is required!" validateAt="onServer"><br>
		StatusID:<cfinput type="text" name="Status_ID" value="Address" required="no" message="An Address is required!" validateAt="onServer"><br>
		Suburb:<cfinput type="text" name="Suburb" value="Suburb" required="no" message="A Suburb is required!" validateAt="onServer"><br>
		Zip Code:<cfinput type="text" name="Zip_Code" value="Zip Code" required="no" message="A Zip Code is required!" validateAt="onServer"><br><br>
		
		Listing Details:<br />
		Price:<cfinput type="text" name="Price" value="Price" required="no" message="Price is required!" validateAt="onServer"><br>
		
		
		Bedrooms:<cfinput type="text" name="Bedrooms" value="Bedrooms" required="no" validateAt="onServer"><br>
		Bathrooms:<cfinput type="text" name="Bathrooms" value="Bathrooms" required="no" validateAt="onServer"><br />
		Comments:<cfinput type="text" name="Comments" value="Comments" required="no" validateAt="onServer"><br />
		<cfselect name="Status" required="yes" message="Make Sure you have...">
						<option value="Sold">Sold</option>
						<option value="Active" selected>Active</option>
						<option value="Under Offer">Under Offer</option>
		</cfselect>
		<cfselect name="Type" required="yes" message="Make Sure you have...">
						<option value="Apartment">Apartment</option>
						<option value="Rural">Rural</option>
						<option value="Luxury" selected>Luxury</option>
						<option value="House">House</option>
		</cfselect>
		
		Amenities details:<br />
		<br />
		Pool:<cfinput type="checkbox" name="Pool" value="Pool" required="no" validateAt="onServer"><br>
		Garage:<cfinput type="checkbox" name="Garage" value="Garage" required="no" validateAt="onServer"><br>
		Laundry:<cfinput type="checkbox" name="Laundry" value="Laundry" required="no" validateAt="onServer"><br>
		Fireplace:<cfinput type="checkbox" name="Fireplace" value="Fireplace" required="no" validateAt="onServer"><br>
		</p>
		<input type = "Submit" name = "" value = "send my information">
</CFFORM>



This post has been edited by BenniMartin: 20 May 2007 - 05:57 PM

Was This Post Helpful? 0
  • +
  • -

#6 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: cfquery syntax error

Posted 20 May 2007 - 08:31 PM

Need to see the error again.

It let's me know what's actually being sent to the SQL server, I can't tell that from your code as I'm not sure of the values being passed in.
Was This Post Helpful? 0
  • +
  • -

#7 BenniMartin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 19-May 07

Re: cfquery syntax error

Posted 20 May 2007 - 11:24 PM

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

The error occurred in O:\Hosted Web Sites\Joel.Day1\bead-mm_com\www\ided\13398620\addlisting.cfm: line 33

31 : <cfelse>
32 :		  'No')
33 : </cfif>
34 :
35 :

L		 INSERT INTO Listings (Price, Address, Status_ID, Suburb_ID, Bathrooms, Bedrooms, Comments, Pool, Fireplace, Garage, Laundry) VALUES (6, '31 j street', 21, 21, 6, 6, 'Comments', 'No', 'No', 'No', 'No')
DATASOURCE	   A13060_13398620
VENDORERRORCODE	   -3502
SQLSTATE	   42000




Is there anything else that you need to see ? the page is located HERE
you can test it out if that helps : (

This post has been edited by BenniMartin: 20 May 2007 - 11:28 PM

Was This Post Helpful? 0
  • +
  • -

#8 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: cfquery syntax error

Posted 22 May 2007 - 06:18 AM

I'm going to move this over to databases cause I don't see a problem and maybe one of our database guys sees something I'm missing.
Was This Post Helpful? 0
  • +
  • -

#9 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: cfquery syntax error

Posted 22 May 2007 - 09:41 AM

i guess that acess is not realy returning "Syntax error in INSERT INTO statement." as to me that is so un-descriptive it can hardly come out of the DB. More likely that it comes out of some database library. I could be wrong, but did you use a 3rd party database library to ease the sql use?

The insert statement looks valid.
If you copypaste it and execute it directly, does it work?

Does the user that the CF script is accessing the DB with have sufficient priviliges to do this insert by the way?
Usualy a database will give another warning if not, but the actual error might have become.... lost.
Was This Post Helpful? 0
  • +
  • -

#10 axelinski  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 01-June 07

Re: cfquery syntax error

Posted 01 June 2007 - 06:28 AM

Try to insert the NULL value for numeric datatypes. This could solve the problem, as a SQL-Insert statement like
Insert (col1, col2, col3) Values ( , , );
is possible with your script, which is an invalid SQL-syntax.

Change your code into something like this:
INSERT INTO Listings (Price, Address, Status_ID, Suburb_ID, Bathrooms, Bedrooms, Comments, Pool, Fireplace, Garage, Laundry, )
VALUES (
#form.Price#, 
'#form.Address#', 
#form.Status_ID#, 
#form.Suburb_ID#,
#IIf(form.Bathrooms is "", De("NULL"), De(form.Bathrooms))#, 
#IIf(form.Bedrooms is "", De("NULL"), De(form.Bedrooms))#, 
... and so on


This post has been edited by axelinski: 01 June 2007 - 06:36 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1