I recently whipped together a small tool for a client and because it was so small (and for only one person to use), I used some simple drag n’ drop wizardry for the form. I created a datasource pointing to a table in the SQL Server 2005 database on my develpment machine then dragged that datasource onto a form to create a navigation toolbar and a screenfull of text boxes and checkboxes.

When I finished it up and pointed it to the live SQL Server 2000 database I was having some strange issues with inserts and updates. Specifically, SQL Server was throwing this error: “Error converting data type varchar to bit.”

A little digging showed me that the TSQL being sent to the databse was passing “True” and “False” to the bit fields rather than 0 and 1.

SQL Server 2005 didn’t seem to mind this, but SS2000 sure did!

I looked at the dataset that was created by the wizard and the bit fields were properly identified as booleans.

But when I looked at the parameters collections of the Insert and Update statements that the wizard had built, I could see that the properties of my boolean values was set to DbType=AnsiString.

So even though I prefered to know why I was seeing a difference between SS2005 and SS2000, I modified the auto-generated Insert & Update statements to make the DbType “Boolean” which passed 0’s and 1’s up to the database and everyone was happy. Changint the DbType to Boolean automatically changed the ProviderType to “Bit”. 

I have seen a few threads where people pointed out this problem but never saw any suggest my solution or provide a reason for SS2000 rejecting it. If this wizard is meant to be used by newbies, I don’t know how they would deal with a problem like this.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!