Developing a web application in VB.NET.  Looking to create a complex
and extensive SQL Select statement.  In the application there are four
drop down list which correspond to fields in the database.  The SELECT
statement should return the results from the database of the criteria
that the user of the application selected from the drop down list. 
The problem is how do you not include a portion of the select
statement or make a drop down list item not searchable, IF one or more
of the drop down list items is NOT selected.  Is there a way to ?hide?
this value in the Select statement so it does not include it in the
search?  Or does the Select statement have to be built as a string
corresponding to the DDL that are selected or not selected through a
series of IF statements.  Any suggestions or examples? Examples of
Select statements or code would be great.

---

Request for Question Clarification by mathtalk-ga on 07 Jul 2004 10:32 PDT

Hi, bdwgarth-ga:

Although the Subject of your Question emphasizes SQL expertise, there
are a number of related issues with VB.Net or more specifically
ASP.Net that are in play here.

For the list price offered it seems you are looking for a single
specific Answer, provided by a single Web link or other brief reply. 
This might make sense if you already have a clear grasp of the way
form variables are exposed in ASP.Net, ie. the selected value(s) in a
drop down list, and have already settled on the general outline of
your SQL "command".

You could then make it easier for a Researcher to provide a specific
Answer by providing the details of your form variables/control names
and sketching the construction of the SQL command.  If you are looking
for broader help with these topics, which might be inferred from the
way the Question is phrased, then a longer time would probably be
required to Answer it well than is usually associated with the list
price offered:

[Google Answers: How to Price your Question]
http://answers.google.com/answers/pricing.html

I'll post a couple of Hints below as a Comment if no one else responds.

regards, mathtalk-ga

As I believe dropdownlists always have a selected value (the first one
in the list), I suggest that you add an entry in your dropdownlists at
index 0 with text="-" or something similar to indicate no selection
has been made.

Hence, you can build your sql statement as such
(C# code) 
strSQL = "SELECT * FROM tblWhatever WHERE " +
(ddl1.SelectedIndex==0?"":"field1=" + ddl1.SelectedValue + " AND ") +
(ddl2.SelectedIndex==0?"":"field2=" + ddl2.SelectedValue + " AND ") +
"1=1;"

The last "1=1" is to ensure that you don't end the where clause with
an AND and it evaluates to true for all records.

the ? : are imbricated IF statements

For VB.NET (forgot that's what you wanted :)), you could do:
strSQL = "SELECT * FROM tblWhatever WHERE ";

if ddl1.SelectedIndex=0 then 
   strSQL = strSQL & "field1=" & ddl1.SelectedValue & " AND ") 
end if
if ddl2.SelectedIndex=0 then 
   strSQL = strSQL & "field2=" & ddl2.SelectedValue & " AND ") 
end if

strSQL = strSQL & "1=1;"



Obviously, if your fields aren't numeric but are strings, you need to
use appropriate quoting.

if ddl3.SelectedIndex=0 then 
   strSQL = strSQL & "field3='" & ddl3.SelectedText.Replace("'","''") & "' AND ") 
end if

or something of the like (haven't done VB in a while)

Hi.,
Set Values to your Drop down list 
say 
0 - All
1 - male
2 - female

and build your sql according to index value if its not equal to 0

sSQL = "select * from tablename "
" where 1=1 ";
if( cmbBox1.index != 0 )
sSQL += " and name Like '%" + cmbBox1.value + "%'";
if( cmbBox2.index != 0 )
sSQL += " and address Like '%" + cmbBox2.value + "%'";

hth
Dishan