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