Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Need help with Cold fusion coding

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
Rob099
Asked:
Rob099
  • 8
  • 6
  • 4
  • +2
1 Solution
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
 
OP_ZaharinCommented:
- 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
 
OP_ZaharinCommented:
- 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob099Author Commented:
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
 
Rob099Author Commented:

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
 
OP_ZaharinCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
 
gdemariaCommented:
> 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
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
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
 
gdemariaCommented:
@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
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
whatever the case be, but as the author is not specying what error is getting, this is just a guesswork
0
 
Rob099Author Commented:
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
 
gdemariaCommented:
> 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
 
Rob099Author Commented:
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
 
gdemariaCommented:

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
 
Rob099Author Commented:
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
 
gdemariaCommented:
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
 
Rob099Author Commented:
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
 
OP_ZaharinCommented:
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
 
Rob099Author Commented:
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
 
gdemariaCommented:
> 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
 
Rob099Author Commented:
To gdemaria

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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