473,320 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Add IF THEN clause to SELECT statement

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!!
Jan 25 '07 #1
3 4959
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***@discussions.microsoft.comwrote in message
news:C0**********************************@microsof t.com...
>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!!
Jan 25 '07 #2
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/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(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.ProductIMS 2Desc,'') 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)

Jan 26 '07 #3
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(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/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(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.ProductIMS 2Desc,'') 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)
Feb 9 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Summa | last post by:
Hi NG, I have the following problem that I hope you can help me with (MS-SQL server 2000) Imagine a statement like this: "select id, firstname, (select top 1 id from testdata) as testid,...
1
by: Kenneth P | last post by:
Hi, I'm trying to do some Custom Paging technique with the datagrid object and with the select command in Sql, thus forcing the server to only select those rows from the database that should be...
4
by: louise raisbeck | last post by:
I have this scenario (simplified) function addnewdata () { check for partial match already in db for information entered by user if (partialmatch succeeds) { open new window aspx page (using...
2
by: Michael C | last post by:
Hi all. When I run the following query against my Access database - from within Access - it returns the correct results (5 records in this instance): SELECT * FROM WHERE = "Clothes" AND LIKE...
9
by: Acupuncture | last post by:
Hi, I am developing a JDBC application and I encountered this problem (DB2 for ISeries). I want to do a select for update and also use the fetch first rows clause. This is my sql statement: ...
26
by: GreatAlterEgo | last post by:
Hi, This is my query which is embedded in a COBOL program. EXEC SQL SELECT DATE, AGE, DURATION, AMT INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT FROM TAB1 WHERE CODE = :KEY.CODE AND...
5
by: pwiegers | last post by:
Hi, I'm trying to use the result of a conditional statement in a where clause, but i'm getting 1)nowhere 2) desperate :-) The query is simple: -------- SELECT idUser,...
1
by: ebo2006 | last post by:
There are two combo boxes in my form: CORP and CLASS. The WHERE clause of the SELECT statement in the Row Source Property of the CLASS combo box works only every time I start the form. It does not...
3
by: Rahul Babbar | last post by:
Hi, I had the following doubts about the "For Read Only" clause. 1. How does a "for Read only" clause improve the performance? 2. How does a "for Read only" clause compare with "With UR"...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.