The following is code from an asp page. This DOES work in establishing the session variable.

<%
'establish viewcode
Set RS = Conn.Execute ("SELECT * From User_rights WHERE username = '" & session("sUsername") & " ' ")
Do until RS.EOF
Vcode = RS("viewcode")
RS.MoveNext
loop
RS.close
Session("sViewcode") = Vcode
%>

I need help with the rest. I need to create a form that would allow the user to select/enter the next 2 variables, gOrder and gFilter, and then re-display the list based upon the user selection.

Here are some SQL samples trying to use the session variable that did NOT work:
a. SELECT * FROM v_currentrecipients WHERE viewcode = sViewcode
a. SELECT * FROM v_currentrecipients WHERE viewcode = :sViewcode:
b. SELECT * FROM v_currentrecipients WHERE viewcode = '&sViewcode'
c. SELECT * FROM v_currentrecipients WHERE viewcode = session("sViewcode")

I have not yet tried to incorporate the next 2 variables - but the final statement should look something like this (&gOrder indicates the value of gOrder):
SELECT * FROM v_currentrecipients WHERE viewcode = sViewcode AND &gOrder=gFilter ORDER BY &gOrder

Any suggestions?

Thank you!

Database Results Error
Description: [Microsoft][ODBC Visual FoxPro Driver]File 'request.prg' does not exist.
Number: -2147217865 (0x80040E37)
Source: Microsoft OLE DB Provider for ODBC Drivers

"SELECT * FROM v_currentrecipients WHERE viewcode = '"&session("sViewcode")&"'"

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

When I enter this into the DRW (Database Results Wizard), I get this message:

"The server encountered an error while processing a database request. For more information, click Details."

Then I tried to manipulate the HTML in the <!--webbot bot="DatabaseRegionStart" section (grey letters). The result in the <% section (red letters) was

fp_sQry="SELECT * FROM v_currentrecipients WHERE viewcode ='"

Notice it cut off the string after the &.

Then I tried to manipulte the string in the <% section (red letters) but Front Page would not allow it. I received this message:

"The contents of a Front Page component have been modified. These contents will be overwritten when you save this page."

I am using the DRW. Do you think this is asking too much from the DRW? If so, can I accomplish the same database results region without using the DRW? I am new to FrontPage so I would need pretty specific instuctions.

Thank you!!

www.outfront.net/spooky/adv_drw_diet.htm

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

"SELECT * FROM v_currentrecipients WHERE viewcode = '"&session("sViewcode")&"'"

But the string beyond the & is still falling off in the red letter section.

Any suggestions?

Thank you!!

Then you will be able to alter the red code that is left over.

http://www.outfront.net/spooky/adv_drw_diet.htm

Joe

To recap, the string that is working is:
fp_sQry="SELECT * FROM v_currentrecipients WHERE viewcode = '" &session("sViewcode")& "'"

Now I have been trying to incorporate the other 2 variables mentioned in the first post - and I'm stuck again.

I have a drop down menu for the user to select the order of output (gOrder) and a text box for the user to enter text (gFilter)to filter the output. I'm just trying to work with gOrder first. How can I add this to the string?

Here are some tries that are NOT working:
a. fp_sQry="SELECT * FROM v_currentrecipients WHERE viewcode = '" &session("sViewcode")& "' ORDER BY " & gOrder
b. fp_sQry="SELECT * FROM v_currentrecipients WHERE viewcode = '" &session("sViewcode")& "' ORDER BY " + gOrder
c. fp_sQry="SELECT * FROM v_currentrecipients WHERE viewcode = '" &session("sViewcode")& "' ORDER BY " + ::gOrder::

Any suggestions?

Thank you!!

You need to 'fetch' the value from the form

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

")& "' ORDER BY " & Request.Form("gOrder")

to the string, but it is not working quite right. I get this message when entering the page and when using the arrows to show more records:
"Database Results Error
Description: [Microsoft][ODBC Visual FoxPro Driver]Syntax error.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers"

But when I select an option from the drop down and press Submit, it works.

Also, when I press Submit, the value the drop down is showing goes back to the 1st option. I'd like it to display what the user last selected.

I tried to establish a variable using this code, but it did not work:
<SCRIPT LANGUAGE="JavaSript">
var gOrder = "progcode"
</SCRIPT>

What am I missing?

Thank you!!

For the option to be pre selected,

<option <%If gOrder = "ThisOption" then response.write " selected">ThisOption</option>

This assumes gOrder is a valid variable on this page!

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

You also sugguested that gOrder must be a valid variable on this page. How do I do that?

It seems like it is wrong to use Request.Form("gOrder") in the sql string prior to pressing the submit button. If so, how can I work around this?

Thank you!!

Are you able to response.write the SQL string, to see what is being passed?

If the page is posting to itself, you could use this :

<option <%If Request.form("gOrder") = "ThisOption" then response.write " selected">ThisOption</option>

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

The problem is that it errors upon loading. I did a response.write to see SQL string as you suggested and here's what I get.

Upon loading and after pressing the > button (to view the next set of records):
SELECT * FROM v_currentrecipients ORDER BY

After selecting an option from the drop down and pressing submit:
SELECT * FROM v_currentrecipients ORDER BY fundcode

Notice the ORDER BY clause is missing in the first case but present in the second case.

It seems that I'm missing the default value. You said that if a default value is selected for that drop down, it should work. But how do I set the default value keeping in mind that when the user submits a different order choice, the new value will be different from the default. Is this a case for a session variable?

Thank you!!

<%
If Request.form("gOrder") = "" then gOrder= "asc"
%>

Do this before the SQL is processed!

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

I added
<%
If Request.form("gOrder") = "" then gOrder= "asc"
%>

and did a response.write to see what is happening. When the page first loads, it does assign "asc" to gOrder but gOrder does not appear in the sql string. I still get
SELECT * FROM v_currentrecipients ORDER BY

Notice missing ORDER BY clause.

Request.form("gOrder") and gOrder set from the <%.. %> seem to be different.

Any suggestions?

Thank you!!

Do this too :

<%
gOrder= Request.form("gOrder")
If gOrder = "" then gOrder= "asc"
%>

Now the variable is usable.
Before, it was still looking for a request value in the SQL

fp_sQry="SELECT * FROM v_currentrecipients WHERE viewcode = '" &session("sViewcode")& "' ORDER BY " & gOrder

------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart

Thank you!!