Simple CF help

Get Started
1

Hello I’m new to coldfusion. What I’m trying to do is use a form with multiple input text fields. I can get it to work with just one text field (sku), but when I add another text field (upc) it then returns nothing. What might I be doing wrong? Code below, first page is the start page, second is the cf form:

Thanks
First:
<head>

<meta http-equiv=“Content-Type” content=“text/html; charset=utf-8” />
<title>Untitled Document</title>
<style type=“text/css”>
<!–
body {
font: 100% Verdana, Arial, Helvetica, sans-serif;
background: #666666;
margin: 0; /* it’s good practice to zero the margin and padding of the body element to account for differing browser defaults /
padding: 0;
text-align: center; / this centers the container in IE 5* browsers. The text is then set to the left aligned default in the #container selector /
color: #000000;
}
.oneColElsCtr #container {
width: 690px;
background: #FFFFFF; / the auto margins (in conjunction with a width) center the page /
border: 1px solid #000000;
text-align: left; / this overrides the text-align: center on the body element. /
margin-top: 0;
margin-right: auto;
margin-bottom: 0;
margin-left: auto;
}
.oneColElsCtr #mainContent {
padding: 0 20px; / remember that padding is the space inside the div box and margin is the space outside the div box */
}
.menuCell {
text-align:center;
padding:10px;
}
a {
text-decoration: none;
margin: 5px;
}
a:hover {
background-color: #9FCCF0;
}
.oneColElsCtr #container #mainContent td {
padding: 5px;
}
–>
</style>
</head>
<body>
<FORM ACTION=“x.cfm” target=“main” METHOD=“post”>
<h1 class=“rrwe”><font color=“#ffffff”>Advanced History Look Up</font></h1>
<body class=“oneColElsCtr”>
<div id=“container”>
<div id=“mainContent”>
<table width=“508” border=“1”>
<tr>
<td bgcolor=“#FFFFFF” width=“253”><div align=“left”>
<div align=“center”><strong><font face=“Arial” size=“2”>SKU</font></strong> 
<input type=“Text” name=“sku” value=“<cfif IsDefined(“Session.sku”) AND session.sku# IS NOT “”>#Session.sku#</cfif>” size=“25” />
</div></td>
<td bgcolor=“#FFFFFF” width=“253”><div align=“left”>
<div align=“center”><strong><font face=“Arial” size=“2”>UPC</font></strong> 
<input type=“Text” name=“upc” value=“<cfif IsDefined(“Session.upc”) AND session.upc# IS NOT “”>#Session.upc#</cfif>” size=“25” />
</div>

</table>
</div></td>
</body>
</html>

second:
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=utf-8” />
<title>Untitled Document</title>
<style type=“text/css”>
<!–
body {
background-color: #666666;
}
–>
</style></head>

<body>
<cfquery name=“qsku” Datasource=“#application.datasource#”>
SELECT WTR.WTID, WTR.SKU, WTR.UPC, WTR.Artist, WTR.ItemName, WTR.Quantity, WTR.QtyReceived, WTR.ReceivedDate, WTR.WTDate, WTR.Attribute2
FROM WTR INNER JOIN
WT ON WTR.WTID = WT.WTID
where WTR.sku = ‘#form.sku#’ and (WT.WTTypeID = 1)
ORDER BY WTDate DESC
<cfif IsDefined(“Session.sku”)>
<cfif session.sku# IS NOT “”>
AND WTR.sku = session.sku#
</cfif>
</cfif>
</cfquery>
<cfif IsDefined(“Session.sku”)>
<cfif session.upc# IS NOT “”>
AND WTR.sku = session.sku#
</cfif>
</cfif>
</cfquery>
<cfif qsku.RecordCount IS 0>
<h1 align=“center”><font color=“#ffffff”>No data was found. </font></h1>
<p>
<cfelse>
</p>
<p>  </p>
<table border=“1”>
<tr>
<td colspan=“2” bgcolor=“#ffffff” width=“100”><div align=“center”><cfoutput>#qsku.RecordCount# records found</cfoutput></div></td></tr>
<table border=“1”>
<table border=“1”>
<tr>
<th width=“150” nowrap=“nowrap” bgcolor=“#999999”>Date Ordered</th>
<th width=“73” nowrap=“nowrap” bgcolor=“#999999”>PO#</th>
<th width=“100” nowrap=“nowrap” bgcolor=“#999999”>SKU</th>
<th width=“100” nowrap=“nowrap” bgcolor=“#999999”>UPC</th>
<th width=“150” nowrap=“nowrap” bgcolor=“#999999”>Artist</th>
<th width=“150” nowrap=“nowrap” bgcolor=“#999999”>Title</th>
<th width=“50” nowrap=“nowrap” bgcolor=“#999999”>Format</th>
<th width=“50” nowrap=“nowrap” bgcolor=“#999999”>Qty Ordered</th>
<th width=“50” nowrap=“nowrap” bgcolor=“#999999”>Qty Received</th>
</tr>

