Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Problem

Status
Not open for further replies.
LearnersPermit

LearnersPermit

Technical User
Joined
May 30, 2001
Messages
70
Location
CA
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>
 
Sort by date Sort by votes
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
-----------
I'll have the roast duck with the mango salsa.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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>
 
Upvote 0 Downvote
nowhere do i see locitemid being set

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

r937.com | rudy.ca
 
Upvote 0 Downvote
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>
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question Question
Displaying results of an SQL Pivot Query
Replies
1
Views
848
jdhilljr
jdhilljr
mdvp
  • Locked
  • Question Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
703
mdvp
mdvp
Ceilicat
  • Locked
  • Question Question
Query of Queries Union Not Totaling Across Fields
Replies
2
Views
471
OneTrickPony
OneTrickPony
ibeenjammin
  • Locked
  • Question Question
FrameMaker 2015 Conditional Text Problems
Replies
0
Views
720
ibeenjammin
ibeenjammin
redoakhg
  • Locked
  • Question Question
SQL Query Help
Replies
5
Views
482
redoakhg
redoakhg

Part and Inventory Search

Sponsor

Back
Top