Link to home
Start Free TrialLog in
Avatar of Mark Lewis
Mark Lewis

asked on

Need help with code on a web page

I have written a series of pages that pulls a list of names from an Access database for a pull down box on the initial entry form. The problem is getting that same form to list the names when I update the record. In a different web page I used the code below to return the list in a dropdown but the dropdown would also default to the selected value.

<%
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../../fpdb/My_Databse.mdb")
conntemp.open myDSN
mySQL = "SELECT DISTINCT ColumnName FROM TableName"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
arrName=myRS.GetRows()
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>

<select size = "1" name= "Variable1">

<%
For i = 0 to Ubound(arrName, 2)
Response.write("<option value="""& arrName(0,i) &"""")
If arrName(0,i)=fp_rs("Variable1") Then Response.Write(" Selected") End If
Response.write(">"&arrName(0,i)&"</option>")
Next
%>
</select>

In the web page that's not working, I get my list but the drop down does not default to the value for variable1. I believe the reason for this is because on the page that works, the values for the drop down list are in the same table as the selected value where on the page that doesn't work, I need to pull the list from a different database, let alone a different table that where the data vaules are stored. I just dont know the fix.

To sum it up, I have two databases A & B wth tables 1 & 2. On my entry form, I am pulling names from A1  to a dropdown box and submitting those values to B2. When I go to update the record pulling values from B2, I need the update form to recreate the pulldown box pulling all the values from A1 again but defaulting to the value the record has in B2.

Dang, I hope this makes sense.
Thanks
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Hi,

Use this

     <%
               'Query your db to populate your database
		SQLstmt = "Select fieldname from yourtable" 'where condition if needed
	        set ResultsSet = conn.Execute(SQLstmt)
            %>
            <select name="Variable1">
            <%
             Do Until ResultsSet.EOF
              variableID = ResultsSet("fieldname")
             If variableID = Variable1 Then sel = "SELECTED" Else sel = "" 'Default to what is selected
            %>
            <OPTION Value="<%=Variable1%>" <%=sel%> > <%=ResultsSet("fieldname")%>
           <%
            ResultsSet.MoveNext
            Loop
            %>

          </select>
          

Open in new window

       
in place of your code below  
         
          <select size = "1" name= "Variable1"> 
	  
	  <% 
	  For i = 0 to Ubound(arrName, 2) 
	  Response.write("<option value="""& arrName(0,i) &"""") 
	  If arrName(0,i)=fp_rs("Variable1") Then Response.Write(" Selected") End If 
	  Response.write(">"&arrName(0,i)&"</option>") 
	  Next 
	  %> 
	  </select> 

Open in new window

Avatar of Mark Lewis
Mark Lewis

ASKER

I hate to ask this but can you walk me through the code? I am pulling data from a database called Case_Opening_Lit.mdb, table named Litigation, and field called pri_atty. This field holds the name of the attorney assigned from the entry form. This is the value I need the dropdown box to default to on the update page. The other database is called BB.mdb, table named INV, field called FULLNAME. This field has the complete list of all our people that needs to go into the dropdown list. I'm not sure which fields get plugged into what places. I put question marks where I need help.

<%
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../../fpdb/??????.mdb")
conntemp.open myDSN
mySQL = "SELECT DISTINCT ?????? FROM ??????"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
arrName=myRS.GetRows()
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>

 <%
               'Query your db to populate your database
 SQLstmt = "Select ?????? from ??????" 'where condition if needed
              set ResultsSet = conn.Execute(SQLstmt)
            %>
            <select name="??????">
            <%
             Do Until ResultsSet.EOF
              variableID = ResultsSet("??????")
             If variableID = Variable1 Then sel = "SELECTED" Else sel = "" 'Default to what is selected
            %>
            <OPTION Value="<%=??????%>" <%=sel%> > <%=ResultsSet("??????")%>
           <%
            ResultsSet.MoveNext
            Loop
            %>

          </select>


Thanks
If I am understanding you correctly, you want a code that queries your database called Case_Opening_Lit.mdb, table named Litigation, and field called pri_atty and populate a dropdown box with this attorney value, no?

If my assumption is correct, then does this table hold value of only ONE attorney?

If yes, why bother with dropdown?

Why not just query the table:

SQL = "select pri_atty from Litigation where pri_atty ='The Attoney value here' "
set RS=conn.Execute(SQL)

IF NOT RS.EOF Then
attorneyval = RS("pri_atty")
End If

Open in new window

'THen your table on the update page:

<table>
<tr>
 <td>Attorney</td>
</tr>
<tr>
 <td><input type="text" name="attorney" value="<%=attorneyval%></td>
</tr>
</table>

Open in new window


That's it.

Now, if the table called Litigation has more than one value in pri_atty fieldname but you just want the value to default to some value, then something I posted earlier, only slightly modified.

     <%
               'Query your db to populate your database
            SQLstmt = "Select pri_atty from Litigation where pri_attorney <> 'the default value'
   set ResultsSet = conn.Execute(SQLstmt)
            %>
            <select name="Variable1">
             <option value="the default value">the default value</option>            <%
             Do Until ResultsSet.EOF
              variableID = ResultsSet("fieldname")
             If variableID = Variable1 Then sel = "SELECTED" Else sel = "" 'Default to what is selected
            %>
            <OPTION Value="<%=Variable1%>" <%=sel%> > <%=ResultsSet("fieldname")%>
           <%
            ResultsSet.MoveNext
            Loop
            %>
          </select>

Notice that I manually created the default value in              
<option value="the default value">the default value</option>

Also notice that I set a filter in the where clause so it doesn't get populated twice in your dropdown although it doesn't matter. You can remove the where clause.

As for the first part of your code, can explain what you expect that code to do?

This way I know how to respond.

Also, if you are still unclear about what my code and explanation are doing, then you probably haven't explained it well.

Break down or ask specific question and I will be glad to assist.






Also, forgot to mention that that this line

variableID = ResultsSet("fieldname")

should be:
variableID = ResultsSet("pri_atty")

And this line:
<OPTION Value="<%=Variable1%>" <%=sel%> > <%=ResultsSet("fieldname")%>

should be
<OPTION Value="<%=Variable1%>" <%=sel%> > <%=ResultsSet("pri_atty")%>

s


ASKER CERTIFIED SOLUTION
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, I will give it a go.

To explain what I'm doing: a user on the initial form has a drop down list of all attorneys pulled from the FULLNAME field in BB.mdb. They will choose a person, say John Doe. "John Doe" is enetered into a field calle pri_atty in Case_Opening_Lit.mdb with tons of other info. Later on, someone else may want to update that atty on that record. When they go to the update page and they see the dropdown box for the primary attorney, I need the dropdown to show the person (John Doe) that's on that record. But I also need that drop down box to go back to BB.mdb and grab all the other names so the user can change the drop down and submit a different name and change the record.
I got it to work using this code. Your code helped me understand what was going on. I just needed to pull the selected item out of the code. I dont mind that the selected guy appears in the list twice.

 
<%
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../../fpdb/BB.mdb")
conntemp.open myDSN
mySQL = "SELECT DISTINCT FULLNAME FROM INV WHERE (JOB =  'Associate' OR JOB =  'Partner') AND (Fullname <> 'None') ORDER BY FULLNAME ASC"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
arrAttyName=myRS.GetRows()
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>

<select size = "1" name= "pri_atty"> 
<option selected><%=FP_FieldHTML(fp_rs,"pri_atty")%></option>

<% 
For i = 0 to Ubound(arrAttyName, 2) 
Response.write("<option value="""& arrAttyName(0,i) &"""") 
Response.write(">"&arrAttyName(0,i)&"</option>") 
Next 
%>

Open in new window

Not the exact solution by led me out of the dark tunnel