none
SELECT Statement for fields that are blank?

    Question

  • I thought this was working, but apparently it was not.  I was wondering how I would create a SELECT Statement for values that are blank (Equal to "").  I really could have swore that this was working, but I guess it wasn't.

    Here is my code:

    Code Block

                try
                {
                    string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"\MyDB.accdb;Jet OLEDB:Database Password=MyPassword;";

                    // create an open the connection          
                    OleDbConnection conn = new OleDbConnection(conString);
                    OleDbCommand command = new OleDbCommand();
                    command = conn.CreateCommand();

                    // create the DataSet
                    DataSet ds = new DataSet();

                    // run the query
                    command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;";
                    OleDbDataAdapter adapter = new OleDbDataAdapter();
                    adapter = new OleDbDataAdapter(command);
                    command.Parameters.Add("@P0", OleDbType.VarChar).Value = "";
                    adapter.Fill(ds);

                    // close the connection
                    conn.Close();

                    bindingSource1.DataSource = ds.Tables[0];

                    DataGridView.DataSource = bindingSource1;

                    // set the size of the dataGridView Columns
                    this.DataGridView.Columns[0].Visible = false;
                    this.DataGridView.Columns[1].Width = 234;
                    this.DataGridView.Columns[2].Width = 50;
                    this.DataGridView.Columns[3].Width = 50;
                    this.DataGridView.Columns[4].Width = 75;

                    //Sort on the Title Column
                    DataGridViewColumn sortColumn = DataGridView.Columns[1];
                    ListSortDirection direction;
                    direction = ListSortDirection.Ascending;
                    DataGridView.Sort(sortColumn, direction);

                    //Set the Selected Property of the First Row to False
                    DataGridView.Rows[0].Selected = false;
                }
                catch
                {
                }



    Thank you again for your help.


    Wednesday, January 09, 2008 7:45 PM

Answers

  • Hey C#Newbie01!

     

    Try this where you set the value of your @P0 parameter:

     

    Code Block

    command.Parameters.Add("@P0", OleDbType.VarChar).Value = String.Empty;

     

    Hope this helps you out!

     

    Brian

    Wednesday, January 09, 2008 9:13 PM
  • You may want to check for null fields as well by using "where Tags = @P0 or Tags = NULL".  Text fields may appear empty if they are either null or empty strings, which are semantically different.

     

    Hope that helps,

     

    John

    Thursday, January 10, 2008 9:08 PM
  • This is actually a helpful technique.  We use it at work all the time (not sure where we'd be without it).

    We'll have stored procedures with 20... 25 parameters.  And any of them may be null.  Instead of writing a SP for 'GetOne', 'GetManyBy...', or 'GetAll', we'll write the SELECT as
    Code Block

    SELECT [Field1], [Field2], [FieldN] FROM Table

    WHERE ((@p1 Is Null) OR (@p1=[Field1])) AND ((@p2 Is Null) OR (@p2=[Field2])) AND ((@pN Is Null) OR (@pN=[FieldN]))


    It's a lot better than checking for NULL on each param and then performing nested IF... ELSE blocks.  More CTS-Friendly.

    Jason
    Friday, January 11, 2008 2:22 AM

All replies

  • Hey C#Newbie01!

     

    Try this where you set the value of your @P0 parameter:

     

    Code Block

    command.Parameters.Add("@P0", OleDbType.VarChar).Value = String.Empty;

     

    Hope this helps you out!

     

    Brian

    Wednesday, January 09, 2008 9:13 PM
  • Thank you very much for your response.  I tried that, but that did not work either.  I did see that suggestion in an article when I was searching for a response as well, but I don't know why it wouldn't work.  I also used some code that in another button click finds the items that do NOT equal blank, and that works.  So, how can my SELECT Statment that finds items where the field does NOT equal blank work, while my SELECT Statement that finds items where the field DOES equal blank doesn't work?  It's so strange...  LOL

    Here is the command like for the does not equal (Which works).

    Code Block

    command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags <> @P0;";


    Thank you again for your help.
    Wednesday, January 09, 2008 10:37 PM
  • You may want to check for null fields as well by using "where Tags = @P0 or Tags = NULL".  Text fields may appear empty if they are either null or empty strings, which are semantically different.

     

    Hope that helps,

     

    John

    Thursday, January 10, 2008 9:08 PM
  • This is actually a helpful technique.  We use it at work all the time (not sure where we'd be without it).

    We'll have stored procedures with 20... 25 parameters.  And any of them may be null.  Instead of writing a SP for 'GetOne', 'GetManyBy...', or 'GetAll', we'll write the SELECT as
    Code Block

    SELECT [Field1], [Field2], [FieldN] FROM Table

    WHERE ((@p1 Is Null) OR (@p1=[Field1])) AND ((@p2 Is Null) OR (@p2=[Field2])) AND ((@pN Is Null) OR (@pN=[FieldN]))


    It's a lot better than checking for NULL on each param and then performing nested IF... ELSE blocks.  More CTS-Friendly.

    Jason
    Friday, January 11, 2008 2:22 AM
  • Thank you all very much for your help.  This worked perfectly.

    Thank you again for your responses.  I really appreciate the help.  Big Smile
    Friday, January 11, 2008 7:45 AM