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

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 2

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 3

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

so use:

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

Answer 4

Generic code

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

Answer 5

Use explicit NULL evaluation like below:

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

Answer 6

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.