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("CategoryDescrip tion")%>
<%
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 Costanzo [MVP]]

1. Why the WHERE clause?

Try this:

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

Ray at work
"Eugene Anthony" <so***********@yahoo.com> wrote in message
news:ek**************@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("CategoryDescrip tion")%>
<%
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 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.

"Eugene Anthony" <so***********@yahoo.com> wrote in message
news:ek**************@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("CategoryDescrip tion")%>
<%
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 Barrows [MVP]]

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 Barrows [MVP]]

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.