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]')
|
and using sName when building up the Insert string, instead of Request.Form("Name"). |
Naughty StringsFinally, 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:
<%
'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>"
<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