<cfoutput query=“qsku”>
<cfif qsku.CurrentRow mod 2 IS 1>
<cfset bgcolor=“white”>
<cfelse>
<cfset bgcolor=“silver”>
</cfif>
<tr bgcolor=“#variables.bgcolor#”>
<td> #Dateformat(qsku.WTDate, ‘mm/dd/yy’)# - #Timeformat(qsku.WTDate, ‘hh:mm tt’)#</td>
<td>#WTID#</td>
<td>#SKU#</td>
<td>#UPC#</td>
<td>#Artist#</td>
<td>#ItemName#</td>
<td>#Attribute2#</td>
<td>#Quantity#</td>
<td>#QtyReceived#</td>
</cfoutput>
</table>
</cfif>
<
</body>
</html>

2

Hi welcome to SPF :slight_smile: I’m not sure if i understand you right. Do you try to generated these textfields with a query? Do you have a link to a test page or so?

3

Nope no test page, it’s from a local POS. I’m trying to have it look at the sku or upc column in the WTR table so here’s the query:
<cfquery name=“qsku” Datasource=“#application.datasource#”>
SELECT WTR.WTID, WTR.SKU, WTR.UPC, WTR.Artist, WTR.ItemName, WTR.Quantity, WTR.QtyReceived, WTR.ReceivedDate, WTR.WTDate, WTR.Attribute2
FROM WTR INNER JOIN
WT ON WTR.WTID = WT.WTID
where WTR.sku = ‘#form.sku#’ and (WT.WTTypeID = 1)
ORDER BY WTDate DESC

But when I add an text field it returns nothing. Not even a CF error. So I’m messing something up somewhere.

4

It is a bit difficult to say from what I see! What are the sessions Session.sku and Session.upc?

Where are those sessions declared?

I’m also confused about the query (you use two closing tags) and don’t understand that you don’t get any errors:


</cfquery>
<cfif IsDefined("Session.sku")>
<cfif #Session.upc# IS NOT "">
AND WTR.sku = #Session.sku#
</cfif>
</cfif>
</cfquery>

furthermore are you not very consistent with the use of case:


SELECT WTR.SKU

AND WTR.sku =

Try to correct those mistakes first, and see what happens!

Edit: You have furthermore AND statements after the ORDER BY statements. ORDER BY should be the last in the row. I think your query should look more like this:


<cfquery name="qsku" Datasource="#application.datasource#">
	SELECT 
  		WTR.WTID
  	, WTR.SKU
    , WTR.UPC
    , WTR.Artist
    , WTR.ItemName
    , WTR.Quantity
    , WTR.QtyReceived
    , WTR.ReceivedDate
    , WTR.WTDate
    , WTR.Attribute2
	FROM WTR 
  INNER 
  	JOIN WT 
    	ON WTR.WTID = WT.WTID
	WHERE 
  		WTR.sku = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( form.sku )#" />
 	AND (WT.WTTypeID = 1)
  <cfif structKeyExists( Session, 'sku' )>
		<cfif Session.sku Neq "">
	AND WTR.sku = <cfqueryparam cfsqltype="cf_sql_integer" value="#Trim( Session.sku )#" />
		</cfif>
	</cfif>
	ORDER BY WTDate DESC
</cfquery>
5

Could you please correct these statements and try

<cfif IsDefined(“Session.sku”)>
<cfif session.sku# IS NOT “”>
AND WTR.sku = session.sku#
</cfif>
</cfif>
</cfquery>
<cfif IsDefined(“Session.sku”)>
<cfif session.upc# IS NOT “”>
AND WTR.sku = session.sku#
</cfif>

6

:nono: no!!

you cannot append something to the WHERE clause after closing the query

<cfif IsDefined(“Session.sku”)>
<cfif session.sku# IS NOT “”>
AND WTR.sku = session.sku#
</cfif>
</cfif>
</cfquery>
<cfif IsDefined(“Session.sku”)>
<cfif session.upc# IS NOT “”>
AND WTR.sku = session.sku#
</cfif>