Select Stament through .ASP 2

[jmiller79]

I have several reports. But now I want the user to be able to select which field to display in the report. I want the user to click on report and then a report creator displays; it will display 5-9 Drops down boxes with all the fields in the drop down available. Does anyone have a clue on how to do this? Or any direction; I think it has to do with a select statement in SQL through .ASP; I do not want to use any third party software. I am using MS SQL, I can create 100 od reports already, but I thought this would be a cool feature to add. Example

Field 1: Dropdown1
Field 2: Dropdown2
Field 3: Dropdown3
Field 4: Dropdown4
Field 5: Dropdown5

Then click create report and it will go to the data grid page. Does not seem very difficult. but any help would be great , Thanks

 

[Genimuse]

Well, in ASP you're going to generate the dropdowns based on the field names in your table, either statically saved in the ASP page itself, selected from a special table or text file you create (still static), or selected by querying the database for the column names.

After the user selects the fields and submits the form, you'll verify that there every dropdown has something selected and that there are no duplicates, take that final list and create your SQL. If, for example, you had you final field names in variables like strField1, strField2, etc., then your ASP might look like this:

strSQL = ""
If strField1 <> "" Then 
    strSQL = strSQL & strField1
End If

If strField2 <> "" Then
    If strSQL <> "" Then strSQL = strSQL & ", "
    strSQL = strSQL & strField2
End If

If strField3 <> "" Then
    If strSQL <> "" Then strSQL = strSQL & ", "
    strSQL = strSQL & strField3
End If

If strField4 <> "" Then
    If strSQL <> "" Then strSQL = strSQL & ", "
    strSQL = strSQL & strField4
End If

If strField5 <> "" Then
    If strSQL <> "" Then strSQL = strSQL & ", "
    strSQL = strSQL & strField5
End If

If strSQL <> "" Then
    strSQL = "SELECT " & strSQL & " FROM MyTable"
End If

That kind of thing.

 

[Bullschmidt]

And for perhaps the same idea in slightly different words:

Example on a page receiving a post of a SQL statement based on two listboxes (Rep and Customer) that might each have a value of "" (i.e. blank) to show all with no criteria restrictions.

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And the (1=1) above is there as a placeholder (which doesn't affect the results since it is always true) as the SQL statement possibly may use AND with criteria after it or possibly the SQL statement may not have anything after it. For example a SQL statement without the (1=1) like this would not work: SELECT * FROM MyTable WHERE AND Customer='API'


J. Paul Schmidt, Freelance ASP Web Developer

http://www.Bullschmidt.com

Classic ASP Design Tips, ASP Web Database Sample (Freely Downloadable)