SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: dropdown list

  1. #1
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    dropdown list

    Hi friends..

    I am doing an application with dropdown box. If i select any thing from the drop down box it should display a table on the from.

    It exactly what i am doing from this example.
    http://www.w3schools.com/ado/showasp...e=demo_query_4


    can any one know where it went wrong....

    <html>
    <head>

    <title>Items used</title>
    </head>

    <body>

    <form method="post" action="item.asp">

    Choose Item<select name="item" style="width: 193px; height: 23px">
    <option id="1">Rice</option>
    <option id="2">Wheat</option>
    <option id="3">flour</option>
    <option id="4">corn</option>
    <option id="5">oats</option>



    </select>


    <%
    OpenSQlConn

    do while rs.EOF//here i am getting an error

    response.write("<option")
    if rs.fields("item")=item then
    response.write(" selected")
    end if
    response.write(">")
    response.write(rs.fields("item"))
    rs.MoveNext
    loop
    rs.Close
    %>



    <input type="submit" value="show items"><br>
    <input type="submit" value="Cancel">


    </form>

    <%
    if item<>"" then
    sql="SELECT * from items WHERE item='" & item & "'"
    Set rs=Conn.Execute(sql)

    %>

    <table width="100%" cellspacing="0" cellpadding="2" border="1">
    <tr>
    <th>Item</th>
    <th>company</th>
    <th>itemid</th>

    </tr>
    <%
    do until rs.EOF
    response.write("<tr>")
    response.write("<td>" & rs.fields("item") & "</td>")
    response.write("<td>" & rs.fields("company") & "</td>")
    response.write("<td>" & rs.fields("itemid") & "</td>")
    response.write("</tr>")
    rs.MoveNext
    loop
    rs.close
    conn.Close
    %>
    </table>
    <% end if %>



    </body>
    </html>

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    PLEASE PLEASE PLEASE use CODE or HIGHLIGHT tags.

    Why are you trying to use a recordset before you have even created it?

    Even if the recordset WAS created, your Do While line is saying "Do the following code while the recordset is at the end of file". You have missed out the all-important NOT ...

    Do While Not rs.EOF (or Do Until rs.EOF like your later example).

    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops i forget to add not..

    Now i added but there is no result.. it is not displaying the table.

  4. #4
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can any one know the actual code for this.. I am not getting the result because i didn't know what code to write for the option selected(if option selected then click on button:show item, it should display the database table)..

  5. #5
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    YOU HAVE NOT CREATED A SQL COMMAND OR A RECORDSET TO BE USED TO WITH THE DROPLIST!

    Your code and the w3schools example are like a flying fish and a bird - they can both fly but that's about it ... they are otherwise completely unrelated. Take another look at the w3schools example, go through it line by line and THINK about what it is doing. THINK about the HTML code that it is creating.

    It seems to me that you are really struggling to grasp the concepts of programming for the web. As such your questions tend to lead to people writing the code for you. In effect, you are getting your application written by others for no cost. Not any more by me - sorry.
    Ian Anderson
    www.siteguru.co.uk

  6. #6
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    YOU HAVE NOT CREATED A SQL COMMAND OR A RECORDSET TO BE USED TO WITH THE DROPLIST!

    Your code and the w3schools example are like a flying fish and a bird - they can both fly but that's about it ... they are otherwise completely unrelated. Take another look at the w3schools example, go through it line by line and THINK about what it is doing. THINK about the HTML code that it is creating.

    It seems to me that you are really struggling to grasp the concepts of programming for the web. As such your questions tend to lead to people writing the code for you. In effect, you are getting your application written by others for no cost. Not any more by me - sorry.
    Hello I am new to ASP.. So it is common to everybody to understand in the early stage....
    I didn't ask anybody to write code for me.. i already wrote the code but i am asking them to check the error what i did in my program....and the same time i am not an employee.. i am a student..i am learning this by myself..

    and till now no one gave correct answers to me..I only find the solution and posted them in my queries.. you can check my queries..

    sorry to tell you.. I don't like your attitude..
    If you are not interested to give answers to my questions ..never mind ..

    are you selling your knowledge for money?...

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2010
    Location
    Dana Point, CA
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your example code left out the Database connection and recordset code as shown in the W3CSchools website. First you have to create a connection to your Database, then you create a Recordset. Your database connection code will depend on what type of database you are trying to connect to. MS SQL, MS Access, MySQL, and Oracle will all have different connection strings. There are many websites that list all the different connection strings.

    Code:
    <%
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open(Server.Mappath("/db/northwind.mdb"))
    
    set rs=Server.CreateObject("ADODB.recordset")
    sql="SELECT DISTINCT Country FROM Customers ORDER BY Country"
    rs.Open sql,conn
    
    country=request.form("country")
    
    %>

  8. #8
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks cemory for connection string..
    Here problem with the drop down option selected..

  9. #9
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lucky20 View Post
    sorry to tell you.. I don't like your attitude..
    If you are not interested to give answers to my questions ..never mind ..

    are you selling your knowledge for money?...
    Sorry to tell you that I'm exasperated that several of us here have given you extensive help in your previous questions yet you still haven't grasped the basic concepts of PROGRAMMING. (Notice I don't say ASP).

    What I'm trying to do is lead you to the answer (i.e. teach) rather than just give you working code (which is what we have done so far). If you don't learn HOW to find the asnwer then you'll never learn how to program - simple as.

    So you're a student, eh? Is this a class project for which you will gain credit? If yes then YOU are the one gaining from this (getting other people's knowledge for no cost, thus gaining educational credit), not me.
    Ian Anderson
    www.siteguru.co.uk

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,032
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Hey what's with the attitude of this forum recently? I know we're all busy important programmers but running database queries and looping through the results isn't the easiest thing to do for a new person especially with an old technology like ASP. When someone new to Sitepoint (as indicated by their start date of Oct 2010) asks for advice let's try and help out a bit rather than just give them attitude. Maybe the answers we've given in the last little while haven't been rudimentary enough so I'll start at the beginning and maybe this will make sense.

    lucky20,
    Get the book Teach Yourself ASP in 21 Days. It is truly a great beginner's guide and reference book for learning all of the basics to ASP. I've been writing ASP for about 12 years and coded Visual Basic before then but that book is a great reference guide when forget how objects work or some syntax error pops up.

    Now back to your task. According to the example on the W3Schools site, you want to select an item from the dropdown menu and when you click the button render a table of results. Their example has a couple of parts. The first when you just come to the page and the second when you select a country.

    I'm going to ignore the first half and cut to the chase which is after you've selected the country and want to display your records...

    In order to understand what's going on you have to break down what you're doing into small steps. Assuming that the dropdown menu is showing up correctly and the page is posting back to itself correctly then you have to do the following to make it work:

    1) You have to create your database connection object because without it you can't connect to the database or run the query. In the W3School example they used the following code to define "conn" as the variable responsible for the connection and then they open the database connection. Notice that the last line is the path to your database so if that's incorrect you will get an error:
    Code:
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open(Server.Mappath("/db/northwind.mdb"))
    2) Then you need to get your country variable. Now that's sent through the form so you have to use the request.form method to get it as follows:
    Code:
    country=request.form("country")
    * Sometimes if things are complex and I'm not sure if the variable is being sent correctly, I'll hard code the variable just to make sure I've got something there. So I would add a temporary line that states: country = "UK" or something like that.

    3) Now that you have a connection to the database and you have the country that you want to look at, you can run a query against the database and create a recordset of results.

    In the example they do a few things. First they check if the country variable is set with an IF conditional statement using the <> operator basically asking the question "if country variable doesn't equal nothing then we'll run the following code".

    Then they create a SQL Query "sql" and use the country variable to select only the records that relate to that particular country and they run it against the database connection "conn" to populate your recordset "rs".

    Then they set up a table and use the DO UNTIL rs.EOF (recordset reaches the end of the file) to loop out the table rows of data for that particular country.

    Once that's all done they close & destroy the recordset and connection and finally end the IF statement.

    (Note: This is just one of several ways to run a query against a database... There are plenty of ways to run a query against a database so keep your eyes open and explore a few ways to find the one that suits your programming style.)

    HTML Code:
    <%
    if country<>"" then
       sql="SELECT Companyname,Contactname,Country FROM Customers WHERE country='" & country & "'"
       set rs=Server.CreateObject("ADODB.Recordset")
       rs.Open sql,conn
    %>
       <table width="100%" cellspacing="0" cellpadding="2" border="1">
       <tr>
         <th>Companyname</th>
         <th>Contactname</th>
         <th>Country</th>
       </tr>
    <%
    do until rs.EOF
       response.write("<tr>")
       response.write("<td>" & rs.fields("companyname") & "</td>")
       response.write("<td>" & rs.fields("contactname") & "</td>")
       response.write("<td>" & rs.fields("country") & "</td>")
       response.write("</tr>")
       rs.MoveNext
    loop
    rs.close
    conn.Close
    set rs=Nothing
    set conn=Nothing%>
    </table>
    <%  end if %>
    Hopefully that makes some sense. Get that book though... When I first got into ASP, I purchased a number of books on the subject and that one is the only one I kept.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  11. #11
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I empathise with your comments, Andrew. But there's only so much exasperation one can take at times.

    BTW - you haven't actually answer the question asked. Which was ... if I have an item predefined in a database, how can I get that item in a droplist pre-selected? That's what I was trying to get the OP to think through - what does the HTML look like, then how can you make the ASP code create that HTML based on a known option value? The OP has the guts of the code already there, but isn't implementing it right.

    I could give the solution, but that won't help the OP learn.
    Ian Anderson
    www.siteguru.co.uk

  12. #12
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,032
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    I empathise with your comments, Andrew. But there's only so much exasperation one can take at times.

    BTW - you haven't actually answer the question asked. Which was ... if I have an item predefined in a database, how can I get that item in a droplist pre-selected? That's what I was trying to get the OP to think through - what does the HTML look like, then how can you make the ASP code create that HTML based on a known option value? The OP has the guts of the code already there, but isn't implementing it right.

    I could give the solution, but that won't help the OP learn.
    Thanks Ian,
    The point I'm trying to make is that on several recent occasions I've come to the forum and seen similar situations where a question is posted and it's as if the OP is being scolded for not knowing the basics. If we're going to mentor people we have to be prepared to break things down into baby steps so that they understand the two or three prerequisite steps required before they can loop through a recordset.

    I don't know lucky20's background but it seems to me that he or she hasn't had answers that say ok, in order to do database transactions, you always need three basic things... A connection object, a recordset object and a SQL query.

    Now for the answer to the dropdown lucky20 if all you want is a method to automatically fill a dropdown you can use the connection established above because it doesn't change:
    Code:
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open(Server.Mappath("/db/northwind.mdb"))
    Then you use a query that grabs all of the DISTINCT country records from the database and order them by the names of the countries. DISTINCT means that the query will only grab a record of each country once:
    Code:
    set rs=Server.CreateObject("ADODB.recordset")
    sql="SELECT DISTINCT Country FROM Customers ORDER BY Country"
    rs.Open sql,conn
    Now if you want to create a dropdown you can use the following code which will only create the HTML select menu without the form or any other text, etc...:
    Code:
    <select name="country">
    <%  
    do until rs.EOF
        response.write("<option")
        if rs.fields("country")=country then
          response.write(" selected")
        end if
        response.write(">")
        response.write(rs.fields("Country"))
        rs.MoveNext
    loop
    rs.Close
    set rs=Nothing 
    %>
    </select>
    I find that code a bit messy and hard to read with all of the response.write statements and I don't like to leave my html elements open so I would clean it up.

    Code:
    <%  
    do until rs.EOF
        if rs.fields("country")=country then
           selected = "selected = selected" 'NOTE: this preselects the list item if the page is posted back to itself
         end if
        response.write "<option " & selected & "> " & rs.fields("Country") & " </option>"
        rs.MoveNext
    loop
    rs.Close
    set rs=Nothing 
    %>
    Don't forget to close & destroy your connection object at the end of the page so that you don't create memory leaks.

    Code:
    conn.Close
    set conn=Nothing
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  13. #13
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you awasson.. I understand the concept now..
    Thanks for helping me..

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,032
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    No problem at all
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •