If, then, else statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

If, then, else statement

Author

Topic

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-22 : 13:03:36

I have a form with 2 input text boxes that takes the information and passes it into a database to display the results. Here are the specs:

Database table name = shoppers
input text box1=appcity
input text box2=appstate

I want it to do the following: If information is put into appcity AND appstate, then the output needs to show only those that match appcity and appstate. I can do this part with no problem; however, I also want to search by one or the other. I think it needs to be something like this:

SELECT * FROM shoppers WHERE

IF (appcity='') THEN

ELSE (appcity=::appcity::)

AND

IF (appstate='') THEN

ELSE (appstate=::appstate::)

So, if appcity is left blank, I want to skip that criteria and go to the next box in the form. If I place simple AND criteria with no if then statements, and leave the box blank, it searches my table for a blank record...which is what I don't want. Any ideas?

Thanks!

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 13:40:55

[code]
SELECT *
FROM shoppers
WHERE (appcity='' OR appcity=::appcity::)
AND (appstate='' OR appstate=::appstate::)
[/code]
But promise me you won't use "SELECT *", OK? :)

Kristen

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-22 : 15:26:18

It still doesn't work...it gives me errors this way. Code is as follows:

SELECT * FROM shoppers WHERE (appcity = '' OR appcity = ::appcity::) AND (appstate = '' OR appstate = ::appstate::)

It is still searching my database for a null field which I dont' want it to do...I want it to simply negate the whole criteria if the field is left blank. THanks :)

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-22 : 16:14:58

It needs to be like this:



SELECT * FROM shoppers 
WHERE (appcity = @AppCity OR @AppCity IS NULL) 
AND (appState = @appState OR @appState IS NULL)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 16:49:10

"It is still searching my database for a null field which I dont' want it to do...I want it to simply negate the whole criteria if the field is left blank."

I am sure this syntax will do that, however as MichealP points out how are you getting the actual parameter data into "::appcity::"? - I presumed you were using some form of 4GL or whatever to insert parameterised data at that point.

Kristen

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-22 : 17:28:08

By putting in Michael's code,it doesn't even allow me to add a text box to search by...hence, gives an error when loading the page.

I am passing the info from text box appcity to field appcity by:

appcity=::appcity::

that pulls the info from my form great; however, if I leave appcity text box empty, then it tries to pull the null values from my table...I want to pass it all together and have it pull nothing.

From Kristens code:
SELECT * FROM shoppers WHERE (appcity = '' OR appcity = ::appcity::) AND (appstate = '' OR appstate = ::appstate::)

It looks like it should work fine; however, it doesn't. If I do a search for Waterloo, IA..it pulls up 2 records. If I do a search for IA, it pulls up nothing when it should pull up like 70 records. What it is doing is trying to find a blank city in my table that matches with IA..hence, returning no results. That is why I think I need an if, then, else statement but am unsure of the correct syntax.

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 19:14:21

Perhaps your hosting language is converting "Blank" textboxes to NULL?

SELECT * FROM shoppers WHERE (appcity IS NULL OR appcity = '' OR appcity = ::appcity::) AND (appstate IS NULL OR appstate = '' OR appstate = ::appstate::)

But its just a guess ...

Perhaps you could do the following to find out wqhat SQL is being given
SELECT [appcity] = ::appcity::, [appstate] = ::appstate::

Kristen

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-22 : 19:26:37

Ok,
What language are you doing this in:

I am passing the info from text box appcity to field appcity by:

appcity=::appcity::

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-22 : 22:53:55

Kristen: It is taking a blank vlaue and considering it a null and searching my table for null values instead of not searching at all.

SELECT * FROM shoppers WHERE (appcity IS NULL OR appcity = '' OR appcity = ::appcity::) AND (appstate IS NULL OR appstate = '' OR appstate = ::appstate::) contains IS NULL and =''...which is the same thing and produces the same results (I tried that one seperately already :(

I will try the other code, but believe it will produce an error from front page (front page 2003 and server 2003 btw if that helps out at all)

Michael: That is the same syntax I use to pass info from text box to table, that is correct (appcity is text box, ::appcity:: is table field)

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 01:19:10

OK, Now I've got it ... hopefully!

SELECT * FROM shoppers WHERE (::appcity:: IS NULL OR ::appcity:: = '' OR appcity = ::appcity::) AND (::appstate:: IS NULL OR ::appstate:: = '' OR appstate = ::appstate::)

You may not need both the IS NULL and = '' tests, but they won't do any harm unless FrontPage has kittens ("IS NULL and =''...which is the same thing " that most certainly is not the case from SQL's perspective!)

Kristen

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-23 : 10:20:08

This code:

SELECT * FROM shoppers WHERE (appcity='' OR appcity=::appcity::) AND (appstate='' OR appstate=::appstate::)

Verifies fine, but gives the error :

Description: Extra ) in query expression '(appcity='' OR appcity=waterloo) AND (appstate='' OR appstate=)'.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for all form fields that are used in the query.

