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
Mark LewisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
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

0
Mark LewisAuthor Commented:
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
0
sammySeltzerCommented:
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.






0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sammySeltzerCommented:
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


0
sammySeltzerCommented:
Oh man another stupid mistake.

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

should be

<OPTION Value="<%=variableID %>" <%=sel%> > <%=ResultsSet("pri_atty")%>

I have a very long and tiring day at work today.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark LewisAuthor Commented:
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.
0
Mark LewisAuthor Commented:
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

0
Mark LewisAuthor Commented:
Not the exact solution by led me out of the dark tunnel
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.