Lengthy SQL giving me problems - can it be shortened?

[kisell]

I'm using a flash form and the checkbox values for Flash checkboxes are defaulted to false are have only a true or false set, this cannot be changed as that is the way Flash is, so no changing the name value.

All of the check boxes run independently.... (except for DINE_IN & DINE_OUT , i'll get to those later) How this works is the user selects what meal type they want and they can select more than one, then the user selects a dine_in or/and dine out value.

In the db there are records where both dine_in and dine_out apply to the meal, so i included that db value into the selection(s). So basically what i'm doing is creating the sql where the first set of selection are true and where dine_in or/and dine_out are selected and pulling all info for those records, then i'm displaying them in a interactive pie chart in flash. The flash aspect i have no problem with its the asp.

I've managed to create this page but Flash only properly gets the values when the sql is "select * from logbook" another words when no selection is made, i'm thinking this is becasue the sql is too much of a hack job, needs to be more refined. This is my asp page (maybe there is some ?other? reason flash can only grab the value pairs when the sql = "select * from logbook" and i've response.writen the sql in this long version and it is like this format:
sql = "select * from logbook WHERE cusine IN ('AFRICAN','GERMAN') AND eat IN ('BOTH','DINE_OUT')"



code:--------------------------------------------------------------------------------

DINE_IN = Request.Form("DINE_IN&quot
DINE_OUT = Request.Form("DINE_OUT&quot

Dim Serv, Conn, RecordView, Ename, output, collect, SQL, Eeat, Ephone, collectaa
Dim collectbb, collectdd , collect4, collectfg, collecthi, collect6, collect7, collectuu
Dim eoutput, eeoutput, e4output, e6eoutput, ae2output, eb7output, e7output, e9output, emoutput

Set Conn = Server.CreateObject("ADODB.Connection&quot
Conn.Open "DBQ=" & Server.MapPath("\4d9b\db.mdb&quot & ";" & _
"DRIVER=Microsoft Access Driver (*.mdb)"

SQL = "SELECT * FROM logbook WHERE cusine IN ("
AFRICAN = Request.Form("AFRICAN&quot
If AFRICAN = "true" Then
SQL = SQL & "'AFRICAN',"
End If
CARIBBEAN = Request.Form("CARIBBEAN&quot
If CARIBBEAN = "true" Then
SQL = SQL & "'CARIBBEAN',"
End If
CHINESE = Request.Form("CHINESE&quot
If CHINESE = "true" Then
SQL = SQL & "'CHINESE',"
End If
CONTINENTAL = Request.Form("CONTINENTAL&quot
If CONTINENTAL = "true" Then
SQL = SQL & "'CONTINENTAL',"
End If
DUTCH = Request.Form("DUTCH&quot
If DUTCH = "true" Then
SQL = SQL & "DUTCH',"
End If
EAST_INDIAN = Request.Form("EAST_INDIAN&quot
If EAST_INDIAN = "true" Then
SQL = SQL & "'EAST_INDIAN',"
End If
ENGLISH = Request.Form("ENGLISH&quot
If ENGLISH = "true" Then
SQL = SQL & "'ENGLISH',"
End If
ETHIOPIAN = Request.Form("ETHIOPIAN&quot
If ETHIOPIAN = "true" Then
SQL = SQL & "'ETHIOPIAN',"
End If
FONDUES = Request.Form("FONDUES&quot
If FONDUES = "true" Then
SQL = SQL & "'FONDUE',"
End If
FRENCH = Request.Form("FRENCH&quot
If FRENCH = "true" Then
SQL = SQL & "'FRENCH',"
End If
FUSION = Request.Form("FUSION&quot
If FUSION = "true" Then
SQL = SQL & "'FUSION',"
End If
GERMAN = Request.Form("GERMAN&quot
If GERMAN = "true" Then
SQL = SQL & "'GERMAN',"
End If
GREEK = Request.Form("GREEK&quot
If GREEK = "true" Then
SQL = SQL & "'GREEK',"
End If
ITALIAN = Request.Form("ITALIAN&quot
If ITALIAN = "true" Then
SQL = SQL & "'ITALIAN',"
End If
JAPANESE = Request.Form("JAPANESE&quot
If JAPANESE = "true" Then
SQL = SQL & "'JAPANESE',"
End If
JEWISH = Request.Form("JEWISH&quot
If JEWISH = "true" Then
SQL = SQL & "'JEWISH',"
End If
KOREAN = Request.Form("KOREAN&quot
If KOREAN = "true" Then
SQL = SQL & "'KOREAN',"
End If
MEDITERRANEAN = Request.Form("MEDITERRANEAN&quot
If MEDITERRANEAN = "true" Then
SQL = SQL & "'MEDITERRANEAN',"
End If
MEXICAN = Request.Form("MEXICAN&quot
If MEXICAN = "true" Then
SQL = SQL & "'MEXICAN',"
End If
MONGOLIAN = Request.Form("MONGOLIAN&quot
If MONGOLIAN = "true" Then
SQL = SQL & "'MONGOLIAN',"
End If
PAKISTANI = Request.Form("PAKISTANI&quot
If PAKISTANI = "true" Then
SQL = SQL & "'PAKISTANI',"
End If
PHILIPPINE = Request.Form("PHILIPPINE&quot
If PHILIPPINE = "true" Then
SQL = SQL & "'PHILIPPINE',"
End If
PIZZA = Request.Form("PIZZA&quot
If PIZZA = "true" Then
SQL = SQL & "'PIZZA',"
End If
PORTUGUESE = Request.Form("PORTUGUESE&quot
If PORTUGUESE = "true" Then
SQL = SQL & "'PORTUGUESE',"
End If
RIBS = Request.Form("RIBS&quot
If RIBS = "true" Then
SQL = SQL & "'RIBS',"
End If
STEAK_HOUSES = Request.Form("STEAK_HOUSES&quot
If STEAK_HOUSES = "true" Then
SQL = SQL & "'STEAK_HOUSES',"
End If
THAILAND = Request.Form("THAILAND&quot
If THAILAND = "true" Then
SQL = SQL & "'THAILAND',"
End If
UKRAINIAN = Request.Form("UKRAINIAN&quot
If UKRAINIAN = "true" Then
SQL = SQL & "'UKRAINIAN',"
End If
VEGETARIAN = Request.Form("VEGETARIAN&quot
If VEGETARIAN = "true" Then
SQL = SQL & "'VEGETARIAN',"
End If
VIETNAMESE = Request.Form("VIETNAMESE&quot
If VIETNAMESE = "true" Then
SQL = SQL & "'VIETNAMESE',"
End If
WEST_INDIAN = Request.Form("WEST_INDIAN&quot
If WEST_INDIAN = "true" Then
SQL = SQL & "'WEST_INDIAN'"
End If
If (DINE_IN = "true" AND DINE_OUT = "true&quot Then
SQL = SQL & &quot AND eat IN ('BOTH','DINE_IN','DINE_OUT')"
elseIf (DINE_IN = "true" AND DINE_OUT = "false&quot Then
SQL = SQL & &quot AND eat IN ('BOTH','DINE_IN')"
elseIf (DINE_IN = "false" AND DINE_OUT = "true&quot Then
SQL = SQL & &quot AND eat IN ('BOTH','DINE_OUT')"
End If

set RecordView = Conn.Execute(SQL)

Do While Not RecordView.EOF
Ername=RecordView("rname&quot
Eeat=RecordView("eat&quot
Ephone=RecordView("phone&quot
Eaddress=RecordView("address&quot
Ecusine=RecordView("cusine&quot
Ehours=RecordView("hours&quot
Especial=RecordView("special&quot
Eaddition1=RecordView("addition1&quot
Eaddition2=RecordView("addition2&quot
Ecrew=RecordView("crew&quot

collect =collect & Ername&","
collectaa =collectaa & Eeat&","
collectbb =collectbb & Ephone&","
collectdd =collectdd & Eaddress&","
collect4 =collect4 & Ecusine&","
collectfg =collectfg & Ehours&","
collecthi =collecthi & Especial&","
collect6 =collect6 & Eaddition1&","
collect7 =collect7 & Eaddition2&","
collectuu =collectuu & Ecrew&","

RecordView.MoveNext
Loop

output="&output="& collect
Response.Write output
Response.Write "end&"

eoutput="eoutput="& collectaa
Response.Write eoutput
Response.Write "end&"

eeoutput="eeoutput="& collectbb
Response.Write eeroutput
Response.Write "end&"

e4output="e4output="& collectdd
Response.Write eeoutput
Response.Write "end&"

e6eoutput="e6eoutput="& collect4
Response.Write eeoutput
Response.Write "end&"

ea2output="ea2output="& collectfg
Response.Write eeoutput
Response.Write "end&"

eb7output="eb7output="& collecthi
Response.Write eeoutput
Response.Write "end&"

e7output="e7output="& collect6
Response.Write eeoutput
Response.Write "end&"

e9output="e9output="& collect7
Response.Write eeoutput
Response.Write "end&"

emoutput="emoutput="& collectuu
Response.Write eeoutput
Response.Write "end&"

Counter = 1

Response.Write "Counter=" & Counter
Response.Write "&"

RecordView.Close
Set RecordView = Nothing
Set Conn = Nothing

 

[foxbox]

Kisell, do you know a certain 2cxc??

thread333-742928

hth,
Foxbox

 

[foxbox]

also:

thread333-745306

hth,
Foxbox

 

[2cxc]

Ya thats me i lost 2cxc login password and didn't give login info correct email address, so i had to create another handle

 

[2cxc]

too bad this forum didn't have a handles merge button