Имеется сайт со страничкой (формами) для поиска - Паге1 и Паге2 в которой обрабатъвается критерий запроса. (пример state: CA -California; city: San Francisco на въходе -школъ в данном регионе учавствующие в данном проекте)
Есть два варианта въхода даннъх: 1. в виде ХТМЛ таблицъ, 2. Excel файл (даунлоад)
Проблема в том, что при 1 варианте все ОК, но во втором, независимо от критериев которъе подаются в запросе, на въходе одни и тежи даннъе. Есть мъсль, что припервом запросе для Варанта (2) задали одни критерии, файл остался , и при последующих запросах, не создается новъй файл!!( Почему, не могу сказать)
Так и загружается все один и тот же файл для каждого запроса. Прошу посмотреть код и въсказать свои соображения, заранее благодарен.
Паге1
<%@Language = VBScript%>
<%option explicit%>
<!-- #include file="../utils/adovbs.inc" -->
<!-- #include file="../utils/db_connection.inc" -->
<!-- #include file="../utils/MathCount_Utils.asp" -->
<%
'    *************************************************************************
'    *                                                                        *
'    *    File Name:            SchoolSearch.asp
'    *    File Description:    Used to search for the schools
'    *    Date Created:
'    *    Created by:
'    *    Dependencies:
'    *    Called by:
'    *    Calls to:
'    *                                                                        *
'    *************************************************************************
%>
<%'on error resume next%>
<%    'Session("pageExist") = ""
    dim objRs
    call GetStateCodes()
   
    sub GetStateCodes()
        dim sqlStr
        sqlStr = "select State_Code from State order by State_Code asc"
        Set objRs = Server.CreateObject("ADODB.Recordset")
        call objRs.Open(sqlStr, objDBConn, adOpenStatic, adLockOptimistic)
    end sub
   
    Session("School") = ""
    Session("State") = ""
    Session("City") = ""
    Session("ZipStart") = ""
    Session("ZipEnd")= ""
    Session("SortBy") = ""
%>
 <em>(Curious which local schools participated in the MATHCOUNTS competition last year? View the list of <A href="http://www.mathcounts.org/webarticles/anmviewer.asp?a=677&z=106">participants in the 2006 competition</A>.)</em></P>               
                            <FORM action="SchoolResult.asp" method=post>
                                <TABLE cellSpacing=0 cellPadding=3 border=0>
                                  <TBODY>
                                    <TR vAlign=top align=left>
                                            <TD><FONT COLOR = "#0000FF"><strong><I>Search Criteria</I></strong></FONT></TD>
                                    </TR>
                                      <TR vAlign=top align=left>
                                        <TH style="font-size:10pt">School Name: </TH>
                                        <TD>
                                         <INPUT maxLength=30 size=30 name=SchoolName></TD></TR>
                                      <TR vAlign=top align=left>
                                        <TH style="font-size:10pt">City: </TH>
                                        <TD>
                                         <INPUT maxLength=20 name=City></TD></TR>
                                      <TR vAlign=top align=left>
                                        <TH style="font-size:10pt">State: </TH>
                                        <TD>
                                        <select name="selStateList">
                                        <option value = "">Select State</option>
                                        <%
                                            dim stateCodeStr
                                            while not objRs.EOF
                                            stateCodeStr = objRs("State_Code")
                                        %>
                                            <OPTION value="<%=stateCodeStr%>"><%=stateCodeStr%></OPTION>
                                        <%
                                                objRs.MoveNext
                                            wend
                                        %>
                                        </SELECT></TD></TR>
                                        <TR vAlign=top align=left>
                                        <TH style="font-size:10pt">Zip: </TH>
                                        <TD> <INPUT maxLength=10 size=5 name=ZipStart>&nbsp; to &nbsp;<INPUT maxLength=10 size=5 name=ZipEnd> </TD>
                                        </TR>
                                        <TR vAlign=top align=left>
                                            <TD><FONT COLOR = "#0000FF"><strong><I>Display Criteria</I></strong></FONT></TD>
                                        </TR>
                                           
                                        <TR vAlign=top align=left> 
                                            <TH style="font-size:10pt">Sort By: </TH>
                                            <TD><Select name = "cmbSortBy">
                                                    <option value = "Zip">Zip Code</option>
                                                    <option value = "School_Name">School Name</option>
                                                    <option value = "City">City</option>
                                                </Select>
                                            </TD>
                                        </TR>
                                       
                                        <TR vAlign=top align=left> 
                                            <TH style="font-size:10pt">Display Format : </TH>
                                            <TD><Select name = "cmbDisplayFormat">
                                                    <option value = "1">Web Page Table</option>
                                                    <option value = "2">Comma-delimited Text File</option>
                                                </Select>
                                            </TD>
                                        </TR>   
                                       
                                  </TBODY></TABLE>
                                        <INPUT type=reset value=Clear>
                                     <INPUT type=submit value=Search> </FORM><BR>
                                     <A href="javascript:void(0)">
                                            [Search Again]</A>

