[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Error with query

Posted on 2007-04-10
9
Medium Priority
?
247 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
[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
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

656 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