SQL Parameter for ALL

Question

I was wondering if there was a parameter in SQL for all (not *!) For example, I'm writing a search table now, and if the user does not input something in the text box, it would mean to ignore that specific parameter and display ALL of them for that field. I understand you could make separate OLEDB or SQL commands for each scenario and it would work, but I would just like to do it in one command where if the textbox is empty, I would just ignore it. So far, what this guy said I tried but didn't work... it said I had some type mismatch

http://timothychenallen.blogspot.com/2007/06/sql-server-all-values-parameters-in.html

This is my code for this portion right now

da.SelectCommand = new OleDbCommand("SELECT *
                                       FROM TestQuery
                                      WHERE (VendorName = @VendorName) 
                                        AND CustomerName = @CustomerName", cs);

if (combo_VendorView.Text != "") 
  da.SelectCommand.Parameters.Add("@VendorName", OleDbType.VarChar).Value = combo_VendorView.Text.ToString();
  da.SelectCommand.Parameters.Add("@CustomerName", OleDbType.VarChar).Value = combo_CustomerView.Text.ToString();

  dsB.Clear();
  da.Fill(dsB);
  dgv_DataLookup.DataSource = dsB.Tables[0];

Say if I leave txt.VendorName blank, I want to basically ignore that parameter. Thanks for your help! :)

UPDATED CODE

 da.SelectCommand = new OleDbCommand("SELECT * 
                                        FROM TestQuery 
                                       WHERE (CustomerName = @CustomerName 
                                           OR @CustomerName IS NULL)", cs);
da.SelectCommand.Parameters.Add("@CustomerName", OleDbType.VarChar).Value = combo_CustomerView.Text.ToString();

i'm using ado.net visual studio 2010 if that makes a difference with oledb (access) it does fine searching with parameters but when i do not put the customer name in, it shows only the names of the columns of test query but no information... i want it to basiclly be like select * for this one column

Answer 1

Is it possible @CustomerName is not equal to NULL but to "" (an empty string)?

Answer 2

You can use the COALESCE operator in this example. If your parameter was passed in as null, or you put in logic to convert an empty string to null, you could essentially do this:

SELECT *
FROM TestQuery
WHERE 
   VendorName = COALESCE(@VendorName,VendorName)
   AND CustomerName = COALESCE(@CustomerName, CustomerName)

If VendorName was NULL, it would simply check if VendorName was equal to VendorName, which would always be true.

Answer 3

If the combo box is not selected, why bother with the parameter in your SQL statement's where clause at all? You already have the logic on the Vendor combo box.

Answer 4

I don't do much with .NET, but I assume that combo_CustomerView.Text.ToString() returns the string "Null" or the empty string ("") when the combo box is empty. What you really want to do there is assign the NULL literal. In pseudocode:

If combo_box is empty then
    ...Parameters.Add().Value = Null
Else
    ...Parameters.Add().Value = combo_box.ToString()
End If

Answer 5

The problem is that you're never setting @CustomerName to null. In your code, if the user selects the empty entry in the ComboBox, the @CustomerName parameter will have an empty string as its value.

You need something like this:

if(string.IsNullOrEmpty(combo_CustomerView.Text))
{
    da.SelectCommand.Parameters.Add("@CustomerName", OleDbType.VarChar).Value = DBNull.Value;
}
else
{
    da.SelectCommand.Parameters.Add("@CustomerName", OleDbType.VarChar).Value = combo_CustomerView.Text;
}

Incidentally, please don't call .ToString() on variables that are strings. It's akin to comparing boolean variables to true (or false).