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!

Juan · Accepted Answer · 2013-12-10 01:16:28Z

4

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

Example

edited Dec 10, 2013 at 1:16

answered Dec 10, 2013 at 1:11

Juan

1,3821 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicholas Carey · Accepted Answer · 2013-12-10 01:26:46Z

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.

Collectives™ on Stack Overflow

SQL Exception Error C# ASP.Net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related