Pass Value in Drop Down List 2

[DebbieC]

I have a form that has a drop down list that I am getting the values of from the database. It also runs a query when opening to list all entries in the database. Now I need to send the value of one of the items selected in the drop down to narrow down the list using a "Go" button. I had it working so that it passed the value and returned the right records but now it's not working. I also need it to keep the previously selected item from the drow down list.

This is what I have:

Set objConn = Server.CreateObject("adodb.Connection")
objConn.ConnectionString = CONN_STRING
objConn.CursorLocation = 3
objConn.Open

Set objRs = Server.CreateObject("adodb.Command")
objRs.ActiveConnection = objConn
objRs.CommandType = &H0004
objRs.CommandText = "stoSelectJurisdiction"

Set objRs = objRs.Execute




Set objConn2 = Server.CreateObject("adodb.Connection")
objConn2.ConnectionString = CONN_STRING
objConn2.CursorLocation = 3
objConn2.Open

Set objRs2 = Server.CreateObject("adodb.Command")
objRs2.ActiveConnection = objConn
objRs2.CommandType = &H0004
objRs2.CommandText = "stoSelectJurAccount"

objRs2.Parameters.Append _
objRs2.CreateParameter("JurID", adInteger, adParamInput, 4, JurID)

Set objRs2 = objRs2.Execute ' Execute stored procedure

<form method="post" action="JurAcct.asp?JurID=<%= JurID %>" name="frmServiceRequest">


<Select Name="JurID">
<option value="All" selected> All</option>

<% do while not objRs.EOF

If objRs("Organization") <> "" Then

If objRs("JurID") = Session("select" & JurID) Then
%>

<option value="<%= objRs("JurID") %>" selected><%= objRs("Organization") %>
</option>

<%
Else
%>

<option value="<%= objRs("JurID") %>"><%= objRs("Organization") %>

</option>

<%
End If

Else
End If

objRs.movenext()
loop
response.write "</select>"

%>

</select>&nbsp;&nbsp;

<input type=submit value="Go" id="btnGo" name="submit"></td>

<input type="hidden" name="JurID" value="<%= JurID %>">


Any suggestions?

 

[FesterSXS]

Where do you declare the variable JurID??

Tony
[red]_________________________________________________________________[/red]
Webmaster -

http://www.SpiraxSarco.com/

 

[DebbieC]

I'm sorry. I didn't put all of the code. I do have it declared. Here it is:

Dim JurID

JurID = Request.Querystring("JurID")

It works when I do "form method=get" but when someone selects another one in the dropdown list and click on the "Go" button again, then the JurID gets added to the end of the current querystring and it returns an error. So I want to pass the JurID to the "action" and call the same page so that it won't concatenate in the querystring. However it only passes the value of JurID with the "get" code. When I use "post" it says "Application uses a value of the wrong type for the current operation." When I do a Response.Write JurID it returns a blank value.

Any help would be appreciated.

 

[Sheco]

Why is the "All" hardcoded to be selected every time?

 

[ethorn10]

if you want to use "POST" on the form, then you need to retrieve the JurID like this:

JurID = Request.Form("JurID")

Querystring is only used for the "get" method.

 

[DebbieC]

To Sheco - I have "All" hardcoded because I want it to default to All when the page opens.

To ethorn10 - I am so embarrassed. I can't believe I missed that. I knew it was something simple and it was driving me crazy. However, even though it returns it correctly now there are still two problems. Even though it runs the query right and returns the right results, it doesn't put the JurID in the querystring. This is probably OK but the real problem is that if the user selects another one in the dropdown menu, it does put the JurID in but it puts a comma at the end of it and returns an error message.

Also, it still resets the list to show "All" instead of the one selected. I changed the code and took out the "selected" for the "All" option but it still does it. I must have something wrong in my If statement. Or maybe I shouldn't have the "All" option hardcoded but I don't know how else to do it when it is retrieving the other records from the database. If I don't have "All" then they can't get a list of all of them after they have selected one.

 

[ethorn10]

it doesn't put it in the querystring because you're now using "POST" which doesn't use the querystring. only using "GET" will put your values in the querystring. if you'd rather pass it in the querystring with the potential for multiple values, we can probably come up with something that'll work.

 

[steven290]

but it should put it on the query even with post, look

<form method="[red]post[/red]" action="[red]JurAcct.asp?JurID=<%= JurID %>[/red]" ...

 

[steven290]

is this

JurID = Request.Form("JurID")

on the top of your page

 

[ethorn10]

steven -- good catch. i didn't even notice her action property.

but if her form is using post and the action creates a querystring, what method of retrieval should she use? just request?

request("JurID")

?

 

[steven290]

yes i would use

request("JurID")

 

[steven290]

but is there a response.redirect somewhere on this page, sometimes people forget that

 

