Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Issue with MSSQL Not Working

This is the query that I need to work. Do you know answer?

<cfquery name="getFlyerProducts" datasource="test">
      select * from flyer_vendor_campaign_products
    left join products
    on flyer_vendor_campaign_products.pid = products.productid
    inner join flyer_vendor_campaigns
    on flyer_vendor_campaign_products.code = products.vcode
    where flyer_vendor_campaign_products.code = '#vcode#'
 </cfquery>
0
pulse239
Asked:
pulse239
  • 9
  • 4
  • 2
  • +1
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
Of course not.  Because we need to know more.  Is the SQL Server on the same machine as the webserver?  If it is not, have you installed the SQL drivers on the web server?  Have you used other methods like SSMS to verify that the query works in the first place?
0
 
pulse239Author Commented:
It is on a separate machine.The site is on shared service, so the updates are completed by them. Does his help?

Also, it says it is an error in the code. Specifically the query. Or at least that is what the host provider says.
0
 
Anthony PerkinsCommented:
Also, it says it is an error in the code.
Actually the code is fine.  At least it compiles fine.  So it must be a run-time error.  Unfortunately, that is all I can see in my crystal ball.  The rest of the mystery you will have to supply in order for us to help you further.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
Dave BaldwinFixer of ProblemsCommented:
Starting with the exact error messages you get.  Sometimes services on different hosts block communication between them.  We need more detail about where the servers are hosted and who is the host.
0
 
pulse239Author Commented:
Currently, the host provider is Newtek/thesba, used to be known as CrystalTech.

The server is on the ColdFusion 10 Premier / PHP 5.

http://webservices.thesba.com/coldfusion-web-hosting/ 

Does this help?
0
 
Dave BaldwinFixer of ProblemsCommented:
Looks like they have all the right buzz words.  Mock up a demo page and try this query and let me know what you get.  Exact error messages if there are errors.
<cfquery name="getFlyerProducts" datasource="test">
      select TOP 10 * from flyer_vendor_campaign_products
 </cfquery> 

Open in new window

0
 
pulse239Author Commented:
That won't work as I need to join the other2 tables in the query.
0
 
Anthony PerkinsCommented:
That won't work as I need to join the other2 tables in the query.
You really are not helping yourself.  Dave is trying to help you troubleshoot the problem. So are you unable or incapable of running the query he posted?

Also and at the risk of sounding like a broken record: What is the exact error message?
0
 
pulse239Author Commented:
I also get syntax errors on this:

update flyer_campaign
    set pid = #pid#
          , dealer_net_sale = #dealer_net_sale#
        , campaign_sale_net_price = #campaign_sale_net_price#
        , selling_unit = #selling_unit#
        , code = '#vcode#'
        , short_description = '#short_description#'
    where fid = #fid#
0
 
pulse239Author Commented:
ERROR

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '='.
 
The error occurred in D:/inetpub/nisscorest/admin/vendors/flyer_step3.cfm: line 21

19 :       select * from flyer_vendor_campaigns
20 :     where fid = #fid#
21 : </cfquery>
22 : <cfquery name="getFlyerProducts" datasource="nisscorest">
23 :       select * from flyer_vendor_campaign_products
0
 
pulse239Author Commented:
I think I have the query working, but now I get a footer display issue.

http://216.119.108.254/admin/vendors/flyer_step3.cfm?fid=35
0
 
Dave BaldwinFixer of ProblemsCommented:
You should be able to find where on that page that 'vcode' is supposed to be defined.  And chance that it is spelled wrong?
0
 
pulse239Author Commented:
Bo, nut I am redeveloping now
0
 
pulse239Author Commented:
removed the footer, but I am getting this error

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.
 
line 25
 line 1
 line 25
 line 1

23 :         , selling_unit = #selling_unit#
24 :         , code = #vcode#
25 :     where fid = #fid#
26 : </cfquery>
27 : <cfif len(ad) gt 0>


This is the actual quey
<cfquery name="updateFlyer" datasource="nisscorest">
      update flyer_vendor_campaign_products
    set pid = #pid#
          , dealer_net_sale = #dealer_net_sale#
        , campaign_sale_net_price = #campaign_sale_net_price#
        , selling_unit = #selling_unit#
        , code = #vcode#
    where fid = #fid#
</cfquery>
0
 
pulse239Author Commented:
Now it says Syntax Error, but I checked the database for numeric fields instead ifnvarchar,

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.
 
The error occurred in D:/inetpub/nisscorest/admin/vendors/flyer_step3a.cfm: line 25
Called from D:/inetpub/nisscorest/admin/vendors/flyer_step3a.cfm: line 1
Called from D:/inetpub/nisscorest/admin/vendors/flyer_step3a.cfm: line 25
Called from D:/inetpub/nisscorest/admin/vendors/flyer_step3a.cfm: line 1

23 :         , selling_unit = '#selling_unit#'
24 :         , short_description = '#short_description#'
25 :         , code = '#vcode#'
26 :     where fid = #fid#
27 : </cfquery>



QUERY:

update flyer_vendor_campaign_products
    set pid = #pid#
          , dealer_net_sale = #dealer_net_sale#
        , campaign_sale_net_price = #campaign_sale_net_price#
        , selling_unit = '#selling_unit#'
        , short_description = '#short_description#'
        , code = '#vcode#'
    where fid = #fid#
0
 
_agx_Commented:
(no points, just a small comment about web app security...)

Nothing to do with your current error but .. those queries leave your db completely vulnerable to sql injection.  You really, really, really should be using cfqueryparam on ALL  query parameters.
0
 
_agx_Commented:
While true, my comment isn't really an answer to the question asked. Not sure why it was marked as such.
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.

  • 9
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now