Library tutorials & articles

Beginning Active Server Pages

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

Returning to our earlier example, we've already collected the username and email of the person who wants to register using a form. Now we need to build an SQL Insert string to insert this information into a table. However, before that, we need to quickly go into our database, and create a new table. Use the following information:

  Name Data Type
Table Name MyUsers  
Fields ID (AutoNumber/Identity) AutoNumber (Access) or Identity (MS SQL). Primary Key
  Name (String/VarChar) Text (Access) or VarChar (MS SQL). Max Length: 20
  Email (String/VarChar) Text (Access) or VarChar (MS SQL). Max Length: 100

We already know that the information we want from the form is in the Request.Form variable, so lets have a go at creating the SQL string.

First a quick reminder of the SQL Insert syntax:

INSERT INTO TableName (Field1, Field2, ...) VALUES (Field1Value, Field2Value, ...)

So, we can start with the following code:

sSQL = "INSERT INTO MyUsers (Name, Email)"

Next, we need to provide the information of the new user:

sSQL = sSQL & " VALUES ('" & Request.Form("Name") & "','" & Request.Form("Email") & "')"

For anyone not used to using SQL in an ASP page that might seem a bit confusing, so lets take a look at what the final value of sSQL will be if the Name was "James" and the email was "james@vbweb.co.uk":

INSERT INTO MyUsers (Name, Email) VALUES ('James','james@vbweb.co.uk')

Naughty Strings
Don't forget that if the user entered a ' in their name, it would ruin the syntax of the SQL statement. Therefore, it is wise to use the Replace statement to replace every ' with a '' (that is ' and ', not a quote!). We won't worry about this in the current example, but you might want to consider doing the following:

sName = Request.Form("Name")
sName = Replace(sName, "'", "''")

and using sName when building up the Insert string, instead of Request.Form("Name").

Finally, you will need to execute this SQL statement:

cConn.Execute sSQL

... and that's almost it! All we need to do is put it all together. Below is the completed source code of inputform.asp:

<%

Dim cConn, sSQL
If Request.Form("posted") = 1 Then 'form posted
    'Open the db connection
    Set cConn = Server.CreateObject ("ADODB.Connection")
    cConn.Open "test_db","",""
    'Build the SQL string
    sSQL = "INSERT INTO MyUsers (Name, Email)"
    sSQL = sSQL & " VALUES ('" & Request.Form("Name") & "','" & Request.Form("Email") & "')"
    'Execute the SQL 
    cConn.Execute sSQL
    'Output message
    Response.Write "<p>Thankyou for signing up. Your information has been added to our database!</p>"
Else 'display the form %>
 <form action="forminput.asp" method="POST">
  Your Name <input type="text" name="Name"><br>
  Your Email <input type="text" name="Email"><br>
  <input type="hidden" name="Posted" value="1">
  <input type="submit" value="Sign Up">
 </form>
<% End If %>

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