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

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 2

Use explicit NULL evaluation like below:

WHERE (Vendorname = @Vendorname OR @Vendorname IS NULL)
AND (CustomerName = @Customername OR @Customername IS NULL)

Answer 3

you probably need a variation on JNK's answer as I think your user input is probably coming through as empty string rather than NULL eg

WHERE (Vendorname = @Vendorname OR @Vendorname = '')
AND (CustomerName = @Customername OR @Customername = '')

This may of course vary from field to field.

Answer 4

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

Answer 5

combo_CustomerView.Text.ToString()  // is converting null to ''   

so use:

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

Answer 6

Generic code

where (col1=@param1 or @param1 is null) and (col2=@param2 or @param2 is null) and...

Answer 7

da.SelectCommand = new OleDbCommand("SELECT * FROM TestQuery WHERE (@VendorName is null or VendorName = @VendorName) AND (@CustomerName is null or CustomerName = @CustomerName)", cs);
da.SelectCommand.Parameters.Add("@VendorName", OleDbType.VarChar).Value = string.isNullOrEmpty(combo_VendorView.Text) ? null : combo_VendorView.Text.ToString();
da.SelectCommand.Parameters.Add("@CustomerName", OleDbType.VarChar).Value = string.IsNullOrEmpty(combo_CustomerView.Text) ? null : combo_CustomerView.Text.ToString();
dsB.Clear();                
da.Fill(dsB);
dgv_DataLookup.DataSource = dsB.Tables[0];

Answer 8

well, maybe it is not better but it is close

set @VendorName = coalesce(@VendorName,'%')
set @CustomerName = coalesce(@CustomerName,'%')

SELECT * FROM TestQuery 
WHERE VendorName like @VendorName
AND CustomerName like @CustomerName

Now if you send it query (new OleDbCommand("SELECT * FROM TestQuery WHERE (VendorName = @VendorName) AND CustomerName = @CustomerName", cs)) why do you build dynamic the query?

Here an good article about that http://weblogs.sqlteam.com/jeffs/archive/2007/09/18/sql-conditional-where-clauses.aspx

Answer 9

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 10

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).

Answer 11

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.