ASP 101 - Active Server Pages 101 - Web01
The Place ASP Developers Go!

Please visit our partners

Windows Technology Windows Technology
15 Seconds
ASP 101
ASP Wire
VB Forums
VB Wire
internet.commerce internet.commerce
Partners & Affiliates

ASP 101 is an site
ASP 101 is an site
Internet News
Small Business
Personal Technology

Corporate Info
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash

ASP Source Code:
' Just in case you're not up on my acronyms:
' DB = database, RS=recordset, CONN=connection
' o/w = otherwise
' Include the VBScript ADO constants file
<!-- #include file="" -->
' *** Begin DB Setup ***
Dim strConnString
' Sample access OLEDB CONN String.
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
	Server.MapPath("db_scratch.mdb") & ";"
' Override with our site-wide CONN string.
strConnString = "Provider=SQLOLEDB;Data Source=;" _
	& "Initial Catalog=samples;User Id=samples;Password=password;" _
	& "Connect Timeout=15;Network Library=dbmssocn;"
' Access likes #, but SQL Server expects ' so you'll need to change
' this based on the DB you're using.
' *** End DB Setup ***
Dim cnnFormToDB       ' CONN object
Dim strSQL            ' String in which to build our SQL command
Dim lngRecsAffected   ' # of records affected... just informational
' Vars for the fields read in from the form.  All fields are read
' in as strings so I need to covert them to the appropriate data
' types to be sure they're appropriate for the DB fields.  These
' variables give me some working space to do this easily.
Dim strTextField      ' text field
Dim intIntegerField   ' integer field
Dim datDateTimeField  ' date field
Dim strErrorMsg       ' Holds error message if we catch any problems.
' See if we have any info to process.
' If we don't (ie. the first time through) we just show
' the form.  If we do we proceed with the insert.
If Request.Form("action") <> "Save Form Data" Then
	' Show the form
	<form action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="post">
	<input type="hidden" name="action" value="Save Form Data" />
	<table border="0">
		<td align="right"><strong>Text Field:</strong></td>
		<td align="left"><input type="text" name="text_field" maxlength="10" /></td>
		<td align="right"><strong>Integer Field:</strong></td>
		<td align="left"><input type="text" name="integer_field" /></td>
		<td align="right"><strong>Date/Time Field:</strong></td>
		<td align="left"><input type="text" name="date_time_field" /></td>
		<td> </td>
			<input type="reset" value="Clear" />
			<input type="submit" value="Save" />
	' Do our DB insert!
	' Retrieve the 3 strings to be entered into the DB
	strTextField     = Request.Form("text_field")
	intIntegerField  = Request.Form("integer_field")
	datDateTimeField = Request.Form("date_time_field")
	' Start error handling... I'm too lazy to check all the criteria
	' on my own so I use VBScript to do it for me.  I simply do a
	' conversion the the expected type and if it fails I catch the
	' error, abort the insert, and display a message.
	On Error Resume Next
	strErrorMsg = ""
	' String (text) field:
	' Nothing should really go wrong here.  It's already a string so
	' I don't bother with a CStr.  I do replace ' with '' for the
	' validity our SQL statement and also check to make sure it's
	' not an empty string.  If it is an empty string ("") then I
	' throw a fake error since I've already got this type of error
	' handling in place... hey I already admitted I was lazy!
	strTextField = Trim(strTextField)
	If Len(strTextField) = 0 Or Len(strTextField) > 10 Then Err.Raise 1
	strTextField     = Replace(strTextField, "'", "''")
	intIntegerField  = Replace(intIntegerField, "'", "''")
	datDateTimeField = Replace(datDateTimeField, "'", "''")
	If Err.number <> 0 Then
		strErrorMsg = strErrorMsg & "Your entry for string_field is " & _
			"inappropriate!<br />" & vbCrLf
	End If
	' Integer field:
	' Lots of possible problems here.  First off it could be text and
	' not a number at all!  Even if it is a number, there are a lot
	' of restrictions on integers.  It needs to be a whole number and
	' it's absolute value can't be bigger than around 32,000.  Using
	' CInt I don't have to worry about it though.
	intIntegerField = CInt(intIntegerField)
	If Err.number <> 0 Then
		strErrorMsg = strErrorMsg & "Your entry for integer_field could " & _
			"not be converted to an integer!  Remember that integers " & _
			"need to be from -32,768 to 32,767.<br />" & vbCrLf
	End If		
	' Date field:
	' Well it needs to be a valid date.  You can enter it in any format
	' the computer can understand.  Doing the CDate will not only make
	' sure it's a date, but will also make them all nice and uniform!
	datDateTimeField = CDate(datDateTimeField)
	If Err.number <> 0 Then
		strErrorMsg = strErrorMsg & "Your entry for date_time_field could " & _
			"not be converted to an date variable!<br />" & vbCrLf
	End If
	' I don't know if this is really documented or a hack,
	' but it turns error trapping back off!
	On Error Goto 0
	' If we have an error in our error string then we show
	' the error message o/w we proceed with the insert.
	If strErrorMsg <> "" Then
		' Show the error message that got us here!
		Response.Write strErrorMsg
		' Open connection to the DB
		Set cnnFormToDB = Server.CreateObject("ADODB.Connection")
		cnnFormToDB.Open strConnString
		' Build our SQL String
		strSQL = ""
		strSQL = strSQL & "INSERT INTO scratch "
		strSQL = strSQL & "(text_field, integer_field, date_time_field) " & vbCrLf
		strSQL = strSQL & "VALUES ("
		strSQL = strSQL & "'" & strTextField & "'"
		strSQL = strSQL & ", "
		strSQL = strSQL & intIntegerField
		strSQL = strSQL & ", "
		strSQL = strSQL & DATE_DELIMITER & datDateTimeField & DATE_DELIMITER
		strSQL = strSQL & ");"
		' Execute the SQL command.  I pass it a variable lngRecsAffected
		' in which to return the number of records affected.  I also tell
		' it that this is a text command and it won't be returing any
		' records... this helps it execute the script faster!
		' And before you ask... I don't know, but YES IT IS OR!!!
		cnnFormToDB.Execute strSQL, lngRecsAffected, adCmdText Or adExecuteNoRecords
		' Dispose of the CONN object
		Set cnnFormToDB = Nothing
		' Display a verification message and we're done!
		<h2>Thanks for submitting your information to us!</h2>
		<strong>The resulting SQL statement was:</strong>
		<pre><%= strSQL %></pre>
		<strong>Number of records affected:</strong> <%= lngRecsAffected %>
	End If
End If

Back the the Sample Output

Back to the Sample Index

Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback


WebMediaBrands Corporate Info

Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Shopping | E-mail Offers | Freelance Jobs