Solved

Error with query

Posted on 2007-04-10
9
234 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 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