469,568 Members | 1,609 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

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("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 ***
Jul 22 '05 #1
4 6170
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 ***

Jul 22 '05 #2
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 ***

Jul 22 '05 #3
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.
Jul 22 '05 #4
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.
Jul 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Sherwood Botsford | last post: by
2 posts views Thread by Joanne Lewis | last post: by
2 posts views Thread by Jeremy Dillinger | last post: by
4 posts views Thread by K R Lal | last post: by
1 post views Thread by Jim | last post: by
4 posts views Thread by Dave | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.