Solved

Need help with Cold fusion coding

Posted on 2011-03-24
23
525 Views
Last Modified: 2013-12-24
I need help with cf coding.

This should be very simple. All I want to do is display the price of the item that I choose in my HTML code. Here is what I came up with but it doesn't work. I get an error.

<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">

SELECT Price
FROM mytable
WHERE ProdID= #product#
</cfquery>

<cfset product="1">
<cfoutput query="prodpricing">#Price#</cfoutput>

I can tell something is wrong but I can't figure it out. Thanks. I attached an excel file for my table.

 Book1.xlsx
0
Comment
Question by:Rob099
  • 8
  • 6
  • 4
  • +2
23 Comments
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 35205181
What Error are you getting!

Can you try something like this!

<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">

SELECT Price
FROM mytable
WHERE ProdID= <cfqueryparam CFSQLType="CF_SQL_INTEGER" value="#product#">
</cfquery>

<cfdump var="#prodpricing#">
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35205198
- will u share with us the error message?

- have u create the datasource name as in #Request.DS# variable in Coldfusion Administrator console under Data Source menu? your datasource needs to be defined in the ColdFusion Administrator.
- as for the username and password, it also is to be specified in the datasource settings in the Coldfusion Administrator

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35205204
- you might want to change the cfset before the cfquery. based on your current code, it is not taking any variables.
- try to follow the following codes:

<cfset product="1"> 

<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
SELECT Price
FROM mytable
WHERE ProdID= #product#
</cfquery>

<cfoutput query="prodpricing">#Price#</cfoutput>

Open in new window


0
 

Author Comment

by:Rob099
ID: 35205434
Sorry, I don't remember the error message. Lets ignore the error message for now and lets try to write the code like its supposed to be.

to myselfrandhawa: The file I attached is just a small sample of the database. Some of the ProdID have letters and numbers in the code. example "C4515500"   Can I use your code for integer even for text?

And I don't see anywhere in your code the option to pick which product I will be displaying the price for.
0
 

Author Comment

by:Rob099
ID: 35205449

To OP_Zaharin:  I don't want to add that whole code before each different item that I have to display the price for. I think the query should only be entered once and they I should be able to call up the price for the multiple items displayed on that page with just a single line of code.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35205584
rob099,
- i'm just put you into the direction where your codes is wrong. hope this explanation helps:

1- your current codes doesn't show how do you get the value #product#? and is there any particular reason for having <cfset product="1">  after the query?
<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
SELECT Price
FROM mytable
WHERE ProdID= #product#
</cfquery>
<cfset product="1">
<cfoutput query="prodpricing">#Price#</cfoutput>


- if you want to display the whole record in the table, take out the WHERE clause. you might want to re-write the codes like this:

<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
SELECT Price
FROM mytable
</cfquery>

<cfoutput query="prodpricing">#Price#</cfoutput>

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35205594
Hi,

First Try this one


<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
SELECT ProdID,Price
FROM mytable
</cfquery>

<cfoutput query="prodpricing">
#ProdID#, #Price#<br>
</cfoutput>


if column containing string value then in filter you need to give Single Quotes '
<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
SELECT ProdID,Price
FROM mytable
where prodid = '#prodid#'
</cfquery>

<cfoutput query="prodpricing">
#ProdID#, #Price#<br>
</cfoutput>

p.s.
Hope this attributes are set properly.
datasource="#Request.DS#" username="#user#" password="#pass#"


- Bhavesh
0
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 35206065
Ok! Let's Assume like this:

<cfparam name="product" default="1">
<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
SELECT Price
FROM mytable
WHERE ProdID= <cfqueryparam CFSQLType="CF_SQL_varchar" value="#product#">
</cfquery>
<cfdump var="#prodpricing#">

as specified you said you have alphabets also in ProdId, so you can use varchar in that!

I used the cfparam tag above that if any value is coming from anyy source like form, url, request or session scope, it will go through your query if it does not dound any value at least it will use the default value of 1.

So my question can u please dump the value of the query and show here, it will be nicer to see the details of what we are getting!
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35207069
> All I want to do is display the price of the item that I choose in my HTML code

Rob, I think the confusion is that you haven't show how you are choosing a product.  Typically, there may be a list of products and each product has a link on it, if you click the link, then it will take you to a product-detail page where you will show the details of that product (including the price).   But it's not clear if you are doing that or if you're doing something else.

