ASP Database Editing, Dynamic Display, Form, and Function Examples

<%
Option Explicit

'********************************* Notes **********************************************************
'	I apologize if anything in this is to basic or to advanced, I have spent so much time
'	trying to explain things to my barely computer literate girlfriend and than to my programming
'	coworkers, that I sometimes get a mish mash of simple and complex ideas/explanations

'*** Three versions of code
'*** See urlList.asp for the commented version
'*** See uncommentedUrlList for base code
'*** See prettierUrlList.asp for base code with a little prettiness

'*** Note: I only defined this so I only had to change the name in one place, it is not necessary
Dim page_name
page_name = "urllist.asp"

'********************************* Defining a couple variables ****************
Dim strConnect

'note: this connection string is actually connecting to an access 2000 db located in my C:\data\ folder
'		you will need to change the driver and data source location to fit your system
'		The easiest way to make a connection string is to open Visual Studio and create a adodb object
'		and use the qizard to make a connection string, than copy + paste it into your code :)

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
				 "Data Source=C:\data\mydatabase.mdb;" & _
				 "Persist Security Info=False"

'I use this form variable to keep track of why I came to this page
Dim next_action
If Request.Form("next_action") <> "" Then		'In other words, if it was sent from the previous page
	next_action = Request.Form("next_action")		' remember it for later
Else											'Otherwise
	next_action = "show list"						' show them the list
End If


'********************************* A Note on Functions ********************************************
' I have listed the functions first. If you follow through you will see each has a specific
'	purpose. Near the bottom you will see the logic behind choosing functions and son on.
'

'********************************* Show List **********************************
Function show_list
	Dim objCommand			'The object that does the work, connection, query execution
	Dim sqlGetList			'Will hold our SQL query to the database
	Dim rsGetList			'The recordset that holds the results of the query					
	
	sqlGetList = "SELECT * FROM urllist ORDER BY websitename"	'When I execute this it will return rows ordered by
																'websitename, the order is not necessary, but nice
																'to have

	on Error Resume Next								'basically if it hits an error, ignore it
														'you can also specify a function to call if you prefer
		Set objCommand = Server.CreateObject("ADODB.Command")	'instantiate the command object
		objCommand.ActiveConnection = strConnect		'set the connection to our string from top of page
		objCommand.CommandText=sqlGetList				'set the query to our string a few lines up
		objCommand.CommandType=adCmdText				'CommandType, I've forgotten :P should be listed in
														'	reference books or online refernces
		Set rsGetList = objCommand.Execute				'execute our query and return it to rsGetList object
		Set objCommand = Nothing						'be kind to your computer, releasing object saves memory

	rsGetList.MoveFirst									'Moves the recordset to first record, always a good idea
														'to do this when you are ordering in your query

	'---- Do the display now
	%>
	<form method=POST action="<%=page_name%>" name="frmUrlList">	<% 'Start our form to hold info				%>
	<input type="hidden" name="next_action" value="list">		<% 'next action is set dynamically to be used
																   'below in case statement in deciding what 
																   'portion of page to display				%>
	<input type="hidden" name="record_number">					<% 'record number is set dynamically for editing %>	
																<% 'and deletion							%>
	<table>														<% 'No pretty html here as it is just an example %>
		<tr>
			<th>
				Website
			</th>
			<th>
				Address
			</th>
		</tr>
	<%
	'This loop continues until the recordset returns an EOF (End of File)
	'	It will print all of the html we have inside over and over until then
	Do While NOT rsGetList.EOF
		%>
		<tr>
			<td>
				<a href="<%=rsGetList("url")%>"><%=rsGetList("websitename")%></a>	<% ' Print the name with the 
																					   ' url inside the href="" %>
			</td>
			<td>
				<%=rsGetList("url")%>												<% ' Print the url			%>
			</td>
			<td>
				<% 
				' Buttons, buttons, whos got my buttons
				' Possibly a little confusing at first, these buttons do very similar things.
				'	Client Side Javascripts (only IE scripts to keep it simple)
				'	-They both set the value of the hidden input called record number to the value
				'	of this rows urlPK. Passing a unique identifier is easier than passing the text
				'	we are going to change or delete. For Example: Suppose you accidentally enter the 
				'	same site twice? Using the pk means we will only change the exact one we choose to
				'	instead of all rows that have the same text
				'	-Then they set this next_action input. When this form is submitted back to this 
				'	page we will be able, the next_action that we set will give us an easy way to 
				'	choose what we are doing, whether it is a delete or displaying an editable record.
				'	-The textSubmit() function is a workaround for IE5.0 javascript form submissions. 
				'	I include it standard in every page I do, it is unnecessary for IE5.5+
				'	In 5.5+ you would only have to do a frmUrlList.submit();
				%>
				<input type="button" value="Update" onClick="frmUrlList.record_number.value='<%=rsGetList("urlPK")%>'; frmUrlList.next_action.value='update record'; textSubmit('frmUrlList');">
				<input type="button" value="Delete" onClick="frmUrlList.record_number.value='<%=rsGetList("urlPK")%>'; frmUrlList.next_action.value='delete record'; textSubmit('frmUrlList');">
			</td>
		</tr>
		<%
		rsGetList.MoveNext		'Important! I always forget this one at least once a project. If you
								'	don't move to the next row, you'll never get to the EOF for the
								'	Do Loop. That equals 90 seconds of IIS eating your resources for
								'	nothing as it processes the same row over and over and over.
	Loop
	
	'Putting one blank entry at the bottom cuts down on the number of clicks the user has to make
	'	to add a new entry. Notice in the button we don't specify a record number, as we have not 
	'	inserted it into the database yet, we do not know, or need, the record number
	%>
		<tr>
			<td>
				<input type="text" name="txtNewWebSite">
			</td>
			<td>
				<input type="text" name="txtNewUrl">
			</td>
			<td>
				<input type="button" value="Add New Website" onClick="frmUrlList.next_action.value='insert record'; textSubmit('frmUrlList');">
			</td>
		</tr>
	</table>
	</form>
	<%
