Beginning Active Server Pages

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 "[email protected]":

INSERT INTO MyUsers (Name, Email) VALUES ('James','[email protected]')

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">
<% End If %>

You might also like...


About the author

James Crowley

James Crowley United Kingdom

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 audien...

Interested in writing for us? Find out more.


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Computer Science is no more about computers than astronomy is about telescopes.” - E. W. Dijkstra