Sorting sub-directories using IComparer in Visual Basic .NET

First we create an IComparer class:

Imports System.IO

Public Class DirectoryCompare
    Implements IComparer

    Public Overridable Overloads Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
        Dim objX As DirectoryInfo = CType(x, DirectoryInfo)
        Dim objY As DirectoryInfo = CType(y, DirectoryInfo)
        Return objX.Name.CompareTo(objY.Name)
    End Function

End Class

And some code showing how to use it:

                    Dim dArray() = New DirectoryInfo("C:\\Results").GetDirectories

                    If Not dArray Is Nothing Then

                        Dim dc As New DirectoryCompare()
                        Dim dirs As ArrayList = New ArrayList(dArray)
                        dirs.Sort(dc)

                        dim lastDirPath as String = CType(dirs(dArray.Length - 1), DirectoryInfo).FullName

                        'step through
                        For Each dir As DirectoryInfo In dArray

                        Next

                        'step backwards
                        For idir As Integer = dArray.Length - 1 To 0 Step -1

                            Dim dir As DirectoryInfo = CType(dirs(idir), DirectoryInfo)
                        Next

                    End If

Table Adapters invalid and cannot be generated after altering ODBC Firebird database

I may have generated a unique cause for this error, but it's a scary moment when all your ODBC table adapter queries disappear so I'll document the experience.

This Visual Basic.Net (2008) project started out with an Access database for convenience, and I prefer mixed case table and field names for legibility.

About 10 days ago I switched to a Firebird database, and it was pretty cool that the table adapters worked with Firebird after minor edits (e.g. remove the back-quotes on field names, handle missing native boolean type in Firebird)

But, today after adding some columns in the Firebird database all my table adapters disappeared. The error is briefly summarized as "Failed to generate code. No mapping exists from DbType Object to a known OdbcType" The IDE showed "unknown type" critical errors on every reference in code.

This error would occur even though I could hit Execute Query in the Table Adapter designer and see valid data appear.

After some thrashing and a restore of all the relevant Dataset files from backup I figured out the error was rooted in case-sensitivity. One of the table adapters could no longer reference it's underlying table, and that meant the code for all the table adapters in the class could not be generated. Interestingly, in dataset design view the obviously broken adapter was *not* on the table with the new columns.

There appeared to be problems in the <Mappings> for the newly altered table. I saw both mixed-case to mixed-case and upper-case to upper-case mappings, but no mixed-case to upper-case mappings. In addition, the column types were declared differently in mixed case from upper case, probably because they were based on Access when first generated, and Firebird when regenerated.

This was going to be a problem because all the queries were written in mixed case, but Firebird stores all field and table names in upper case (unless you quote each field and table name, which I figured is only going to cause problems later), so there was no possible way to map the text in the commands and field definitions to the Firebird interface.

After converting queries and fields on the broken table adapter to upper case, and eliminating conflicting mapping statements and using all upper case in the newly altered table, the table adapters all work again. For safety I converted all the other table adapters to upper case as well.

It looked like the behind-the-scenes work to incorporate the newly added columns had a problem with the mixed case of the fields. All the columns of the table were there in upper case

One symptom you can look for is the field list in the table adapter disappears, all that's left is a list of queries. A second symptom which I missed because the relevant table has 30+ fields is that field names are listed twice, once in mixed case and once in all upper.

Another symptom is after doing Configure on any query in the table adapter you get the "unable to convert object to a known ODBC type" error when you hit Finish.

Since the case and field type issues were a result of moving from Access to Firebird I'm not sure how many people are going to run into this. Bottom line, if you think your going to adopt Firebird later then declare everything in upper case to start with or prepare to comb your code for case-dependencies.

Case-insensitive sorting of Firebird field in a Visual Basic web application

I wanted to sort elements in a ListBox alphabetically without regard to case. However, Firebird does a case-sensitive sort, and I couldn't get it to recognize UPPER() in the SELECT statement.

One solutions is to add the rows in the table adapter data table to an array of elements of a custom class. The custom class implements IComparable, so when you do Array.sort you can get the case-insensitive order desired. Here's my custom class:

