Dynamic List box/drop downs

[jacob94]

Hello,
I have a view that displays the following information
ID NAME Selection
1 jason jason
1 john jason

The selection field gets updated from another page. they can chose jason or john and click submit; hence the above jason was the choice.

I simply need to create another drop down showing the selection (jason) as the default, then jason and john in the drop down...

Can someone help me with a small example. this has to be easy, but i just spent 4 hours searching and can't get it to work right. sorry to bother with something this simple...it is not for me....

 

[Sheco]

OK, go back and make a plain HTML page that shows what you want to show.

Once you've got that then the ASP should be easy.

 

[emozley]

1 thing I noticed was that both rows in the recordset have an identical value for ID. This means that whatever is posted from the form will be the same. Make sure the column in the database is set to Autonumber.

 

[jacob94]

sheco,
Thanks for the reply.

I went ahead and got it this far.

new db structure
gameid TeamID Selection
1 1 2
1 2 2
2 3 3
2 4 3


'Open the database.
call OpenDB()
sql = "select * from view"
set rs = DbConn.Execute(sql)
do while not rs.EOF

if rs.Fields("GameID").Value = 1 and rs.Fields("TeamID").Value = 1 and rs.Fields("Selection").Value = 1 THEN
strGameID1 = strGameID1 & "<option selected value=" & rs("TeamID") & ">" & rs("TeamName") & "</option>"
else
if rs.Fields("GameID").Value = 1 and rs.Fields("TeamID").Value = 2 and rs.Fields("Selection").Value = 2 THEN
strGameID1 = strGameID1 & "<option selected value=" & rs("TeamID") & ">" & rs("TeamName") & "</option>"
else
if rs.Fields("GameID").Value = 1 then
strGameID1 = strGameID1 & "<option value=" & rs("TeamID") & ">" & rs("TeamName") & "</option>"
end if
end if
end if

'Loop through the data.
rs.MoveNext
loop

set rs = nothing
dbconn.close


the strGameID1 is a variable used below to populate the dropdown box. I have other code that updates the boxes or the selection field. This code seems to work however I have to click REFRESH explorer so the dropdowns update the default selected value based on my choice.

How can I make the page refresh the default selected dropdown choice based on the slection field. It works if I refresh the web page after the update.

I also want to add a button that says continue, in which it will take me to another page (removing all the url parameters at the top of the page...)

 

[emozley]

Try:

<%
Do While Not rs.EOF
%>

<option value="<% = rs("TeamID") %>">
<% = rs("TeamName") %>
</option>

<%
rs.MoveNext
Loop

rs.Close
Set rs=Nothing
Set dbConn=Nothing
%>

 

[Sheco]

How can I make the page refresh the default selected dropdown choice based on the slection field. It works if I refresh the web page after the update

I'm not clear on the question. Are you asking about changing the selected item in one dropdown select box based on the user changing the selection in a different box?

 

[jacob94]

Sheco
My code above with the update statement works. After I click submit, the update from the dropdown box is recorded to the db. The dropdown box does not show the selected item until I hit refresh one time?

Box:
John
Jason

When I select Jason and click submit, it is record in the db but john still shows as the default selected. When I click refresh it updates the selected item to Jason?

Not sure why?

 

[jacob94]

emozley,
I need to have my if statement in there? How and where to I place your code, where the drop down boxes are or where I set the variables?

I am not sure I follow you...

 

[Sheco]

So you need an IF statement to put the word selected into your HTML <option> when the condition is correct.

Using emozley's code it might be something like this:
[TT]<%
Do While Not rs.EOF
%>
<option value="<% = rs("TeamID") %>"[red]
<% IF (ThisItemIsSelected) THEN Response.Write " Selected " %>[/red]
>
<% = rs("TeamName") %>
</option>
<%
rs.MoveNext
Loop

rs.Close
Set rs=Nothing
Set dbConn=Nothing
%>[/TT]

... except you insert your own logic where I put "ThisItemIsSelected

 

[jacob94]

Can you show me how to do it with the if statement I have above? I am still a little shaky on it?

 

[Sheco]

Under what conditions is an option selected?

Probably when the value that was just submitted is equal to the value in the <option> line that you are currently writing.

So something along the lines of:[TT]
IF Request.Form("MySelect") = rs("TeamID") THEN
Response.Write " Selected "
END IF[/TT]

... where MySelect is the name of your <select> form element.

 

[jacob94]

The drop down box should show the current selection based on the db selection field. There are always 2 choices and whichever one is in the selection field is the one that the drop down should default to.

 

[jacob94]

hello,
I spent another 6 hours on this last night. Please, can you try to follow my example and let me know why it fails and a soultion.

at the top of my asp page between <% %> I set all my variables.

'Open the database.
call OpenDB()
sql = "select * from view"
set rs = DbConn.Execute(sql)
do while not rs.EOF


if rs.Fields("GameID").Value = 1 THEN
strGameID1 = strGameID1 & "<option Selected value="
& rs("VisitorID") & ">" & rs("VisitorID") & "</option>"

strGameID1 = strGameID1 & "<option value=" & rs("HomeID") & ">" & rs("HomeID") & "</option>"
end if

'Loop through the data.
rs.MoveNext
loop

set rs = nothing
dbconn.close

I changed the DB yet again to work this. Now their is a gameid = 1 and a visitorID and HomeID field associated with it. In the example above, the visitorid is selected because I added Selected Value. I have a field called selection on the same record (GameID 1). I want to default to what is in that field. (would be either the VisitorID field or HomeID field). Please help me modify the ASP portion of the code above.

