My Portal Project

Beginnings of data and Forms Redux

Hopefully you had a chance to browse through the various design objects that .NET gives you, and I hope you found some cool things. The last installment was nothing more than downloads and playtime with a little background. In this installment we will take some more concrete first steps in designing our help desk application.

In the Domino world the typical first step is to design your forms since you will need these to create data to display in the views that come next. Some of the data bound controls that .NET offers can make this process significantly easier. Once we create a database table, for example a keyword table, these data bound controls create default CRUD (create/read/update/delete) controls with little work on the designers part.

First things first; we need to create a data store. For the sake of brevity, if you have not already installed MySql and MySql Admin console, then come on back when you are done. If you have installed both, then I need you to do one more install (oops). Even though .NET loves to work with Microsoft’s SQL server, there are many DB platforms out there and I really wanted to make sure what we learn here is transferable regardless of your DB of choice. In fact, there are a few things in here that I am certain we are the first to know with respect to .NET using an ODBC connector. I will say that if you are against installing a DB server on your machine, you can use Access as a datastore, but you will need to do you own translation of my instructions. In fact, if anyone does use Access, I am sure we would all benefit from a quick install document. Also, if you use Access, you do not need to do any of the steps that involve configuring an ODBC data source.

Anyway, that ODBC connector for MySQL is here :

When you have the MySQL ODBC connector installed, create an ODBC connection for your local MySQL server, and give it a data source name (DSN) of “Local”.

For help in configuring ODBC for Mac and Linux: and Windows

In either case, make sure you select the MySQL ODBC driver.

All set? Good. Now we open the MySQL Administrator so we can create a new database and table for our example. Hopefully you remember your SA password and you are in. Your screen should look something like this:

On the left side, click on the “Catalogs” link which should then populate a list of existing databases on this server. I am FAR from being a respectable DBA, but for some reason they now call databases “schemas”, so when we want to create a new database/schema, we right click in the same pane where the database list is, and select “Create New Schema”

After you have created a new database (I called mine “helpdesk”), now we create a table. At the bottom of the right pane are four buttons, click on the “Create Table” button.

Our table is pretty basic with four columns; ID, kwCategory, kwName, and kwValue. The ID column is an integer that is self-incrementing, and the rest are variable character columns. The table name is “hd_keywords, and make sure that the only index column you have is “id”.

Click OK/Apply and we are almost there.

Although we could use the SA username and password for the sake of our application, let’s just pretend we care a little about security and create a special ID for our application. In the MySQL Administrator, click on the “User Administration” link, and in the lower left pane right-click and select “Add New User”

After typing in the user name (I called mine “helpdesk” ; how original) and password (mine is “passw0rd”), click on the “Schema Privileges” tab at the top of the right pane, and click on our helpdesk database/schema. You will see a list of assigned priveleges, and a list of available privileges, and for our user we will give him all the privileges for our helpdesk database.

Once that is all done, we are ready to do the fun stuff (sorry if I offended any DBA’s).

From the last installment, we have created a page called “default.aspx” with some labels, text fields, and a submit button. Unfortunately we will need none of it, so just delete all the objects we added and go back to a blank page layout.

Instead of wasting your time with some fluff examples that have no real world application, we’ll design our form using some of the server controls that will allow us to create applications that behave like a Domino developer is used to. The method will be different, but the end result (hopefully) will be familiar.

On our nice clean form, drag out a “MultiView” control from the Standard controls category, and place it on the page. The Multiview control is something like a hide-when object that can display different parts of a page using conditionals. In our case, we are going to have one page that both displays our keywords table, and provides an editing and creating UI.

Next drag a “View” object INTO the MultiView object. Then using the properties box on the right, change the ID of the View object to “createEdit”. This portion of the page will be displayed when we are editing an entry, or creating a new one.

Side Note: There are no hard and fast rules that say you must place view/edit/create controls all on one page and use a multi-view. You could create a separate page for every function. Since all of these functions have alot of common pieces, like data source objects that point to the same db and table, I felt that they would be best consolidated into one physical page.

Now we can drag a data source onto the page. From the “Data” category of tools on the left, drag and drop a “SqlDataSource” object onto the page outside of the MultiView control. Then click on the little arrow in the top right corner of the SqlDataSource control and select “Configure Data Source”.

Then click “New Connection” and select “Microsoft ODBC Data Source”.

