locked
What is the best way to convert 'boolean' to 'bit' when migrating from MS Access DB to a SQL Serve 2008 DB? RRS feed

  • Question

  • I had an MS Access database that I have been using with an ASP.NET 3.5 application for a very long time - I decided to migrate the DB to SQL Server.. Now the migration went well with all the structure and existing data... But the problem is that in my VB.NET code which handles the old MS Access DB I always used 'boolean' as a data type for columns, the problem is that when migrated, SQL Server changed all columns that had the 'boolean' data type (such as Yes/No, true/false) to bit (1/0)... I now get this error when running the application (the connection string is correct to reflect the SQL server DB instead of the Access one):

     

    Invalid column name 'true'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'true'.

     

    Any ideas?


    Junior Software Developer
    Sunday, January 23, 2011 9:38 PM

Answers

  •  

    There weren't any problems with the query prior to migrating to SQL Server (the code has been working for over a year with little problems with SQL)...

     

    I did change my VB code to expect 0 for 'false' and 1 for 'true' and it worked nicely... sort of.


    Junior Software Developer
    • Marked as answer by WeiLin Qiao Wednesday, February 9, 2011 5:11 AM
    Monday, January 24, 2011 2:40 AM

All replies

  • Could you please check the query being sent to SQL Server when this error is raised? I believe the query is using true without quotes.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, January 24, 2011 2:26 AM
  •  

    There weren't any problems with the query prior to migrating to SQL Server (the code has been working for over a year with little problems with SQL)...

     

    I did change my VB code to expect 0 for 'false' and 1 for 'true' and it worked nicely... sort of.


    Junior Software Developer
    • Marked as answer by WeiLin Qiao Wednesday, February 9, 2011 5:11 AM
    Monday, January 24, 2011 2:40 AM
  • CREATE TABLE #t (c BIT)
    INSERT INTO #t VALUES ('true')
    SELECT * FROM #t
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 24, 2011 6:16 AM