Or, if I fill out both fields, I get:

Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine

Your code of:

SELECT * FROM shoppers WHERE (::appcity:: IS NULL OR ::appcity:: = '' OR appcity = ::appcity::) AND (::appstate:: IS NULL OR ::appstate:: = '' OR appstate = ::appstate::)

Produces the same errors unfortunately :(

Do I need to be referencing some sort of Schema or something? When I try to do an IIF statement on it it says I need a Schema.

I have no idea why this logic is not working, it makes sense reading it as english? ARRGGG!

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-23 : 10:35:24

Also, this is just a test scenerio as the ultimate goal will have around 5-10 different fields to add; so, this code may get very long. I know in VB I can do a sfilter command to have this completed, and I know that code!! Anyone know how to incorporate that into front page maybe? Or convert an sfilter into SQL? It would be something like this:

Dim sfilter As String

If Not IsNull(appcity) Then
sfilter = "([appcity]='" & appcity & '") and"
End If

If Not IsNull(appstate) Then
sfilter = sfilter &"([appstate]='" & appstate & '") and"
End If

If Len(sfilter) Then sfilter = Left(sfilter, Len(sfilter) -5)

<Some sort of code to display the results here>

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 11:26:50

You need to wrap your data in single quotes.

The error

Description: Extra ) in query expression '(appcity='' OR appcity=waterloo) AND (appstate='' OR appstate=)'

shows (in red) where the SQL expression being created does not have the data within quotes - i.e. your data is blank, that's fine, but it needs to be contained inside single quotes

The "waterloo" bit IS working OK, but only because there is no space in the search string "waterloo" and it does not start with a number - i.e. just a lucky fluke.

Actually,. looking at your "sfilter" you ahve got the single quotes logic in there, so you just need to do that in frontpage.

Is this for a public facing site? Because the style of coding in your "sfilter" would enable a hacker to use SQL injection and hack into your database, drop the database, delete all the data and so on.

Kristen

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-23 : 12:08:36

I sort of understand the error message, but don't understand how to fix it. I think I have tried every combination of ' possible....what would you say the correct code should be?

The sfilter is something I made for an internal database just as a "hack" of code as I had like 15 criterias, which obviously made for long sfilter code and crashed often. I probably wouldn't make it public; and, I wouldn't even know how to incorporate it to a web site to display results...how would I do that just for my own giggles? Thanks for help btw :)

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 13:30:41

Can you do something like

appstate = "'" & appstate * "'"

(double quotes are in blue)

before you throw the variables at:

SELECT * FROM shoppers WHERE (::appcity:: IS NULL OR ::appcity:: = '' OR appcity = ::appcity::) AND (::appstate:: IS NULL OR ::appstate:: = '' OR appstate = ::appstate::)

Or maybe just put some single quotes in the SQL template - so:

SELECT * FROM shoppers WHERE ('::appcity::' IS NULL OR '::appcity::' = '' OR appcity = '::appcity::') AND ('::appstate::' IS NULL OR '::appstate::' = '' OR appstate = '::appstate::')

But you need someone who knows FrontPage - which isn't me I'm afraid

Kristen

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-23 : 14:42:38

I think you need to get away from this inline SQL stuff and call a stored proc. Use the code I gave you and put it in a stored proc and be done with it.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 14:47:49

hehehe ... how many sinners can we convert() before the sabbath?!

Kristen

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-07-23 : 14:50:04

I tried putting that code into the query and it verifies fine, but when you go to the page it is looking to enter in a parameter from a text box..I have added the text box, but it doesn't affect the online display of the database that only produces errors.

I have tried all of the abocve portions of "'", but nothign works :(

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 15:11:33

Well, I'm afraid you need someone who understands the syntax of FrontPage then, 'coz the error message shows that SQL isn't getting the right syntax, and I don't know how to get thsoe pesky single quotes in there in FrontPage.

Kristen

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-23 : 15:17:01

Yes, this is a Frontpage question at this point.
If it were ASP or ASP.net with Visual Studio I could help ya, but I've not used FrontPage this century! Ever since FrontPage started changing my code on me years ago, I swore I'd never use it again.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 15:37:44

"Ever since FrontPage started changing my code on me"

Seems that its still doing that, only now between Application and Server, rather than just the roundtrip-HTML!

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources