INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • 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.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ASP 102

ASP Database Editing, Dynamic Display, Form, and Function Examples by Tarwn
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: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close