PDA

Click to See Complete Forum and Search --> : SQL /ASP

Hutty
Jul 5th, 2000, 03:42 PM
I would like to query a search using SQL and ASP. The user would select a name from a dropdown box which would retrieve the desired results. I would need the code to fill drop down box with names from table and upon click to execute query. This is what I have so far that retrieves the basic. Any help in improving would be appreciated. Thanks.

<HTML>
<!--#INCLUDE FILE="RECTOTABLE.ASP" -->
<% ' -- Get a Recordset Object
Set MyRecordsetObject = Server.CreateObject("ADODB.Recordset")

' -- Retrieve the database information
MyRecordsetObject.Open "SELECT * FROM ACT_1 WHERE NAME = 'LFDIV' or name = 'nansh.tpc' and account = 'ret_prem_g'", "DSN=DB1" %>

<table> <%
' -- Loop through the employee records in the Recordset Object until End Of File
Do While Not MyRecordsetObject.EOF
%>
<tr>
<td><%=MyRecordsetObject("Name") %></td>
<td><%=MyRecordsetObject("ACCOUNT") %></td>
<td><%=MyRecordsetObject("QTR1") %></td>
<td><%=MyRecordsetObject("QTR2") %></td>
</tr>
<%
' -- Move to next employee record
MyRecordsetObject.MoveNext
' -- Continue looping
Loop
%>
</table>
%>

<%
MYRECORDSETOBJECT.Close
Set MyRecordsetObject = Nothing
%>
Ianpbaker
Jul 6th, 2000, 03:09 AM
Hi hutty

To fill the list box try replacing the code with this

<HTML>
<!--#INCLUDE FILE="RECTOTABLE.ASP" -->
<% ' -- Get a Recordset Object
Set MyRecordsetObject = Server.CreateObject("ADODB.Recordset")

' -- Retrieve the database information
MyRecordsetObject.Open "SELECT * FROM ACT_1 WHERE NAME = 'LFDIV' or name = 'nansh.tpc' and account = 'ret_prem_g'", "DSN=DB1" %>
<Body>
<FORM action="Query.asp" Method="POST">
<SELECT name="MylistBox>
' -- Loop through the employee records in the Recordset Object until End Of File
Do While Not MyRecordsetObject.EOF
%>
<OPTION value="<%=MyRecordsetObject("PrimaryKey")%>">MyRecordsetObject("Name")</OPTION>
<%
' -- Move to next employee record
MyRecordsetObject.MoveNext
' -- Continue looping
Loop
%>
</SELECT>
<INPUT type="Submit" value="Get Details">
</FORM>
</BODY>
</HTML>
%>

<%
MYRECORDSETOBJECT.Close
Set MyRecordsetObject = Nothing
%>

This Will fill out the list box with the name of each field, just replace the value part of the option with what ever your primary key is for that table. Then once the user clicks on the submit button it will take them to query.asp which is below.

<HTML>
<!--#INCLUDE FILE="RECTOTABLE.ASP" -->
<% ' -- Get a Recordset Object
Dim Listboxid
Set MyRecordsetObject = Server.CreateObject("ADODB.Recordset")

Listboxid = Request.Form("MylistBox")

' -- Retrieve the database information
MyRecordsetObject.Open "SELECT * FROM ACT_1 WHERE primarykey = " & Listboxis , "DSN=DB1" %>

<Body>
<TABLE>
<%' -- Loop through the employee records in the Recordset Object until End Of File
Do While Not MyRecordsetObject.EOF
%>
<tr>
<td><%=MyRecordsetObject("Name") %></td>
<td><%=MyRecordsetObject("ACCOUNT") %></td>
<td><%=MyRecordsetObject("QTR1") %></td>
<td><%=MyRecordsetObject("QTR2") %></td>
</tr>
<%

' -- Move to next employee record
MyRecordsetObject.MoveNext
' -- Continue looping
Loop
%>
</BODY>
</HTML>
%>

<%
MYRECORDSETOBJECT.Close
Set MyRecordsetObject = Nothing
%>

Hope this helps

