Archive for category VB.NET

TypeInitializationException using Zoom.Net.YazSharp.Connection object

I have done work with the YazSharp.dll, which allows you, through .NET, to connect to servers using the Z39.50 protocol. While debugging, I was seeing an exception when I initialized my Zoom Connection object:

Dim conn As New Zoom.Net.YazSharp.Connection(server, port)

 This error was throwing a TypeInitializationException:

 System.TypeInitializationException: The type initializer for ‘Zoom.Net.YazSharp.Connection’ threw an exception. —> System.DllNotFoundException: Unable to load DLL ‘yaz’: The specified module could not be found

 The solution to this is not only add references to the Zoom.Net.dll and Zoom.Net.YazSharp.dll files, but to add all the other relevant files from the Release folder into the bin directory of your project. In my case, I had forgotten to put the Zoom.Net.Factory.config file in the folder. Once I had put that file in along with all the *.dll files, it works. Jai himself mentions that in the comment of his blog post, but I thought I would reinforce that this will solve the issue.

Leave a comment

Comparing rows from disparate tables in VB.NET and SSRS

I love using the word “disparate”, by the way.

I needed to compare two fields returned from a SQL query to determine which of the two fields occurred later. The fields were text, and were in the format “Consulting Services – 6/3/2013”. I had two different tables that this data was being returned from, Products and Services. (Yes, the names have been changed to protect the innocent.) And I needed to show this data in a SSRS report.

Here is the VB.NET function I used inside Reporting Services to compare the dates of these two fields. I have added plenty of comments, because everyone loves over-commented code:

Public Function GetLatestProductOrService(ByVal Product As String, ByVal Service As String) As String

        ‘ In SSRS, if the fields are empty, they are sent as Nothing.
If Product Is Nothing Then Product = String.Empty
If Service Is Nothing Then Service = String.Empty

        ‘ Get the space right before the date portion of the string
Dim ProductSpace As Integer = Product.LastIndexOf(” “)
Dim ServiceSpace As Integer = Service.LastIndexOf(” “)

       ‘ If neither products nor services were returned, then don’t display anything
If ProductSpace < 0 AndAlso ServiceSpace < 0 Then
Return String.Empty
End If

       ‘ If there is a value for the product, strip out the date from the string. If there was no service passed to the function,

       ‘no need to go further, so return the product
Dim ProductDate As String = String.Empty
If ProductSpace > -1 Then
ProductDate = Product.Substring(ProductSpace, Product.Length – ProductSpace)
If ServiceSpace < 0 Then
Return Product
End If
End If

       ‘ Like above, just for services
Dim ServiceDate As String = String.Empty
If ServiceSpace > -1 Then
ServiceDate = Service.Substring(ServiceSpace, Service.Length – ServiceSpace)
If ProductSpace < 0 Then
Return Service
End If
End If

Dim LastUpdateDate As String = String.Empty

       ‘ If both products and services were passed to the function, see which was processed last
If CDate(ProductDate) > CDate(ServiceDate) Then
LastUpdateDate = Product
Else
LastUpdateDate = Service
End If

Return LastUpdateDate

End Function

Calling this beautiful function through VB.NET code is simple:

Dim Result As String = GetLatestProductOrService(Nothing, “Widget – 06/17/2013”)

Calling it in Reporting Services is not much harder. I just added an expression to the TextBox where I wanted the text to appear:

=Code.GetLatestProductOrService(Fields!LastProduct.Value, Fields!LastService.Value)

 

,

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

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

DataTable error – “This row already belongs to another table”

I was attempting to add a DataRow from an existing DataTable into a new DataTable. 

                            Dim TempResultsFile As New DataSet
                            TempResultsFile.ReadXml(“C:\MyFile.xml”)

                           For Each Row As DataRow In TempResultsFile.Tables(0).Rows
                                   ‘Snip excess code
                                    OeaaResults.Rows.Add(Row)
                            Next

At the OeaaResults.Rows.Add(Row) line, I received the message “This row already belongs to another table”. Well duh, of course it belongs to another table. What’s your point?

But that kind of brilliant arguing did not solve my problem. Instead, I found http://www.geekzilla.co.uk/View37EB5230-5B79-4D00-800C-52D7A46CFB15.htm which did solve my problem. In the article, Paul says to use the DataTable.Import method instead of DataTable.Add. So I changed

OeaaResults.Rows.Add(Row)

to

OeaaResults.ImportRow(Row)

and the error was resolved. Joy and happiness abound.

Leave a comment