Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3279
  • Last Modified:

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

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
Westside2004
Asked:
Westside2004
  • 4
1 Solution
 
pinaldaveCommented:
Hi Westside2004,

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

Regards,
---Pinal
0
 
pinaldaveCommented:
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
 
jyokumCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Westside2004Author Commented:
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
 
pinaldaveCommented:
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
 
pinaldaveCommented:
thank you and have a good day/night:)
---Pinal
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now