Retrieving data from DB via 2 different sql statements

[ifeyinwa]

your input will be highly appreciated.worked on this all thru yesterday without sucess

I am displaying data from my access DB into a form that shows one record with the select statement below

SELECT * FROM tblopgaCOm2 WHERE billNo = '" & Request.Form("BILLNO") & "' AND area LIKE 'AUDIT'"

Example -The same "billNO"-d456 appears more than once but the "area" makes it unique ie billNo d456 can appear 3 times but for different areas like audit, finance, accounting. Therefore the sql above displays billNo eg d456 in the audit area.
On the same form or at least the same page i want to include another Textbox that will capture or display the other "area" where billNo d456 appears. So a user will not only see what the above sql displays but also the other areas the bill was assigned. Therefore I may need another sql statement to do this like below;

SELECT area FROM tblopgaCOm2 WHERE billNo = '" &
Request.Form("BILLNO") "

I guess I need to include 2 record sets for the 2sql statements??? but do not know how.with what has been built so far I get no errors but the new textbox field which should be populated with the second sql is NOT.
below is the code built so far;

dim Rs1
dim Rs2
dim sSQL
dim StrbillNo

on error resume next

Test = request.querystring("Test")
StrbillNo = Trim(Request.form("billNo"))

Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs1 = Server.CreateObject("adodb.Recordset")
Set Rs2 = Server.CreateObject("adodb.Recordset")
Conn.Open "eiwp"
sSQL = "Select * from tblopgaCOm2 WHERE Test = " & Test
Rs1.Open sSQL ,Conn

sSQL ="Select area FROM tblopgaCOm2 WHERE billNo = '" & Request.Form("billNO")& "'"
Rs2.Open sSQL ,Conn

Rs1.MoveFirst
%>

(below is some lines of html codes with the rest of asp codes)

<input type="text" name="billNo" style="background-color: #D2D2D2" size="9" value="<%=Rs1("billNo")%>" readonly></td>
<input type="text" name="area" style="background-color: #D2D2D2; color: #FF0000; font-weight: bold; text-align: Left" size="8" value="<%=Rs1("area")%>" readonly>
<textarea rows="8" name="priorcomments" readonly style="background-color: #D2D2D2" cols="50"><%=Rs1("priorcomments")%></textarea>

<% do until Rs2.EOF = true
sText = sText & Rs2("area") & ","
Rs2.MoveNext
loop

%>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><font size="2"><b>DESCRIPTION:</b></font></td>
<td width="239%" bgcolor="#E2E0E1" height="36" colspan="4"><textarea rows="4" name="Description" readonly style="background-color: #D2D2D2" cols="30"><%Response.Write chr(34) & sText & chr(34) %></textarea>
<td width="52%" height="36">&nbsp;</td>
<tr>

 

[ifeyinwa]

Nothing Nothing has changed!!!!

 

[amorous]

I am out of ideas...

the same works fine when i tested with my own queries.. i dont know why you are having problems...

I am sorry..i could on help you more on this..

-VJ

 

[ifeyinwa]

thank you so much for your effort any way.
I am honestly surprised that it did not work.
it did not seem like it was that tough a question?? well guess I was wrong. will keep trying I need to put this project together within a week & will let you know if I do crack it
thanks again

 

[CassidyHunt]

I know I had talked to you about this earlier and couldn't get it to work. I was curious what type of database you are using.

I want to see if closing hte first recordset after you are finished before getting the second recordset would make a difference.

add an rs.close after you done with that object and then instead of using rs1 to populate the textbox reopen rs with the criteria you would use to open rs1. Then make sure that rs1 is changed to rs.

I don't know if this would make a difference but I have ran into instances that databases will only allow me to have one recordset open at a time.

Let me know if you need the whole code.

Thanks

Cassidy