SQL Exception Error C# ASP.Net

Question

It's been awhile since I've messed with anything SQL, and I'm trying to build a little Todo app to learn some ASP.Net with C#. I'm using Visual Studio 2013 with whatever version of SQL Express it comes packaged with, all locally.

I have the following table todo_list, made with the following script, through Visual Studio:

CREATE TABLE [dbo].[todo_list] (
    [id]       INT  NOT NULL,
    [task]     TEXT NOT NULL,
    [complete] BIT  NOT NULL,
    [date]     DATE NOT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
);

When the web application starts, I'm trying to get all of the records where complete is false. I'm assuming I can read/write to the complete column as true/false because of it being of type bit.

I get an exception thrown when the following code goes to execute...

    private void Get_Tasks()
    {
        //Get the connection string
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = System.Configuration.ConfigurationManager.
            ConnectionStrings["Database1ConnectionString"].ConnectionString;

        //Build SQL query
        string query = "SELECT * FROM todo_list WHERE complete=False";
        System.Diagnostics.Debug.WriteLine(query);

        //Build SQL Command Object
        SqlCommand command = new SqlCommand(query, connection);

        //Grab all uncompleted tasks from database
        SqlDataReader cursor;

        try
        {
            using(connection)
            {
                //Open and execute SQL stuffz...
                connection.Open();
                cursor = command.ExecuteReader();

                //Get all the tasks
                while (cursor.Read())
                {
                    //Build the task from record set
                    Todo task = new Todo(
                        (int)cursor["id"], (string)cursor["task"], 
                        (bool)cursor["complete"], (DateTime)cursor["date"]);

                    //Append to DOM
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "alert" + UniqueID, "alert('About to append to DOM!');", true);
                    tasklist.InnerHtml = task.toHtml();
                }

                //Close connection
                connection.Close();
            }
        }
        catch (Exception e)
        {
            System.Diagnostics.Debug.WriteLine(e.ToString());
            connection.Close();
        }

        //TODO - Grab all completed tasks from database

    }

The Exception that is thrown when cursor = command.ExecuteReader(); executes -

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll'

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'False'.

I have no idea why it is taking False as a column name?

Thanks in advance for any help!

Answer 1

Your SQL query is invalid. Did you read the error message?

Have you tried running in in SQL Server Management Studio (assuming your using SQL Server...if not, the interactive tools of choice)?

Your query

select *
from todo_list
where complete = False

is [trying to, anyway] selecting all the rows from the table todo_list where the table's two columns complete and False are equal. Since your table has no column named False, SQL Server's query compiler gives you the obvious error::

Invalid column name 'False'

SQL Server's bit datatype is not a boolean in the C# sense. It's fundamentally a 1-bit integer whose domain is {0,1}. You need to rephrase your query like this:

select *
from todo_list
where complete = 0

The CLR bidirectionally maps SQL Server's bit to a CLR System.Boolean. If the bit column is nullable, any SQL Server null values will be mapped to the sole instance of System.DbNull.

Answer 2

You can change the False for 0 or for 'False'

Example