fill combo box with data from database

[Eugene Anthony]

This is a table created in ms sql:

create table categories
(
CategoryID int IDENTITY,
CategoryDescription varchar(30),
ParentCategoryID int
);

This is a stored procedure created in ms sql:

create procedure usp_retrieveCategories
AS SET NOCOUNT ON

SELECT CategoryID, CategoryDescription FROM categories WHERE
CategoryID >= 1

Return
GO


This is a code to fill my combo box on my .asp page:

<select name="select" class="TextField1">
<%
openDB()

objConn.usp_retrieveCategories, rs

while not rs.eof
%>
<option
value=<%=rs("CategoryID")%>><%=rs("CategoryDescription")%>
<%
wend

CloseDB()
%>
</select>


I am getting an error:
Error Type:
ADODB.Connection (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.


The error points to objConn.usp_retrieveCategories, rs

How do I solve the problem?

Your help is kindly appreciated.

Regards

Eugene Anthony

 

[Ray Costanzo [MVP]]

1. Why the WHERE clause?

Try this:

openDB()
Set rs = objConn.Execute("EXEC usp_retrieveCategories")

Ray at work

 

[Aaron Bertrand [SQL Server MVP]]

Where do you define rs?

Here is how I would write it.

<select name="select" class="TextField1">
<%
openDB()
set rs = objConn.Execute("usp_retrieveCategories")
do while not rs.eof
response.write "<option value='" & rs(0) & "'>" & rs(1)
rs.movenext ' <---- important step!
loop
closeDB()
%>
</select>

If this is not the only database thing you are doing on this page, I
strongly recommend against opening and closing the connection every time.
It will be more efficient to open the object once (just before the *first*
time you need it), and close it just after the last time you use it.

A



I am sure Bob will show you how to properly define rs before stuffing a
resultset into it.

 

[Bob Barrows [MVP]]

objConn.usp_retrieveCategories, rs

should be:

objConn.usp_retrieveCategories rs

No comma

 

[Bob Barrows [MVP]]

openDB()

objConn.usp_retrieveCategories, rs

And, as Aaron pointed out <grin>, the rs variable needs to be defined before
using it in this statement:

set rs=createobject("adodb.recordset")
objConn.usp_retrieveCategories rs

Bob Barrows