[DebbieC]

Yes, JurID = Request.Form("JurID") is now at the top of my page but it still doesn't work if they select a new one.

I thought about a "response.redirect" but I was afraid that it wouldn't keep the selected option when it returned the page. I just tried it and it's still doing the same.

Where would I use the request("JurID")? In the following:

<form method="post" action="JurAcct.asp?JurID=<%= Request("JurID") %>" name="frmServiceRequest">

This doesn't work either.

Or should I put it in the response.redirect?

 

[steven290]

do you have a live link, so we can we see what you mean, seems like a simple fix - can't get the handle

also, put this at top like this

JurID = Request("JurID")

and the form like this "JurAcct.asp?JurID=<%= JurID %>

 

[DebbieC]

I changed JurID = Request.Form("JurID") to JurID = Request("JurID") and I also changed the action and I get an error message saying the value is the wrong type so I changed it back.

Unfortunately it's on a development server so no one else can access it. I'll try to find an example on the web but in the meantime I will try to explain it a little bit better.

When the page is opened it displays a dropdown list that is populated from the database. The default it set at "All". Just below it, there is a table that displays all entries in a table in the database. The user needs to narrow down the selection by Jurisdiction. When they select one Jurisdiction from the dropdown list and click on the "Go" button, it's supposed to send the JurID to the stored procedure, select only those that have that JurID in the table and return a new list. I also want it to still show which Jurisdiction was selected so that they know which list it is. Then they need to be able to select another Jurisdiction and return another new list.

I know I'm close I just can't get it right.

I hope this helps to figure out the problem.

 

[ethorn10]

aaah -- throw an if statement before your dropdown.

.look for jurid to have a value
.if it does
     .set default of dropdown to jurid
.if not
     .set default of dropdown to all

 

[steven290]

b ut you are sort of doing that feature here

...
If objRs("JurID") = Session("select" & JurID) Then  

...

i don't know what the value of objRs("JurID") or Session("select" & JurID)

and are they matching

 

[DebbieC]

When I try either one:

If objRs("JurID") = Session("select" & JurID) Then

or

If objRs("JurID") <> "" Then

I get the same thing with both of these. It opens the page initially (before anything is selected) and the Jurisdiction that is displayed in the list is the last one on the list (instead of "All"). However it does return all of the records in the table below like it's supposed to. Then when I select one from the dropdown list to narrow down the search, it returns the right information down below but still lists the last one on the list instead of the one I selected. But at least now I don't get an error message when I select another one from the list. For JurID in the querystring it keeps showing JurID=45 which isn't even in the list.

Just to confirm what I have, this is the pertinent code:

<code>
JurID = Request.Form("JurID")

...
Call stored procedure for dropdown list of Jurisdictions

Call stored procedure for records for all Jurisdictions
...


<form method="post" action="JurAcct.asp?JurID=<%= objRs("JurID") %>" name="frmServiceRequest">


<Select Name="JurID">

<option value="0" selected> All</option>

<%
do while not objRs.EOF
If objRs("Organization") <> "" Then
If objRs("JurID") <> "" Then

%>
<option value="<%= objRs("JurID") %>" selected><%= objRs("Organization") %></option>

<% Else
%>

<option value="<%= objRs("JurID") %>"><%= objRs("Organization") %></option>
<%
End If Else End If

objRs.movenext()
loop
%>

</select>

<input type=submit value="Go" id="btnGo" name="submit"></td>


<tr>
<td width="82"><font face="Arial" size="2">&nbsp;<%= objRs2("DateReceived") %>&nbsp;</font></td>
<td width="78"><font face="Arial" size="2">&nbsp;<%= objRs2("Status") %>&nbsp;</td>
<td width="77"><font face="Arial" size="2">&nbsp;<%= objRs2("StatusDate") %>&nbsp;</td>
<td width="72"><font face="Arial" size="2">&nbsp;<%= objRs2("TestID") %></font></td>
</tr>

<%
objRs2.movenext()
loop

'Close connection
objConn2.Close
Set objConn2 = nothing
%>
</code>

 

[ethorn10]

you should use just the variable JurID and not the value from the database -- objRs("JurID") -- for your dropdown selection. the variable JurID is the value you selected whereas the objRs("JurID") will at best be the same value every time and at worse be some random value from the database.

 

[DebbieC]

OK. I changed it to If JurID <> "" Then and it does the same thing. I realized that it's finding a NULL value in the database so it's returning that one as the selected one.

I'm sorry but after looking at this code all day I'm having trouble concentrating on it and figuring out what I need to do.

Basically (I think) what I need to do is when the page originally opens JurID should have a value like "0" and then if that's the case, have the "All" selected. Or if it has another value, then make that one selected. Unfortunately I can't even get that to work. Am I on the right track?

Anyone have any more ideas?