473,544 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to output with a control variable inside cfquery name?

769 Contributor
Hey Everyone,

Well i am having a problem outputting for my report and hoping someone can explain what i am doing wrong.

What the report is about is comparing my company's part price's to d and h, synnex and techdata. What i was told to do was take are part numbers and combine them with d and h, synnex and techdata to display only one column of parts (did this). Then i was told to create a cfloop using the query i created to combine the parts (did this). then inside the cfloop i created a cfif for each company, doing the cfif is suppose to get the price for each company(did this).Then i was told to add a loop control variable so that if one price is empty it will skip it instead of add the next price.

The problem is that i was able to get one price to output, but now i can not get any of the prices to output at all an not sure what i am doing wrong. Before i added a loop control variable i was able to output techdata's prices by doing #techdata.cost# but now with the loop control variable i am not able to. I was also not able to output synnex and techdata before adding the loop control variable, but i was told that how i was outputting it could be the problem. if anyone could tell me what i am doing wrong, atleast to how i am displaying techdata i would really appreciate it. Here is what i currently have.

Expand|Select|Wrap|Line Numbers
  1. This combines all the parts
  2. <cfquery name="matchmanu" datasource="Configurator">
  3. select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
  4. from [svr-htssqldb].configurator.dbo.[Master Parts List] a
  5. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
  6. on a.mpn=b.mfpn
  7. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
  8. on b.prodid=c.prodid
  9. where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
  10. </cfquery>
  13. <table width="95%" align="center">
  14. <thead>
  15. <tr>
  16. <th>Manu Part number </th>
  17. <th>Techdata_Price</th>
  18. <th>Synnex Price</th>
  19. <th>D&H Price</th>
  20. </tr>
  21. </thead>
  22. <cfset alt=0>
  23. <cfset classStr="">
  25. <cfoutput>
  26. <cfloop query="matchmanu">
  27. <cfset t = 1>
  28. this gets the prices for techdata
  29. <cfif distiid EQ 'C00030'>
  30. <cfquery name="techdata#t#" datasource="Distributor">
  31. Select  COST
  32. From dbo.PRODUCTS
  33. where PART_NUM = '#matchmanu.distisku#'
  34. </cfquery>                          
  35. <cfelse>
  37. </cfif>
  38. <cfset t = t + 1>
  40. <cfset s = 1>
  41. this gets the prices fo synnex
  42. <cfif distiid EQ 'C00055'>
  43. <cfquery name="synnex#s#" datasource="Distributor">
  44. Select Unit_Cost__wo_PromoRebate
  45. From dbo.tbl_synnex_price
  46. where SYNNEX_SKU = '#matchmanu.distisku#'
  47. </cfquery>
  48. <cfelse>
  49. </cfif>
  50. <cfset s = s + 1>
  52. <cfset d = 1>
  53. this gets the prices for dandh
  54. <cfif distiid EQ 'C00791'>
  55. <cfquery name="dandh#d#" datasource="Distributor">
  56. Select unit_cost
  57. From dbo.tbl_dandh_price
  58. where dandh_item_num = '#matchmanu.distisku#'
  59. </cfquery>
  60. <cfelse>
  61. </cfif>
  62. <cfset d = d + 1>
  66. <cfif alt eq 0>
  67.     <cfset classStr="">
  68.     <cfset alt=1>
  69. <cfelse>
  70. <cfset alt=0>
  71. </cfif>
  72. <tr>
  73. <td class="#classStr#">
  74. #matchmanu.distisku#
  75. </td>
  76. this outputs techdata
  77. <td class="#classStr#">
  78. #techdata#t#.COST#
  79. </td>
  80. thisoutputs synnex
  81. <td class="#classStr#">
  82. #synnex.Unit_Cost__wo_PromoRebate#
  83. </td>
  84. this outputs dandh
  85. <td class="#classStr#">
  86. #dandh.unit_cost#
  87. </td>
  88. </tr>
  89. </cfloop>
  90. </cfoutput>
  91. </table>
Thank you,
Feb 3 '09 #1
109 12694
16,027 Recognized Expert Moderator MVP
Some of the code doesn't make sense. For example, on line 38, you increment the variable t, but for what? In the next loop round, it's going to be set back to 1 (line 27). This is the same for d and s.

There's some other problems too, but first, can you post some example data to make things easier.
Feb 3 '09 #2
769 Contributor
Hey Acoder,

Well its hard to really post examples from the database because its a mess, and its hard to make it come up. But i can tell you an example. Basically each company mine,techdata,s ynnex and d and h all have are own part numbers. However, there are many that have the same part number so i combined all the ones that are the same. Then i need to get the price for techdata, synnex and d and h. when i get the price the price has to match up to the part. (basically got to get the price that is suppose to be with the part). an well some parts will not have a price for that part so it has to be able to account for that by simply leaving a space. Right now though if i brought it up the products will match the price, but if a price is missing it will not account for it and so it will take the next price an add it in the blank spot instead of leaving it blank. the #t# was suppose to do the blank part (atleast that is what i was told), but it didn't make any since to me which is why i decided to ask because i don't think its right.

