Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2002
    Location
    Florida, USA
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting query from option selected

    Hello all,

    I'm having some trouble figuring this one out. I'm trying to make a form for my boss to use. Initially I'm pulling a list of names from an Access DB, populating a select field with the employee names, then she would pick a name, submit it, and the rest of the form would be populated. I'm stuck on getting the selected field from the select input.

    Any help would be greatly appreciated.

    Here's the code I'm using.

    PHP Code:
    <form action="<%=Request.Servervariables("URL")%>?fLookup=TRUE"  method="post">

    <
    div align="right"><h1>UTILITIES COMMISSION</h1></div>

    <
    table width="750" border="0">
      <
    tr>
        <
    td class="bighead">Employee Performance Review Hourly Employees</td>
      </
    tr>
    </
    table>
    <
    table width="750" border="0">
      <
    tr>
        <
    td align="center" bgcolor="#000000" class="littlehead">Employee Information</td>
      </
    tr>
    </
    table>
    <% 
        
    'Dimension variables
        Dim adoCon         '
    Holds the Database Connection Object
        Dim rsEval         
    'Holds the recordset for the records in the database
        Dim strSQL         '
    Holds the SQL query to query the database

        Dim EmpNo
        Dim EmpJob
        Dim EmpDept
        Dim EmpSuper
        Dim EmpHired
        Dim EmpEvalDate

        EmpNo 
    ""
        
    EmpJob ""
        
    EmpDept ""
        
    EmpSuper ""
        
    EmpHired ""
        
    EmpEvalDate ""

        'Create an ADO connection object
        Set adoCon = Server.CreateObject("ADODB.Connection")

        '
    Set an active connection to the Connection object using a DSN-less connection
        adoCon
    .Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" Server.MapPath("employeesdb1.mdb")

        
    'Create an ADO recordset object
        Set rsEval = Server.CreateObject("ADODB.Recordset")
    %>

    Lookup Check...
    <%
    IF Request.QueryString("fLookup") = "TRUE" THEN
        Response.Write("Lookup is True...")
        IF Request.QueryString("EmployeeName") <> "" THEN
            Dim NameLookup
            NameLookup = Request.QueryString("EmployeeName")
            strSQL = "SELECT * FROM [All BU] WHERE (FName + ' ' + MInitial + ' ' + LName) = '"&NameLookup&" " 
    The above line fails horribly.
    PHP Code:
            Response.Write(strSQL)
            
    rsFinal.Open strSQLadoCon
            EmpNo 
    rsFinal("EmployeeNo")
            
    EmpJob rsFinal("JobTitle")
            
    EmpDept rsFinal("Department")
            
    EmpSuper rsFinal("Supervisor")
            
    EmpHired rsFinal("HireDate")
            
    EmpEvalDate rsFinal("Date of Eval")
            
    rsFinal.Close
            Set rsFinal 
    Nothing
        
    ELSE
            
    Response.Write("Failed Query")
        
    END IF
    END IF
    %>
    <
    table width="750" border="0">
      <
    tr>
        <
    td width="250">Name of Employee Being Reviewed:</td>
        <
    td>
            <
    select name="EmployeeName">
                <
    option value=""></option>
                <%
                    
    strSQL "SELECT * FROM [All BU] ORDER by LName"
                    
    rsEval.Open strSQLadoCon
                    
    Do While NOT rsEval.EOF
                        Response
    .Write ("<option value=")
                        
    Response.Write (rsEval("FName"))
                        
    Response.Write ("&nbsp;")
                        
    Response.Write (rsEval("MInitial"))
                        
    Response.Write (rsEval("LName"))
                        
    Response.Write (">" rsEval("FName") & " " rsEval("MInitial") & " " rsEval("LName") )
                        
    Response.Write ("</option>")
                        
    rsEval.MoveNext
                    Loop
                
    %>
            </
    select>
            <%
                
    strSQL "SELECT EmployeeNo FROM [All BU] where string from [employee name] = employee being reviewed" 
    The above and below lines are where I'm stuck getting the lookup value from the select box. The line directly above is just a little pseudo code so I remember what I'm trying to do.
    PHP Code:
                rsEval.Open strSQLadoCon
            
    %>
            <
    input name="empID" type="hidden" value="<%=empID%>">
        <
    td><input name="emplookup" type="submit" value="Go"></td>
        </
    td>
        <
    td width="120">Employee ID:</td>
        <
    td><input name="EmpNo" type="text" readonly="true" value="<%=EmpNo%>"></td>
      </
    tr>
    </
    table>
    <
    table width="750" border="0">
      <
    tr>
        <
    td width="80">Job Desc:</td>
        <
    td><input name="JobTitle" type="text" readonly="true" value="<%=JobTitle%>"></td>
        <
    td>Dept:</td>
        <
    td><input name="EmpDept" type="text" readonly="true" value="<%=EmpDept%>"></td>
        <
    td>Supervisor:</td>
        <
    td><input name="EmpSuper" type="text" readonly="true" value="<%=EmpSuper%>"></td>
        <
    td>Review Period:</td>
        <
    td>&nbsp;</td>
        <
    td>to</td>
        <
    td>&nbsp;</td>
      </
    tr>
    </
    table
    Do I need to pre-populate the select box and use an array of some sort to get the lookup value?

    Thanks for any advice.

    Eric
    if ( bad && possible ) happen();
    Sig re-written for faster processing...

  • #2
    Regular Coder
    Join Date
    Nov 2002
    Posts
    567
    Thanks
    2
    Thanked 4 Times in 4 Posts
    I do the same thing at work but I use this;

    Code:
    Dimension variables, Get your connection, and stuff.
    
    By this point you have your connection and recordset for the select.
    
    <form name="BigForm" action="ThisPage.asp" method="Post">
    <select id="lstEmp" size="1" onChange="DoIt()">
        <% While Not RS.EOF %>
                        <option selected value=""></option>
                        <option value="<% =rs("empNo") %>"><% =rs("empName") %></option>
         <% Wend %></select>
         <% If Request.Form("lstEmp")<> "" Then
                   SQL="Select * FROM Employees WHERE EmpID=" & Request.Form("lstEmp")
                   DbConnection.Execute SQL
    
     If you have already been through you have a RS containing the employee, else you have an empty RS
    
         If NOT RS.EOF Then
    Put your form fields like inputs or just data values in here. 
    
          <td class="FieldName"><% =RS("EmployeeName" %>
    
           Etc, Etc.
           End If<%
    </form>
    I didn't have my files here at home, so it is from memory. The only problem I see is how the selected value is exposed in the Request.Form part.
    Scott Stewart
    Always happy to learn from pros.

  • #3
    New Coder
    Join Date
    Jul 2002
    Location
    Florida, USA
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I'm stumped.

    I've managed to sort through most of the initial problems with the form, posting the variables, etc -- I think, at least. Now I'm coming up with an unspecified error dealing with the adoCon.Open statement.

    PHP Code:
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" Server.MapPath("employeesdb1.mdb"
    This only happens when the page is submitted.

    PHP Code:
    Error Type:
    Provider (0x80004005)
    Unspecified error
    /website/eval/hourly.aspline 73 
    I've uploaded the entire page as a .txt file if I could talk anyone into taking a look at it.
    Goofy web page text file

    (my remote server isn't setup to handle ASP pages, only testing server and work server, so the page wont actually do anything at the above URL)

    Thanks for any help!

    Eric
    if ( bad && possible ) happen();
    Sig re-written for faster processing...

  • #4
    New Coder
    Join Date
    Jul 2002
    Location
    Florida, USA
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nevermind this. Apparently my IIS setup at home is fubar'd or something equally sinister. Works fine at work.
    if ( bad && possible ) happen();
    Sig re-written for faster processing...

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, that's cool. I will close this for now, please post with any other problems.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •