fill combo box with data from database

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

*** Sent via Developersdex http://www.developersdex.com ***

Ray
Thu Jul 14 09:29:30 CDT 2005

1. Why the WHERE clause?

Try this:

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

Ray at work


"Eugene Anthony" <solomon_13000@yahoo.com> wrote in message
news:ekRzOzHiFHA.1252@TK2MSFTNGP09.phx.gbl...
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***

Aaron
Thu Jul 14 09:34:24 CDT 2005

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.



"Eugene Anthony" <solomon_13000@yahoo.com> wrote in message
news:ekRzOzHiFHA.1252@TK2MSFTNGP09.phx.gbl...
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***

Bob
Thu Jul 14 09:56:43 CDT 2005

Eugene Anthony wrote:
>
> objConn.usp_retrieveCategories, rs

should be:

objConn.usp_retrieveCategories rs

No comma

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Bob
Thu Jul 14 09:58:46 CDT 2005

Eugene Anthony wrote:
> 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


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.