pulse239
asked on
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_prod ucts
left join products
on flyer_vendor_campaign_prod ucts.pid = products.productid
inner join flyer_vendor_campaigns
on flyer_vendor_campaign_prod ucts.code = products.vcode
where flyer_vendor_campaign_prod ucts.code = '#vcode#'
</cfquery>
<cfquery name="getFlyerProducts" datasource="test">
select * from flyer_vendor_campaign_prod
left join products
on flyer_vendor_campaign_prod
inner join flyer_vendor_campaigns
on flyer_vendor_campaign_prod
where flyer_vendor_campaign_prod
</cfquery>
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?
ASKER
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.
Also, it says it is an error in the code. Specifically the query. Or at least that is what the host provider says.
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.
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.
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.
ASKER
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?
The server is on the ColdFusion 10 Premier / PHP 5.
http://webservices.thesba.com/coldfusion-web-hosting/
Does this help?
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>
ASKER
That won't work as I need to join the other2 tables in the query.
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?
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?
ASKER
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#
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#
ASKER
ERROR
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec t syntax near '='.
The error occurred in D:/inetpub/nisscorest/admi n/vendors/ flyer_step 3.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_prod ucts
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec
The error occurred in D:/inetpub/nisscorest/admi
19 : select * from flyer_vendor_campaigns
20 : where fid = #fid#
21 : </cfquery>
22 : <cfquery name="getFlyerProducts" datasource="nisscorest">
23 : select * from flyer_vendor_campaign_prod
ASKER
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
http://216.119.108.254/admin/vendors/flyer_step3.cfm?fid=35
You should be able to find where on that page that 'vcode' is supposed to be defined. And chance that it is spelled wrong?
ASKER
Bo, nut I am redeveloping now
ASKER
removed the footer, but I am getting this error
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec t 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_prod ucts
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>
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec
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_prod
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>
ASKER
Now it says Syntax Error, but I checked the database for numeric fields instead ifnvarchar,
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec t syntax near ','.
The error occurred in D:/inetpub/nisscorest/admi n/vendors/ flyer_step 3a.cfm: line 25
Called from D:/inetpub/nisscorest/admi n/vendors/ flyer_step 3a.cfm: line 1
Called from D:/inetpub/nisscorest/admi n/vendors/ flyer_step 3a.cfm: line 25
Called from D:/inetpub/nisscorest/admi n/vendors/ flyer_step 3a.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_prod ucts
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#
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec
The error occurred in D:/inetpub/nisscorest/admi
Called from D:/inetpub/nisscorest/admi
Called from D:/inetpub/nisscorest/admi
Called from D:/inetpub/nisscorest/admi
23 : , selling_unit = '#selling_unit#'
24 : , short_description = '#short_description#'
25 : , code = '#vcode#'
26 : where fid = #fid#
27 : </cfquery>
QUERY:
update flyer_vendor_campaign_prod
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#
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
While true, my comment isn't really an answer to the question asked. Not sure why it was marked as such.