I have a working sql but its too long

[havey]

I would like to shorten the following sql, is it possible somehow?SQL = "SELECT * FROM logbook WHERE rname IN ("
AFRICAN = Request.Form("AFRICAN")
If AFRICAN = "true" Then
SQL = SQL & "'AFRICAN',"
End If
CARIBBEAN = Request.Form("CARIBBEAN")
If CARIBBEAN = "true" Then
SQL = SQL & "'CARIBBEAN',"
End If
CHINESE = Request.Form("CHINESE")
If CHINESE = "true" Then
SQL = SQL & "'CHINESE',"
End If
CONTINENTAL = Request.Form("CONTINENTAL")
If CONTINENTAL = "true" Then
SQL = SQL & "'CONTINENTAL',"
End If
DUTCH = Request.Form("DUTCH")
If DUTCH = "true" Then
SQL = SQL & "DUTCH',"
End If
EAST_INDIAN = Request.Form("EAST_INDIAN")
If EAST_INDIAN = "true" Then
SQL = SQL & "'EAST_INDIAN',"
End If
ENGLISH = Request.Form("ENGLISH")
If ENGLISH = "true" Then
SQL = SQL & "'ENGLISH',"
End If
ETHIOPIAN = Request.Form("ETHIOPIAN")
If ETHIOPIAN = "true" Then
SQL = SQL & "'ETHIOPIAN',"
End If
FONDUES = Request.Form("FONDUES")
If FONDUES = "true" Then
SQL = SQL & "'FONDUE',"
End If
FRENCH = Request.Form("FRENCH")
If FRENCH = "true" Then
SQL = SQL & "'FRENCH',"
End If
FUSION = Request.Form("FUSION")
If FUSION = "true" Then
SQL = SQL & "'FUSION',"
End If
GERMAN = Request.Form("GERMAN")
If GERMAN = "true" Then
SQL = SQL & "'GERMAN',"
End If
GREEK = Request.Form("GREEK")
If GREEK = "true" Then
SQL = SQL & "'GREEK',"
End If
ITALIAN = Request.Form("ITALIAN")
If ITALIAN = "true" Then
SQL = SQL & "'ITALIAN',"
End If
JAPANESE = Request.Form("JAPANESE")
If JAPANESE = "true" Then
SQL = SQL & "'JAPANESE',"
End If
JEWISH = Request.Form("JEWISH")
If JEWISH = "true" Then
SQL = SQL & "'JEWISH',"
End If
KOREAN = Request.Form("KOREAN")
If KOREAN = "true" Then
SQL = SQL & "'KOREAN',"
End If
MEDITERRANEAN = Request.Form("MEDITERRANEAN")
If MEDITERRANEAN = "true" Then
SQL = SQL & "'MEDITERRANEAN',"
End If
MEXICAN = Request.Form("MEXICAN")
If MEXICAN = "true" Then
SQL = SQL & "'MEXICAN',"
End If
MONGOLIAN = Request.Form("MONGOLIAN")
If MONGOLIAN = "true" Then
SQL = SQL & "'MONGOLIAN',"
End If
PAKISTANI = Request.Form("PAKISTANI")
If PAKISTANI = "true" Then
SQL = SQL & "'PAKISTANI',"
End If
PHILIPPINE = Request.Form("PHILIPPINE")
If PHILIPPINE = "true" Then
SQL = SQL & "'PHILIPPINE',"
End If
PIZZA = Request.Form("PIZZA")
If PIZZA = "true" Then
SQL = SQL & "'PIZZA',"
End If
PORTUGUESE = Request.Form("PORTUGUESE")
If PORTUGUESE = "true" Then
SQL = SQL & "'PORTUGUESE',"
End If
RIBS = Request.Form("RIBS")
If RIBS = "true" Then
SQL = SQL & "'RIBS',"
End If
STEAK_HOUSES = Request.Form("STEAK_HOUSES")
If STEAK_HOUSES = "true" Then
SQL = SQL & "'STEAK_HOUSES',"
End If
THAILAND = Request.Form("THAILAND")
If THAILAND = "true" Then
SQL = SQL & "'THAILAND',"
End If
UKRAINIAN = Request.Form("UKRAINIAN")
If UKRAINIAN = "true" Then
SQL = SQL & "'UKRAINIAN',"
End If
VEGETARIAN = Request.Form("VEGETARIAN")
If VEGETARIAN = "true" Then
SQL = SQL & "'VEGETARIAN',"
End If
VIETNAMESE = Request.Form("VIETNAMESE")
If VIETNAMESE = "true" Then
SQL = SQL & "'VIETNAMESE',"
End If
WEST_INDIAN = Request.Form("WEST_INDIAN")
If WEST_INDIAN = "true" Then
SQL = SQL & "'WEST_INDIAN'"
End If
If (DINE_IN = "true" AND DINE_OUT = "true") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_IN','DINE_OUT')"
elseIf (DINE_IN = "true" AND DINE_OUT = "false") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_IN')"
elseIf (DINE_IN = "false" AND DINE_OUT = "true") Then
SQL = SQL & ") AND eat IN ('BOTH','DINE_OUT')"
End If

