Archive for October, 2010

Getting the DataKey of a GridView bound inside a DataList

You know you nest your GridViews inside your DataLists. Everybody is doing it, and peer pressure is something you are going to give in to. I know that when my friends started doing it, I couldn’t resist. But once you start doing that sort of thing, then you have to do things like get your DataKey values from nested GridView, and that is no longer as simple as it used to be.

First, a bit of HTML to show exactly what I’m doing, with the standard disclaimer that I took a bunch of “highly relevant to me but not to thee” items:

     <asp:DataList ID="lstTest" DataKeyField="ID" DataSourceID="OC" OnItemCommand="lstTest_ItemCommand" runat="server">
        <ItemTemplate>
            <asp:Panel ID="pnlPanel" runat="server">
                <asp:Label ID="lblLocationName" Text='<%#Eval("Name")%>' runat="server" />
                <asp:Label ID="lblLocOwner" Text='<%#Eval("OwnerName")%>' runat="server" />
                <asp:GridView ID="grdDetails" OnRowCommand="grdDetails_OnCommand" DataKeyNames="ID"  runat="server">
                    <Columns>
                        <asp:BoundField DataField="UserName"  />
                        <asp:ButtonField CommandName="IgnoreRequest" Text="Ignore Request" />
                        <asp:ButtonField CommandName="IgnoreUser" Text="Ignore User" />
                    </Columns>
                </asp:GridView>
            </asp:Panel>
        </ItemTemplate>
    </asp:DataList>

 So each item in the DataList has its own GridView assigned to it. Beautiful. But when I click on the Ignore button inside the DataGrid, I can’t just say grdDetails.DataKeys[grdDetails.SelectedIndex].Value. Why? Because grdDetails doesn’t exist at the page level, only in the ItemTemplate of the DataList. Thus, type grdDetails. and expecting your methods, properties, and events goodness will bring bitter tears to your face. But not for long.

 Let’s do some .NET-foo on it:

     protected void grdDetails_OnCommand(object sender, GridViewCommandEventArgs e)
    {
        GridView gv = (GridView)sender;
        int Index = Convert.ToInt32(e.CommandArgument);
        if (e.CommandName == "IgnoreRequest")
           DataClass.IgnoreRequest(Convert.ToInt64(gv.DataKeys[Index].Value));
    }

So I’m first casting the sender as a GridView, because that’s what the sender is. Since the GridViewCommandEventArgs .CommandArgument is actually the index of the GridView row (it’s tricky like that), I can then use that index an pull the value from the GridView’s DataKeys collection. Bingo boingo done.

Leave a comment

Notifying users when a long running process is ready

I was forced to delve into the world of Win32 coding to solve an issue of calling the SaveFileDialog method. I have a long-running WPF application that I wanted to get the user’s attention when it was finally ready for some love (i.e. the Save File dialog box appeared). I was having an issue where the app would quietly sit there until the user decided to check it – not the kind of passive relationship I was looking for. I needed to spice it up.

Luckily, I found the solution to shy application.

First, add a few DllImport statements to delve into the user32.dll file:

    <Runtime.InteropServices.DllImport("user32.dll")> _
    Private Shared Function ShowWindow(ByVal hwnd As Integer, ByVal nCmdShow As Integer) As Integer
    End Function
    <Runtime.InteropServices.DllImport("user32.dll")> _
    Private Shared Function SetForegroundWindow(ByVal hwnd As Integer) As Integer
    End Function
    <Runtime.InteropServices.DllImport("user32.dll")> _
    Private Shared Function IsIconic(ByVal hWnd As Integer) As Integer
    End Function

The meat of the code is this For Each loop. The only modification you will have to make is to change the MainWindowTitle to match the result of the window you want to give focus to:

        'Give focus to the dialog box when it is ready
        For Each p As Process In Process.GetProcesses
            If p.MainWindowTitle = "Results" Then
                If IsIconic(p.MainWindowHandle.ToInt32) <> 0 Then
                    ShowWindow(p.MainWindowHandle.ToInt32, &H1)
                Else
                    SetForegroundWindow(p.MainWindowHandle.ToInt32)
                End If
            End If
        Next

If you are interested in the internals of the calls, you can check MSDN for the IsIconic, ShowWindow and SetForegroundWindow functions.

Leave a comment

Deleting Excel worksheets with VB.NET and LINQ

I never claim that the way I do it is the best way…

I created code to insert a worksheet into Excel for each user in the query. But the focus of this post is deleting those default sheets that Excel starts with, Sheet1, Sheet2, and Sheet3. When I tried to delete them at the beginning of my code, I was graciously given this friendly COMException:

A workbook must contain at least one visible worksheet.

No worries, I’ll just do it at the end of the process. I came up with two ways. First, the easy, so un-cool way to do it: Just count backwards from the worksheet count:

Dim bkWorkBook As Excel.Workbook = DirectCast(oXL.Workbooks.Add(), Excel.Workbook)
Dim shWorkSheet As Excel.Worksheet = DirectCast(bkWorkBook.Sheets("Sheet1"), Excel.Worksheet)
'Code to populate the worksheets
Dim Counter As Integer = bkWorkBook.Sheets.Count
CType(bkWorkBook.Sheets(Counter), Excel.Worksheet).Delete()
CType(bkWorkBook.Sheets(Counter - 1), Excel.Worksheet).Delete()
CType(bkWorkBook.Sheets(Counter - 2), Excel.Worksheet).Delete()

Oh, that’s too easy, though. Isn’t there some way I could use some cool LINQ extension method to do this? You betcha. This is the round-about way I came up with using OrderByDescending:

Dim SheetName As String = String.Empty
Dim SheetIds As New List(Of Integer)
For i As Integer = 1 To bkWorkBook.Sheets.Count
            SheetName = CType(bkWorkBook.Sheets(i), Excel.Worksheet).Name
            If SheetName = "Sheet1" OrElse SheetName = "Sheet2" OrElse SheetName = "Sheet3" Then
                SheetIds.Add(i)
            End If
Next
For Each Counter As Integer In SheetIds.OrderByDescending(Function(i As Integer) i)
            CType(bkWorkBook.Sheets(Counter), Excel.Worksheet).Delete()
Next

By the way, I used the first, much simpler, method.

Leave a comment

Yes, that 65,536 rows maximum in Excel is true

I was exporting into Excel a list of users and the files they “owned” in a selected directory when I ran into an error at row 65,537 in the Excel sheet.

Dim shWorkSheet As Excel.Worksheet = DirectCast(bkWorkBook.Sheets("Sheet1"), Excel.Worksheet)
...
For Each File As System.IO.FileInfo In FileCollection(i - 1).FileList
        shWorkSheet.Cells(RowCounter, 2).Value = File.FullName
        shWorkSheet.Cells(RowCounter, 3).Value = File.Length
        RowCounter += 1
Next

So 65,536 was the last row successfully added to the Excel sheet. That number sounded familiar, so I diligently checked and found an MSDN article on Excel. And there, plain as day, was:

A Microsoft Office Excel worksheet contains 65,536 rows and 256 columns.

So I guess I’ll be splitting my records between different sheets. At least it was an easy error to diagnose.

2 Comments

Return an ID from a LinkButton

I needed to pass the ID of a record that was bound to an ASP.NET LinkButton to the code behind page. Though there are other ways to do it, this is the way that worked for me. First, the GridView control that held the LinkButton, having removed other controls for clarity:

 <asp:GridView ID="grdLocations" DataKeyNames="ID" runat="server">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="lnkViewLocation" CommandArgument='<%#Bind("ID")%>' OnClick="ViewLocation_Click" Text='<%#Bind("Name")%>' runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

 Secondly, the code behind. I just casted the sender object as a LinkButton and was able to get the CommandArgument from the LinkButton:

 protected void ViewLocation_Click(object sender, EventArgs e)
{
LinkButton MyButton = (LinkButton)sender;
Int64 LocationId = Convert.ToInt64(MyButton.CommandArgument.ToString());
}

Leave a comment

XAML error – System.Windows.Baml2006.TypeConverterMarkupExtension

Notice, if you will, this itty bitty block of code:

<ListBox Name="FilesList" ItemsSource="{Binding FileList}" Width="100%">
 <ListBox.ItemTemplate>
  <DataTemplate>
   <TextBlock Text="{Binding}" />
  </DataTemplate>
 </ListBox.ItemTemplate>
</ListBox>

While attempting to databind a custom collection class to a ListBox, I encountered an oddly named exception:

XamlParseException occurred
Provide value on 'System.Windows.Baml2006.TypeConverterMarkupExtension' threw an exception.

What the heck is Baml2006?

Anyway, here is the offending line:

<ListBox Name="FilesList" ItemsSource="{Binding FileList}" Width="100%">

Guess which part is the problem? The ‘Width=”100%”‘ part. When I changed it to read ‘Width=”350″‘, the error went away. No worries, but a more helpful error message would have been nice.

I’ve also received the “BAML” error where there are other invalid characters in a property value. So don’t put those invalid characters in property fields, kids – that’s bad.

1 Comment

Using Sum operator in LINQ

I needed to populate a list of record with file names along with how large the files were and who owned the file. Then, I needed to find how much space a particular user was taking up on the shared drive. The user would then be tarred and feathered, but this was nothing my code was going to have to deal with.

So I have a class of three properties, though I’ve only included the two relevant ones. I also declare a List(Of Results) to store my wonderfully awesome data:

Public Class Results
    Private _fileSize As Int64
    Public Property FileSize() As Int64
        Get
            Return _fileSize
        End Get
        Set(ByVal value As Int64)
            _fileSize = value
        End Set
    End Property
    Private _userName As String
    Public Property UserName() As String
        Get
            Return _userName
        End Get
        Set(ByVal value As String)
            _userName = value
        End Set
    End Property
 ... Lots of other code omitted
End Class
Dim ResultsFile As List(Of Results)

The details of how I populate the ResultsFile variable aren’t important, but here is the LINQ statement I use to total the file sizes:

Dim TotalFileSize As Int64 = Aggregate Result In ResultsFile Where Result.UserName = UserName Into Sum(Result.FileSize)

Simple and sweet.

Leave a comment