End Function


'********************************* Update Page **********************************
Function update_page
	'This will look familiar if you read through the show_list function, copy and paste is a beautiful thing

	Dim objCommand			'## hasn't changed
	Dim sqlGetRecord		' If you notice I do change the name, just so I will remember what this variable is for
	Dim rsGetRecord			' Ditto, there is no rule that says they have to be named like this, just my technique
	
	sqlGetRecord = "SELECT * FROM urllist WHERE urlPK = "&Request.Form("record_number") 'get the record they clicked

	
	on Error Resume Next								'## hasn't changed
		Set objCommand = Server.CreateObject("ADODB.Command")	'## hasn't changed
		objCommand.ActiveConnection = strConnect		'## hasn't changed
		objCommand.CommandText=sqlGetRecord				'## only the variable name changed
		objCommand.CommandType=adCmdText				'## hasn't changed
		Set rsGetRecord = objCommand.Execute			'## only the variable name changed
		Set objCommand = Nothing						'## hasn't changed
	
	'Why are you putting a single record in a table, you ask
	'	consistency only, just a habit, keep pages looking similar
	'	and the user has less confusion
	'	granted they aren't very pretty pages right now, only standard html, but same concept
	
		'-- Important. The form here has an onSubmit action. If the user decides to cancel on the query
		'	it will return false which will in turn return false to the submission action, which will 
		'	make the browser forget the user ever hit that little submit button and innocently look the
		'	other way while the user continues to edit the text boxes in the form
	%>
	<form method=POST action="<%=page_name%>" name="frmUpdateForm" onSubmit="return confirm('Are you sure?');">
	<input type="hidden" name="next_action" value="update submit">
	<%	'Notice we enter the record number from the previous page back into the form	%>
	<input type="hidden" name="record_number" value="<%=Request.Form("record_number")%>">
	<table>
		<tr>
			<th>
				Website
			</th>
			<th>
				Address
			</th>
		</tr>
		<tr>
			<td>
				<input type="text" name="txtWebSite" value="<%=rsGetRecord("websitename")%>">
			</td>
			<td>
				<input type="text" name="txtUrl" value="<%=rsGetRecord("url")%>">
			</td>
			<td>
				<input type="submit" value="Update!">
			</td>
		</tr>
	</table>
	</form>
	<%
End Function

'********************************* Update Submit ********************************
Function update_submit
	'Again, a little copy and paste and we can create an instant update function
	'	with only a couple minor changes

	Dim objCommand			'## hasn't changed
	Dim sqlUpdateRecord		'## only the name has changed
							'We don't need a recordset because we are not asking for anything back
	
	sqlUpdateRecord = "UPDATE urllist SET url='"&Request.Form("txtUrl")&"', websitename='"&Request.Form("txtWebSite")&"' WHERE urlPK = "&Request.Form("record_number")
							'Ok, you may ask about all of the quotes here. Any time you enter a string
							'	into a text field in the database you need quotes around it. But since we
							'	are building a string variable, we are already using double quotes, so in this
							'	case double quotes are used during concatenation to build the sqlUpdateRecord 
							'	variable, but single quotes are actually a part of that string we are building

	
	on Error Resume Next								'## hasn't changed
		Set objCommand = Server.CreateObject("ADODB.Command")	'## hasn't changed
		objCommand.ActiveConnection = strConnect		'## hasn't changed
		objCommand.CommandText=sqlUpdateRecord			'## only the variable name changed
		objCommand.CommandType=adCmdText				'## hasn't changed
		objCommand.Execute				'Look close, we aren't setting a recordset variable
										'  equal to the statement anymore
										'  meaning: Execute, don't wait for reply
										'  without that there, the update still occurs, but does
										'  not show up until the entire page is refreshed
										'  we want it to show up immediately because we are calling
										'  the list function right after the update function.
		Set objCommand = Nothing						'## hasn't changed
