My Portal Project

Last pieces of our first form/view

Welcome back intrepid sailors, and let us waste no time on pleasantries. In our last installment we created our first table in MySql, and created the beginnings of our first view and first form. I left you with some homework to finish the view/form, and the first thing we will do is finish off our Keywords view/form.

First, just to make sure we are all on the same page, here is the code as it stands now.

BIG WOOPS: This version of the code is the version I had used to connect to a SQL server that I now have. It may have some issues with MySQL which is what I promised I would use for this series. Part 4 will have the updated code that will work with MySQL. Sorry for the confusion.

Once you extract the contents you can click “File - Open Website” and choose to select from the File System. Browse to your extracted directory and open “keywords.aspx”. You may notice that I have two similarly named files, one called “keywords.aspx” and the other named “keywords.aspx.vb”. This is an option with the .NET 2.0 framework that allows you to either seperate your VB code from your page, or keep it together. There is no “right” way to do it, but some folks may find it easier to navigate large amounts of VB code from a seperate file than along side all of the markup code. You choose which method you want when you create a new page. From the Solutions Explorer in the top right pane, right click on the solution name and select “Add New Item”.

You can see the option at the bottom that allows you to place the code in a seperate file.

With that said, let’s move on to our keywords page.

So when we view our Keywords list we should have a form that looks something like this.

When you click on the “Create” link, we should have something like this:

Hopefully you all noticed how silly it is to have “Create” links next to each entry and I hope you thought I did that on purpose. Unfortunately I was getting punchy late in the evening and that link should have been an “Edit” link. The reason I lost track of things is that there are multiple ways to edit data when using a GridView object like we are. The first method is to link out to a DetailsView (or FormsView) object, but you can also edit the data while remaining in the GridView. This is similar to R6’s ability to edit data from the view. In our example we will not use the in-place editing because of limitations in how SQL queries are formed in .NET. I will delve into this in a later installment.

So let’s change our “Create” link to say “Edit”, and change the link URL to pass the our “ID” field. Mouse over the GridView object and click on the little arrow in the upper right corner, and select “Edit Columns”. In the bottom left of the dialog box select our Collumn named “Create” and change its “Text” property to “Edit”.

Then we change the “DataNavigateURLFields” property to “id”, and DatanavigateUrlFormatString property to “?editCreate=1&id={0}”. What this combo does is establish a list of form fields that we can use in the URL, and then a way to structure the link to use these field values. If we had multiple fields to pass, the subsequent field values would be referenced like this {1},{2},etc.. Now save the form and view it in the browser. When you mouse over the “edit’ link you should see a URL like this in the status bar “keywords.aspx?editCreate=1&id=4″.

SideNote: In the second installment I hard coded the page name in the DataNavigateURLFormatString property which is badbadbadbadbadetcetc. The syntax I used this time allows for a page name change without effecting the code.

Now let’s take care of opening the DetailsView in edit mode for the proper record. I hope that you all remember that I am writing this as I learn it so sometimes I may make a slight deviation in my direction, and here is a good example. It seems that the use of the SqlDataSource objects should be more specific to a task. In the page we are creating, we are using a MultiView object to toggle between reading in a view, and editing in a form, all using the same SqlDataSource object. This gets difficult since the GridView uses a “Select *” on the data, but the DetailsView will select based upon the querystring parameter passed. So we will create another SqlDataSource object for the DetailsView, and when we do another form/page, we will use a more conventional design of seperating view pages and edit/create pages.

The good news is that creating a new SQLDataSource is alot easier once you have already created one. That is because the connection string that we built for our first data source is reusable. So drag a new SQLDataSource object into the editCreate pane of the multi-view control. When positioned, click “Configure Data Source”.

Select the connection string that we made for the first data source, and click “Next”.

Now we build our SQL statement, so first select the table name (hd_keywords for me), then select the asterisk for all fields. Since this data source is for our editing, we will need a “WHERE” clause to specify which record to edit. So click the “Where…” button. The next screen is where we will choose what will be the source of our WHERE clause. Our where clause is going to use the querystring “id” parameter so the options should look like this

When done, click “Add” then “OK”. Remember when we added code into the Page_Load event that looked at the querystring and toggled the multiview control? Well, now we are going to add some new code to the page_load to determine if we need the DetailsView object in Edit, Insert, or Read-Only mode. If we have an “ID” parameter in the querystring then we are obviously going to edit the record. Here is the code we need:

Now let’s save our form and show in browser. Hopefully, if all has gone well, when you click the “edit” link next to a record, you will get the Detailsview in edit mode with the appropriate record. Now all we are missing is a way to submit the data since our Insert link is gone. This is because we have not set the UpdateQuery property on the new SqlDataSource we created for the DetailsView. We are going to do this one a bit different which will involve creating parameters for our data source. Creating parameters for a data source is a way to create a generic SQL statement and then specifying the values that go into a statement at runtime.

Side Note: Although this may seem like more work than Domino, keep in mind that this process allows us to use ANY data source, even business objects and XML. Plus, you are stuck trying to follow my disjointed learning processes that will involve some backtracking. Hopefully we can get through this together with some of you correcting me when you find a better way.