<cfoutput query="getProds">
 <tr>
  <td><a href="prodDetail.cfm?prodID=#getProds.prodID#">#getProds.productName#</a>
 </tr>
</cfoutput>


> Some of the ProdID have letters and numbers in the code

as a coding tip, it's often best to have an ID on every table that is an auto incremented integer value.  If you have a unique indentifier for the product, you could call it productNumber and make it a varchar.  Use the numeric productID as foreign keys to other table.

0
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 35207206
you have some Coldfusion process page like

<cfform method="post" action="abc.cfm">
<cfoutput query="getProds">
 <tr>
  <td><a href="prodDetail.cfm?prodID=#getProds.prodID#">#getProds.productName#</a>
 </tr>
</cfoutput>
<input type="submit" name="check" value="Submit">
</cfform>

in your abc.cfm page do it like this

<cfparam name="form.product" default="1">
<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
SELECT Price
FROM mytable
WHERE ProdID= <cfqueryparam CFSQLType="CF_SQL_varchar" value="#form.product#">
</cfquery>
<cfdump var="#prodpricing#">

making sure you dsn info is correct
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35207272
@myselfrandhawa, you're mixing up a form with links.  The product name has a link on it, there are no check boxes or input tags, so it doesn't need to be in a form, you're receiving variable should be URL scope, not form scope.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 35208187
whatever the case be, but as the author is not specying what error is getting, this is just a guesswork
0
 

Author Comment

by:Rob099
ID: 35210390
Ok I will try to better explain my question.

First of all I attached a new database.

I want to display the price of a perticular prodID on my webpage. The way I want to call the price is by writing the prodID number directly in the HTML where I want that price to display.


Book2.xlsx
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35210460
> The way I want to call the price is by writing the prodID number directly in the HTML where I want that price to display.

Sorry, this isn't clear.   What do you mean "writing the prodID" into "HTML" ?  

Are you saying that you will have a form field and the user will type a product number into the form field and when finished he/she will hit a button and the price will appear next to the form field?

Or are you saying you just want to put the price onto the page like this..

<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
   SELECT Price
  FROM mytable
  WHERE ProdID= #product#
</cfquery>

<table><tr>
  <td>
 <cfoutput>#prodpricing.Price#</cfoutput>
 </td></tr></table>


or .... ?
0
 

Author Comment

by:Rob099
ID: 35210618
To: gdemaria

I will enter the ProdID myself directly into the HTML code.

There will not be any forms and the user will not enter this info.

I think you have the right idea for the code but I don't see where I would enter the ProdID I wish to display the price for.


0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35210660

Is this what you're looking for?  

You want to hard code the product ID into code, fetch the price from the database and display in on a web page?
<cfset TheProductID = 'AB1234'>  <!---- change this to your product ID ----->


<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
  SELECT Price
  FROM mytable 
  WHERE ProdID= '#TheProductID#'
</cfquery>

<table>
<cfoutput>
 <tr>
  <td>Product: </td>
  <td>#prodpricing.productName#</td>
 </tr>
 <tr>
  <td>Price: </td>
  <td>#prodpricing.Price#</td>
 </tr>
 </cfoutput>
</table>

Open in new window

0
 

Author Comment

by:Rob099
ID: 35210728
to: gdemaria
Yes that is what I'm trying to do. But I have over 20 items on that page. And all I want to do is be able to write the code once on top of the page and then just set my prodID to fetch the price from the database and display it on the webpage.

From what I understand from that coe, it looks like I would have to paste all that code before each of the item price I would like to display, is that correct?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35210826
Man, it's so hard to picture what you want.

If you have 20 items displayed on the page, why are you entering ONE product ID?

If you are already listing 20 products, you just want the price next to each product?

 ACME T-Shirt  $100

 POLAR ICE CREAM $4


Why don't you post that code and tell us where you want the price.  Or you can take a screen shot and show us.

I would think whatever query you are doing to show the list of items, you can expand to get the price too.   Then you don't need to hard code the product ID.  

It's just sooo confusing.



0
 

Author Comment

by:Rob099
ID: 35211135
The thing is the items are not pulled from any database. They are hard coded in the HTML.

The coding that I will paste here is not correct or it wont make sense, but I will display it to show what I am trying to do.


