I am trying to filter a table that is coming directly from SQL Server. Here is the scenario:
I created a form in asp which list the contract number and estimate number and it's information (such as net amount, estimate amount, etc...). Each contract number can have more than one estimate number. For example, contract number S-111 can have 5 estimates. My mission for the past week has been to try and filter each contract no by estno. For instance if I search for S-111 I want the estno field to automatically show 1 through 5 on the drop down list. Here is the code for the estno drop down list. Any help pleaaaase let me know. Thank you very very much. :)

<td>
           <select name=estno>
           <%
               Set rsContr = server.CreateObject("adodb.recordset")
               SQL = "Select * tblEstimates"
               rsContr.open SQL, cn
               Do While Not rsContr.EOF
                   if rsContr.fields("estno") = estno then
                   %>
                      <option selected value='<%=rsContr.fields("estno")%>'><%=rsContr("e stno")%></option>
                   <%
                   else
                   %>
                      <option value='<%=rsContr.fields("estno")%>'><%=rsContr("e stno")%></option>
                   <%
                   end if

              rsContr.MoveNext
              Loop
              rsContr.Close

           'end if
           Set rsContr = Nothing
           %>
           </select>
        </td>


slypunk

__________________
slypunk

When you supply Contract Number why dont you use that in the SELECT statement directly to filter that out straightaway from the database?

May be your SELECT statement look like This would return the recordset with just the estno that pertains to the contractno that you passed/searching for. Then use that recordset to fill the listbox later.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

What do you mean by contractnumber_passed_as_variable?

slypunk

Ok great!! I got it to work! However, it is not display the correct information for each estimate number. Is there a reason to this? Thanks so much :)

slypunk

Does anyone know why it's not giving me the right information for each estimate number? Thank you

slypunk

Can you post the recent (changed) code relavant to that? Let me take a look at it.

_________________________
- Vijay G
Strive for Perfection

I got this to work I used a hidden variable and it worked now I am working on the search function which is giving me the Contract Not Found message.

function searchContract()
    ' Local Recordset
    dim rsLocal
    ' Search for a contract
    response.write "Ok now we are addding the estno check to the DISPLAY recordset"
    response.write "instead of adding it to the dropdown"
    response.write "LETS SEE IF WE ARE GOOD TO GO: " & strEstimateNum
    'response.end
    Set rsLocal = FindEstimateContracts(strContractNum, strEstimateNum)
    ' Check for end of file (eof)
    if strEstimateNum="" then
        sql= "Select * from tblEstimates where contno=strContractNum"
        sql= SQL & "order by estno"
        %>
        <script language=javascript>
            alert("Contract Number Not Found");
        </script>
        <%
    if strEstimateNum <>"" then
        FindEstimateContracts
        'same as below
        'response.write "<script language=javascript>alert('Project Number Not Found');</script>"
    else
        contno = rsLocal("contno")
        estno = rsLocal("estno")
        contractor = rsLocal("contractor")
        estamt = iif(isnull(rsLocal("estamt")), 0, rsLocal("estamt"))
        retainage=iif(isnull(rsLocal("retainage")), 0, rsLocal("retainage"))
        otherdeduct=iif(isnull(rsLocal("otherdeduct")),0, rsLocal("otherdeduct"))
        netestamt=iif(isnull(rsLocal("netestamt")), 0, rsLocal("netestamt"))
        amttodate=iif(isnull(rsLocal("amttodate")),0, rsLocal ("amttodate"))
        rsvdcontamt=iif(isnull(rsLocal("rsvdcontamt")),0, rsLocal("rsvdcontamt"))
        estdate=rsLocal("estdate")
        perenddate=rsLocal("perenddate")
        eststatus=rsLocal("eststatus")
        prelimrsvd=rsLocal("prelimrsvd")
        projmgrapproval=rsLocal("projmgrapproval")
        contractorsigned=rsLocal("contractorsigned")
        assistdirector=rsLocal("assistdirector")
        cmdchief=rsLocal("cmdchief")
        staffdone=rsLocal("staffdone")
        contreviewapproval=rsLocal("contreviewapproval")
        receiveddate=rsLocal("receiveddate")
        submittoap=rsLocal("submittoap")
        checkdate=rsLocal("checkdate")
        checkno=rsLocal("checkno")
        recfromap=rsLocal("recfromap")
        comments=rsLocal("comments")
        paiddate=rsLocal("paiddate")
        trackingnum=rsLocal("trackingnum")
    end if
    end if
    set rsLocal = nothing
end function

But it's giving me this message now:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I might be missing something which I do not know what it can be. Any suggestions?? THank you for your time

slypunk

Hi,
 i am sure that the record might have been deleted and you are
trying to access the same reccord.
And for filtering purpose after getting all the data from the server without using where clauseyou can use recordset.filter property..
syntax:

recordsetName.filter("ename") = "xxxx"

Thanks
Suresh

Are you missing an ampersand there? Check if this works as expected

_________________________
- Vijay G
Strive for Perfection