So let’s create the Update statement for our new SqlDataSource. This is done by clicking the “…” icon in the SqlDataSource’s UpdateQuery property. We will need four parameters to do the update and they are the same fields from the form.

I matched my parameter names with the field names only for the sake of simplicity, and I am binding the id parameter to the querystring “id” value because the ID field is set for read only and it does not exist on the form as a field. Now that the Update statement is done, we can show two different ways to trigger an update. The first is to simply click on the little arrow in the upper right corner of the DetailsView object and click “Enable Editing” (which appears when we have an update query). This will place an “Update” link at the bottom of the DetailsView. The second method involves making a “Save” button that will Update or Insert depending on the demand.

So drag and drop a Button object, from the “Standard” toolbox category and place it at the bottom of the DetailsView. Change the button text to “Save” (using the objects properties on the right”.

Now we will need to tell the button when to update and when to insert. Just double click the new button to edit the code behind the button. Because the SqlDataSources use generic SQL statements, all we need to do is call the “UpdateItem” method, or the “InsertItem” method. We can determine which method to call based upon what mode the detailsview is in.

The parameter that goes with these two methods is a boolean parameter that will trigger validation if it is true. Since validation is our next topic on this installment we will set it to false and come back later.

While we are changing code, let’s add a way for our form to toggle back the view when the update is done. Just create a new function using this block:

Protected Sub DetailsView1_IsUpdated(ByVal sender As Object, ByVal e As_
 System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles DetailsView1.ItemUpdated
        MultiView1.SetActiveView(viewDelete)
End Sub

You can browse the Help to see how you handle the ItemUpdated method, but as you can see this method simply toggles back to the view. Let’s save and view in browser. Your page should look something like this:

Click the “Edit” link and you should have something like this:

Whether you click the “Update” link or the “Save” button you should then be back at the View with your new values.

Now let’s create a way to add new entries. We will need to first update our new SqlDataSource with an Insert statement. This will be done the same way as the Update statement, by using the field values in the form as parameters.

Now add a Hyperlink object, from the Standard category of the toolbox, and drag it into the same pane as the Gridview. In the properties window set its text to “New Entry” and its NavigateURL property to “?editCreate=1″.

Lesson Learned: Beware the mixing of fields for different operations on the same page, and using a multi-view object. When we switch between views in the multi-view, or even switching between modes of a DetailsView, .NET holds onto all of the existing values in the fields. The only way to clear the fields is to do a GET on the page as opposed to the way that the buttons work which is a POST. Hence, why I used a hyperlink to create a new entry. If anyone knows of a way to clear the cache (or is it the ViewState?) please post back.

Now add another function to the backend VBcode:

Protected Sub DetailsView1_IsInserted(ByVal sender As Object, ByVal e As_
 System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
        MultiView1.SetActiveView(viewDelete)
End Sub

SideNote:Although it is a good practice to name your functions to show the object and the method, it is the last part of the method declaration that binds this method to the detailsview object. We could have named this function “foo” as long as we appended the “Handles DetailsView1.ItemInserted” at the end and make sure that all the inputs for this method match the ItemInserted method of the detailsview object.

We already created all of the other code to make this work so just save the form and view in browser. You should have a page that looks something like this:

Click “New Entry” and you should have something like this:

Whether you click the Insert link or the save button, you should eventually end up back at the grid view.

Does it make sense? Hopefully yes, but if not just drop a comment and maybe I can make some sense of it for you.

We will finish this installment by adding our DELETE capability. Although the GridView object has the capability to add a default delete function, it appears if you are not using SQL server that option becomes a touch difficult so we will do our delete function the same way we did our Edit function, which is to perform a GET on the page, pull the querystring parameters and delete the appropriate record.

The delete function will occur from the GridView object so click on the little arrow in the upper right corner of the gridview and select “Edit Columns”. We will need to make another HyperLink field with the text property set to “Delete”, the DataNavigateURLFields value set to “id”, and the DataNavigateURLFormatString set to “?delete=1&id={0}”. Just like our edit link, we first declare which fields we need from the bound data source, then we use each field in our URL with the {0},{1},etc.. syntax. When done, make sure you move the new Delete HyperLinkField up the list so it is first or second in the column list. When all finished, your gridview should look something like this:

Next we add the DELETE query to the SQLDataSource that is used by the GridView, which is different than the one used by the detailsview. Right click on the Gridview data source and select properties and change the DeleteQuery to look like this:

When that is done all we need to do is modify that page_load event again. We will add an ElseIf statement that will check for the “delete=1″ in the querystring, and if found will call the Delete command directly from the SQLDataSource. Our command listed in the datasource will look for the querystring “id” parameter for which row to delete. The page_load will look like this:

Now let’s save and view in browser. We should now be able to Add, Edit, and Delete from our single page.

Although somewhat restrictive, the use of the gridview and detailview objects are the quickest and easiest way to show data binding since they both map fields automatically. The next installment we will create a table of supported users and then design seperate pages for viewing and editing. This will allow us ALOT more flexibility in our design and greatly simplify a future topic which is validation. Our next form will require a little more effort to bind data, but because of the extra effort, validation is a snap.

This entry was posted on Monday, January 2nd, 2006 at 10:49 am and is filed under .NET for Domino Devs. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.