End Function

'********************************* Insert Record ********************************
Function insert_record
	'Again, a little copy and paste and we can create an instant update function
	'	with only a couple minor changes

	Dim objCommand			'## hasn't changed
	Dim sqlInsertRecord		'## only the name has changed
							'We don't need a recordset because we are not asking for anything back
	
	sqlInsertRecord = "INSERT INTO urllist(url,websitename) values('"&Request.Form("txtNewUrl")&"', '"&Request.Form("txtNewWebSite")&"')"
			'again with the quotes, this time trailing at the end, we had to finish the single quotes
			'	that would show up inside the new string and the parantheses, so we get a group of mix
			'	and match trailing quotes

	on Error Resume Next								'## hasn't changed
		Set objCommand = Server.CreateObject("ADODB.Command")	'## hasn't changed
		objCommand.ActiveConnection = strConnect		'## hasn't changed
		objCommand.CommandText=sqlInsertRecord			'## only the variable name changed
		objCommand.CommandType=adCmdText				'## hasn't changed
		objCommand.Execute								'## No recordset, same as the update above
		Set objCommand = Nothing						'## hasn't changed
End Function

'********************************* Delete Record ********************************
Function delete_record
	'Again, a little copy and paste and we can create an instant update function
	'	with only a couple minor changes

	Dim objCommand			'## hasn't changed
	Dim sqlDeleteRecord		'## only the name has changed
							'We don't need a recordset because we are not asking for anything back
	
	sqlDeleteRecord = "DELETE FROM urllist WHERE urlPK = "&Request.Form("record_number")
							'Very easy to do with a key. Always take a contribution form job over an 
							'edit form job, and always take the delete job over everything else :)

	on Error Resume Next								'## hasn't changed
		Set objCommand = Server.CreateObject("ADODB.Command")	'## hasn't changed
		objCommand.ActiveConnection = strConnect		'## hasn't changed
		objCommand.CommandText=sqlDeleteRecord			'## only the variable name changed
		objCommand.CommandType=adCmdText				'## hasn't changed
		objCommand.Execute								'## No recordset, same as the insert above
		Set objCommand = Nothing						'## hasn't changed
End Function

'********************************* The Real Thing *****************************
'This section will actually build our page now. It will place our html tags and
'	evaluate what action was sent from the previous page.

'--- Standard head html, anything you want here
%>
<html>
<head>
	<title>Url List</title>
	<%	
	'----Text Submit Javascript from buttons earlier----
	%>
	<script language="JavaScript">
	<!--
	function textSubmit(form_obj_name){
	window.setTimeout(form_obj_name+'.submit()',1);
	}

	//-->
	</script>
</head>
<body>
<%
'Now the important part. We will use tha value of the next_action variable to 
'	decide which functions to display/execute

Select Case next_action
	Case "show list"		'If they want the list
		show_list				'call the show_list function
	Case "insert record"	'If the are adding the record
		insert_record			'call the insert_record function
		show_list				'then give them the list where (hopefully) the new record is added
	Case "update record"	'They want to update a record
		update_page				'they need to enter some info
	Case "update submit"	'They are done and submitting to database
		update_submit			'do the changes
		show_list				'with a newly edited record
	Case "delete record"	'You get the idea
		delete_record			'how did you guess?
		show_list				'with one less record
	Case Else				'Very important, how did they get here? We only used the cases above, right?
		'Show some kind of page error
		'	We ask them to contact us and give them a link back that will work
		'	This case should never happen, but if your pages are complex enough
		'	there is always a possibility you overlooked something, so this is nice to have
		%>
		<h1>Internal Error</h1>
		Please contact site administrator.<br>
		Error: Unknown Action Request<br>
		<a href="<%=page_name%>">Click here to return to the list</a>
		<%
End Select

'All done, end our html tags
%>
</body>
</html>