Library tutorials & articles

Beginning Active Server Pages

  • Comments (23)
  • PDF
  • AddThis
By James Crowley, published on 08 Mar 2002
Page 9 of 16
  1. Introduction
  2. Before you start
  3. Outputting Data
  4. Outputting Data 2
  5. Forms
  6. Forms 2
  7. Using Databases
  8. Using Databases 2
  9. Getting DB Content
  10. Query Strings
  11. Debugging
  12. Error Handling
  13. Including Files
  14. Making Content Expire
  15. Cookies
  16. Conclusion

Getting DB Content

A more often task, however, is to retreive information from a database. This can be done using ADO recordsets and the Execute statement to run an SQL query.

First, using the form we created in the last section, add a few records to the database. In this section, you will find out how to display them! First, we execute an SQL statement, and assign a variable to its result:

Set rData = cConn.Execute ("My SQL Statement")

First, add the code below to a file called viewusers.asp. We've come across it all before.

<%
Dim cConn, rData
'Open the db connection
Set cConn = Server.CreateObject ("ADODB.Connection")
cConn.Open "test_db","",""
'Execute the SQL 
Set rData = cConn.Execute ("SELECT * FROM MyUsers")
%>

All the last statement does is return all the fields and rows from the table MyUsers into the recordset rData. rData is now a standard ADO Recordset.

We can use the rData.MoveNext command in a Do...Loop statement to loop through the data, and check the rData.EOF property to see if we have reached the end. All you need to do now is retreive the values for each field. This is done using rData("FieldName"). Using this information, we can now write some code to loop through the available records, and output some HTML:

<%
Dim cConn, rData
'Open the db connection
Set cConn = Server.CreateObject ("ADODB.Connection")
cConn.Open "test_db","",""
'Execute the SQL 
Set rData = cConn.Execute ("SELECT * FROM MyUsers")
%>

<p>Below is a list of the current members</p>
<table border="1">
 <tr>
  <td><b>ID</b></td>
  <td><b>Name</b></td>
  <td><b>Email</b></td>
 </tr>
<%
Do While rData.EOF = False
    'output ID
    Response.Write " <tr>" & vbNewLine & "  <td>" & rData("ID") & "</td>" & vbNewLine
    'output name
    Response.Write "  <td>" & rData("Name") & "</td>" & vbNewLine
    'output email
    Response.Write "  <td>" & rData("Email") & "</td>" & vbNewLine & " </tr>" & vbNewLine
    rData.MoveNext
Loop
%>

and that's it! It's really that simple... Now take a look in your browser to see the list of members who have signed up!