In the next screen select the name of the data source that you created, I used the name “Local”. Then enter the credentials for the user who we gave full rights to, in my case “helpdesk”.

The next screen allows you to select a unique name for this connection that you can reuse in other parts of the application. I used the name “HelpDeskConnect”.

The last step is to create a SQL SELECT statement for this control. In our case we want all the fields so we simply check the asterisk.

Once we have the data source object setup for reading we can place a data bound object to make use of this data source. From the “Data” category of tools, drag and drop a “DetailsView” object INSIDE the “createEdit” view object. Once in place, click on the right pointing arrow at the top right of the detailsview object.

In the “Choose Data Source” field, select our newly created SqlDataSource. Once you do this, the DetailsView object will change to look like our keywords table. Now check the “Enable Paging” checkbox.

Notice the “1 2″ links? These controls will allow us to browse our table with pagination. Now we need to add the capability to add new records, and to do this we will need to tell the SqlDataSource control what SQL statement to execute when inserting new records. Open up the properties window for the SqlDataSource (click once on the object and the properties are visible in the right pane), and click in the “InsertQuery” property.

Now click the “…” icon to open up the query configuration screen.

Our INSERT statement is pretty basic but syntax varies from DB to DB. If you are using SQL Server then you would not use the DatabaseName.TableName format but only the table name. Notice that in the place of our field values we will use “?” as placeholders. An important thing to remember here is that the order that we send the fields to the insert call needs to match the order of the field names in the SQL statement, and the order that they are sent is the order they appear in the form. So if we change the field order in the form, we will need to change the field name order in the SQL statement.

Now that we have that done, let’s go back to our page design layout and click on the little arrow in the upper right corner of the detailsview object. Now we have an option “Enable Inserting”. Check this option and the control will now have both the pagination controls and the “New” link.

Now save the page and preview in the browser (right click on page and select “View in Browser”). See anything? Nope, and that is because we do not have any data in the table. So we will need to tell the detailsview control what to do when there is no data. Again, click on the little arrow in the upper-right corner of the detailsview control and select the “Edit Templates” option.

The detailsview object provides us with various formatting templates like Footer, Header, EmptyData, and Pager templates. So for our purposes, we need to edit the EmptyData template by selecting it. The detailsview object functions in various modes like Read, Insert, and Edit, and the default is read mode. We don’t want to change that so we will need to provide a way to switch to insert mode when there is no data.

Into the EmptyDataTemplate we will need to drag a button from the “Standards” category of controls. Edit the button properties to change the “Text” of the button to something like “Add Data”. Then double click on the button.

We have now entered the realm of code writing for VB.NET. By double clicking on the button, we have created a Button1_Click event handler where we will place our code to switch the mode of the detailsview object.

Now brace yourselves for something extremely difficult. Inside our new event handler, type the ID of the detailsview object (which is “DetailsView1″ if you did not change it) and then add a “.”. Just like the LotusScript UI, you get all the properties and methods of the DetailsView1 object listed for you, but .NET also provides detailed descriptions of each event/method/property. Just click on any event/method/property in the list and you will see the description as a callout. We will need the “ChangeMode” method.

Once you select the “ChangeMode” event by double-clicking, you are then prompted with the different modes you can select from.

Select the “Insert” mode and add a “)” and we are all done. That was a bitch, wasn’t it?! So save the page and view in the browser. See anything? Remember that we have a MultiView control in place and we have not set any conditionals for when to show the “createEdit” view. So let’s go back to the “Design” view of the page and double-click anywhere on the page but outside of a control object. This will take us back to the back-end VB code and will create a “Page_Load” event handler.

So same drill as before, type in the name of the object we need to effect and then add a period to see all the events/methods/properties, in this case type the ID of the MultiView object which will be “MultiView1″ if you have not changed it. The method we want is “SetActiveView” and it takes one parameter and that is the ID of the view, which for us is “createEdit” unless you changed it.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        MultiView1.SetActiveView(createEdit)
    End Sub

So save the page and view in browser and what should appear but our “Add Data” button. Click the button and you should have our default out-of-the-box data entry and pager. Let’s create an entry for Help Desk request severities.

After you click “Insert” you should have a resulting page that looks like this:

