Solved

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

Posted on 2004-08-11
6
3,131 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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