Creating a web To-Do List with an Active Desktop Interface

• Download source files - 9.86 Kb

Introduction

I'm a very unorganized person. I usually depend on my feeble brain to remember projects or bugs I need to fix. Unfortunately, I am all too human and I will occasionally forget something important. So, when I started using Windows XP Professional (with IIS conveniently installed), I decided to make a web "to-do list" to remind me of all the important things I need to do.

I could have easily left this web app consigned to a browser window, but I thought it would be really cool to finally be able to utilize Active Desktop. My to-do list would always be right in front of me on my desktop!

Assumptions

Of course, to make a web application work, you must have a web server at your disposal. This project uses Internet Information Server and Active Server Pages to perform most of the functionality.

The to-do items are stored in a database (I'm using Sybase ASA 7) running as a service on the web server machine. There are only two tables, item and user with the specifications below.

The ToDoLists database

• Item table
  SQL

  item_num      integer      primary key
  description   char(256)
  status        char(1)
  user_id       char(10)
  date_entered  date
  date_due      date
  priority      char(1)

• User table
  SQL

  user_id       char(10)     primary key
  name          char(80)
  password      char(20)

Active Server Pages

Several ASP pages are used to update and display the ToDoLists database items.

First of all, new users are directed to login.asp where they enter a user ID, name and password.

<form id=FORM1 name=FORM1 action="default.asp" method=get>

If you have never logged in, choose a User ID, Password and Name.

<table>
<tr><td>User ID:</td><td><input name="U" size=5 maxlength=10></td></tr>
<tr>
   <td>Password:</td>
   <td><input type="password" name="P" size=10 maxlength=20></td>
</tr>
<tr><td>Name:</td><td><input name="N" size=20 maxlength=80></td></tr>
<tr>
  <td colspan=2><input type="submit" name="submit" value="Log In"></td>
</tr>
</table>
</form>

The FORM tag's action value will submit the form data to default.asp which will then add the new user, or login the existing user, and handle the main user interface of the application.

Once the user is logged in through default.asp, the user can perform three main functions:

1. Insert a new item
2. Update an existing item
3. Delete completed items.

Each of these functions are performed in separate ASP pages which, when finished, redirect the user back to default.asp to update the main user interface.

Inserting new items

When the user fills out the "Enter a new item" form and clicks the "Save" button, the form data is sent to insertitem.asp where it is added to the item database table. The user is then redirected to the main page.

strUser = Request.QueryString("U")
strPwd = Request.QueryString("P")
strName = Request.QueryString("N")

strDesc = Request.QueryString("description")
strDueDate = Request.QueryString("due_date")
strPriority = Request.QueryString("priority")

if (Len(strDesc) > 255) then
    strDesc = Left(strDesc, 255)
end if

strSelect = "INSERT INTO item (description, date_entered, date_due, " &_
    "priority, status, user_id) VALUES ('" + strDesc + "', today(), " &_
    "'" + strDueDate + "', '" + strPriority + "', 'O', '" + strUser + "')"

...

Response.Redirect("default.asp?U=" + strUser + "&P=" + _
                              strPwd + "&N=" + strName)

Updating existing items

Each item listed is displayed in a table nested in a form. The table has three cells, one for a "completed" checkbox, one for a priority indicator (Low=nothing, Medium=*, High=!), and one for a description of the item. (At this time, the only thing the user can update is whether the item is completed or not, via the checkbox.) When the user wants to mark the item completed, he/she checks the box and the form is submitted to updateitem.asp. The item is updated in the database and the user is redirected to the main page.

strUser = Request.QueryString("U")
strPwd = Request.QueryString("P")
strName = Request.QueryString("N")

strSelect = "SELECT item_num FROM item WHERE user_id = '" + strUser + "'"
set adorItems = Server.CreateObject("ADODB.RecordSet")
adorItems.Open strSelect, Cnxn

while (not adorItems.EOF)
    strStatus = Request.QueryString(CStr(adorItems("item_num")))

    if (strStatus = "on") then
        strUpdate = "UPDATE item SET status = 'C' WHERE item_num = " &_
            + CStr(adorItems("item_num")) + _
            " AND user_id = '" + strUser + "'"
    else
        strUpdate = "UPDATE item SET status = 'O' WHERE item_num = " &_
            + CStr(adorItems("item_num")) + _
             " AND user_id = '" + strUser + "'"
    end if

    set adorCmd = Server.CreateObject("ADODB.Command")
    set adorCmd.ActiveConnection = Cnxn
    adorCmd.CommandText = strUpdate

    adorCmd.Execute()
    set adorCmd = nothing

    adorItems.MoveNext()
wend

...

Response.Redirect("default.asp?U=" + strUser + _
                 "&P=" + strPwd + "&N=" + strName)

Deleting completed items

When the user clicks the "Delete Completed Items" button, the user ID, name, and password are sent to deletecompleted.asp, where all items checked on the main page are removed from the database. The user is then redirected to the main page again.

strUser = Request.QueryString("U")
strPwd = Request.QueryString("P")
strName = Request.QueryString("N")

strDelete = "DELETE FROM item WHERE status = 'C' AND user_id = '" + _
                                                      strUser + "'"

set adorCmd = Server.CreateObject("ADODB.Command")
set adorCmd.ActiveConnection = Cnxn
adorCmd.CommandText = strDelete

adorCmd.Execute()

...

Response.Redirect("default.asp?U=" + strUser + _
                    "&P=" + strPwd + "&N=" + strName)

Active Desktop

To utilize the Active Desktop, I hade to create a Channel Definition Format (CDF) file which is used to implant the web page onto the desktop.

Since we would rather not have to see the login page all of the time, default.asp will log the user in by sending the user ID, name, and password in the URL. This added convenience made it impossible to have a generic CDF for everyone, so I overwrite the CDF file after the user logs in, making the CDF specific to that user.

' create an active desktop cdf
dim objFileSys, objTextStream
dim sText

Set objFileSys = Server.CreateObject("Scripting.FileSystemObject")

Set objTextStream = _
  objFileSys.OpenTextFile("drive:yourpath\todolists\todolists_ad\" &_
  "activedesktop.cdf", ForWriting, true)

sText = "<?XML VERSION=""1.0""?>" &_
  "<Channel HREF=""http://yourdomain/todolists/default.asp?U=" + _
  strUser & + "&P=" + strPwd + "&N=" + strName + """" &_
  " SELF=""http://yourdomain/todolists/todolists_ad/activedesktop.cdf"">" &_
  "<ITEM HREF=""http://yourdomain/todolists/default.asp?U=" + strUser &_
  + "&P=" + strPwd + "&N=" + strName + """ LASTMOD=""2002-02-04"" >" &_
  "<TITLE>ToDo List</TITLE>" &_
  "<USAGE VALUE=""DesktopComponent""><OPENAS VALUE=""html"" />" &_
  <WIDTH VALUE=""350"" /><HEIGHT VALUE=""400"" />" &_
  "</USAGE></ITEM></CHANNEL>"

objTextStream.Write sText
objTextStream.Close

This could potentially cause problems if a user A logs in before user B and adds the Active Desktop channel after user B has logged in. User A's Active Desktop channel would then log him in as user B... bummer, it's not perfect.

To add the channel to the active desktop, the user clicks the "Add to Active Desktop" button at the bottom of the main page and Viola! Every time the user restarts, the to-do list is in plain sight.

Conclusion

I hope that I have given enough information in this article to please the CP gods, and any average Joe programmer out there. In case I haven't, I have included the source for all of the pages and some screen shots.

Overall, this was a pretty simple project, except for the Active Desktop implementation. I had a difficult time getting the channel to refresh updates without opening a new browser window every time. After much trial and error, I realized that it seemed to work after calling a separate ASP and then quickly redirecting back to the main page. For my first foray into Active Desktop programming, I'd say it was pretty successful.