Solved

Error with query

Posted on 2007-04-10
9
226 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 200 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now