Ian
Hutty
Jul 6th, 2000, 03:04 PM
Thanks IanpBaker. after a couple of syntax corrections, i was able to get it to work. However, the listbox doesn't fill up with all the names from the query. I've played with it, but figure i would post the code here in the meantime. I'm trying to get uniques names. Same name is used several times. This fills my listbox with just one name:

<HTML>
<!--#INCLUDE FILE="RECTOTABLE.ASP" -->
<% ' -- Get a Recordset Object
Set MyRecordsetObject = Server.CreateObject("ADODB.Recordset")

' -- Retrieve the database information
MyRecordsetObject.Open "SELECT * FROM ACT_1", "DSN=DB1" %>
<Body>
<FORM action="Query.asp" Method="POST">
<SELECT name="MylistBox">
' -- Loop through the employee records in the Recordset Object until End Of File
Do While Not MyRecordsetObject.EOF

%>
<OPTION value="<%=MyRecordsetObject("id")%>">
<%=MyRecordsetObject("Name")%>
<%=MyRecordsetObject.MoveNext %>

</OPTION>

<%
Do While Not MyRecordsetObject.EOF
' -- Move to next employee record
MyRecordsetObject.MoveNext
' -- Continue looping
Loop
%>
</SELECT>
<INPUT type="Submit" value="Get Details">
</FORM>
</BODY>
</HTML>


<%
MYRECORDSETOBJECT.Close
Set MyRecordsetObject = Nothing
%>

Also, i would like the results to be displayed in a table or grid format for each field or row retrieved. Here's the query.asp code:

<HTML>
<!--#INCLUDE FILE="RECTOTABLE.ASP" -->
<% ' -- Get a Recordset Object
Dim Listboxid
Set MyRecordsetObject = Server.CreateObject("ADODB.Recordset")

Listboxid = Request.Form("MylistBox")

' -- Retrieve the database information
MyRecordsetObject.Open "SELECT * FROM ACT_1 WHERE id = " & Listboxid , "DSN=DB1" %>

<Body>
<TABLE><%
' -- Loop through the employee records in the Recordset Object until End Of File

Do While Not MyRecordsetObject.EOF
%>
<tr>
<td><%=MyRecordsetObject("Name")%></td>
<td><%=MyRecordsetObject("ACCOUNT")%></td>
<td><%=MyRecordsetObject("QTR1")%></td>
<td><%=MyRecordsetObject("QTR2")%></td>
</tr>
<%

' -- Move to next employee record
MyRecordsetObject.MoveNext
' -- Continue looping
Loop
%>
</table>
</BODY>
</HTML>


<%
MYRECORDSETOBJECT.Close
Set MyRecordsetObject = Nothing
%>
Ianpbaker
Jul 7th, 2000, 02:51 AM
Hi Hutty

Sorry about he syntax, but when I'm writing out of my head into this little textbox, I occasionally make mistakes.

When you say you have got some fields with the same Name but different Id's, Why do you need the different fields, if the user is only allowed to see one?.

The SQL to get only Unique names back is as follows

SELECT DISTINCT name, id FROM ACT_1

Hope this helps

Ian
Hutty
Jul 7th, 2000, 07:07 AM
Well, the ID is of no importance to me. It's the name that gets the wheel turning. Each name has several accounts associated with them. The idea is to fill the listbox with each distinct name, then based on the listbox name display the various accounts for that name along with the values. Example of names = LFDIV_AN, LFDIV_EB.... each name has the same set of accounts.

The user will see a listbox will all the distinct names. Will then choose a name that would trigger the query.asp page.

Thanks
Ianpbaker
Jul 7th, 2000, 08:14 AM
Hi Hutty

In that case, It sounds like you want another table that holds the unique account information for each account then link that into your existing table with a one-to-many relationship. So then you can fill the list box with the base table, then select all of the acount information from the second table depending on what the user has selected.

Hope this helps

Ian
Hutty
Jul 7th, 2000, 08:23 AM
Well, the ID is of no importance to me. It's the name that gets the wheel turning. Each name has several accounts associated with them. The idea is to fill the listbox with each distinct name, then based on the listbox name display the various accounts for that name along with the values. Example of names = LFDIV_AN, LFDIV_EB.... each name has the same set of accounts.

The user will see a listbox will all the distinct names. Will then choose a name that would trigger the query.asp page.

Thanks