Query Problem

[LearnersPermit]

I have a Stores Catalogue that I have created a problem with the edit page.

What happens is that the user selects the item id and description of the item that they want to edit. So long as all information is completed, the item is displayed. If only part of the information is displayed, there are no records returned.

Any suggestions?

<!--- Get the list of Items - this is where they choose the item to be editted--->
<cfquery name="qryGetAllItems" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT itemid,description
FROM tblStores
ORDER BY itemid ASC
</cfquery>

<!--- Get one item - this should display what is to be editted --->
<cfquery name="qryGetItem" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT a.itemid,a.description,a.unit,a.price,a.other,a.catid,a.supplierid,a.enabled,b.catid,b.category,c.suppid,c.supplier
FROM tblStores a,
tblCategory b,
tblSupplier c
WHERE itemid = #locitemid#
AND a.catid=b.catid
AND a.supplierid=c.suppid
ORDER BY itemid ASC
</cfquery>

 

[philhege]

I don't see anything wrong with your queries, although I'd use JOIN statements instead of a table laundry list.

The real problem seems like it has to do with your business logic. Show the CF code that handles this data and we could diagnose the problem.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[r937]

the "no records returned" problem obviously arises because #locitemid# isn't a valid itemid

by the way, you don't need the ORDER BY in qryGetItem ;-)

r937.com | rudy.ca

 

[LearnersPermit]

Thank you r937.

You're right about the ORDER BY in qryGetItem, it doesn't serve a purpose.

No doubt you are also right about my business logic. Here is the code

<table cellpadding="10">
<tr><th>Edit a Item</th>
</tr>
<tr>
<td>
<p>Select an Item to edit or delete</p>
<form name="edititems" action="#cgi.script_name#" method="post">
<select name="itemid">
<option value="">Select Item
<cfoutput query="qryGetAllItems">
<option value="#itemid#"<cfif locitemid EQ itemid> selected</cfif>>#itemid# - #description#</option>
</cfoutput>
</select>
<input type="Submit" value="Confirm" name="smtEditItem">
</form>

<form action="edit_items.cfm" method="post">

<pre>
<cfoutput>
Item No.:
<input type="text" name="itemid" value="#locitemid#">

