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

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
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="adovbs.inc" -->
<%
' *** 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=10.2.2.133;" _
	& "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.
Const DATE_DELIMITER = "'"
' *** 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">
	<tr>
		<td align="right"><strong>Text Field:</strong></td>
		<td align="left"><input type="text" name="text_field" maxlength="10" /></td>
	</tr>
	<tr>
		<td align="right"><strong>Integer Field:</strong></td>
		<td align="left"><input type="text" name="integer_field" /></td>
	</tr>
	<tr>
		<td align="right"><strong>Date/Time Field:</strong></td>
		<td align="left"><input type="text" name="date_time_field" /></td>
	</tr>
	<tr>
		<td> </td>
		<td>
			<input type="reset" value="Clear" />
			<input type="submit" value="Save" />
		</td>
	</tr>
	</table>
	</form>
	<%
Else
	' 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
		Err.Clear
	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
		Err.Clear
	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
		Err.Clear
	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
	Else
		' 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
		cnnFormToDB.Close
		Set cnnFormToDB = Nothing
		' Display a verification message and we're done!
		%>
		<h2>Thanks for submitting your information to us!</h2>
		<p>
		<strong>The resulting SQL statement was:</strong>
		<pre><%= strSQL %></pre>
		</p>
		<p>
		<strong>Number of records affected:</strong> <%= lngRecsAffected %>
		</p>
		<%
	End If
End If
%>

Back the the Sample Output

Back to the Sample Index


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

internet.commediabistro.comJusttechjobs.comGraphics.com

Search:

WebMediaBrands Corporate Info

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