so heres an example of how it should come up

the part number is the part and techdata, synnex and d and h are listing

what price they have for that particular part number. some companys might not have a price for that particular part because they don't have that particular part.

Expand|Select|Wrap|Line Numbers
  3. part number   techdata   synnex       d and h
  5. 5555               6.50        7.50            6.78
Thank you,
Feb 3 '09 #3
16,027 Recognized Expert Moderator MVP
OK, can you explain lines 29, 42 and 54 where the distiid value is compared?

I've also noticed something else. You set, for example, t to 1, but then it's incremented so the query name on line 78 is techdata2 instead of techdata1.
Feb 3 '09 #4
769 Contributor
Hey Acoder,

well lines 29,42 and 54. The numbers themselves specify which vendors i am looking for, in this case i am looking for synnex, d and h and techdata.the cfif is saying distributor (the company) is equal to that number. It basically just makes sure that it gets the right company's (synnex, d and h, and techdata) based on the number. without the distiid eq number i would get all the parts for all the vendors we have instead of just the 3 i need.Its hard to really fully explain because the system is confusing (an not sure why they have made it so difficult).Also , it was not fully explained to me either i was just told the basic.

an yes i admit i got the incrementing pretty confused, i am use to using cfloop not the cfquery.

Thank you,
Feb 3 '09 #5
16,027 Recognized Expert Moderator MVP
But the different prices for the companies are in separate tables anyway, so I don't see the purpose of those cfifs. Also, the distiid will be only one for each row, so that would mean you would only get one company price for each row which is not what you want, I don't think. You should be comparing on part number (which you're already doing in the queries).
Feb 4 '09 #6
769 Contributor
Hey Acoder,

well i must admit its kind of confusing and hard to explain because i admit it didn't make much since to me either. The point of the cfif is to get the right vendor and for cfquery to make sure that the vendor match with the price and then if there is no price for that vendor to make that field blank for that row of prices (which havent told it to go blank yet). All i know for sure is without the cfif is number it will not do this right (atleast that is what i was told).An well i need the price for each row because its going to display as so.

part number synnex price techdata price d and h price
5555 1.04 1.09 20.00
3393 3.00 3.43 9.99

sorry that i can not explain it further. I must admit i was not explained what was done just shown. But without the cfif for each cfquery i will not get the right vendor information for that cfquery.

Thank you,
Feb 4 '09 #7
16,027 Recognized Expert Moderator MVP
Rather than all of these multiple queries, you may be better off with combining it into the main query. You can join the tables on the distisku and distiid values.
Feb 4 '09 #8
769 Contributor
Hey Acoder,

Well i wanted to write an say doing it this way i was able to get one entire row of information to output (which i was not able to before). The thing is there is suppose to be 14 thousand rows appearing (atleast thats what we saw when we did the cfquery to put all the manufacturer part numbers together ).But i am not sure what i am doing wrong that i am only getting one row to appear. Also, i know right now that if a price does not exist for that part that it will not make that price column blank, it will just put the next price in line in that spot.

I been trying to think of a better way to explain the cfif and hope this might be a better way. Each distributor we have is associated with an ID number.Without having cfif it will not know which distributor i am getting the parts out of. Because we have lots of distributors an they all have similar part numbers. The cfif says the distributor is equal to the number or cfif eqal to techdata.Then inside the cfquery it basically selects the price out of the table an then to make sure it gets the right price to the right part it compares the part number out of the table i am selecting the cost from against distisku which is the product id for synnex,techdata and d and h. I don't know if thats a better explanation (i hope it is). But basically the cfif is just saying which company to check for the price for that particular part. if there is no price for that particular part then the price column should be blank for that company on that particular part (which i don't have it going blank).Right now if i output it each part will have a price even if the price don't go with that part and i need to do it where the price matches up to the part.