<cfset temp = Replace(qryGetItem.description, """", "''", "All")>
Description:
<input type="text" name="description" value="#qryGetItem.description#" size="60" maxlength="255">

price:
<input type="text" name="price" value="#qryGetItem.price#" size="25">

unit:
<input type="text" name="unit" value="#qryGetItem.unit#" size="60">

other:
<input type="text" name="other" value="#qryGetItem.other#" size="60" maxlength="255">

</cfoutput>
<!---Category drop down box --->
Category:
<select name="catid">
<option value="#qryGetItem.category#">
</option>
<cfoutput query="qryselectCat">
<option value="#qryselectCat.catid#" <cfif qryGetItem.catid EQ qryselectCat.catid> selected</cfif>>#qryselectCat.category#</option></cfoutput>
</select>

<!---Supplier drop down box --->
Supplier:
<select name="supplierid">
<option value="#qryselectCat.supplier#">
</option>
<cfoutput query="qryselectSupplier">
<option value="#qryselectSupplier.supplierid#" <cfif qryGetItem.supplierid EQ qryselectSupplier.supplierid> selected</cfif>>#qryselectSupplier.supplier#</option></cfoutput></option>
</select>

 

[r937]

nowhere do i see locitemid being set

that's probably what's causing the query to return no rows

r937.com | rudy.ca

 

[LearnersPermit]

Here is my entire code. It does work as it is so long as the user has every field filled. It fails when they don't.

<cfparam name="form.action" default="disp">
<cfparam name="form.name" default="">

<cfif not isdefined("form.submit")>
<cfparam name="form.itemid" default="">
<cfparam name="form.description" default="">
<cfparam name="form.price" default="">
<cfparam name="form.unit" default="">
<cfparam name="form.catid" default="">
<cfparam name="form.supplierid" default="">
<cfparam name="form.other" default="">

</cfif>

<!--- Set a local var for the item id --->
<cfif ISDEFINED("form.itemid") AND form.itemid GT 0>
<cfset locitemid=form.itemid>
<cfelse>
<cfset locitemid="0">
</cfif>

<cfif ISDEFINED("form.smtDeleteItem") AND locitemid GT 0>
<!--- Delete the Item --->
<cfquery name="qrydelItem" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
DELETE FROM TBLSTORES
WHERE itemid = #locitemid#
</cfquery>

<cfset locPostMessage="Deleted Successfully">

<cfelseif ISDEFINED("form.smtSubmitItemInfo") AND locitemid GT 0>
<!--- Edit the Item --->
<cfquery name="qryupdateItem" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
UPDATE tblStores
SET description = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.description#">,
price = <cfqueryparam cfsqltype="cf_sql_real" value="#form.price#">,
unit = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.unit#">,
other = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.other#">,
catid = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.catid#">,
supplierid = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.supplierid#">
WHERE itemid = #locitemid#
</cfquery>

<cfset locPostMessage="The Items List was Edited">
</cfif>

<!--- Get the list of Items --->
<cfquery name="qryGetAllItems" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT itemid,description
FROM tblStores
ORDER BY itemid ASC
</cfquery>

<!--- Get one Item --->
<cfquery name="qryGetItem" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT a.itemid,a.description,a.unit,a.price,a.other,a.catid,a.supplierid,b.catid,b.category,c.suppid,c.supplier
FROM tblStores a,
tblCategory b,
tblSupplier c
WHERE itemid = #locitemid#
AND a.catid=b.catid
AND a.supplierid=c.suppid

</cfquery>



<cfif ISDEFINED("locPostMessage")>
<cfoutput><h3>#locPostMessage#</h3></cfoutput>
</cfif>

<!-- Get a category --->
<cfquery name="qryselectCat" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT catid, category
FROM tblCategory
ORDER BY category ASC
</cfquery>

<!---Get a supplier--->
<cfquery name="qryselectSupplier" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT suppid as supplierid,supplier
FROM tblSupplier
ORDER BY supplier ASC
</cfquery>

<cfif form.action eq "disp">
<html>
<head>
<title>"Stores Catalogue - Edit an Item"</title>
<link rel="stylesheet" href="stores.css" type="text/css">

<br />

</head>
<body>
<table cellpadding="10">
<tr><th>Edit a Item</th>
</tr>
<tr>
<td>
<p>Select an Item to edit or delete</p>
<form name="edititems" action="#cgi.script_name#" method="post">
<select name="itemid">
<option value="">Select Item
<cfoutput query="qryGetAllItems">
<option value="#itemid#"<cfif locitemid EQ itemid> selected</cfif>>#itemid# - #description#</option>
</cfoutput>
</select>
<input type="Submit" value="Confirm" name="smtEditItem">
</form>

<form action="edit_items.cfm" method="post">

<pre>
<cfoutput>
Item No.:
<input type="text" name="itemid" value="#locitemid#">

<cfset temp = Replace(qryGetItem.description, """", "''", "All")>
Description:
<input type="text" name="description" value="#qryGetItem.description#" size="60" maxlength="255">

price:
<input type="text" name="price" value="#qryGetItem.price#" size="25">

unit:
<input type="text" name="unit" value="#qryGetItem.unit#" size="60">

other:
<input type="text" name="other" value="#qryGetItem.other#" size="60" maxlength="255">

</cfoutput>
<!---Category drop down box --->
Category:
<select name="catid">
<option value="#qryGetItem.category#">
</option>
<cfoutput query="qryselectCat">
<option value="#qryselectCat.catid#" <cfif qryGetItem.catid EQ qryselectCat.catid> selected</cfif>>#qryselectCat.category#</option></cfoutput>
</select>

<!---Supplier drop down box --->
Supplier:
<select name="supplierid">
<option value="#qryselectCat.supplier#">
</option>
<cfoutput query="qryselectSupplier">
<option value="#qryselectSupplier.supplierid#" <cfif qryGetItem.supplierid EQ qryselectSupplier.supplierid> selected</cfif>>#qryselectSupplier.supplier#</option></cfoutput></option>
</select>

</pre>
<input type="submit" value="Edit" name="smtSubmitItemInfo">
<cfif locitemid GT 0>
<input type="Submit" value="Delete" name="smtDeleteItem">
<input type="button" value="Cancel" onclick="location.href='edit_items.cfm'">
<cfelse>
<input type="Submit" value="Delete" name="" disabled>
<input type="button" value="Cancel" disabled>
</cfif>
</form>
</td>
</tr>
</table>
</body>
</html>
</cfif>

 

[r937]

well, there's your answer

if the user doesn't know the itemid to search for, you set locitemid to 0, and that's why the query cannot find any items

r937.com | rudy.ca

 

[LearnersPermit]

Thank you.

Curious question, why does it work when all of the fields are complete? It only fails if the user leaves some of the fields empty.

 

[LyndonOHRC]

Beacuse you are setting the item to a value that is not in your data and zero rows are being returned.

You need to put conditions around your select logic to not filter the query if a criteria item is not selected.

quick example:

select * from mytable
where 0=0
<cfif myfirstselecteditem gt 0>
and dataFirstItem=myfirstselecteditem
</cfif>

Another tip: return the RecordCount on your result page while your trouble shooting your code.

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey