Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Error with query

Posted on 2007-04-10
9
Medium Priority
?
249 Views
Last Modified: 2010-08-05
Hello, I'm getting the following error:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '='.


SQL = "select distinct company_id, company_name, netting_flag, standard_gtc from v_companies where company_id ="

Data Source = "CORE"

Here is the code:

<cfquery name="qry_companyDetail" datasource="core">
      select distinct company_id, company_name, netting_flag, standard_gtc
      from v_companies
      where company_id = #cid#
</cfquery>

<cfquery name="qry_companyEpicorCompanies" datasource="core">
      select a.company_id, epicor_code, epicor_db_name, convert(varchar(30), company_name) as company_name
      from company_epicor_xref a, pltmaster..smcomp b
      where epicor_db_name = db_name
      and a.company_id = #cid#
</cfquery>

It has something to do with the #cid# but I don't see why this would be problem. When I put single quote around it gives me this error:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.


SQL = "select distinct company_id, company_name, netting_flag, standard_gtc from v_companies where company_id = ''"

Data Source = "CORE"

I don't have solution for this.
0
Comment
Question by:DancingFighterG
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18885143
This looks like a Cold Fusion problem, not a SQL problem.  The term #cid# is not being properly translated by Cold Fusion into the numeric ID you expect.  Check your syntax on this.
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18885169
Make sure that #cid# is defined.  Add logic to not run the query if it is not defined or set it to a default value before the cfquery tag.
0
 

Author Comment

by:DancingFighterG
ID: 18892129
Ok, in the coldfusion page I see that:

<!--- include all queries --->
<cfinclude template="qry_epicorCompanies.cfm">
<cfinclude template="qry_companyDetails.cfm"> <-The error is coming from here

<cfset type = "Company">
<cfset id = #cid#>
<cfinclude template="qry_notes.cfm">

It seems as if the id is not being set for cid?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18892174
That would be why the query failed - if nothing is being put into the spot where the id should be, the query would give the error you describe.  Well, at least it isn' t the query - good luck with the Cold Fusion.
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18897728
So, if you can't be sure that cid is set you should:

<cfif IsDefined('cid') and cid GT 0>
  <cfquery name="qry_companyDetail" datasource="core">
      select distinct company_id, company_name, netting_flag, standard_gtc
      from v_companies
      where company_id = #cid#
  </cfquery>
  <cfquery name="qry_companyEpicorCompanies" datasource="core">
        select a.company_id, epicor_code, epicor_db_name, convert(varchar(30), company_name) as   company_name
        from company_epicor_xref a, pltmaster..smcomp b
        where epicor_db_name = db_name
        and a.company_id = #cid#
  </cfquery>
<cfelse><!---No cid, take alternative action--->
      <cfoutput>No Company Provided</cfoutput>
</cfif>

0
 

Author Comment

by:DancingFighterG
ID: 18901200
Ok, I might have to open up another post for this but in a cfm that is used with this I am sending an email to a user to look at our company detail but for some reason it's not pulling the cid number into the url to show the info. Here is the line of code that does this:

<!--- Finally, send out notification that there has been a significant change --->
      <cfif #msg# neq "">
            <cfset msg=#msg# & "<br><br>" & '<a href="#application.location#/markwest/CompanyDatabase/index.cfm?action=CompanyDetail&cid=#cid#">Click here to see company details.</a>'>
            <cf_groupMailer groupName="Company Monitors" subject="Company Details Changed" message=#msg#>
      </cfif>

So when it comes up in the browser it looks like this:

http://192.168.100.14/markwest/CompanyDatabase/index.cfm?action=CompanyDetail&cid=

What isn't the cid not being pulled!!
0
 
LVL 23

Accepted Solution

by:
Christopher Kile earned 800 total points
ID: 18901409
Do you in fact have a cfinput with the name of cid defined anywhere within the scope??
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18901421
and if so, can you show us the code?

BTW, My only experience with Cold Fusion was replacing a ColdFusion app with a Java/JSP app. PLEASE cross-post this question in the ColdFusion section.  However, I'll do my best to help.
0
 

Author Comment

by:DancingFighterG
ID: 18901539
Yhea, I will close this one and open this topic in the CFML section. Thanks!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

564 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