ASP Tutorials - Herong's Tutorial Examples

∟Working with MS Access Database

∟Protecting Data in SQL Statements - Test Script

This section provides a tutorial example on running the test script, insert_access.asp, persist data to MS Access database using the Execute() method to run an INSERT statement.

Here is a test script to show you how to apply those rules described in the previous section:

<script language="vbscript" runat="server">
'  protect_data_access.asp
'  Copyright (c) 2005 by Dr. Herong Yang, http://www.herongyang.com/

   Set oConn = Server.CreateObject("ADODB.Connection")
   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      Server.MapPath("/cgi-bin/hello.mdb")
   
   If Request.Form("submit") = "Submit" Then
      Set oRes = oConn.Execute("INSERT INTO [order]" _
         & " ([Item], [Quantity], [Date]) VALUES (" _ 
         & " '" & Replace(Request.Form("Item"), "'", "''") & "'" _
         & ", " & Clng(Request.Form("Quantity")) _
         & ", #" & Replace(Request.Form("Date"), "#", "") & "#)")
   End If
   
   Response.Write("<table><form action=" _ 
      & Request.ServerVariables("SCRIPT_NAME") & " method=post>")
   Response.Write("<tr><td>Item:</td>" _ 
      & "<td><input type=text size=40 name=Item></td></tr>")
   Response.Write("<tr><td>Quantity:</td>" _ 
      & "<td><input type=text size=40 name=Quantity></td></tr>")
   Response.Write("<tr><td>Date:</td>" _ 
      & "<td><input type=text size=40 name=Date></td></tr>")
   Response.Write("<tr><td></td>" _
      & "<td><input name=submit value=Submit type=submit><td></tr>")
   Response.Write("</form></table>")

   Set oRes = oConn.Execute("SELECT * FROM [order]")
   Response.Write("<table border=1>")
   Do While NOT oRes.EOF
      Response.Write("<tr><td>" & oRes("ID") & "</td>" _
         & "<td>" & oRes("Item") & "</td>" _
         & "<td>" & oRes("Quantity") & "</td>" _
         & "<td>" & oRes("Date") & "</td></tr>")
      oRes.MoveNext
   Loop
   Response.Write("</table>")

   oRes.close
   oConn.close 
</script>

Of course, you need to create a new table called "Order" in the "hello.mdb" before running this script. The "Order" must have the following fields:

ID - "Autonumber" type
Item - "Text" type
Quantity - "Number" type
Date - "Date/Time" type

Run this script with Internet Explorer, you should see a blank form. Enter a set of good values, like:

Item: Coke
Quantity: 3
Date: 12/25/2020

Then press the "Submit" button. You will see the page comes back again with one row display the values you entered below the form.

The second test you can do is to enter some protected characters in the values, like:

Item: Today's paper
Quantity: 1
Date: #25/10/2020#

The script should have no problem to insert those values into the database.

Another test you can do is to enter some bad values, like:

Item: Milk
Quantity: 2L
Date: 12/25/2020

This time, you will get a crash page with an error: "Microsoft VBScript runtime (0x800A000D), Type mismatch: 'Clng'"

As you can see, my script does not have any logic for type checking. I will leave it to you to improve the script in this area.

Last update: 2005.

Table of Contents

 About This Book

 ASP (Active Server Pages) Introduction

 IIS (Internet Information Services) 5.0

 MS Script Debugger

 VBScript Language

 ASP Built-in Run-time Objects

 ASP Session

 Creating and Managing Cookies

 Managing Sessions with and without Cookies

 scrrun.dll - Scripting Runtime DLL

 Managing Response Header Lines

 Calculation Speed and Response Time

 ADO (ActiveX Data Object) DLL

►Working with MS Access Database

 Connecting ASP Pages to MS Access Databases

 "hello_access.asp" - MS Access Example

 Persisting Data to MS Access Databases

 Running "insert_access.asp"

 Protecting Data in SQL Statements

►Protecting Data in SQL Statements - Test Script

 Guest Book Application Example

 References

 PDF Printing Version