The HTML for portion simply reads the strGameID1 variable.

Please help, it just does not work any way I try this?

 

[emozley]

I think you are approaching it from the wrong angle. Let's look at it in pseudo-code:

First we create a recordset by executing the SQL query:

gameid TeamID Selection
1 1 2
1 2 2
2 3 3
2 4 3

Now a drop down list has 2 components - the underlying value that is submitted as part of the form (x) and the value that is displayed to the user .

eg <option value="x">y</option>

Now if we follow your code:

if rs.Fields("GameID").Value = 1 THEN
strGameID1 = strGameID1 & "<option Selected value="
& rs("VisitorID") & ">" & rs("VisitorID") & "</option>"

strGameID1 = strGameID1 & "<option value=" & rs("HomeID") & ">" & rs("HomeID") & "</option>"
end if

We look at the first row in the recordset and the GameID value is 1 so the code in the If...End If statement is executed.

We look at the 2nd row in the recordset and the GameID value is again 1 so the code is again executed. this gives a problem because the code generated then contains to <option selected> but obviously you can only have one value selected from a drop down list.

We look at the 3rd and 4th rows but the GameID value is 2 so the code in the If...End If statement is NOT executed. That is no more <option>...</option> HTML will be added to the variable strGameID1 and the drop down list will not be built.

So, I think it is far better to concentrate first on deciding what you want the underling value to be x, and what you want the value y to be for each line in the drop down menu code. In one line you are using rs("VisitorID") and the next you are using rs("HomeID") which leaves room for chaos.

Hope that is some help in getting you there - imagine your self as the webserver, step through your program and loops and write down the HTML with pen and paper and compare it with what you are trying to achieve. That way you will find it much easier to see where the logic is going wrong.

Once you've got the drop down list working we can concentrate on making sure the correct value is selected by default when the page loads.

Good luck!

 

[jacob94]

Ok I understand the first db table i posted was not good. I revised it:

gameid VisitorID HomeID Selection
1 RED BLUE RED
2 Orange Purple Orange
3 Grey Teal Grey
ETC ETC ETC ETC


With that in mind, please revisit my above post. Sorry for the confusion, but this is the table design I will stick with...

How do I make the selection (red) for GameID1 display as the default selected value.


I manually did it this way because I no the red team was the visitor, see below...I need to make it dependant off the selection field.
if rs.Fields("GameID").Value = 1 THEN
strGameID1 = strGameID1 & "<option Selected value="
& rs("VisitorID") & ">" & rs("VisitorID") & "</option>"

strGameID1 = strGameID1 & "<option value=" & rs("HomeID") & ">" & rs("HomeID") & "</option>"
end if

 

[emozley]

OK - if this is your recordset:

gameid VisitorID HomeID Selection
1 RED BLUE RED
2 Orange Purple Orange
3 Grey Teal Grey
ETC ETC ETC ETC

Which column(s) do you want to use in the drop down list?

We shall concentrate on building the list before anything else.

cheers

Ed

 

[emozley]

If I am right in understanding the HTML we need to generate is as follows:

<option value="red">RED</option>
<option value="blue">BLUE</option>
<option value="orange">ORANGE</option>
<option value="purple">PURPLE</option>
<option value="grey">GREY</option>
<option value="teal">TEAL</option>

And based on a value submitted to the page you want one of those options selected. Since you need all the values from the recordset displayed but you only need one selected you should have a loop which writes each line of those <option>'s and inside that loop check whether or not it needs to be selected.

The problem you have is that you want two options for each GameID in the drop down list. Let's say someone chooses GameID 2, would you want orange or purple to be selected as default in the list when the page loads?

 

[Sheco]

I imagine that the reason that it only works after you refresh is that the code for creating the HTML executes before the code for inserting the values into the database.

Giving the following assumptions:
* All games have exactly 2 players
* All games will have exactly one player named "Home" and one player named "Visitor"

The code for creating the HTML should look something like this:
[tt]
...

'Get recordset of all games:
Set rs = cn.Execute("SELECT * FROM GameResultsTable")

'Error checking on recordset:
IF rs.State <> 1 THEN
Response.Write "ADO recordset error"
Response.End
END IF

'Draw one HTML <select> for each game in table
Do While Not rs.EOF
%>
<BR>
Winner Game #<%= rs("gameid")%>
<select name='GameID_<%= rs("gameid")%>'>
<%
Response.Write "<option value='" & rs("VisitorID") & "' "
IF (rs("Selection") = rs("VisitorID")) THEN
Response.Write " Selected "
END IF
Response.Write ">" & rs("VisitorID") & "</option>" & vbCrLf

Response.Write "<option value='" & rs("HomeID") & "' "
IF (rs("Selection") = rs("HomeID")) THEN
Response.Write " Selected "
END IF
Response.Write ">" & rs("HomeID") & "</option>" & vbCrLf
%>
</select>
<BR>
<%
rs.MoveNext
Loop
...
[/tt]

... but it still requires that the proper values actually be inside the database... the other option is to create the HTML using the same Request.Form values used to update the database.

 

[jacob94]

emozley:
Their is a drop down box for each gameid. Lets stick to GameID 1 for now. I need VisitorID and HomeID to be the two selections in the drop down. The Selection field captures the choice of the user.

Does this clear up any confusion.

 

[jacob94]

sheco,
I am not sure your are following my example and I do appologize. Can we stick to my example? Let me know what else I can provide, at this point I am completly stuck.