refreshing the page while populating the drop down from the database

[pinkcat_02]

As whammy suggested I am posting my problem into a new thread:


This is the next part of my other thread which is populating drop down menu from database.

As raf suggested me a way of achieving this task it was posting the page to itself for it to allow to write the selected value to the server so that it can use this value to populate the second drop down menu but

When I have 2 drop down menus and a result depending on the values of the first and the second drop down menu it fails to get the data that the user has choosen as the pages refeshes itself and the value of the first drop down menu goes back to the it's own value rather than the value that user has selected.

This refreshing gave me enough headaches it works fine when the drop downs just get the values of the one above it but fails when the third sql needs the value from the first and the second drop downs.

Any suggestions?

Thanks

[raf]

kinda cryptic description of the problem.

what exactly goes wrong ? Can you post the code you're using here ?
I can't see why this shouldn't work + i don't understand what you're trying to say about the "refreshing of the page"

As far as i remember, you should only replase the response.redirect in my code, with the code to run the sql and display the results. So, use the if then - control of flow on top of the page to determine what actions need to be run (when the page is processed for the third time.

[skalag]

Im not sure what you mean by this, i have similar set up, with 4 select options in a frameset, they pass the values okay for me, the only problem i get with the results displaying is that i tried 'paging the recordset' of results to 1 record per page which did not work correctly as i think was gretting a blank value for the Request.Form("SELECTOPTIUONVALUE") when i tried to view the next record, this i think is because the previous form is only posted for the first time and consequently my sql statement selects all from that field as i have not written any code to redirect or stop if a value has not been sent, so instead i just displayed all the recordset (4 main fields) down the page and put an HREF on the id field to a separate display page to look at all the details of that selection and it works fine...i dont have problems with the page refreshing itself, you can have a look at this if you wish, maybe the good souls on this Forum will point us both in the right direction to making this more secure, ie. error trapping and preventing user misuse which i dont know much about...

[pinkcat_02]

<%
if Len(request.form("Staff_Position")) > 0 then
%>
<%
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdPopulateName = Server.CreateObject("ADODB.Recordset")
%>
<%
DataConn.Open "DBQ=" & Server.Mappath("\anticus\db\deneme1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

sql="select Staff_Name from Staff where Staff_Position='aname' and Branch_Code='bname'"
sql=replace(sql,"aname",request.form("Staff_Position"))
sql=replace(sql,"bname",request.form("Branch"))
CmdPopulateName.Open SQL, DataConn
%>


<Select Name="Staff_Name" size="1" >
<%While Not CmdPopulateName.EOF%>

<option value="<%= CmdPopulateName("Staff_Name") %>"><%= CmdPopulateName("Staff_Name") %></option>

<%
CmdPopulateName.MoveNext
Wend
%> <%

CmdPopulateName.Close
Set CmdPopulateName = Nothing
DataConn.Close
Set DataConn = Nothing

%>
<input type="Submit" value="Submit" >

</Select>

<%else %>

<%
' Not neccesary but a good habit
Dim DataConn
Dim CmdPopulateBranch
Dim CmdPopulatePos
Dim CmdPopulateName
Dim SQL
Dim CURRENT_STATE_NAME
%>
<%
CURRENT_Branch_Code = ""
%>

<%
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdPopulateBranch = Server.CreateObject("ADODB.Recordset")
%>

<%

DataConn.Open "DBQ=" & Server.Mappath("\anticus\db\deneme1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

SQL = "SELECT DISTINCT Branch_Code FROM Staff"
CmdPopulateBranch.Open SQL, DataConn
%>
<form method="POST" action="staff.asp">
<Select Name="Branch" size="1">

<%While Not CmdPopulateBranch.EOF%>

<option <% If CURRENT_Branch_Code = CmdPopulateBranch("Branch_Code") Then %>
<% Response.Write(" selected ") %><% End If %>value="<%= CmdPopulateBranch("Branch_Code") %>"><%= CmdPopulateBranch("Branch_Code") %></option>

<%
CmdPopulateBranch.MoveNext
Wend
%>

<%
' Not neccesary but a good habit
CmdPopulateBranch.Close
Set CmdPopulateBranch = Nothing
DataConn.Close
Set DataConn = Nothing
%>

<input type="Submit" value="Submit" >

</Select>

<%
if Len(request.form("Branch")) > 0 then
%>

<%
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdPopulatePos = Server.CreateObject("ADODB.Recordset")
%>


<%
DataConn.Open "DBQ=" & Server.Mappath("\anticus\db\deneme1.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

sql="select DISTINCT Staff_Position from Staff where Branch_Code='aname'"
sql=replace(sql,"aname",request.form("Branch"))

CmdPopulatePos.Open SQL, DataConn
%>


<Select Name="Staff_Position" size="1" >
<%While Not CmdPopulatePos.EOF%>

<option value="<%= CmdPopulatePos("Staff_Position") %>"><%= CmdPopulatePos("Staff_Position") %></option>

<%
CmdPopulatePos.MoveNext
Wend
%> <%

CmdPopulatePos.Close
Set CmdPopulatePos = Nothing
DataConn.Close
Set DataConn = Nothing
%>
<input type="Submit" value="Submit" >
<%
else
end if %>
</Select>

<%end if %>
</form>


What I mean is like the last action I put at the top doesn't read the right value, it ends up selecting the first value from the first drop down (not the one user has selected but value=1 in the list) and read the value the user selected from the second drop down which at the end gives the wrong result.

Because what I really try to do is get the value that the user selected from the first, get the value rhe user selected from the second drop down and do the last thing with those 2 values the user has selected.

Thanks

[raf]

i think that the "good souls" need some more material to work on (even God needed some clay)

skalag,

i can't quite understand what your post means (it contains the longest sentence i've read here though)
if you have trouble about paging, run a search on this forum. there have been quite some threads about that issue here.
you can have a look at this if you wish,
Well, bring it on ! (Were can we look at it).
I always use that (displaying all records with alternating backgrounds and as a link with the ID in the querystring), since paging is nice, but i don't like clicking myself, and after 2 screens of paging, i quite. Scrolling is far better and faster (= personal opinion)

[pinkcat_02]

that can't be for me is it raf?

[raf]

nope. are posts crossed eachother. I'll look into your code.

[raf]

about halfway your code, youve got this line


CURRENT_Branch_Code = ""

a few lines later, you have

<option <% If CURRENT_Branch_Code = CmdPopulateBranch("Branch_Code") Then %>
<% Response.Write(" selected ") %><% End If %>value="<%= CmdPopulateBranch("Branch_Code") %>"><%= CmdPopulateBranch("Branch_Code") %></option>

now, this CURRENT_Branch_Code will always be empty and no option will ever be selected.

changing that line to
CURRENT_Branch_Code = request.form("branch")
should fix the first dropdown-options problem.

In your code, i see you're building a third dropdown (depending on the values the user selected in the first two. Well : you'll then have to put this dropdown inside the form (so the form-tag should be moved higher in the code (beore the select-tag). Otherwise, the value from this dropdown will not be in the form-collection

If you use this third dropdown, then you'll have te place another condition on to of the page, that checks if there is a value for this third dropdown (just like you have one now for the second dropdown.) So you'll need an extra if-then-else clause.

Does this makes any sense to you ?

[skalag]

RAF -
I mean that i used 4 select boxes down a framset in 4 frames and did not get a problem like described...it is very basic and i did not try to enforce user selection by IF/ELSE Request.form=""(so every select box used resources by populating AUTO-but this was just to test it works)

pinkcat-
I thought your error was like in my paged records attempt where my sql statement

strSQL="SELECT * FROM VEHICLE WHERE Model LIKE '%" & Request.Form("Hmodel") & "%' "

When i clicked to view next record i think Request.Form("Hmodel") & "%' " became = to " " cos the database then displayed all records, i think this is like the page refreshing and the values being lost with you..

anyway this my working model, needs a hell of a lot more work like enforcing certain user actions and saving on resources but it works fine like this


Code ----------------------



(my first Frame-Fcarsearch2- looks like this)



<HTML>
<head>
<title>carsearchmake</title>
</head>

<BODY bgcolor="#CCCCCC" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">

<FORM action="carsearchmodel.asp?" method="post" name="form" target="Fcarsearch3">

<TABLE width="100%" border="0" cellspacing="0" cellpadding="0" height="100%" align="center" bgcolor="#336699">
<TR valign="top">
<TD height="142" valign="top">

<FONT size="2" color="#FFFFFF" face="Arial, Helvetica, sans-serif"><B>Select
Search Parameters</B></FONT> <BR>
<BR>

<SELECT name="Smake" onchange="this.form.submit();">
<OPTION disabled>Make</OPTION>

<%
Set conn = Server.CreateObject("ADODB.connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/database/MULLANS2000.mdb") & ";" &_
"Persist Security Info=False"
conn.Open(strConn)

strSQL="SELECT DISTINCT Make FROM VEHICLE"

set rs= conn.execute(strSQL)


If Not rs.EOF Then
rs.MoveFirst


Do While Not rs.EOF
Response.Write "<option value="""
Response.Write Trim(rs.Fields("Make"))
Response.Write """"
Response.Write ">"
Response.Write Trim(rs.Fields("Make"))
Response.Write "</option>" & vbCrLf


rs.MoveNext
Loop

%>
</SELECT>
<%
END IF
rs.Close
Set rs = Nothing
conn.close
%>
<INPUT type="submit" name="Submit" value="Submit">
</TD>
</TR>
</TABLE></FORM></BODY></HTML>


(my second frame Fcarsearch3 looks like this)

<% @Language="VBScript" %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<% Response.Buffer = True %>


<%

Dim strSQL
Dim rs
Dim strconn
Dim conn
Dim strselectvalue

%>

<HTML>
<head>
<title>carsearch3</title>
</head>

<BODY bgcolor="#CCCCCC" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">

<FORM action="carsearchcolor.asp?" method="post" name="form" target="Fcarsearch4">
<TABLE width="100%" border="0" cellspacing="0" cellpadding="0" height="100%" align="center" bgcolor="#336699">
<TR valign="top">
<TD height="142" valign="top">
<SELECT name="Smodel" onchange="this.form.submit();">
<OPTION disabled>Model</OPTION>
<%
Set conn = Server.CreateObject("ADODB.connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/database/MULLANS2000.mdb") & ";" &_
"Persist Security Info=False"
conn.Open(strConn)



' a basic attempt to stop select boxes populating on frameset page load, i think should be changed to if "" or "make" redirect else if "no selection" (an option to be added) then populate..i havent worried about this yet

IF Request.Form("Smake") = "" Then
strSQL="SELECT StockNo FROM VEHICLE"
set rs= conn.execute(strSQL)
ELSE



strSQL="SELECT DISTINCT Model FROM VEHICLE WHERE Make LIKE '%" & Request.Form("Smake") & "%' "

set rs= conn.execute(strSQL)


If Not rs.EOF Then
rs.MoveFirst


Do While Not rs.EOF
Response.Write "<option value="""
Response.Write Trim(rs.Fields("Model"))
Response.Write """"
Response.Write ">"
Response.Write Trim(rs.Fields("Model"))
Response.Write "</option>" & vbCrLf

' Move to next record
rs.MoveNext
Loop

%>
</SELECT>
<%
END IF
END IF
rs.Close
Set rs = Nothing
conn.close
%>
<INPUT type="hidden" name="Hmake" value="<%response.write Request.form("Smake")%>">
<INPUT type="submit" name="Submit" value="Submit">
</TD>
</TR>
</TABLE>
</FORM>
</BODY></HTML>


(2 more like this then display page like this)

<%
Dim strSQL
Dim rs
Dim conn
Dim Tblrow
Dim TblBgcolor


Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/database/MULLANS2000.mdb") & ";"
Set rs = Server.CreateObject("ADODB.Recordset")


IF Request.Form("Hmake") = "" Then
strSQL="SELECT StockNo FROM VEHICLE"
set rs= conn.execute(strSQL)
ELSE


strSQL="SELECT Model, Make, Year, Colour, NewVehicle, Retailprice, Doors, Extras, Gears, StockNo FROM VEHICLE WHERE Model LIKE '%" & Request.Form("Hmodel") & "%' " & "AND Make Like '%" & Request.Form("Hmake") & "%' " & "AND Colour Like '%" & Request.Form("Hcolour") & "%' " & "AND Year Like '%" & Request.Form("SYear") & "%' "


set rs= conn.execute(strSQL)

%>



<HTML>
<head>
<title>carsearch6</title>
</head>

<BODY bgcolor="#CCCCCC" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">

<%

Tblrow = "0"
TblBgcolor="blue"
response.write("<table border='1' bordercolor='white' cellpadding='1' align='center' cellspacing='0' width='100%'>")
response.write("<tr bgcolor='#3333ff'><td>N / U</td><td>Make</td><td>Model</td><td>Colour</td></tr>")

DO WHILE NOT rs.EOF


if Tblrow MOD 2 = 0 then
response.write("<tr bgcolor='#CCCCCC'>")
else
response.write("<tr bgcolor='#CCCC99'>")
end if



'set href to wherever for displaying more details############

response.write("<td><a href=adminedit.asp?recid=" &rs.Fields("StockNo") & ">" & server.HTMLEncode (rs.Fields("StockNo")) &"</a></td>")
response.write("<td>" & server.HTMLEncode(rs.Fields("Model")))
response.write("<td>" & server.HTMLEncode(rs.Fields("Make")))
response.write("<td>" & server.HTMLEncode(rs.Fields("Colour")))





Tblrow = Tblrow +1
rs.MoveNext
loop
response.write("</td>")
response.write("</tr>")
response.write("</table>")
END IF
rs.Close
Set rs = Nothing
conn.close
%>
</BODY>
</HTML>

[raf]

Pinkcat_02 didn't want to use frames. (it was on the previous thread). That was also my first sollution since it is the most simple and elegant sollution

about your paging problem. Yeah, well. You need that value buth when you post an empty form ...

You could always include the value in the querystring of the action tag, when you build the page that displays the results.
like

action="carsearchmodel.asp?<%request.form("variable")%>"

this way, when the form is posted, the value is dragged along. On the next page, you can get this value from the querystring, and add it to the form of the third page etc

action="carsearchmodel.asp?<%request.querystring("variable")%>"

but there are a lot of tutorials on the subject (that use more efficient code). Like
http://www1.overture.com/d/sr?xargs=02u3hs9yoaRSssNT85tSUooCs/uTWsBLEwQLiovyC4PTEvMw9dUchICyosS8myLU3NB00zclJTSotTixCLsG51NDMytjBxNHE0NDZ1MoYzwjgtRDE5Py/Qr6gWAUcaYl3EfEgRiJkhqbkGtuBg7GZhYuhuaWqs4gAGlInmOA==
(short and comprehensive)

[pinkcat_02]

raf,

thanks the problem was setting to current branch to "" now i sorted that out so it gives the right value.

Thanks again.:thumbsup:

[raf]

you're welcome.

whammy is right in one of your other posts: you're getting ahead :thumbsup: but your code isn't realy elegant and efficient. Don't worry to much about that though. If you try to learn this on your own, this is bound to happen.

If you take the time to read a good book (or some on-line tutorials) on ASP and/or VBscript, your coding will improve faster + you'll learn more about the build in functionalitys of asp (and lotts a problems will disappear):)

also: read some stuff on database-design and sql. It's essential you realy master that. Coding on a good designed database is so much easier.

if you realy master this multipages stuff, you're gonna get there. (and then you'll probably get into some other languages;) )

i absolutely don't want to slow or put you down and i think youre making great progress. but believe me, take the time now to really master the basics and you'll be going faster.

in the meanwhile: were here to help you out