Public Class trendpoint
    Implements IComparable

    Public name As String
    Public description As String

    Public Sub New(ByVal name As String, ByVal description As String)
        Me.name = name
        Me.description = description
    End Sub

    Public Function CompareTo(ByVal obj As Object) As Integer Implements System.IComparable.CompareTo

        Dim comparepoint As trendpoint = CType(obj, trendpoint)

        If Me.name.ToUpper < comparepoint.name.ToUpper Then
            Return -1
        ElseIf Me.name.ToUpper > comparepoint.name.ToUpper Then
            Return 1
        Else
            Return 0
        End If

    End Function

End Class

Enable double click event on a ListBox in a vb.net web application

There's a nice concise C# example of supporting double click event on a listbox control in an asp.net web application, here is the equivalent code (for the page load event) in Visual Basic.

If (Not Request.Item("__EVENTARGUMENT") Is Nothing And Request.Item("__EVENTARGUMENT") = "move") Then

Dim idx As Integer = ListBox1.SelectedIndex
Dim item As ListItem = ListBox1.SelectedItem
ListBox1.Items.Remove(item)
ListBox2.SelectedIndex = -1
ListBox2.Items.Add(item)

End If

ListBox1.Attributes.Add("ondblclick", ClientScript.GetPostBackEventReference(ListBox1, "move"))

Connecting to a Firebird database from a VB.net 2008 web application

There's a couple of posts related to connecting to a Firebird database from an asp.net 2008 web application that are helpful but not complete.

First, you establish a DSN connection.

When adding the DSN connection the prompts were a little different on my Vista machine than the post describes. First, I picked the Database source

Then, after clicking New Connection button, click Change button on Data Source to switch from SQL Server to ODBC driver

Then, pick the Microsoft ODBC Data Source option

Once this is done when you add a new DSN connection the Firebird/Interbase drivers will be selectable.

During the configuration of the Firebird DSN connection I ran into one other change from the earlier post, rather than browse to the database on the local machine I used the localhost: connection method (e.g. Database = "localhost:c:\program files\ ... \_WEBLINK.FDB")

The Database string is quoted on both ends, in the picture some of the connection string is whited out for (excessive) security.

Now you can follow the second post to establish a data connection

Registry key for IIS subauthenticator is not configured correctly

Installed a Visual Basic web app on a Windows Server 2003 box with IIS 6 today and got some strange intermittent symptoms. The customer could login about 2 times out of 3, and could access some second-level pages intermittently, but other second-level pages would fail immediately. Failures took the user back to the login page.

The Event Viewer showed an error each time the user got bumped back to the login page, saying "Registry Key for IIS subauthenticator is not configured correctly"

I found the Microsoft KB article with instructions to enable IIS management of the anonymous user password. After applying the 3 steps the website works perfectly.

Took a while to resolve because I was baffled by the intermittent nature of the error, but once the correlation to the Event Viewer error was observed it was quick.

Insert multiple records to Microsoft Access database

Did not find a simple answer as to how to insert multiple rows of raw data into an Access database using an Insert Into SQL statement, this post is one of many to point out that VALUES is for single-row inserts, and you need to use a SELECT to insert more than 1 row - which means your values have to already be in the database.

I don't believe there's a straightforward way to do it with a single SQL statement, but you can use free MyOLEDBExpress database editing tool to accomplish it by cutting/pasting your columns of data from Excel spreadsheet or other source directly into a table. If you are combining data with values already in the database you can use a SELECT clause with the INSERT INTO statement to extract the insert values from tables.

It may be worth the effort if you have hundreds of rows of data or more. Here's the Insert statement after the temp table has been created.

INSERT INTO the_Access_Table (field1, field2) SELECT col1, col2 FROM temp_Table INNER JOIN other_Table ....

VB.net Opening Connection to SQLServer

Was unable to connect from Visual Basic .NET 2008 to a SQL Server Express 2008 database using the online examples. I was getting the error "network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible"

Changing from Data Source=(local) to Data Source=.\SQLEXPRESS fixed the issue

Full example:

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim dr As SqlDataReader

myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;User ID=youruser;Password=yourpassword;Initial Catalog=yourdatabase")

Try
myConnection.Open()

myCommand = New SqlCommand("Select * from tblRoom", myConnection)

dr = myCommand.ExecuteReader()

While dr.Read()

MessageBox.Show("Field1 " & dr(0).ToString() & " Field2 " & dr(1).ToString())

End While
dr.Close()
myConnection.Close()

Catch e As Exception
'handle errors
End Try