DevBlog

Day 3 turned out to be very frustrating. I had to take a step back and fix two problems, something I did Tuesday broke both the add and delete of folders in the tree view. Each of these required a different fix.

Adding records
I'm not sure what I did that broke this, but it was very difficult to find a fix. I was getting an error that one column in the DataSet could not be null when adding a new row. Yes, I know this...it's the PK that's generated by the database. It shouldn't have a value at this point. So, this one problem was really two problems: how to add the row without generating the error and how to get the PK back from the database once it's been updated.

I searched through four different books and did lots of searching on line. Everything I found either showed how to use the drag and drop tools onto the form, ignored PKs, or showed how to do this with a Stored Procedure. None would work for my simple application. So, I did more web searches and finally found the solution here. The link uses OleDb instead of SQLClient, but it was easy to change that.

So, here's what I did. First, I stored a dummy value in the PK column before adding the new row to the DataSet.

DataRow row = dsAllData.Tables["Groups"].NewRow();
row["GroupPK"] = -1;
row["Description"] = folderName;
dsAllData.Tables["Groups"].Rows.Add(row);
daGroups.Update(dsAllData, "Groups"); 

Next, I needed to get the new PK. It turns out that an event is fired when a row is updated. So, first I added the call to the event handler.

daGroups.RowUpdated += new SqlRowUpdatedEventHandler(daGroups_OnRowUpdate); 

Then, add the code for the handler.

static void daGroups_OnRowUpdate(object sender, SqlRowUpdatedEventArgs  e)
{
   if (e.StatementType == StatementType.Insert)
   {
      SqlCommand cmd = new SqlCommand("SELECT @@IDENTITY", e.Command.Connection);
      e.Row["GroupPK"] = cmd.ExecuteScalar();
      e.Row.AcceptChanges();
   }
}

Now, coming from the Fox world, what I had to do sucks. It took me most of the day to track down the solution. Working with data should not be this hard. Why can't .Net automatically identify that the column was the PK and not throw the error? Why can't it automatically retrieve the new PK after the database has been updated? Why can't Microsoft show us the way this should be coded instead of always concentrating on drag and drop? For that matter, why can't the book authors do the same?

Treeview
Now that I've ranted about data access in .Net, I want to gripe about TreeView controls in general. I've wrestled with TreeViews in VFP and looked at LOTS of third party TreeViews. All are downright HARD to use. Why should they be? You need to do a few things, add, and delete a node, know where you clicked and right-clicked, maybe sort and rename. You need ways to store additional information about each node to track it back to some data source. NONE of the TreeViews I've ever looked at made this easy. The TreeView in .Net is no different.

The problem I had with deleting a folder in the TreeView occurred when I changed how I was handling right-click to display the context menu. At first, I was trapping the NodeMouseClick event and using the X, Y coordinates to know where the click occurred, getting the node at that location, and using that information to know what context menu to display (there's a different one depending on what level the node is at). Then, I found a property for each node that would allow you to hook in the ContextMenu. .Net would handle the display of the menu. This sounds great, but the problem is, you can't figure out what node got the click.

So, I went back to the first solution. Here's the code:

private void tvSubscriptions_NodeMouseClick(object sender,
  TreeNodeMouseClickEventArgs e)
{
   if (e.Button == MouseButtons.Right)
   {
      TreeNode currentNode = tvSubscriptions.SelectedNode;
      TreeNode clickedNode = tvSubscriptions.GetNodeAt(e.X, e.Y);
      tvSubscriptions.SelectedNode = clickedNode;
      switch (e.Node.Level)
      {
         case 0:
            tvRootContextMenuStrip.Show(tvSubscriptions, e.X, e.Y);
            break;
         case 1:
            tvNodeContextMenuStrip.Show(tvSubscriptions, e.X, e.Y);
            break;
         case 2:
            tvItemContextMenuStrip.Show(tvSubscriptions, e.X, e.Y);
            break;
      }
   }
}

So, today, I'm back to where I was Tuesday evening. Next step is to be able to add new subscriptions. It will be next week before I can get that done. I'm in meetings most of the day today and off tomorrow to close up the ski season.