Solved

Database Error: [Macromedia][SQLServer JDBC Driver][SQLServer]Line 35: Incorrect syntax near ','.

Posted on 2004-08-11
6
3,184 Views
Last Modified: 2013-12-24
Hi,

Was not sure whether to post this question here or SQL Server Area....

For some reason I get this error:

Database Error: [Macromedia][SQLServer JDBC Driver][SQLServer]Line 35: Incorrect syntax near ','.

 I think its because I am submitting a form to the db and not all the fields on the form are filled in.

Can anyone see why there is a problem?  I did a little test and entered everything the but the Area Codes in the form and it didnt work.  I then filled in all 6 area codes and it worked.  So the problem must be related to me not filling anything in the Area Code fields and the database is expecting something I think.

The Area Code fields (there are six of them) in my database table are set to accept nulls so I should not be require to put anything in there.

I got the insert to work, but it only worked when I entered all the form fields in.   A piece of the code is below

<!--- If the user is submitting the Form--->
<cfif CGI.REQUEST_METHOD IS "Post">

<!--- Set the form fields to a blank value initially --->
<cfparam name="FORM.AgentType" default="">
<cfparam name="FORM.AgentName" default="">
<cfparam name="FORM.AgentName2" default="">
<cfparam name="FORM.Keywords" default="">
<cfparam name="FORM.Keywords2" default="">
<cfparam name="FORM.Category" default="">
<cfparam name="FORM.Category2" default="">
<cfparam name="FORM.SubCategory" default="">
<cfparam name="FORM.SubCategory2" default="">
<cfparam name="FORM.City" default="">
<cfparam name="FORM.City2" default="">
<cfparam name="FORM.LocationType" default="">
<cfparam name="FORM.NearbyCities" default="">
<cfparam name="FORM.NearbyCities2" default="">
<cfparam name="FORM.AreaCode1" default="">
<cfparam name="FORM.AreaCode2" default="">
<cfparam name="FORM.AreaCode3" default="">
<cfparam name="FORM.AreaCode4" default="">
<cfparam name="FORM.AreaCode5" default="">
<cfparam name="FORM.AreaCode6" default="">
<cfparam name="FORM.Frequency" default="">
<cfparam name="FORM.Frequency2" default="">
<cfparam name="FORM.EmailFormat" default="">
<cfparam name="FORM.EmailFormat2" default="">
<cfparam name="FORM.ReceiveType" default="">
<cfparam name="FORM.CompanyType" default="">
<cfparam name="FORM.JobType" default="">
<cfparam name="FORM.stateProvince" default="">
<cfparam name="FORM.stateProvince2" default="">
<cfparam name="FORM.Country" default="">
<cfparam name="FORM.Country2" default="">

<cfquery name="qInsert" datasource="#DSN#">
INSERT INTO MyTable (
                                                                                          AgentType,                                                                              AgentName,                                                                               Keywords,                                                                                     Category,                                                                                     SubCategory,                                                                               LocationType,
                City,                                                                                     CitiesWithinRadius,                                                                               AreaCode1,                                                                               AreaCode2,                                                                               AreaCode3,                                                                               AreaCode4,                                                                               AreaCode5,                                                                               AreaCode6,                                                                              Frequency,                                                                                    EmailFormat,                                                                              ReceiveType,                                                                              CompanyType,                                                                              JobType,                                                                                    CountryCode,                                                                              StateProvinceCode,      
                ID
      )
      VALUES (
                                                                                            '#FORM.AgentType#',                                                                         '#FORM.AgentName2#',                                                                         '#FORM.Keywords2#',                                                                         '#FORM.Category2#',                                                                         '#FORM.subCategory2#',                                                                         '#FORM.locationType#',                                                                         '#FORM.City2#',                                                                               #FORM.nearbyCities2#,                                                                         #FORM.areaCode1#,       
 #FORM.areaCode2#,
      #FORM.areaCode3#,                                                                               #FORM.areaCode4#,                                                                               #FORM.areaCode5#,                                                                               #FORM.areaCode6#,                                                                               '#FORM.Frequency2#',                                                                         '#FORM.EmailFormat2#',                                                                         '#FORM.ReceiveType#',                                                                        '#FORM.CompanyType#',                                                                        '#FORM.JobType#',                                                                               '#FORM.Country2#',                                                                                 '#FORM.StateProvince2#',                                                                            #Session.UniqueID#                                                                        )
</cfquery>

Thanks and any help appreciated...

-WS
0
Comment
Question by:Westside2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 21

Accepted Solution

by:
pinaldave earned 350 total points
ID: 11780267
Hi Westside2004,

 #val(FORM.areaCode2)# around your all the area code and it will work.

Regards,
---Pinal
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11780270
Hi Westside2004,

the reason it was not working is that... your database accepts null for sume but as they are numeric/integer they do not accept empty string which is "" . Hope this gives explaination also.

Regards,
---Pinal
0
 
LVL 12

Expert Comment

by:jyokum
ID: 11780287
you should use <cfqueryparam> for all your values

...
VALUES (
     <cfqueryparam value="#FORM.AgentType#" cfsqltype="cf_sql_varchar">,
     <cfqueryparam value="#FORM.AgentName2#" cfsqltype="cf_sql_varchar">,
     <cfqueryparam value="#FORM.Keywords2#" cfsqltype="cf_sql_varchar">,
     ...
     ...
)

just use the appropriate cfsqltype for each parameter
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:Westside2004
ID: 11780427
Hi,

Yes, that makes good sense. "" does not work with integer field.... didnt think about it... but makes good sense...

Thanks for the quick response...

It worked like a charm..

I checked my db and it inserts a 0 in each field ( I guess from the val function).....  What is the difference between that and setting a default value in the db for that column(s)?

The reason I didnt use <cfqueryparam> was because I plan on just using a stored procedure..

But your right, <cfqueryparam> is the right way to go.

Best regards,

-West
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11780863
well... glad to help you.
When you set the default value in the database it will be the initially value but your CF try to overwrite it with '' ( empty string) ... so that is not valid...
now what we did here is we use val around that and it will insert if the that is integer otherwise it will make the value as 0 so you that will be fine... as only integer is suppose to enter in the databas not the string( even though it is empty)...
it is fun to learn on this board. :=)
Regards,
---Pinal
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11781055
thank you and have a good day/night:)
---Pinal
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question