Add IF THEN clause to SELECT statement

[=?Utf-8?B?TWlrZSBD?=]

I have a SQL 2000 table named IMSRejects with the fields:
IMSRejects.Prod uctDescription
IMSRejects.Vend orName
IMSRejects.Cata logNumber
IMSRejects.Prod uctIMS2Desc
IMSRejects.CMA8 Desc
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.Pro ductDescription LIKE '%" & strProductName & "%' AND "
& _
"IMSRejects.Ven dorName LIKE '%" & strVendorName & "%' AND " & _
"IMSRejects.Cat alogNumber LIKE '%" & strCatalogName & "%' AND " & _
"IMSRejects.Pro ductIMS2Desc LIKE '%" & strFranchiseNam e & "%' AND "
& _
"IMSRejects.CMA 8Desc LIKE '%" & strProductLineN ame & "%' " & _
"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.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************** *************** **************
Think outside the box!
*************** *************** **************
"Mike C" <Mi***@discussi ons.microsoft.c omwrote in message
news:C0******** *************** ***********@mic rosoft.com...

>I have a SQL 2000 table named IMSRejects with the fields:
IMSRejects.Prod uctDescription
IMSRejects.Vend orName
IMSRejects.Cata logNumber
IMSRejects.Prod uctIMS2Desc
IMSRejects.CMA8 Desc
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.Pro ductDescription LIKE '%" & strProductName & "%' AND
"
& _
"IMSRejects.Ven dorName LIKE '%" & strVendorName & "%' AND " & _
"IMSRejects.Cat alogNumber LIKE '%" & strCatalogName & "%' AND " & _
"IMSRejects.Pro ductIMS2Desc LIKE '%" & strFranchiseNam e & "%' AND "
& _
"IMSRejects.CMA 8Desc LIKE '%" & strProductLineN ame & "%' " & _
"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!!

[=?Utf-8?B?SmFzb24gVmVybWlsbGlvbg==?=]

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(IMSRej ects.ProductIMS 2Desc, '') LIKE '%yabayaba%'
....

or if you used the isnull() function
.....
isnull(IMSRejec ts.ProductIMS2D esc, '') 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 SelectParameter s to help prevent sql
injection attacks (see
http://www.codeproject.com/cs/databa...gAdHocSQL.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(2 56)
Dim strProductName As String = ""
Dim strVendorName As String = ""
Dim strCatalogName As String = ""
Dim strFranchiseNam e As String = ""
Dim strProductLineN ame As String = ""
Dim _SqlAccounts As String = ""

strProductName = "%" & strProductName & "%"
strVendorName = "%" & strVendorName & "%"
strCatalogName = "%" & strCatalogName & "%"
strFranchiseNam e = "%" & strFranchiseNam e & "%"
strProductLineN ame = "%" & strProductLineN ame & "%"

_GenSql.AppendL ine("Select * FROM IMSRejects WHERE")
_GenSql.AppendL ine("IMSRejects .ProductDescrip tion LIKE @ProductName
AND")
_GenSql.AppendL ine("IMSRejects .VendorName LIKE @VendorName AND")
_GenSql.AppendL ine("IMSRejects .CatalogNumber LIKE @CatalogName AND")

' Use the t-sql Coalesce() or isnull() function to
If (Me.cbShowAll.C hecked = True) Then
_GenSql.AppendL ine("coalesce(I MSRejects.Produ ctIMS2Desc,'') LIKE
@FranchiseName AND")
_GenSql.AppendL ine("coalesce(I MSRejects.CMA8D esc,'') LIKE
@ProductLineNam e")

Else
_GenSql.AppendL ine("IMSRejects .ProductIMS2Des c LIKE
@FranchiseName AND")
_GenSql.AppendL ine("IMSRejects .CMA8Desc LIKE @ProductLineNam e")
End If

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

_SqlAccounts = _GenSql.ToStrin g()

_SqlSource.Sele ctCommand = _SqlAccounts
_SqlSource.Sele ctParameters.Ad d("ProductName" , strProductName)
_SqlSource.Sele ctParameters.Ad d("VendorName ", strVendorName)
_SqlSource.Sele ctParameters.Ad d("CatalogName" , strCatalogName)
_SqlSource.Sele ctParameters.Ad d("FranchiseNam e", strFranchiseNam e)
_SqlSource.Sele ctParameters.Ad d("ProductLineN ame", strProductLineN ame)

[=?Utf-8?B?TWlrZSBD?=]

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

"Jason Vermillion" wrote:

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(IMSRej ects.ProductIMS 2Desc, '') LIKE '%yabayaba%'
...

or if you used the isnull() function
....
isnull(IMSRejec ts.ProductIMS2D esc, '') 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 SelectParameter s to help prevent sql
injection attacks (see
http://www.codeproject.com/cs/databa...gAdHocSQL.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(2 56)
Dim strProductName As String = ""
Dim strVendorName As String = ""
Dim strCatalogName As String = ""
Dim strFranchiseNam e As String = ""
Dim strProductLineN ame As String = ""
Dim _SqlAccounts As String = ""

strProductName = "%" & strProductName & "%"
strVendorName = "%" & strVendorName & "%"
strCatalogName = "%" & strCatalogName & "%"
strFranchiseNam e = "%" & strFranchiseNam e & "%"
strProductLineN ame = "%" & strProductLineN ame & "%"

_GenSql.AppendL ine("Select * FROM IMSRejects WHERE")
_GenSql.AppendL ine("IMSRejects .ProductDescrip tion LIKE @ProductName
AND")
_GenSql.AppendL ine("IMSRejects .VendorName LIKE @VendorName AND")
_GenSql.AppendL ine("IMSRejects .CatalogNumber LIKE @CatalogName AND")

' Use the t-sql Coalesce() or isnull() function to
If (Me.cbShowAll.C hecked = True) Then
_GenSql.AppendL ine("coalesce(I MSRejects.Produ ctIMS2Desc,'') LIKE
@FranchiseName AND")
_GenSql.AppendL ine("coalesce(I MSRejects.CMA8D esc,'') LIKE
@ProductLineNam e")

Else
_GenSql.AppendL ine("IMSRejects .ProductIMS2Des c LIKE
@FranchiseName AND")
_GenSql.AppendL ine("IMSRejects .CMA8Desc LIKE @ProductLineNam e")
End If

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

_SqlAccounts = _GenSql.ToStrin g()

_SqlSource.Sele ctCommand = _SqlAccounts
_SqlSource.Sele ctParameters.Ad d("ProductName" , strProductName)
_SqlSource.Sele ctParameters.Ad d("VendorName ", strVendorName)
_SqlSource.Sele ctParameters.Ad d("CatalogName" , strCatalogName)
_SqlSource.Sele ctParameters.Ad d("FranchiseNam e", strFranchiseNam e)
_SqlSource.Sele ctParameters.Ad d("ProductLineN ame", strProductLineN ame)