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.CreateObje ct("ADODB. Connection ")
myDSN ="Provider=Microsoft.Jet.O LEDB.4.0;D ata Source=" & Server.Mappath("../../fpdb /My_Databs e.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("Variab le1") Then Response.Write(" Selected") End If
Response.write(">"&arrName (0,i)&"</o ption>")
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
<%
Set conntemp=Server.CreateObje
myDSN ="Provider=Microsoft.Jet.O
conntemp.open myDSN
mySQL = "SELECT DISTINCT ColumnName FROM TableName"
Set myRS = Server.CreateObject("ADODB
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("Variab
Response.write(">"&arrName
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
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.CreateObje ct("ADODB. Connection ")
myDSN ="Provider=Microsoft.Jet.O LEDB.4.0;D ata Source=" & Server.Mappath("../../fpdb /??????.md b")
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
<%
Set conntemp=Server.CreateObje
myDSN ="Provider=Microsoft.Jet.O
conntemp.open myDSN
mySQL = "SELECT DISTINCT ?????? FROM ??????"
Set myRS = Server.CreateObject("ADODB
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:
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.
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
'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>
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
%>
ASKER
Not the exact solution by led me out of the dark tunnel
Use this
Open in new window
in place of your code below
Open in new window