Add IF THEN clause to SELECT statement

[Guest]

I have a SQL 2000 table named IMSRejects with the fields:
IMSRejects.ProductDescription
IMSRejects.VendorName
IMSRejects.CatalogNumber
IMSRejects.ProductIMS2Desc
IMSRejects.CMA8Desc
Many of the ProductIMS2Desc and CMA8Desc values are Null. I have an ASP.NET
2.0 web page which populates a datagrid with this data. I also have text
boxes on my web page so users can filter the datagrid based on keywords. The
problem I'm having is that the SELECT statement that populates the datagrid
does no handle null values for ProductIMS2Desc and CMA8Desc. Here's the
statement:

Dim _SqlAccounts As String = "Select * FROM IMSRejects WHERE " & _
"IMSRejects.ProductDescription LIKE '%" & strProductName & "%' AND "
& _
"IMSRejects.VendorName LIKE '%" & strVendorName & "%' AND " & _
"IMSRejects.CatalogNumber LIKE '%" & strCatalogName & "%' AND " & _
"IMSRejects.ProductIMS2Desc LIKE '%" & strFranchiseName & "%' AND "
& _
"IMSRejects.CMA8Desc LIKE '%" & strProductLineName & "%' " & _
"ORDER BY VendorName ASC, ProductIMS2Desc ASC, CMA8Desc ASC"

I'd like to default to True a checkbox on my web page named cbShowAll and
add an IF clause that will show all records including those where
ProductIMS2Desc and CMA8Desc contain NULL values. When the checkbox is FALSE,
I'd like the grid to only show records ProductIMS2Desc and CMA8Desc don't
have NULL values.

THANKS!!

 

[Cowboy \(Gregory A. Beamer\)]

Set up the query as a stored procedure rather than write it on the fly. You
will find that it is much easier to control the paths by sending a single
boolean than building a statement blindly.

 

[Guest]

Mike,

As Cowboy suggested, I also think you should use a stored proc for this.

To address your NULL value problems; If you have the option, make the
database columns ProductIMS2Desc and CMA8Desc not nullable. Use empty
strings ('') instead of NULLs.

Sometimes this not always possible, so the second thing you could do is to
use the t-sql coalesce() or isnull() functions to help handle the database
NULL values.

Your where clause would be something like this:
....
coalesce(IMSRejects.ProductIMS2Desc, '') LIKE '%yabayaba%'
....

or if you used the isnull() function
.....
isnull(IMSRejects.ProductIMS2Desc, '') LIKE '%yabayaba%'
.....

Your VB code should look something like the code below. I've used a
StringBuilder to help with performance and readability on the string
contatenations. I'd recommend using SelectParameters to help prevent sql
injection attacks (see
http://www.codeproject.com/cs/database/ParameterizingAdHocSQL.asp).
Also the _SqlSource variable is your SqlDataSource object that your GridView
is bound to.

Hope this helps,
Jason Vermillion

Dim _GenSql As StringBuilder = New StringBuilder(256)
Dim strProductName As String = ""
Dim strVendorName As String = ""
Dim strCatalogName As String = ""
Dim strFranchiseName As String = ""
Dim strProductLineName As String = ""
Dim _SqlAccounts As String = ""

strProductName = "%" & strProductName & "%"
strVendorName = "%" & strVendorName & "%"
strCatalogName = "%" & strCatalogName & "%"
strFranchiseName = "%" & strFranchiseName & "%"
strProductLineName = "%" & strProductLineName & "%"

_GenSql.AppendLine("Select * FROM IMSRejects WHERE")
_GenSql.AppendLine("IMSRejects.ProductDescription LIKE @ProductName
AND")
_GenSql.AppendLine("IMSRejects.VendorName LIKE @VendorName AND")
_GenSql.AppendLine("IMSRejects.CatalogNumber LIKE @CatalogName AND")

' Use the t-sql Coalesce() or isnull() function to
If (Me.cbShowAll.Checked = True) Then
_GenSql.AppendLine("coalesce(IMSRejects.ProductIMS2Desc,'') LIKE
@FranchiseName AND")
_GenSql.AppendLine("coalesce(IMSRejects.CMA8Desc,'') LIKE
@ProductLineName")

Else
_GenSql.AppendLine("IMSRejects.ProductIMS2Desc LIKE
@FranchiseName AND")
_GenSql.AppendLine("IMSRejects.CMA8Desc LIKE @ProductLineName")
End If

_GenSql.AppendLine("ORDER BY VendorName ASC, ProductIMS2Desc ASC,
CMA8Desc ASC")

_SqlAccounts = _GenSql.ToString()

_SqlSource.SelectCommand = _SqlAccounts
_SqlSource.SelectParameters.Add("ProductName", strProductName)
_SqlSource.SelectParameters.Add("VendorName", strVendorName)
_SqlSource.SelectParameters.Add("CatalogName", strCatalogName)
_SqlSource.SelectParameters.Add("FranchiseName", strFranchiseName)
_SqlSource.SelectParameters.Add("ProductLineName", strProductLineName)

 

[Guest]

That worked great. Thank you! I have some parts of my code as stored
procedures but still trying to understand the syntax for others.