Side Note: If you have any errors the most likely culprit is the SQL insert statement. MySQL insists that tables be reference using DatabaseName.TableName, whereas SQL Server does not like that syntax. Also, the error page will not show you any detail unless you make a change to the web.config file that you can open from the top-right pane. You will need to track down the “compilation” line and change the “debug” attribute from false to true.

After our first insert we will not have any pagination controls but let’s add another severity to the list. After that you will see the “New” link and the pagination controls.

So big deal! It’s ugly and I want more control! Well between now and part three, take a look at all of the formatting options you have with the DetailsView object. If you click on the little arrow in the upper right corner of the detailsview control you can select “Auto Format” option and check out some of the styles that are already there. Also remember that “Pager Template” that we saw before which allows you to set the style of the pagination controls.

Let’s finish up this installment with something that looks like a Notes view, and is called a “GridView”. The GridView object is best described as a highly customizable table that displays a record set from a SQL query. Remember I mentioned that we were going to use the MultiView control object to toggle between a view display and a form edit/insert display? We will continue with that by adding another view to our MultiView and placing our GridView within the new pane.

So, from the left we drag and drop a “View” control, from the “Standard” category, into the MultiView but outside of the existing pane. The order is not important so above or below is fine. Once it is placed, change its ID to something you will remember like “readView”. Then drag a GridView object, from the “Data” category, into the “readView” pane. After it is positioned we can pick our data source which will be the same one we used in the DetailsView. You can select the Data Source by clicking the little arrow in the upper right corner of the GridView object.

After selecting the data source, the control will resemble the table layout and have the field names across the top row of the table. Click the little arrow at the top right of the GridView object and check the boxes for “Enable paging”, and “Enable Sorting”. Notice the new links in the control?

Want to take a look? Remember that we have the MultiView control in place and we wrote code that made the editCreate View panel the default. So we will need a way to switch bewtween the two modes and the most common approach is the use of the queryString. We will make the readView pane the default and switch over to the editCreate pane when we need to create a new entry or edit an exiting one. What is needed is a link from the GridView that will call the same page with a querysting that will cause the panes to switch.

Click the arrow in the upper right corner of the GridView object and select the “Edit Columns” option. This will show you a dialog of the existing columns with an Add button for our purposes. Click the “-” besides the “BoundField” category in the upper left to display all the column types we can create.

We will need to create a “HyperLinkField” by selecting it in the upper left, and clicking the “Add” button. The right hand pane will now be showing all the properties of our HyperLinkField. You can play with the formatting later, we need to create the link itself. All we need for our “New” link is to set a queryString value, so in the “NavigateURL” property just type in the name of our page with “?view=editCreate” appended to the end. I had already changed the name of my page to “keywords.aspx” so my string is “keywords.aspx?view=editCreate”. Then change the “Text” property to whatever you want the link to show, in my case just “Create”. The last thing to do is use the arrow keys to move this new field to the first position.

Now we need to change the behavior of the MultiView but double-clicking anywhere on the page outside of any object to see the page_load event. Here we will check for the value of the querystring by using the “Request” object.

Side Note: Make sure that when you type these URL’s and write code to check for values that you keep case sensitivity in mind. VB.NET will see “editCreate” and “EditCreate” as two different values and your behaviors will not work.

Anyway, let’s save the page and “View in Browser”. Notice how you can sort the columns by clicking on the column name? Now click our “Create” link and hopefully you will see our DetailsView object in the “editCreate” pane. Unfortunately, the detailsview control is showing the first record in the table and you still need to click the “New” link to create a new record. So here is your homework with a few hints.

1. Get the DetailsView object to show the Insert screen when you click the “Create” link. Hint: Remember the DetailsView property called DefaultMode?

2. Get the DetailsView object to show a keyword entry in Edit mode by clicking a link called “Edit” in the GridView. Hints: HyperLinkField; Querystring to change the mode of the detailsView, and another SQLDataSource to SELECT with parameters.

3. Get the DetailsView object to return back to the GridView after editing or inserting, Hints: Check out the HELP and look at the events of the detailsView object, especially ItemInserted, and ItemUpdated. You might need to create functions called DetailsView1_ItemInserted, and DetailsView1_ItemUpdated, and within those functions do something to the active pane of the multi view.

As usual if you have any questions/comments/corrections just let me know.

This entry was posted on Thursday, December 22nd, 2005 at 8:18 pm and is filed under .NET for Domino Devs. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.