here is what i tried, i took the #t#,#s# and #d# out of each of them an then the value for all 3 outputed. But it not showing all the rows it should have only one row.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="matchmanu" datasource="Configurator">
  2. select a.[hc part number]'hcpartnumber',a.mpn,a.Mfg,a.lastbuyquote,a.[description],b.prodid,c.distisku,c.distiid
  3. from [svr-htssqldb].configurator.dbo.[Master Parts List] a
  4. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_prod b
  5. on a.mpn=b.mfpn
  6. inner join [svr-htssqldb2].cnet_datasource.cnet_connector.cds_metamap c
  7. on b.prodid=c.prodid
  8. where a.lastbuyquote is not null and (c.distiid='c00030' or c.distiid='c00055' or c.distiid='c00791')
  9. </cfquery>
  11. <table width="95%" align="center">
  12. <thead>
  13. <tr>
  14. <th>Manu Part number </th>
  15. <th>Techdata_Price</th>
  16. <th>Synnex Price</th>
  17. <th>D&H Price</th>
  18. </tr>
  19. </thead>
  20. <cfset alt=0>
  21. <cfset classStr="">
  23. <cfoutput>
  24. <cfloop query="matchmanu">
  25. <cfset t = 1>
  26. <cfif distiid EQ 'C00030'>
  27. <cfquery name="techdata" datasource="Distributor">
  28. Select  COST,DESCR
  29. From dbo.PRODUCTS
  30. where PART_NUM = '#matchmanu.distisku#'
  31. </cfquery>                          
  32. <cfelse>
  33. </cfif>
  34. <cfset t = t + 1>
  38. <cfset s = 1>
  39. <cfif distiid EQ 'C00055'>
  40. <cfquery name="synnex" datasource="Distributor">
  41. Select Unit_Cost__wo_PromoRebate
  42. From dbo.tbl_synnex_price
  43. where SYNNEX_SKU = '#matchmanu.distisku#'
  44. </cfquery>
  45. <cfelse>
  46. </cfif>
  47. <cfset s = s + 1>
  49. <cfset d = 1>
  50. <cfif distiid EQ 'C00791'>
  51. <cfquery name="dandh" datasource="Distributor">
  52. Select unit_cost
  53. From dbo.tbl_dandh_price
  54. where dandh_item_num = '#matchmanu.distisku#'
  55. </cfquery>
  56. <cfelse>
  57. </cfif>
  58. <cfset d = d + 1>
  59. </cfloop>
  62. <cfif alt eq 0>
  63.     <cfset classStr="">
  64.     <cfset alt=1>
  65. <cfelse>
  66. <cfset alt=0>
  67. </cfif>
  68. <tr>
  69. <!---<cfoutput>--->
  70. <td class="#classStr#">
  71. #matchmanu.distisku#
  72. </td>
  73. <td class="#classStr#">
  75. #techdata.COST#
  76. <td class="#classStr#">
  77. #synnex.Unit_Cost__wo_PromoRebate#
  78. </td>
  79. <td class="#classStr#">
  80. #dandh.unit_cost#
  81. </td>
  82. </tr>
  84. <!---</cfloop>--->
  85. </cfoutput>
  87. </table>
Thank you,
Feb 4 '09 #9
769 Contributor
Hey Acoder,

I am a bit confused on what you mean by joining the distisku and distiid values on the main cfquery i have. I must admit i am afraid to mess with that big cfquery based on i don't know how much data is really involved or what all happens in each table. i was just shown what to do an not givin an explanation.

Thank you,
Feb 4 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Bill Sneddon | last post by:
Can any one tell me how to output the following string? <%response.write "<tr><td><a href=""file://SERVER/mmlogs/TNAME" & yearmonth & """>"& "MYJUNK" & "</a><BR></td></tr>" %> <xsl:variable name="SERVER" select="MM_NAME" /> <xsl:variable name="TNAME" select="TOOL_NAME" />
by: Marcel Akkerman | last post by:
Hi, Does anyone have a clue how to reduce the number of nodes using XSLT? When outputing all nodes in order I could just use <xsl:for-each select="name"> But what if I, besides sorting and grouping, I also output conditionally? <xsl:for-each
by: Richard Hollenbeck | last post by:
I noticed I can't push a value into a text box by saying something like, "txtThisTextBox = intSomeVariable * 0.5" because I get an run-time error saying I can't assign a value to this object. So instead I made the control source a function to pull the value from the function. That works. Inside the text box I simply write something...
by: martin | last post by:
Hi, I am a web page and a web user control. My web user control is placed in my web page using the following directive <%@ Register TagPrefix="uc1" TagName="Header" Src="WebControls/Header.ascx" %> The web user control contains the following server controls
by: John Lau | last post by:
Hi, Is there documentation that talks about the page lifecycle, the lifecycle of controls on the page, and the rendering of inline code, in a single document? Thanks, John
by: Russ Chinoy | last post by:
Hi, This may be a totally newbie question, but I'm stumped. If I have a function such as: function DoSomething(strVarName) { ..... }
by: fernandezr | last post by:
I would like to use a user control as a template inside a repeater. Some of the fields in the control should be hidden depending on whether or not there is data. I'm still a ASP .Net newbie so the way I'm going about doing this might be a little off. I'd appreciate some help. Below is the code I have thus far but I'm not sure how to...
by: eric.goforth | last post by:
Hello, I'm getting: msxml3.dll (0x80004005) Error while parsing "file:///c:/WWWROOT/includes/Inc.xsl". A name was started with an invalid character. When I put in the "{$sDisabledString}"
by: Giff | last post by:
Hi again, I need a suggestion. Right now I am developing an application and I want to be able to output on screen some variables. Anyway, I want to remove these output operations when passing on the implementation of another function or finiship up the program etc.. Still I whish to be able to have the output again in case I will need to...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.