Solved

Error with query

Posted on 2007-04-10
9
239 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

689 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