Adding Records to a Database using SQL

By Steve Schofield

My purpose of this example is to show how to get records from an html form and submit that form to an ASP page. This ASP page will then use SQL to enter the records into the database When I started doing ASP, getting records from an ASP page to a database was my 1st thing I wanted to do. I quickly discovered there was a few ways you could do that. This example uses formatted SQL and the Execute method of the Connection object to take data from a html form and submit to an ASP page that processes the data using SQL.

1st page the Input form- This is just a standard html Input form

<html><head>
<title>SQL add record example</title>
</head>
<body>
<h3 align="center">SQL example</h3>
<td width="424"><form method="post" name="form1" action="addrecordsql2.asp">
<p><strong>First Name</strong><br>
<input type="text" size="40" name="FirstName"><br>
<strong>Last Name</strong><br>
<input type="text" size="40" name="LastName"><br>
<strong>FavoriteColor</strong><br>
<input type="text" size="40" name="FavoriteColor"><br>
<p><input type="Submit" value="Submit New Example" name"b1"> </font></p>
</form>
</td>
</tr>
</table>
</center></div>
</body></html>

2nd The ASP page that process the information

<%@ Language = "VBScript"%>
<%
'Declare all local variables
dim conn
dim rs
dim strconn
dim strsql

strsql = ""
'set connection string to local variable-I use a DSN-less connection
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("adoandsqladd.mdb")

'build the sql statement based on the input from the form
strSQL = "INSERT INTO tblSqlAdd(FirstName, LastName, FavoriteColor)"
strSQL = strSQL & " SELECT "
strSQL = strSQL & "'" & request("FirstName") & "' as text1,"
strSQL = strSQL & "'" & request("LastName") & "' as text2,"
strSQL = strSQL & "'" & request("FavoriteColor") & "' as text3"

'Set connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
conn.execute(strSQL)
conn.close
set conn = nothing
%>
<html><head>
<title>SQL Add record example</title>
</head>

<body>
<% = "Your record has been added" %>
</body>
</html>

Next: Adding Records to a Database using SQL II

Previous: Updating Records to a Database using ADO(ActiveX Data Objects)

Index: Active Server Pages\IIS

The Author
Steve Schofield has been working with computers for over three years and for the last year doing web development. "I'm not one of these whizkids that was born with a keyboard in their hands. I have struggled, scratched and clawed my way to learn this stuff. not in college either,!). Once I discovered ASP pages and haven't looked back. Using ASP, A.D.O., vbscript. I have been able to access all kinds of databases (DB2, Sybase, MS Sql Server 6.5/7.0 & Access97)."