fill combo box with data from database

Discussion in 'ASP General' started by Eugene Anthony, Jul 14, 2005.

  1. 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 ***
    Eugene Anthony, Jul 14, 2005
    #1
    1. Advertising

  2. 1. Why the WHERE clause?

    Try this:

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

    Ray at work


    "Eugene Anthony" <> wrote in message
    news:...
    > 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 Costanzo [MVP], Jul 14, 2005
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:...
    > 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 Bertrand [SQL Server MVP], Jul 14, 2005
    #3
  4. 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], Jul 14, 2005
    #4
  5. 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.
    Bob Barrows [MVP], Jul 14, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?QW5nZWw=?=

    COMBO BOX INSTEAD OF DROP DOWN BOX

    =?Utf-8?B?QW5nZWw=?=, Jun 29, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    599
    Ken Cox [Microsoft MVP]
    Jun 29, 2004
  2. Adrian
    Replies:
    1
    Views:
    207
    Bullschmidt
    Feb 18, 2004
  3. dthmtlgod

    Fill Combo Box based on another Combo Box

    dthmtlgod, Nov 16, 2005, in forum: ASP General
    Replies:
    1
    Views:
    568
    Bob Barrows [MVP]
    Nov 16, 2005
  4. Mike

    Combo Box and text box

    Mike, Sep 24, 2003, in forum: Javascript
    Replies:
    2
    Views:
    144
  5. SM
    Replies:
    2
    Views:
    353
Loading...

Share This Page