INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I have tons of books, have book marked tons of tutorials, which have helped, but this forum has answered those "impossible to find" solutions. I am thrilled with this site..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Active Server Pages (ASP) FAQ
|
ASP 102
|
ASP Database Editing, Dynamic Display, Form, and Function Examples
Posted: 2 Jul 02 (Edited 16 May 03)
|
First a short note:
This was originally in response to a post asking to create a simple ASP page that would list the contents of a specific table and allow a user to choose to add new entries, edit entries, or delete entries. I wrote up the first file (urllist.asp) with full commenting. by the time I finished I realized no one would be able to reuse the code so I created a second version (uncommentedUrlList.asp).
Then since I was already past my bedtime and in response to later emails and ideas, I created a quick version that included styles for look and feel, more confirmation for changes, re-ordering, and a few other things here and there. Enough of the talking already :)
File Download
All three files and the database can be downloaded from this link:
http://www.tiernok.com/downloads/urllist.zip
I will attempt to update this periodically as it is on my web server at home (cable modem, dynamic ip, yech). For convenience and the fact that this is supposed to be a tutorial, I will paste the first version below after the database explanation.
Database: Access 2000
The name of the db is mydatabase.mdb and is located at C:\data\mydatabase.mdb
Obviously this is going to change depenedant on where yours is located and what you called it. If you are using a differant database type, then your entire connection string will change, as differant drivers are necessary to create the connections and process the queries. There are tutorials on how to do this and many quick and dirty ways to do it as well.
Database Design:
This is a one table design.
urllist - Table name
urlPK - autonumber, primary key
websitename - text 255
url - text 255
urllist.asp:
I will not explain to much of this as it already has such an abundance of code, that very little should be unexplained. There are several places in the file where you will see code that you may know a shorter way for. I attempted to write it to work with any size table, and hopefully was consistent about that the entire time through.
On to the Code!
I can't read the code!
I just looked at the preview, the comments are a lot less obvious in one color so I would suggest you copy it into an editor to read.
ÿ
<%
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>
ÿ
I hope this helps you in some way in the future and have fun with it.
Cheers,
-Tarwn |
Back to Microsoft: Active Server Pages (ASP) FAQ Index
Back to Microsoft: Active Server Pages (ASP) Forum
My FAQ Archive
 Email This FAQ To A Friend |
|
 |
|
