ASP DB problem dynamic

[crmpicco]

How can i make this dynamic as not all fields will be available everytime as the variable values are coming from a form field.



set rs1=con.execute("select * from net_fares where company_id = '"& company_id &"' and contract_id = '"& contract_id &"' and dep like '%"& dep &"%' and arr like '%"& arr &"%' and via like '%"& via &"%' and season = '%"& season &"%' and cabin = '%"& cabin &"%' and zone = '%"& zone &"%' and region = '%"& region &"%'")

[crmpicco]

all i can think of at the moment is having about 30 sql hits?

[Mukul Ranjan]

HELLO SIR,
i m student of Indian Inistitute of Information Technology-Calcutta.sir i want to pad project. i have some econimical problem because my father have not more much money therefore i do the project on ur campus please sir help me or mail me my mail id (manoj01_iiitc@yahoo.com)
rset is fine sir,
my contect no +9231587388
my postal address
DL 108,SECTOR-2
SALT LAKE SITY
KOLKATA-91
PIN 700091

[crmpicco]

i dont understand?

[nikkiH]

Just make the whole statement dynamic, with many IFs.
I don't have an ASP example, but this snippet of a JSP bean is similar and should give you the idea.


String q = "select eventId as \"Event ID\", " +
"officeName as \"Office Name\", " +
"baseControl as \"Base & Control\", " +
"ordernumInternal as \"Internal (SAP) Order Number\", " +
"eventType as \"Event Type\", " +
"processMethod as \"Process Method\", " +
"toRelease as \"To Release\", " +
"dtssFirstName || ' ' || dtssLastName as \"DTSS Name\", " +
"nvl(to_char(nvl(schTurnoverDate,schRetrofitDate),'MM/DD/YYYY'),'') as \"Sch Event Date\", " +
"eventTbl.notes as \"Notes\" " +
"from officeTbl, eventTbl, dtssTbl, customerTbl where ";
q += "officeTbl.officeId=eventTbl.officeId ";
q += "and eventTbl.dtssId = dtssTbl.dtssId(+) ";
q += "and customerTbl.customerId = officeTbl.customerId ";

...

if (schEventDateFrom != null)
{
q += "and nvl(schRetrofitDate,schTurnoverDate) >= to_date('" +
schEventDateFrom + "','MM/dd/yyyy') ";
}
if (schEventDateTo != null)
{
q += "and nvl(schRetrofitDate,schTurnoverDate) <= to_date('" +
schEventDateTo + "','MM/dd/yyyy') ";
}
if (actEventDateFrom != null)
{
q += "and nvl(actRetrofitDate,actTurnoverDate) >= to_date('" +
actEventDateFrom + "','MM/dd/yyyy') ";
}
if (actEventDateTo != null)
{
q += "and nvl(actRetrofitDate,actTurnoverDate) <= to_date('" +
actEventDateTo + "','MM/dd/yyyy') ";
}
...


HTH

[miranda]

Having done something similar I made a variable called whereclause and then testing for these values wrote the whereclause

Dim whereclause : whereclause = ""
If Len(contract_id ) > 0 Then
whereclause = whereclause & "AND contract_id = '"& contract_id &"'"
End If
If Len(dep) > 0 Then
whereclause = whereclause & "AND dep like '%"& dep &"%'"
End If
If Len(arr) > 0 Then
whereclause = whereclause & "AND arr like '%"& arr &"%'"
End If
If Len(via) > 0 Then
whereclause = whereclause & "AND via like '%"& via &"%'"
End If
If Len(season) > 0 Then
whereclause = whereclause & "AND season = '%"& season &"%'"
End If
If Len(cabin) > 0 Then
whereclause = whereclause & "AND cabin = '%"& cabin &"%'"
End If
If Len(zone) > 0 Then
whereclause = whereclause & "AND zone = '%"& zone &"%'"
End If
If Len(region) > 0 Then
whereclause = whereclause & "AND region = '%"& region &"%'"
End If
set rs1=con.execute("select * from net_fares WHERE company_id = '"& company_id &"'" & whereclause)