SQLs

L

LC

Hi,

I have two or more sql statements that depends on the
other on the same page. For example I have strSQL3 and
then strSQL4 will need the result from the dropdown of
strSQL3 to start its sql.

strSQL3 = "SELECT DISTINCT tblEV3Location.Code FROM
tblEV3Location ORDER BY tblEV3Location.Code;"

<% set cars3=adoCon.execute(strSQL3) %>
<select name="Code">
<% 'Loop through the recordset to make each entry
in the list. %>
<% do while not cars3.eof %>
<Option value = "<%= cars3(0) %>"> <%=
cars3(0) %></Option>
<%cars3.movenext
loop%>
</select>

How would I do something like this where the Code is the
name of the drop-down?:
strSQL4 = "SELECT DISTINCT tblEV3Location.Division FROM
tblEV3Location WHERE tblEV3Location.Division='Code';"

Thanks.
 
J

Jim Buyens

Howdy.

strSQL4 = "SELECT DISTINCT tblEV3Location.Division " & _
"FROM tblEV3Location " & _
"WHERE tblEV3Location.Division='" & _
request("code") & "';"

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
 
G

Guest

Hi,

How about if I wanted to display the Code value on the
page?

This unfortunately doesnt work.
<% response.write(request("Code")) %>

Thanks.
 
L

LC

Hi,

For some odd reason that code doesnt work.
Is there something missing at the WHERE
tblEV3Location.Division= ?

Thanks.
 
J

Jim Buyens

Howdy.

Please define "doesn't work". How does it fail?

Also, does putting <%="code=" & request("code")%> immediately
after the <body> tag display the code value you expect?

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
 
L

lc

Hi,

It doesnt create an error but the drop down is blank and
there is no value printed after the division =

Here is my new code:

<%@ Language=VBScript %>
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGuestbook 'Holds the recordset for the records in
the database
Dim strSQL 'Holds the SQL query to query the
database
Dim strSQL4

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using
a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=" & Server.MapPath("alpha.mdb")

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to
query the database
strSQL = "SELECT DISTINCT tblEV3Location.Division FROM
tblEV3Location ORDER BY tblEV3Location.Division;"


'Open the recordset with the SQL query
rsGuestbook.Open strSQL, adoCon


set cars=adoCon.execute(strSQL)
%>
<select name="Division">
<% 'Loop through the recordset to make each entry
in the list. %>
<% do while not cars.eof %>
<Option value = "<%= cars(0) %>"><%= cars
(0) %></Option>
<%cars.movenext
loop%>
</select>

<%

strSQL4 = "SELECT DISTINCT tblEV3Location.Code,
tblEV3Location.Division FROM tblEV3Location WHERE
tblEV3Location.Division='" & Request("Division") &"';"
%>
<%set cars4=adoCon.execute(strSQL4)
%>


<select name="Code">
<% 'Loop through the recordset to make each entry
in the list. %>
<% do while not cars4.eof %>
<Option value = "<%= cars4(0) %>"><%=
cars4(0) %></Option>
<%cars4.movenext
loop%>
</select>
<html>

<head>
<title> select </title>
</head>
<body><%="Division=" & request("Division")%>

</body>

</html>

Thank you.
 
J

Jim Buyens

Welk then, let's look at the requesting page. Is it true that

o You have a text box or some other form field named "Division"?
o You have Submit button.
o Both of the above are surrounded by <form> and </form> tags?
o The form's action property points to your query page?

Here's an example:

<form method="POST" action="secondpage.asp">
<input type="text" name="Division">
<input type="submit" value="Submit" name="btnSub">
</form>

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop-downs 2
Non-repeating values in drop down 1
report record source 6
Improving treeview load time 13
drop-down value 1
ADO statement with text arguments - how to add into VBA 3
increment variable in for loop 4
Error 3061: Too few parameters. Expected 1 1

Top