[whammy]

Dim Message, i
For i = 1 To Request.Form.Count
Message = Message & _
Replace(Request.Form.Key(i),"_"," ") & ": " & Request.Form(i) & "<br />" & vbCrLf
Next
Response.Write(Message)


:)

From your previous code, I think you can work that out. :)

P.S. That replace statement is for an email, so you can make the text message look better with a minimum of extra work, while just using form field names, i.e.: "First_Name" becomes "First Name".

[whammy]

P.S. If you just named all of those "language" fields the same name, and used checkboxes, they you'd already have all of those in an array - perfect to use with the SQL "IN()" function. :)

Depending upon the datatype in your database, you might want to do something such as name all of your checkboxes for that particular "subject" the same, for instance:


<input type="checkbox" name="cb" value="AFRICAN" /> AFRICAN<br />
<input type="checkbox" name="cb" value="BOLIVIAN" /> BOLIVIAN<br />
<input type="checkbox" name="cb" value="CANADIAN" /> CANADIAN<br />


Then what you'd recieve for Request.Form("cb") after a post operation would look like:

Dim cb
cb = Request.Form("cb")

' at this point, cb would have this value if the first three checkboxes were selected:
' "AFRICAN, BOLIVIAN, CANADIAN"

You could just then replace the string like this:

cb = Replace(cb,",","','")

' Another way: cb = "'" & Replace(cb,",","','") & "'"

... and then your string is perfectly formatted to use in a SQL "IN()" function (and you can add quotes above in the replace statement like above, or later like below), i.e.:


Dim SQL

' I'm assuming that you already have a
' connection here named "Conn", and a
' recordset named "rs"!

SQL = "SELECT * FROM TBL_NATIONALITIES WHERE NATIONALITY IN('" & cb & "')"

' The single quotes in the above line
' aren't necessary if you used them
' above, as in the commented out
' Replace statement, but I think it
' looks a little better to do it this way...

Set rs = Conn.Execute(SQL)

Do While NOT rs.EOF
'Code here
rs.MoveNext '(forgetting this will bring down a SQL Server!)
Loop

Set rs = Nothing
Conn.Close
Set Conn = Nothing



:)

Note that any checkbox that ISN'T checked won't be passed to the next form!

[fractalvibes]

First things first - is this list of potential selections made by the user mutually exclusive?

i.e. you may choose 'Chinese' or 'Mexican' or 'Outer Slobivan', but only 1 of the above?

'Pizza' and 'Ribs' certainly must be an entirely different form object and SQL predicate....

You will be doing yourself a favor by putting all the nationalities into a domain table and likewise with the food types. That way you don't code yourself into a corner...and you can manage a virtualy infinite number of each without hard-coding all of this.
If Texas becomes an independant republic next month - add an entry to the nationalities domain table and it is covered....no extra coding required...

fv

[havey]

fv, the list of potential selections are not mutually exclusive, ie you can choos any and all.

Pizza and Ribs are NOT firrerent for objects they are part of AFRICAN as these are cuisines

I am looking to refine the sql. The checkboxes are in a Flash form and Falsh sets the default value of the checkboxes "false" and other option is 'true' i cannot change this to my own. I have a list of cuisines with check boxes and a seconday list of check boxes (eating style) consisting of:
dine in
dine out

either and both of these can be selected by the user.

Some of the records in the db have 'both' as a value for eating style. The flash value pairs created are like AFRICAN=true&CARIBBEAN=false&.....
I am only concerned with the cuisines where the value = true and the eating style where it is true as well. I'm trying to create a SQL where cusines IN ('the selections that are true') AND eat IN ('BOTH', the selections that are true)

[havey]

Your questions have been answered, thanks for the reply & help!