Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-11
6
Medium Priority
?
3,229 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 1400 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

721 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