<%@Language = VBScript%>
<%option explicit%>
<!-- #include file="../utils/adovbs.inc" -->
<!-- #include file="../utils/MathCount_Utils.asp" -->
<%on error resume next%>
<%
    const SIZE = 25
    const TIMEOUT = 300
   
    dim txtSchool
    dim txtCity
    dim txtState
    dim txtZipStart
    dim txtZipEnd
    dim txtSortBy
    dim txtDisplayFormat
   
    dim ifPageExist
    dim errorString
    dim objDBConn
    dim totalRecord
    dim abspage
    dim pagecnt
    dim rsObj
    dim sqlStr
    dim sqlWhere
    dim strSortBy
    dim g_strFileContent
    'Retrieving the values from the session if exist
    if request("pageExist") <> "" then
        txtSchool = Session("School")
        txtState = Session("State")
        txtCity = Session("City")
        txtZipStart = Session("ZipStart")
        txtZipEnd = Session("ZipEnd")
        strSortBy = Session("SortBy")
        txtDisplayFormat = Session("DisplayFormat")
    else
        call getFormValues
    end if
    g_strFileContent = ""
   
    if errorString = "" then
        call getConnection
        if Err.number <> 0 then
            errorString = Err.description
        end if
    end if
    if errorString = "" then
        call makeSqlString
        call getRecords
    end if
    'Retrieving the form values from the request object
    sub getFormValues()
        txtSchool = request.form("SchoolName")
        txtCity = request.form("City")
        txtState = request.form("selStateList")
       
        txtZipStart = Request.Form("ZipStart")
        txtZipEnd = Request.Form("ZipEnd")
        txtDisplayFormat = Request.Form("cmbDisplayFormat")
       
        strSortBy = Request.Form("cmbSortBy")
       
        if txtState = "" and txtZipStart = "" then
            'errorString = "Unkown Error... Please Search Again"
            errorString = "Please Select a State or a Zip Code Range"
        end if
    end sub
    'Getting the connection object
    sub getConnection
        dim connectionString
        dim database
        database = "../db/SchoolRegistration.mdb"
        '--- Instantiate ADO Connection object.
        Set objDBConn = Server.CreateObject("ADODB.Connection")
        objDBConn.ConnectionTimeout = TIMEOUT
        objDBConn.CommandTimeout = TIMEOUT
        '--- OLEDB connection.
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& server.mappath(database)
        objDBConn.open connectionString
    end sub
   
    sub makeSqlString
   
        dim lngZipStart
        dim lngZipEnd
       
        if txtZipStart  <> "" then
            lngZipStart  = clng(txtZipStart)
        end if
       
        if txtZipEnd <> "" then
            lngZipEnd = clng(txtZipEnd)   
        end if
       
        sqlStr = "select ID, School_Name, Address, Address_Line2, Teams, Date_Registered as dateReg, "&_
            "Coach_Email as email, Individuals, Coach,Coach_Phone,Coach_Email,City,Zip,State,"&_
            "County,Chapter,Country,Principal,School_Phone,Customer,Yrs_School_Participation"&_
            " from Registered_Schools "
                   
        if txtState <> "" then
            sqlWhere = " where State='" & txtState & "'"
            sqlStr = sqlStr & sqlWhere
        end if   
       
        if txtSchool <> "" then
            if sqlWhere <> "" then
                sqlStr = sqlStr & " and School_Name like '%" & txtSchool & "%' "
            else   
                sqlWhere =  " where School_Name like '%" & txtSchool & "%' "
                sqlStr = sqlStr & sqlWhere
            end if   
        end if
       
        if txtCity <> "" then
            if sqlWhere <> "" then
                sqlStr = sqlStr & " and City like '%" & txtCity & "%'"
            else   
                sqlWhere = " where City like '%" & txtCity & "%'"
                sqlStr = sqlStr & sqlWhere
            end if   
        end if
       
        if txtZipStart <> "" and txtZipEnd = "" then
            if sqlWhere <> "" then
                sqlStr = sqlStr & " and Zip = '" & txtZipStart & "'"
            else   
                sqlWhere = " where Zip = '" & txtZipStart & "'"
                sqlStr = sqlStr & sqlWhere
            end if
        end if
        
        if txtZipStart <> "" and txtZipEnd <> "" then
            if sqlWhere <> "" then
                sqlStr = sqlStr & " and Zip >= '" & lngZipStart & "' and Zip <= '" & lngZipEnd & "'"
            else   
                sqlWhere = " where Zip >= '" & lngZipStart & "' and Zip <= '" & lngZipEnd & "'"
                sqlStr = sqlStr & sqlWhere
            end if       
        end if
       
        if strSortBy = "School_Name" then
            sqlStr = sqlStr & " order by School_Name asc"
        elseif strSortBy = "Zip" then   
            sqlStr = sqlStr & " order by Zip asc"
        elseif strSortBy = "City" then   
            sqlStr = sqlStr & " order by City asc"
        end if   
    end sub
   
    'Retrieving the records from the database
    sub getRecords
        Set rsObj = Server.CreateObject("ADODB.Recordset")
       
        if Err.Number <> 0 then
            errorString = Err.description
        end if
       
        rsObj.PageSize = SIZE
        rsObj.CacheSize = SIZE
        rsObj.CursorLocation = adUseClient
       
        call rsObj.Open(sqlStr, objDBConn, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect)
        'response.write "*********" & sqlStr & rsObj.RecordCount
        if Err.number <> 0 then
            errorString = errorString & Err.description
        else
            totalRecord = rsObj.RecordCount
            if totalRecord <> 0 then
                If Len(Request("pagenum")) = 0  Then
                    rsObj.AbsolutePage = 1
                Else
                    If CInt(Request("pagenum")) <= rsObj.PageCount Then
                        rsObj.AbsolutePage = Request("pagenum")
                    Else
                        rsObj.AbsolutePage = 1
                    End If
                End If
                    abspage = rsObj.AbsolutePage
                    pagecnt = rsObj.PageCount
                   
                    ifPageExist = "true"
                    Session("State") = txtState
                    Session("SortBy") = strSortBy
                    Session("DisplayFormat") = txtDisplayFormat
                   
                    if txtSchool <> "" then
                        Session("School") = txtSchool
                    end if
                   
                    if txtCity <> "" then
                        Session("City") = txtCity
                    end if
                   
                    if txtZipStart <> "" then
                        Session("ZipStart") = txtZipStart
                    end if
                    if txtZipEnd  <> "" then
                        Session("ZipEnd") = txtZipEnd
                    end if
                'end if
            end if
        end if
    end sub
    'Function to show the page link in the pagination process only
    function showPageLink
   
        Response.Write "<div align=""center"">" & vbcrlf
        Response.Write "<a href="""
        Response.Write Request.ServerVariables("SCRIPT_NAME")
        Response.Write "?pagenum=1" & "&pageExist=" & ifPageExist & """><b>First Page</b></a>"
        Response.Write "    |    "
        If abspage = 1 Then
            Response.Write "<span style=""color:silver;"">Previous Page</span>"
        Else
            Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME")
            Response.Write "?pagenum=" & abspage - 1 & "&pageExist=" & ifPageExist & """><b>Previous Page</b></a>"
        End If
        Response.Write "    |    "
            If abspage < pagecnt Then
                Response.Write "<a href=""" & _
                    Request.ServerVariables("SCRIPT_NAME")
                Response.Write "?pagenum=" & abspage + 1 & "&pageExist=" & ifPageExist & """>Next Page</a>"
            Else
                Response.Write "<span style=""color:silver;""" & _
                    "><b>Next Page</b></span>"
        End If
        Response.Write "    |    "
        Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME")
            Response.Write "?pagenum=" & pagecnt & "&pageExist=" & ifPageExist & """><b>Last Page</b></a>"
        Response.Write "</div>" & vbcrlf
    end function
   
    function GetTwoDigitDate(DtReg)
        dim dayreg
        dim monthreg
        dim yearReg
       
        dayReg = day(DtReg)
        monthREg = month(DtReg)
        yearReg = year(dtreg)
       
        yearReg = right(yearReg,2)
        GetTwoDigitDate = monthReg & "/" & day(DtReg) & "/" & yearReg
       
    End Function
   
    function concatAllData( strSchoolName, strAddress, strAddress2, _
                                strCity,strState,strZip, _
                                strCounty,strChapter,strCountry, _
                               strTeam, strIndividuals, _
                               strCoach, strCoachPhone, _
                               strCoachEmail, strPrincipal, strSchoolPhone,strCustomer, _
                               strDate, strYrs)   
                          
        g_strFileContent = g_strFileContent &  """" _
                           & strSchoolName & """,""" _
                           & strAddress & """,""" _
                           & strAddress2 & """,""" _
                           & strCity & """,""" _
                           & strState & """,""" _
                           & strZip & """,""" _
                           & strCounty  & """,""" _
                           & strChapter & """,""" _
                           & strCountry  & """,""" _
                           & strTeam & """,""" _
                           & strIndividuals & """,""" _
                           & strCoach & """,""" _
                           & strCoachPhone & """,""" _
                           & strCoachEmail & """,""" _
                           & strPrincipal & """,""" _
                           & strSchoolPhone & """,""" _
                           & strCustomer & """,""" _
                           & strDate & """,""" _
                           & strYrs & """" & vbcrlf   
   
    end function
   
    function GenerateCsvFile(StringToWrite ,CsvFile)
   
        const TEMP_FOLDER = "_temp"
        dim  objFSO
        dim  strFilePath
        dim  CsvFilePath   
        dim  objTextStream
        dim strFolderPath
       
        set objFSO = Server.CreateObject("Scripting.FileSystemObject")
       
        'if objFSO.FolderExists(TEMP_FOLDER ) then
            strFilePath = Server.MapPath(TEMP_FOLDER)
            CsvFilePath = strFilePath & "\" & CsvFile
    '        Response.Write "<br>csvFilePath1 = " & CsvFilePath
    '    else
    '        strFolderPath = server.MapPath(Request.ServerVariables("PATH_INFO"))
    '        Response.Write "<br>strFolderPath = " & strFolderPath
    '        strfolderpath = strfolderpath & "\" & TEMP_FOLDER
    '        objFSO.CreateFolder(strFolderPath)
    '        strFilePath = Server.MapPath(TEMP_FOLDER)
    '        CsvFilePath = strFilePath & "\" & CsvFile
    '        Response.Write "<br>csvFilePath2 = " & CsvFilePath
    '    end if
        set objTextStream = objFSO.CreateTextFile(CsvFilePath,true)
        'Response.Write "<br>csvFilePath = " & CsvFilePath
        '''write the text to the csv file
        call objTextStream.write(StringToWrite)
       
        call objTextStream.close
        set objFSO = nothing
        set objTextStream = nothing
end function
   
function GetCity(City_state_Zip)
        dim i
        i = instrrev(City_state_Zip," ")
        i = i - 4
       
        if i > 0 then
            GetCity = left(City_state_Zip,i)
        end if   
End function
%>
<CENTER><Strong>Search Criteria</Strong>
                  (School: <Strong>
                  <%
                    if trim(txtSchool) = "" then
                        response.write "None"
                    else
                        response.write txtSchool
                    end if
                  %></Strong>&nbsp;|&nbsp;
                City: <Strong>
                  <%
                    if trim(txtCity) = "" then
                        response.write "None"
                    else
                        response.write txtCity
                    end if
                  %></Strong>&nbsp;|&nbsp;
                State:  <Strong>
                  <%
                    if trim(txtState) = "" then
                        response.write "None"
                    else
                        response.write txtState
                    end if
                  %></Strong>&nbsp;|&nbsp;
                Zip: <Strong>
                  <%
                    if txtZipStart = "" and txtZipEnd = "" then
                        response.write "None"
                    elseif txtZipStart <> "" and txtZipEnd = "" then
                        response.write txtZipStart
                    elseif txtZipStart <> "" and txtZipEnd <> "" then
                        Response.Write txtZipStart & " to " & txtZipEnd
                    end if
                  %></strong>&nbsp;|&nbsp;
                Sort By: <Strong>
                  <%
                    if strSortBy = "Zip" then
                        response.write "ZipCode"
                    elseif strSortBy = "School_Name" then
                        response.write "School Name"
                    elseif    strSortBy = "City" then
                        Response.Write "City"
                    end if
                  %>
                )
                </CENTER>
                <CENTER>There are <B><%=totalRecord%></B> matching records.