Retrieving data from DB via 2 different sql statements

ifeyinwa · May 26, 2004

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"> </td>
<tr>

Alfredomulet · May 26, 2004

Hi ifeyinwa, check the post Subtotals in a query

The short answer is yes, you might need two different recordsets. The way to do it is:

First, create an instance of ADO Recordset and populate the first Recordset with the appropiate data from your first query. Do the neccessary processing with that recordset:

Code:

...
'connection
sconectstring = "..."
'recordset
set strProd= "...."
'first query
strProd.Open "the first sql query", sconectstring
'processing
Do  while not strProd.EOF
  'first tables here...
   strProd.MoveNext
Loop
'close first query
strProd.Close

Once you've finished processing the first query Recordset, you can populate the second Recordset with a different query, and do some processing with it:

Code:

'open second query

strProd.Open "the second sql query", sconectstring
'processing again
Do  while not strProd.EOF
  'totals here...
   strProd.MoveNext
Loop
'close second query
strProd.Close

Finnaly you just need the cleanup of the Recodset objects by:

Code:

'free it up
set strProd = Nothing

I hope this helps,
Alfredo

ifeyinwa · May 27, 2004

Nothing changed.The Textbox is still not been populating with the 2nd select statement.
my queries have been checked in the query analizer and it is okay.below is my code cannot figure out what I did wrong or did not do. An input will be appeciated.
<%
dim Rs1
dim Rs2
dim sSQL
dim StrbillNo

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

Do while not Rs1.EOF

%>
'Html for textboxes for the Ist sql

<%
Rs1.MoveNext
Loop
'close first query

Rs1.close
%>

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

Do while not Rs2.EOF
%>
"HTML for the textbox that is populated from the 2nd sql

<%
Rs2.MoveNext
Loop
'close second query
Rs2.Close

set Rs2= nothing
%>

amorous · May 27, 2004

Let me tell you this... You are not clear about what you actually want to do..

In your description the two queries you showed are as follows:

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

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

but in you code your queries are different...

one morething... for the query 1 above you dont to code like
Rs1.MoveFirst because there is only one UNIQUE record satisfying the conditions of your query 1

And for query 2 you have many records and since you want to show them to the user you can use a text area-- which you are on right track on this ..

-VJ

ifeyinwa · May 27, 2004

sorry if I threw you off there. Disregard the 1st sql in the description.For clarity the codes in the script is the final authority.
However It really does not matter if I use the first sql in my description or the one in my code.The first sql is NOT where the problem lies . Its in getting the additional text area to be populated by the 2nd sql statements ie GETTING TO ARRANGE 2RECORD SETS/2SQL STATEMENTS IN ONE FORM /page.

The way I have arranged the 2 record sets or the 2 sql statements has some flaws.while the form is being populated by the firstsql statement

sSQL = "Select * from tblopgaCOm2 WHERE Test = " & Test
Rs1.Open sSQL ,Conn

The second is not.

amorous · May 27, 2004

can you post your present complete code including the HTML.

I mean post your complete code as it is..

-VJ

ifeyinwa · May 27, 2004

why don't I start afresh.Below is my wonderful fresh & error free code, before I started wanting to add a new record & a new sql statement.
I think its best/easier to start from the beginning.

Below is my code & what I would like to add to this exsisting script is;
A second select statement

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

that will display its data in the text area called Description

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>legislative update</title>

</head>

<body>
<b><i>       </i><font size="4" color="#003399">      </font><i>                          
</i><font size="4" color="#003399">       </font></b>
<%
Test = request.querystring("Test")
StrbillNo = Trim(Request.form("billNo"))
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("adodb.Recordset")
Conn.Open "eiwp"
SQLQuery = "Select * from tblopgaCOm2 WHERE Test = " & Test
RS.Open SQLQuery ,Conn,1,1
%>

