Solved

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

Posted on 2004-08-11
6
3,155 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Two nodes for updates and forwarding 8 51
IIS 8.5 2 54
IIS Site Configuration Copy 2 87
How can i point a subdomain to directory? 5 43
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
What You Need to Know when Searching for a Webhost Provider
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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