How to dynamically switch between "WHERE column-name = value" and "WHERE column-name IS NOT NULL"
<p>I am creating a small Visual Studio Windows Form application in C#, and I am using SQLite as my database. Suppose the db stores data for school club members, and they have a value for the state they live in. I am trying to create a parameterized query string so that if the user selects "All States" in my program, then it will load all members. But if they select a specific state, then it will only show members living in that state. How can I dynamically switch between, for example, <code>WHERE USstate = 'FL'</code> and <code>WHERE USstate IS NOT NULL</code> when using a parameterized query string? I want the value of the state to be dynamically chosen, but if "All States" (a check box) is selected, then all records in the db should show up. </p>

<p>Code example of what is not working for me:</p>

<pre><code>string queryString = @"Select * from Membership WHERE USstate = @state;";
List&lt;SQLiteParameter&gt; paramList = new List&lt;SQLiteParameter&gt;();
paramList = addStateParam(paramList);

private List&lt;SQLiteParameter&gt; addStateParam(List&lt;SQLiteParameter&gt; paramList)
    {
        if (!checkBoxFilterAllStates.Checked)
        {
            string selectedState = comboBoxFilterState.Items[comboBoxFilterState.SelectedIndex].ToString();
            var usState = new SQLiteParameter("@state", selectedState);
            paramList.Add(usState);
        }
        else
        {
            var usState = new SQLiteParameter("@state", "IS NOT NULL");
            paramList.Add(usState);
        }
        return paramList;
    }
</code></pre>

<p>I tried changing <code>USstate = @state</code> to just <code>USstate @state</code> and then having <code>@state</code> replaced with either <code>"= " + selectedState</code> or <code>IS NOT NULL</code>, but if there is no equal sign before the <code>@state</code>, an syntax or logic error appears every time. However, if I do keep the equals sign, then the <code>IS NOT NUll</code> code will not work. </p>

<p>How do I dynamically fix this issue? Thanks in advance! And sorry if I was too wordy or redundant. </p>