Archive for category VB.NET
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.
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
‘ 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
‘ 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
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
LastUpdateDate = Service
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:
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.
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.
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.
I was attempting to add a DataRow from an existing DataTable into a new DataTable.
Dim TempResultsFile As New DataSet
For Each Row As DataRow In TempResultsFile.Tables(0).Rows
‘Snip excess code
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
and the error was resolved. Joy and happiness abound.