Solved

Issue with MSSQL Not Working

Posted on 2013-01-23
17
160 Views
Last Modified: 2013-01-25
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
Comment
Question by:pulse239
  • 9
  • 4
  • 2
  • +1
17 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38812208
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
 

Author Comment

by:pulse239
ID: 38812666
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38812719
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38812784
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
 

Author Comment

by:pulse239
ID: 38813152
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38813328
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
 

Author Comment

by:pulse239
ID: 38814298
That won't work as I need to join the other2 tables in the query.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38814435
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
 

Author Comment

by:pulse239
ID: 38816724
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
 

Author Comment

by:pulse239
ID: 38816727
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
 

Author Comment

by:pulse239
ID: 38816988
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38817060
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
 

Author Comment

by:pulse239
ID: 38817092
Bo, nut I am redeveloping now
0
 

Author Comment

by:pulse239
ID: 38817104
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
 

Author Comment

by:pulse239
ID: 38817148
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38817441
(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
 
LVL 52

Expert Comment

by:_agx_
ID: 38818873
While true, my comment isn't really an answer to the question asked. Not sure why it was marked as such.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
Import New Records From Access Table To SQL Database Table 7 32
Parse this column 6 27
SQL Find Carriage Return and Delete it. 3 13
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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