<cfset TheProductID = 'AB1234'>  <!---- change this to your product ID ----->

<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
  SELECT Price
  FROM mytable
  WHERE ProdID= '#TheProductID#'
</cfquery>

<table>
<cfoutput>
 <tr>
<td><img src="images/specialk.jpg">Special K</td>
</tr>
<tr>
  <td>Price: </td>
  <td>#prodpricing.Price#</td><!---- This is where the price of the "Special K" displays ----->
 </tr>

 <tr>
<td><img src="images/CornFlakes.jpg">Corn Flakes</td>
</tr>
<tr>
  <td>Price: </td>
  <td>#prodpricing.Price#</td><!---- This is where the price of the "Corn Flakes" displays ----->
 </tr>

 <tr>
<td><img src="images/fruit.jpg">Fruit loops</td>
</tr>
<tr>
  <td>Price: </td>
  <td>#prodpricing.Price#</td><!---- This is where the price of the "Fruit Loops"  displays ----->
 </tr>
 </cfoutput>
</table>

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35211835
hi rob,
- if you want to display the whole product record in the database to the HTML. you dont need to specify the  TheProductID and the WHERE clause.
- like i suggest before, you don't need the WHERE clause. taking out the WHERE clause from the query , will return the whole Product data into the HTML.
- you also have images for each product and you want the images to display before each product name and price. therefore i use the cfif condition againts the product id and display the image for each product and the product description/name
- is this what you are trying to achieve? do correct me if i understand wrongly :)
- if my understanding is correct, you can use the following code to display whole record without specifying any product ID. i also pickup the ProdID, ProdDesc and Price in the query so that it can be displayed in HTML:
 
<cfquery name="prodpricing" datasource="#Request.DS#" username="#user#" password="#pass#">
  SELECT ProdID, ProdDesc, ProdPrice
  FROM mytable 
</cfquery>

<table>
<cfoutput query="prodpricing">
<tr>
<td>
<cfif ProdID = 'BG5632'>  <!--- display image and name for special K --->
         <img src="images/specialk.jpg">#ProdDesc#</img> 
<cfelseif ProdID = 'BG5632'>
         <img src="images/CornFlakes.jpg">#ProdDesc#</img>  <!--- display image and name for cornflakes --->
<cfelseif ProdID = 'F45215'>
         <img src="images/fruit.jpg">#ProdDesc#</img>  <!--- display image and name for fruit loops --->
</cfif>
</td>
</tr>
<tr>
  <td>Price: </td>
  <td>#Price#</td><!---- This is where the price of the each product displays in a loop ----->
 </tr>

 </cfoutput>
</table>

Open in new window


- you can continue write the condition of cfifelse for other productid and images. hope this code helps :)

0
 

Author Comment

by:Rob099
ID: 35211897
Hi OP, thanks for trying but you are answering quesitons that I didn't ask. I don't want to output images, description or other data from the database. I only want to fetch the price. That is it.

Thanks for all for trying. This was solved from another source. Thanks again.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 35212441
> The coding that I will paste here is not correct or it wont make sense, but I will display it to show what I am trying to do.

Excellent!  I get it !!  


Here is some reusable code for you.  Place the function at the top of the page and then call it below as needed.  If any changes need to be made, you can just change the function.


<!---- put this at the top of the page ----->

  <cffunction name="getPrice" access="public" output="false" >
	 <cfargument name="p_productID"  required="true" default=""/>

     <cfset var getPrice = "">
     <cfif len(trim(arguments.p_productID))>
       <cfreturn "">
     </cfif>
     <cfquery name="getPrice" datasource="#Request.DS#" username="#user#" password="#pass#">
      SELECT Price
      FROM mytable 
      where prodID = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.p_productID#">
     </cfquery>
     
     <cfreturn getPrice.price>
 </cffunction>




Now use it in your code, before each hard coded product

<cfset thePrice = getPrice("ABC12345")>

The ACME Fishing Rod Costs:  <cfoutput>#thePrice#</cfoutput>


<cfset thePrice = getPrice("XYZ2222")>

The Polar Ice Boat Costs:  <cfoutput>#thePrice#</cfoutput>

Open in new window

0
 

Author Comment

by:Rob099
ID: 35212707
To gdemaria

 Thanks, I will try it out tonight. I will give you an update later. Thanks again.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now