Library tutorials & articles

Beginning Active Server Pages

By James Crowley, published on 08 Mar 2002
Page 7 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

Using Databases

Unlike other server-side scripts such as PHP, Active Server Pages itself don't provide very many functions for accessing Databases, performing file operations, or anything else. However, you can use the Server.CreateObject (identical to the CreateObject command in VB), to create other ActiveX objects. For database access, we generally use ADO. Another of the great benefits of using ASP ... if you know ADO, you'll find database access in ASP pages a breeze. Also, if you have a VB component which retreives information from a database, you can use that too.

This topic is one of the widest in ASP, and we're only going to touch on the surface, but lets get started. For this example, we're going to assume that you have a DSN connection set up, which is the easiest way, and then we don't need to worry what sort database you are going to use either.

DSN Connections
If you don't know how to set up a DSN connection, follow these steps.

  1. Click on Start Menu | Programs | Adminstrative Tools | Data Sources (ODBC)
  2. Click the System DSN tab, and click Add...
  3. Select the driver for your database. This will be 'Microsoft Access Driver' for a Access database, or 'SQL Server' for an MS SQL database. Click Finish.
  4. A configuration dialog will appear. This will vary from driver to driver, and I have assumed you are using an Access database. If you're using MS SQL, you should know how to do this anyway!
  5. Enter a name for your DSN, such as 'test_db'.
  6. Click Select... and choose the database you want the connection to use.
  7. Click OK.

First, you need to create an instance of an ADO connection. We'll also declare the connection variable:

Dim cConn
Set cConn = Server.CreateObject ("ADODB.Connection")

Next, we need to establish a connection with the database. When using ADO, you can simply call

cConn.Open "dsn_name","username","password"

DSN_name is the name of the DSN connection you set up in the System DSN control panel. Username and Password is used to access the DB. If your database doesn't have a password, these can be left blank. For this example, use

cConn.Open "test_db","",""

unless you have a different DSN connection you want to use. (See the note above for information on how to create one).

Now you have an open connection, we can execute SQL statements using the Execute method:

cConn.Execute "My SQL commands"

For the moment, we'll only concern ourselves with inserting data to the database. See Getting Database Content, later in this tutorial for information on retreiving it. If you don't know any SQL, or are a bit rusty, you might want to take a quick look at our SQL commands tutorial before continuing.

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

visual basic, asp
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 ...
AddThis

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
We'd love to hear what you think! Submit ideas or give us feedback