Need help with ASP and Store proc

[MrHelpMe]

Hi Guys,

I have a stored proc that returns data on the fly. What I mean by this is if I run the proc it returns a listing of categories. If I run the proc with a category it returns a listing of types and if I run the prco with a category and type it returns and listing of items. Now my asp code to retunr the list is not working. This is the code. Why do I not get a listing of categories. Thanks.

oConn.Open

oCmdTerritory.ActiveConnection = oConn
oCmdTerritory.CommandType = adCmdStoredProc
oCmdTerritory.CommandText = "p_aic_sam_fatchCat"

errstatus = false
err.Clear
On Error Resume Next
Set objRSTerritory = oCmdTerritory.Execute
if err.number <> 0 then
err.Clear
errstatus = true
end if
%>


<tr>
<td width="26%" height="13"><font face="Arial" size="2"><b>Category</b></font></td>
<td width="83%" height="13"><font face="Arial" size="1">
<SELECT NAME="Territory" SIZE="1" style="font-family: Arial; font-size: 8pt; position: relative; width: 200">
<%
while not objRSTerritory.EOF
if Trim(objRSTerritory("Category")) <> 0 then
%>
<center><option value="<%=Trim(objRSTerritory("Category"))%>">
<%=Trim(objRSTerritory("Category"))%></option>
</center></option>
<% end if
objRSTerritory.MoveNext()
wend
%>
</SELECT>
</TD>
<TD></TD>
</TR>

 

[DreXor]

could you please post your SQL statement you're using and how you're creating your RS

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[MrHelpMe]

This is my sql statment. Again it is a stored procedure. I have my recordset and database information in an include file.

CREATE PROCEDURE dbo.p_aic_sam_fatchCat
@Category varchar(50) = '',
@Type varchar(50) = ''
AS
/*This procedure will return a list of Categories, Types or Items, depends on parameters*/

if @Category <> ''
begin
if @Type <> ''
SELECT C200000005 Item
FROM T23
WHERE C200000003 = @Category
and C200000004 = @Type
ORDER BY Item
else
SELECT DISTINCT C200000004 Type
FROM T23
WHERE C200000003 = @Category
ORDER BY Type
end
else
SELECT DISTINCT C200000003 Category
FROM T23
ORDER BY Category
GO

 

[DreXor]

the SQL statement i was referring to is the one in the ASP.
seems i overlooked it before, my apologies.

and as for not getting a list :

try removing the conditional statment about trim(category) <> 0 that way you output everything in the recordset, empty or not.

secondly, might want to add an :

If RS.EOF or RS.BOF then
response.write "no records returned"
Else
<your code>
End if


this will at least give you a visual feedback if your SP is even giving you a recordset.

and it's been ages since i've used an SP but shouldn't there be some kind of statement that denotes the recordset to be returned? ( sorry really SQL rusty )

perchance is it a possibilty of variable vs field name conflict?

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[ignoreme]

Why do have two parameters in the Stored Proc?

And if they are required, the ASP code does not appear to pass any values to the proc.

In addition, from I can recall from SQL procs you initialized the parameters to empty strings...if that is the case then the first conditions will always be true.

Hmm.







"I think there is a world market for maybe five computers."
--Thomas Watson, chairman of IBM, 1943

 

[MrHelpMe]

Thanks ignoreme,

I have 2 parameters in the proc for this reason. I need a category to display all types and I need a type to display all itmes. The proc works because if I just run the proc I get categories which I am just trying to do in the asp page. Maybe I'm not understanding the asp part, but I have a page that has catgory box, a type box and a item box. If I want to populate those boxes with results from the Stored procedure how would I code the asp. I thought I was doing it correctly. What do you mean by your last statement. If I just run the proc I get a listing of categories so why can I not populate the first category box with this data. Thanks again.