Adding IF/ELSE Logic to the creation of SQL Query

Dreamweaver Specific Question in C#

[Argblat]

I keep running into a dilemna in my application development, which I will try to outline here, in general, in the hopes that someone can help me.

The basic idea is, how do you add logic (such as IF, ELSE) into your MM:DataSet queries (CommandText) so that the query changes dependent upon certain prerequisites.

Here is an example:
Lets say that we have a search with two texts: one for First Name, and One for Last Name. When we hit 'Search' we send to a query to the database with a WHERE clause checking something like 'SELECT // WHERE First_Name = @FirstName AND Last_Name = @LastName'.

This is simple enough...BUT..........what if somebody enters a Last Name, but DOES NOT put anything into the First Name box?

Now, becuase of the way the code is set up, we are testing WHERE Last_Name = 'entered name' and First_Name = "". This is obviously a problem since it is actually testing against a blank ("").

Instead I would like to be able to first check that neither First_Name nor Last_Name is equal to "" (blank or null) before I create the sql statement, and then create the statement according to the situation.

The logic is simple to insert the proper if statements and write out the correct sql query based on what has been posted back, but I do not know how to do this in dreamweaver (please be specific to dreamweaver, as I have to use their controls anyway) given the custom controls that build the dataset and all the other dynamic features.

Can someone please help me to understand how to do this, as it is keeping me from accomplishing SO MUCH in the applications I am currently developing.

thank you so much,
Mike

[wingnut]

Select *
FROM TABALENAME
Where FIRSTNAME = '%varFIRSTNAME%' and LASTNAME = '%varLASTNAME%'

Create 2 variables named varFIRSTNAME and varLASTNAME
default = %

Run-time Value = Request("FIRSTNAME")
Run-time Value = Request("LASTNAME")

Based on search form with two inputs named FIRSTNAME and LASTNAME

[Argblat]

Unfortunatley, your answer does not solve my 2 basic questions.

1. If either varFirstName or varLastName is left blank, then the sql statement tests for that blank as opposed to not testing for anything.

2. How do I insert this logic into code using dreamweaver, which has its own custom controls...

ie...where does the code go in here:

<MM:DataSet
id="DataSet_Name"
runat="Server"
IsStoredProcedure="false"
ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_STRING_connString"] %>'
DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_DATABASETYPE_connString"] %>'
CommandText='<%# "SELECT * FROM dbo.NAME%>'
PageSize="10"
Debug="true"
>
</MM:DataSet>

or better yet, how do I accomplish this using the gui dreamweaver wizards.

thank you
-Mike

[aspdaddy]

I have one searching various text fields, you can construct the sql something like this :

strFirst=trim(Request("First"))
if strFirst <> "" then
strFirst = "(FirstNames ='%" & strFirst & "%')"
blnSearched=true
end if

strLast=trim(Request("Last"))
if strLast <> "" then
strLast = "(LastName = '%" & strLast & "%')"
if blnSearched then
strLast= " AND " & strLast
end if
blnSearched=true
end if

if (blnSearched =true) then
strSQL = "SELECT " & strSelect & " FROM " & strView & " WHERE " & strFirst & strLast
end if
If you are using a stored proc check out these ideas:
[sommarskog.se...]

[edited by: aspdaddy at 10:06 pm (utc) on July 25, 2005]

[wingnut]

This prolly beyond my skill set but the idea behind behind the previous answer was not needing to test for blanks but allowing for the fact there may or may not be anything in each variable. That is, if there is a FIRSTNAME AND LASTNAME then search using both or just one, why need to test to see if one is blank.

I am sure someone will provide a more efficient answer. GL

[syber]

You should be able to construct you SQL statement as a string variable using IF logic to determine if @LastName is empty or null. Then have CommandText referer the string variable rather than a hard coded SQL statement.

[tomasz]

This is simple enough...BUT..........what if somebody enters a Last Name, but DOES NOT put anything into the First Name box?

just use like and it should work
... and FirstName like '%" + varFirstName + "%'"

[Argblat]

So I was looking forward to having an excuse to take the Dreamweaver training wheels off and start to get my hands a little dirtier with asp.net and c#...but tomasz had to go and screw that all up.

he is 100% right, the wildcard operators are the simplest solution to the problem

because...

WHERE FNAME = '%@FNAME%' AND LNAME = '%LNAME%'
will elegantly handle blank/nulls by opening up the entire row , as opposed to actually comparing against blank/null and getting nothing back

when I have a little more time, I will try to flesh out the IF/ELSE within the sql statement [in Dreamweaver] based on some of the other good suggestions, to share with everyone.

thank you all for your help,
-Mike

[Argblat]

I made a crucial error in my last post...

replace the '=' with 'LIKE'

to make it

WHERE FNAME LIKE '%@FNAME%' AND LNAME LIKE '%@LNAME%'

Without the LIKE we get all the same old issues.

sry,
Mike