<div align="left">
<table border="0" cellpadding="2" cellspacing="4" width="120%">
<tr>
<td width="100%">
<p align="left"><b><i>                                                <font color="#003399"> </font><font color="#FF0000">
Legislative Tracking Comments</font></i><font size="4" color="#000080"> </font></b><font color="#FF0000"> </font><font color="#C0C0C0">               </font>                                </td>
</tr>
<tr>
<td width="100%">
<b><i>                                       <font color="#000080">
<u>Comment on a Bill and hit
the submit button</u></font></i></b><font color="#000080">  </font></td>
</tr>
<tr>
<td width="100%">
</td>
</tr>
</table>
</div>
<form method="POST" action="legconfirm.asp" >
<table border="0" cellpadding="2" cellspacing="4" width="121%" height="172">
<tr>
<td width="20%" bgcolor="#99CCFF" height="36"> <b><font size="2">ID#:
</font> </b><input type="text" name="Test" style="background-color: #D2D2D2" size="9" value="<%=RS("Test")%>" readonly></td>
<td width="43%" bgcolor="#99CCFF" height="36"> <b><font size="2">BILL</font>#</b>
<input type="text" name="billNo" style="background-color: #D2D2D2" size="9" value="<%=RS("billNo")%>" readonly></td>
<td width="10%" bgcolor="#99CCFF" height="36">    <input type="text" name="billNo" style="background-color: #D2D2D2" size="13" value="<%=RS("billNo")%>" readonly </td>
<td width="33%" bgcolor="#99CCFF" height="36"> <input type="text" name="billNo" style="background-color: #D2D2D2" size="12" value="<%=RS("billNo")%>" readonly</td>
<td width="129%" height="36" bgcolor="#C0C0C0">
<p align="left"><b><font color="#000080"><input type="text" name="area" style="background-color: #D2D2D2; color: #FF0000; font-weight: bold; text-align: Left" size="8" value="<%=RS("area")%>" readonly></font></b></p>
</td>
<td width="52%" height="36"> </td>
<tr>
<td width="66%" bgcolor="#99CCFF" height="36" colspan="2"><b><font size="2"> HEARING
DATE:</font></b>        <input type="text" name="Time" readonly style="background-color: #D2D2D2" size="15" value="<%=RS("updDate")%>"></td>
<td width="34%" bgcolor="#99CCFF" height="36"><font size="2"><b>TIME:<input type="text" name="Time" readonly style="background-color: #D2D2D2" size="10" value="<%=RS("updDate")%>"></b></font></td>
<td width="162%" bgcolor="#99CCFF" height="36" colspan="2"><font size="2"><b>REQUEST
DATE</b></font><b><font size="2">:</font> </b> <input type="text" name="reqdate" style="background-color: #D2D2D2" size="12" value="<%=RS("ReqDate")%>" readonly></td>
<td width="52%" height="36"> </td>
<tr>
<td width="262%" bgcolor="#99CCFF" height="36" colspan="5"><font size="2"><b>REQUEST
FOR COMMENTS:  <input type="text" name="updDate" readonly style="background-color: #D2D2D2" size="50" value="<%=RS("updDate")%>"></b></font></td>
<td width="52%" height="36"> </td>
<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"><input type="text" name="Description" readonly style="background-color: #D2D2D2" size="60" value="<%=RS("Description")%>"></td>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><font size="2"><b>PRIORCOMMENTS:</b></font></td>
<td width="239%" bgcolor="#C0C0C0" height="36" colspan="4"><textarea rows="8" name="priorcomments" readonly style="background-color: #D2D2D2" cols="50"><%=RS("priorcomments")%></textarea>
<p> </td>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><b><font size="2">COMMENTS:</font></b></td>
<td width="239%" bgcolor="#E6E3E4" height="36" colspan="4"><textarea rows="6" name="comments" cols ="50"><%=RS("comments")%></textarea>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></td>
<td width="239%" bgcolor="#E6E3E4" height="36" colspan="4"> </td>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" height="36" bgcolor="#99CCFF"> </td>
<td width="26%" height="36" bgcolor="#99CCFF">
<p align="center">  <b>
<applet code="fphover.class" width="120" height="24">
<param name="color" value="#000080">
<param name="textcolor" value="#FFFFFF">
<param name="text" value="LINK TO MLIS">
<param name="effect" value="glow">
<param name="url" valuetype="ref" value="

http://mlis.state.md.us/2004rs/billfile/<%=rs.fields("billno")%>.htm">

amorous · May 27, 2004

Ok here you go.......... i tried this and it should work:
i have added a new description field called otherdescription and it lists all the values of area obtained from the second sql query...

Code:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>legislative update</title>

</head>

<body>
<b><i>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</i><font size="4" color="#003399">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><i>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</i><font size="4" color="#003399">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font></b>
<%
Test = request.querystring("Test")     
StrbillNo = Trim(Request.form("billNo"))
Set Conn = Server.CreateObject("ADODB.Connection") 
Set Rs = Server.CreateObject("adodb.Recordset")
[b]Set Rs1 = Server.CreateObject("adodb.Recordset")[/b]

Conn.Open "eiwp" 
SQLQuery = "Select * from tblopgaCOm2 WHERE Test = " & Test 
RS.Open SQLQuery ,Conn,1,1

[b]SQLQuery1= "SELECT area FROM tblopgaCOm2 WHERE billNo = '"&Request.Form("BILLNO")&"' " 
Rs1.Open SQLQuery1, conn [/b]
%>

<div align="left">
  <table border="0" cellpadding="2" cellspacing="4" width="120%">
    <tr>
      <td width="100%">
        <p align="left"><b><i>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#003399">&nbsp;</font><font color="#FF0000">
        Legislative Tracking Comments</font></i><font size="4" color="#000080">&nbsp;</font></b><font color="#FF0000">&nbsp;</font><font color="#C0C0C0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    </tr>
    <tr>
      <td width="100%">
        <b><i>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#000080">
        <u>Comment on a Bill and hit
        the submit button</u></font></i></b><font color="#000080">&nbsp;&nbsp;</font></td>
    </tr>
    <tr>
      <td width="100%">
      </td>
    </tr>
  </table>
</div>
<form method="POST" action="legconfirm.asp" >          
     <table border="0" cellpadding="2" cellspacing="4" width="121%" height="172">
         <tr>
      <td width="20%" bgcolor="#99CCFF" height="36">&nbsp;<b><font size="2">ID#:
        </font> </b><input type="text" name="Test" style="background-color: #D2D2D2"  size="9" value="<%=RS("Test")%>" readonly></td>
      <td width="43%" bgcolor="#99CCFF" height="36">&nbsp;<b><font size="2">BILL</font>#</b>
        <input type="text" name="billNo" style="background-color: #D2D2D2"  size="9" value="<%=RS("billNo")%>" readonly></td>
      <td width="10%" bgcolor="#99CCFF" height="36">&nbsp;&nbsp;&nbsp; <input type="text" name="billNo" style="background-color: #D2D2D2"  size="13" value="<%=RS("billNo")%>" readonly                </td>
      <td width="33%" bgcolor="#99CCFF" height="36"> <input type="text" name="billNo" style="background-color: #D2D2D2"  size="12" value="<%=RS("billNo")%>" readonly</td>
      <td width="129%" height="36" bgcolor="#C0C0C0">
        <p align="left"><b><font color="#000080"><input type="text" name="area" style="background-color: #D2D2D2; color: #FF0000; font-weight: bold; text-align: Left"  size="8" value="<%=RS("area")%>" readonly></font></b></p>
      </td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="66%" bgcolor="#99CCFF" height="36" colspan="2"><b><font size="2">&nbsp;HEARING
        DATE:</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <input type="text" name="Time" readonly style="background-color: #D2D2D2"  size="15" value="<%=RS("updDate")%>"></td>
      <td width="34%" bgcolor="#99CCFF" height="36"><font size="2"><b>TIME:<input type="text" name="Time" readonly style="background-color: #D2D2D2" size="10" value="<%=RS("updDate")%>"></b></font></td>
      <td width="162%" bgcolor="#99CCFF" height="36" colspan="2"><font size="2"><b>REQUEST
        DATE</b></font><b><font size="2">:</font>&nbsp;</b> <input type="text" name="reqdate" style="background-color: #D2D2D2"  size="12" value="<%=RS("ReqDate")%>" readonly></td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="262%" bgcolor="#99CCFF" height="36" colspan="5"><font size="2"><b>REQUEST
        FOR COMMENTS:&nbsp; <input type="text" name="updDate" readonly style="background-color: #D2D2D2"  size="50" value="<%=RS("updDate")%>"></b></font></td>
      <td width="52%" height="36">&nbsp;</td>
         <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"><input type="text" name="Description" readonly style="background-color: #D2D2D2" size="60" value="<%=RS("Description")%>"></td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="23%" bgcolor="#99CCFF" height="36"><font size="2"><b>PRIORCOMMENTS:</b></font></td>
      
      <td width="239%" bgcolor="#C0C0C0" height="36" colspan="4"><textarea rows="8" name="priorcomments" readonly style="background-color: #D2D2D2" cols="50"><%=RS("priorcomments")%></textarea>
        <p>&nbsp;</td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="23%" bgcolor="#99CCFF" height="36"><font size="2"><b>OTHER 
		DESCRIPTION</b></font><b><font size="2">:</font></b></td>
      <td width="239%" bgcolor="#E6E3E4" height="36" colspan="4">
		[b]
		<%While Not rsEquip.EOF%>
		<%
		Dim desc
		desc=desc & Rs1("area")&","
		%>
   
    <%
      	Rs1.MoveNext
	    Wend
      %>[/b]
    <textarea rows="2" name="otherdep" cols="20"><%=desc%></textarea></td>
         <tr>
      <td width="23%" bgcolor="#99CCFF" height="36"><b><font size="2">COMMENTS:</font></b></td>
      <td width="239%" bgcolor="#E6E3E4" height="36" colspan="4"><textarea rows="6" name="comments" cols ="50"><%=RS("comments")%></textarea>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="23%" bgcolor="#99CCFF" height="36"><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></td>
      <td width="239%" bgcolor="#E6E3E4" height="36" colspan="4">&nbsp;</td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="23%" height="36" bgcolor="#99CCFF">&nbsp;</td>
      <td width="26%" height="36" bgcolor="#99CCFF">
        <p align="center">&nbsp; <b>
        <applet code="fphover.class" width="120" height="24">
          <param name="color" value="#000080">
          <param name="textcolor" value="#FFFFFF">
          <param name="text" value="LINK TO MLIS">
          <param name="effect" value="glow">
          <param name="url" valuetype="ref" value="[URL unfurl="true"]http://mlis.state.md.us/2004rs/billfile/<%=rs.fields("billno")%>.htm">[/URL]
          <param name="hovercolor" value="#FF0000">
          <param name="font" value="Dialog">
          <param name="fontstyle" value="bold">
          <param name="fontsize" value="14">
        </applet>
        </b></p>
      </td>
      <td width="34%" height="36" bgcolor="#99CCFF">
        <p align="center">&nbsp;<font color="#FFFFFF"><b><input type="button" value="PRINT PAGE" onclick="window.print()" style="color: #FFFFFF; font-weight: bold; background-color: #003399"/></b></font>&nbsp;
        </p>
      </td>
      <td width="33%" height="36" bgcolor="#99CCFF">&nbsp;</td>
      <td width="129%" height="36" bgcolor="#99CCFF">&nbsp;</td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="23%" height="36">&nbsp;</td>
      <td width="43%" height="36">&nbsp;</td>
      <td width="34%" height="36">&nbsp;</td>
      <td width="33%" height="36">&nbsp;</td>
      <td width="129%" height="36">&nbsp;</td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="23%" height="36">&nbsp;</td>
      <td width="43%" height="36">&nbsp;</td>
      <td width="34%" height="36">&nbsp;</td>
      <td width="33%" height="36">&nbsp;</td>
      <td width="129%" height="36">&nbsp;</td>
      <td width="52%" height="36">&nbsp;</td>
         <tr>
      <td width="23%" height="36"></td>
      <td width="43%" height="36">&nbsp;</td>
      <td width="34%" height="36">&nbsp;</td>
      <td width="33%" height="36">&nbsp;</td>
      <td width="129%" height="36">&nbsp;</td>
      <td width="52%" height="36">&nbsp;</td>
  </table>
</form>
<p>&nbsp;</p>

-VJ

ifeyinwa · May 27, 2004

getting
error '80020009'
Exception occurred.

/eiwp/audit3_copy(6).asp, line 89 which is the line below

desc=desc & Rs1("area")&","

amorous · May 27, 2004

Sorry i had a typo: i hope you changed it:

<%While Not Rs1.EOF%>
<%
Dim desc
desc=desc & Rs1("area")&","
%>

<%
Rs1.MoveNext
Wend
%>
<textarea rows="2" name="otherdep" cols="20"><%=desc%></textarea></td>

-VJ

ifeyinwa · May 27, 2004

yes I have changed it .
NO more errors . BUT the new field other description field has no data

amorous · May 27, 2004

sorry one more typo:

<textarea rows="2" name="otherdescription" cols="20"><%=desc%></textarea></td>

-VJ

amorous · May 27, 2004

if that does not work:

post your code then.. i will take a look.

-VJ

ifeyinwa · May 27, 2004

noop ... no data displaying

ifeyinwa · May 27, 2004

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>legislative update</title>

</head>

<body>
<b><i>       </i><font size="4" color="#003399">      </font><i>                          
</i><font size="4" color="#003399">       </font></b>
<%
Test = request.querystring("Test")
StrbillNo = Trim(Request.form("billNo"))
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("adodb.Recordset")
Set Rs1 = Server.CreateObject("adodb.Recordset")
Conn.Open "eiwp"
SQLQuery = "Select * from tblopgaCOm2 WHERE Test = " & Test
RS.Open SQLQuery ,Conn,1,1

SQLQuery1= "SELECT area FROM tblopgaCOm2 WHERE billNo = '"&Request.Form("billNO")&"' "
Rs1.Open SQLQuery1, conn

%>

<div align="left">
<table border="0" cellpadding="2" cellspacing="4" width="120%">
<tr>
<td width="100%">
<p align="left"><b><i>                                                <font color="#003399"> </font><font color="#FF0000">
Legislative Tracking Comments</font></i><font size="4" color="#000080"> </font></b><font color="#FF0000"> </font><font color="#C0C0C0">               </font>                                </td>
</tr>
<tr>
<td width="100%">
<b><i>                                       <font color="#000080">
<u>Comment on a Bill and hit
the submit button</u></font></i></b><font color="#000080">  </font></td>
</tr>
<tr>
<td width="100%">
</td>
</tr>
</table>
</div>
<form method="POST" action="legconfirm.asp" >
<table border="0" cellpadding="2" cellspacing="4" width="121%" height="172">
<tr>
<td width="20%" bgcolor="#99CCFF" height="36"> <b><font size="2">ID#:
</font> </b><input type="text" name="Test" style="background-color: #D2D2D2" size="9" value="<%=RS("Test")%>" readonly></td>
<td width="43%" bgcolor="#99CCFF" height="36"> <b><font size="2">BILL</font>#</b>
<input type="text" name="billNo" style="background-color: #D2D2D2" size="9" value="<%=RS("billNo")%>" readonly></td>
<td width="10%" bgcolor="#99CCFF" height="36">    <input type="text" name="billNo" style="background-color: #D2D2D2" size="13" value="<%=RS("billNo")%>" readonly </td>
<td width="33%" bgcolor="#99CCFF" height="36"> <input type="text" name="billNo" style="background-color: #D2D2D2" size="12" value="<%=RS("billNo")%>" readonly</td>
<td width="129%" height="36" bgcolor="#C0C0C0">
<p align="left"><b><font color="#000080"><input type="text" name="area" style="background-color: #D2D2D2; color: #FF0000; font-weight: bold; text-align: Left" size="8" value="<%=RS("area")%>" readonly></font></b></p>
</td>
<td width="52%" height="36"> </td>
<tr>
<td width="66%" bgcolor="#99CCFF" height="36" colspan="2"><b><font size="2"> HEARING
DATE:</font></b>        <input type="text" name="Time" readonly style="background-color: #D2D2D2" size="15" value="<%=RS("updDate")%>"></td>
<td width="34%" bgcolor="#99CCFF" height="36"><font size="2"><b>TIME:<input type="text" name="Time" readonly style="background-color: #D2D2D2" size="10" value="<%=RS("updDate")%>"></b></font></td>
<td width="162%" bgcolor="#99CCFF" height="36" colspan="2"><font size="2"><b>REQUEST
DATE</b></font><b><font size="2">:</font> </b> <input type="text" name="reqdate" style="background-color: #D2D2D2" size="12" value="<%=RS("ReqDate")%>" readonly></td>
<td width="52%" height="36"> </td>
<tr>
<td width="262%" bgcolor="#99CCFF" height="36" colspan="5"><font size="2"><b>REQUEST
FOR COMMENTS:  <input type="text" name="updDate" readonly style="background-color: #D2D2D2" size="50" value="<%=RS("updDate")%>"></b></font></td>
<td width="52%" height="36"> </td>
<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="2" name="Description" readonly style="background-color: #D2D2D2" cols="20"><%=RS("Description")%></textarea>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><font size="2"><b>PRIORCOMMENTS:</b></font></td>
<td width="239%" bgcolor="#C0C0C0" height="36" colspan="4"><textarea rows="8" name="priorcomments" readonly style="background-color: #D2D2D2" cols="50"><%=RS("priorcomments")%></textarea>
<p> </td>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><font size="2"><b>OTHER
DESCRIPTION</b></font><b><font size="2">:</font></b></td>
<td width="239%" bgcolor="#E6E3E4" height="36" colspan="4">

<%While Not Rs1.EOF%>
<%
Dim desc
desc=desc & Rs1("area")&","
%>

<%
Rs1.MoveNext
Wend
%>
<textarea rows="2" name="otherdescription" cols="20"><%=desc%></textarea></td>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><b><font size="2">COMMENTS:</font></b></td>
<td width="239%" bgcolor="#E6E3E4" height="36" colspan="4"><textarea rows="6" name="comments" cols ="50"><%=RS("comments")%></textarea>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" bgcolor="#99CCFF" height="36"><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></td>
<td width="239%" bgcolor="#E6E3E4" height="36" colspan="4"> </td>
<td width="52%" height="36"> </td>
<tr>
<td width="23%" height="36" bgcolor="#99CCFF"> </td>
<td width="26%" height="36" bgcolor="#99CCFF">
<p align="center">  <b>
<applet code="fphover.class" width="120" height="24">
<param name="color" value="#000080">
<param name="textcolor" value="#FFFFFF">
<param name="text" value="LINK TO MLIS">
<param name="effect" value="glow">
<param name="url" valuetype="ref" value="

http://mlis.state.md.us/2004rs/billfile/<%=rs.fields("billno")%>.htm">

amorous · May 27, 2004

Try this small change before i move to the other parts of the code:

instead of this ..

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

try

SQLQuery1= "SELECT area FROM tblopgaCOm2 WHERE billNo = "&Request.Form("billNO")

-VJ

ifeyinwa · May 28, 2004

It does not like that it gives errors .

amorous · May 28, 2004

Post your complete present code again....

Also check the second SQL statement in query analyzer to see if it is producing any results..

-VJ

ifeyinwa · May 28, 2004

I had checked the sql in the query analizer prior and it was okay.
The present code has not changed same as above.

amorous · May 28, 2004

A small change

Conn was written as conn

Rs1.Open SQLQuery1, Conn

Also try

Rs1.Open SQLQuery1, Conn,1,1

-VJ

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Retrieving data from DB via 2 different sql statements

Programmer

MIS

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor