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 %>