Comments

  1. michael poxon
    16 Dec 2006 at 21:38
        Hi,
    When I say I'm an ASP beginner, I mean it! I've only written one trivial bit of code, and got a blank page. I see now that's described in the article snippet below. I did indeed go to the saved .asp page on my hard drive!
    So my questions:
    I have XP, so IIS is presumably installed. In the snippet below, are 'localhost' and c:\inetpub\wwwroot the actual strings to type in? For example, I currently don't have a folder called "inetpub". Do I have to create one? And where on my actual server (starman.co.uk) should I put any ASP pages?
    Alternatively, is there a good introductory book you can recommend?
    • When you create an ASP page, you cannot view it on your hard disk by going to C:MyWebmyasppage.asp. It won't work! That references a direct page on your hard disk, and the server doesn't get a chance to parse any of the ASP code.
    • If you have IIS installed, it means your PC is a server (even if your PC is the only one which can access it)! You can access the pages on the server at http://localhost/ or http://PCName/. The physical location of your web site is by default C:\inetpub\wwwroot. Save your ASP pages there, and view them in a browser by visiting the URLs above.





  2. writetoksk
    04 Oct 2006 at 06:26

    hi,

    its first i started ASP.I want to know how to save the files.whats the extensions...Where can i get the sample programs

  3. James Crowley
    28 Dec 2004 at 08:13

    Are you running IIS ? And are you viewing it in your browser via the correct URL? (ie something starting with http:// rather than file:// ) ?

  4. James Crowley
    28 Dec 2004 at 08:12

    It does - you just can't see it We've got an ISAPI filter that rewritse /show/1010/ to something like /show.aspx?id=1010

  5. Sonic D Hedgehog
    12 Dec 2004 at 17:52
    Is there something special I have to do besides saving it as a .asp file, or does it just not work on my server? It just spits out the code, even HTML, as plain text.

    Code:
    <html>
    Let's see if ASP works!<br>
    <%
    Response.Write "Yep!"
    %>
    </html>
  6. Sonic D Hedgehog
    12 Dec 2004 at 17:46

    "The VB code can act on information passed to the page, such as from an internet form or a querystring (this is data passed in the URL after the ? ... take a look at this pages URL!)"


    http://www.developerfusion.com/show/1010 doesn't have a question mark in it...

  7. butro com
    31 Mar 2004 at 19:34

    you can not include a file from a remote storage site other than the one your website is being hosted on , like writing <!--#include file = " www.someplace.com/somefile.inc " --> but by using asp tear codes you can if you have any support this features.

  8. zabawka
    04 Feb 2004 at 21:30
    What do I do if I need to include a file from a remote storage site other than the one my website is being hosted on. The following doesn't work anymore

    <!--#include file = " www.someplace.com/somefile.inc " -->

    or even less the " virtual " call which starts searching the virtual directory the website is stored on.

    What now?

    Do I need to write a routine to access the file remotely then spilling it onto the page using response.write???


  9. James Crowley
    03 Feb 2004 at 10:58
    The trick for this is to get .NET to generate a TLB library for your the .NET class. In Vs.NET, you can modify the current build profile and set the "Register for COM Interop" section to true. See http://www.c-sharpcorner.com/Code/2002/April/COMInteropP2AJ.asp for more information.
  10. mak
    03 Feb 2004 at 08:58
    hi all,

            I am very new to ASP technology.  
            I am in a need to use DLLs (written in C#) in ASP Pages.
            Could anyone suggest me how to proceed.

    Regards,
    MAK,
  11. ushaRamesh
    02 Feb 2004 at 00:25

     i'd used ActiveX treeview control in my page and created tree by using ASP codings. but the tree is shown as collapsed one on load. i'd tried "TreeView1.Nodes(count).EnsureVisible " which produces an error of "index out of bounds". anyone pls help me as early as possible as i 've to complete by today itself. Thanks

  12. Alex
    05 Jan 2004 at 04:49

    thanks, but it wasn't the sql. i did that. it's long since solved.

  13. Anuradha Vasudeva
    05 Jan 2004 at 01:49

    chk your SQL statement. Stop before execting cConn.Execute sSQL . Print sSQL and try executing the same in the backend. If it doesnt work then you need to change the SQL statement.

  14. Alex
    13 Nov 2003 at 15:43

    i solved the problem a while ago, but i forgot how.

  15. zoglchaim
    12 Nov 2003 at 18:18

    heppens to be that i have the same prolem.

  16. kyayhmon
    21 Oct 2003 at 00:51

    I'm beginner of ASP.I make my thesis with ASP.But I've got problem for building project in Visual InterDev.I'd already installed IIS5.It shows web server can't connect.How I do?If it's cause of IIS error,please explain me step by step.let me know solution during five days.please help me.

  17. Alex
    15 Jul 2003 at 09:07

    It doesn't work it all. I created the table using Access and set of the OBDC fine. What's wrong?

  18. Alex
    15 Jul 2003 at 06:27

    I get this error when I submit forminput.asp:


    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'


    [Microsoft][ODBC Microsoft Access Driver] Unknown


    /forminput.asp, line 12


    What's wrong? I even copied the code over exactly.
    the error's with this line:
    cConn.Execute sSQL

  19. ankush_vachher
    23 Jun 2003 at 02:54

    Quote:
    [1]Posted by JadesNet on 22 May 2003 09:07 AM[/1]
     I tried this code out exactly, but I get an INSERT INTO syntax error.  I have also tried using the rs.AddNew to add to my database but I get a read-only error.  I have tried changing the lock type and cursor types around but I can't get anything to work.


    pls see t he comment

  20. ankush_vachher
    23 Jun 2003 at 02:53

    U Have to include a file name adovbs.inc file i yr asp page. thenu can use the addnew and update methods

  21. JadesNet
    22 May 2003 at 09:07
     I tried this code out exactly, but I get an INSERT INTO syntax error.  I have also tried using the rs.AddNew to add to my database but I get a read-only error.  I have tried changing the lock type and cursor types around but I can't get anything to work.
  22. mallyone
    03 Jun 2002 at 20:39

    One thing to note, this only works on pages which have been posted to

  23. Developer Fusion Bot
    01 Jan 1999 at 00:00

    This thread is for discussions of Beginning Active Server Pages.

Leave a comment

Sign in or Join us (it's free).

Related tags

asp, visual basic
James Crowley James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audience ...

Related articles

Ask a question

Related discussion

Related podcasts

  • Scott Guthrie

    Scott catches up with Scott Guthrie in an interview covering Ajax, Asp 2.0, extender controls, CSS adapters and more.

Languages

Web Development

Frameworks & Architecture

Other major sections

Cancel
Want to stay in touch with what's going on? Follow us on twitter!