All values of a field not been dispalyed on my page

[ifeyinwa]

I am getting just one value returned from the field in the

DB called 'area into the textarea in my form called

'otherdescription based on my 2nd sql statement in the

code below .

it just returns one value when it should return 2values -
Because each billNo from the DB appears twice in the

field called area like below
Is it that it is not looping at all and just loops once thru

the database that is why it is not picking up the other

value????


<%
Test = request.querystring("Test")
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

strBillNo=RS("billNo")

SQLQuery1= "Select area FROM tblopgaCOm2 WHERE

billNo = " & strBillNo &"' "
RS1.Open SQLQuery ,Conn,1,1

%>
<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="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">
<textarea rows="4" name="otherdescription"

cols="20">
<%While Not Rs1.EOF
Response.Write(Rs1("area") & ", ")
Rs1.MoveNext
Wend
%>
</textarea></td>

 

[DreXor]

couple things make sure 'area' doesn't contain any < or > cause it might be offsetting the HTML, view source and make sure they're not listing, vs listing and not visible.

secondly, in SQLQuery1 i see a single quote all by itself at the end, if billno is a string you need an opening single quote, if it's not, then you need to remove the hermit quote.

also by the way it's being called, are you sure there's more than one billno in that table? are you referencing the right table, it appears you're querying the same table for both queries versus querying say a customer table, then referencing an orders table by cust number

 

[ifeyinwa]

* area does not contain any <or>
* billno is an alphanumeric field in the DB
* yes there is more than One bill number in the DB .A bill number usually appears more than once. its uniqueness is in the dept it is assigned which is captured by the database field called area.
* yes I am quering the same table for both queries

let me step back to give you a general idea of what I am attempting to achieve for better understanding.

*page1.asp
have a user sign on/password screen(page1.asp).each user in the DB can only view & update infor for his own area. so when sue from the audit dept logs in succesfully she is sent to the audit page.asp.

*auditpage.asp
On this page (auditpage.asp) you have general infor for the audit dept you also have a button that says to view /update files.
when this button is clicked it triggers a select statement that displays on a new page auditpage2.asp just all the billnos that have been assigned just to the audit dept from the DB
it appears exactly like below( not all the fields from the DB is represented here)

*auditpage2.asp
Test area billNo RequiredDate
139 AUDIT SB0005 1/6/2004
140 AUDIT HB0124 1/20/2004
143 AUDIT HB0120 1/20/2004


and this is the code below that generates the above result
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS=Server.CreateObject("ADODB.RecordSet")
Conn.Open "eiwp"
SQLquery = "Select * from tblopgaCom2 where area LIKE 'AUDIT' order by reqDate asc"
RS.Open SQLquery, Conn
If RS.EOF then
Response.write "<center>There are no records in the table"
Respose.write "<br>please check back later</center>"
Response.end
Else
DO WHILE NOT RS.EOF
%>
<tr>
<td width="7%" style="color: #000080; font-weight: bold" bgcolor="#F8FDD7"><b><a href ="auditpage3.asp?Test=<%=RS("Test")%>"><font size="4" color="#FF0000">
<p align="center"><%=RS("Test")%></font></a></b></td>
<td width="7%" style="color: #000080; font-weight: bold" bgcolor="#F8FDD7"><b><font color="#003399" size="4">
<p align="center"><%=RS("area")%></font></b></td>
<td width="7%" style="color: #00000B; font-weight: bold" bgcolor="#EBEAEC"><b><font color="#003399" size="4">
<p align="center"><%=RS("billNo")%></font></b></td>
<td width="7%" style="color: #00000B; font-weight: bold" bgcolor="#F8FDD7"><b><font color="#003399" size="4"><%=RS("Description")%></font></b></td>
<td width="7%" style="color: #00000B; font-weight: bold" bgcolor="#F8FDD7"><b><font color="#003399" size="4">
<p align="center"><%=RS("reqDate")%></font></b></td>

</tr>
<%
Rs.MoveNext
Loop
End If
Rs.close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
%>
The user looks thru the list above and which ever record the user wishes to view /update he clicks a record under the test field eg 140 which is a link and that takes the user to another page (which is the page we are working on now called auditpage3.asp) where all results are from the DB are displayed in a form for the user to view and update.

The 1st sql statement in the code below displays all data on to the form okay. But the 2nd sql stat which should display the other dept from the field area where the billNo HB0124 (from test =140) has also been assigned into the textarea called otherdescription just returns one value
'Audit when it should also return 'finance (see ex of tblopgaCom2 table & auditpage3.asp below too)

*tblopgaCom2 table extract
Test area billNo RequiredDate
139 AUDIT SB0005 1/6/2004
140 AUDIT HB0124 1/20/2004
141 FINANCE SB0005 1/20/2004
142 FINANCE HB0124 5/11/2004
143 AUDIT HB0120 1/20/2004


*auditpage3.asp
Test = request.querystring("Test")
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

strBillNo=RS("billNo")

SQLQuery1= "Select area FROM tblopgaCOm2 WHERE

billNo = " & strBillNo &"' "
RS1.Open SQLQuery ,Conn,1,1

<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="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">
<textarea rows="4" name="otherdescription"

cols="20">
<%While Not Rs1.EOF
Response.Write(Rs1("area") & ", ")
Rs1.MoveNext
Wend
%>
</textarea></td>

 

[amorous]

Try this:

strBillNo=TRIM(RS("billNo"))

SQLQuery1= "Select area FROM tblopgaCOm2 WHERE

billNo = '" & strBillNo &"' "

-VJ

 

[amorous]

and try this too:

textarea rows="4" name="otherdescription"

cols="20">
<%While Not Rs1.EOF
otherdescription=otherdescription & Rs1("area") & ", "
Rs1.MoveNext
Wend
%>

-VJ

 

[ifeyinwa]

nothing has changed ???? same same

 

[DreXor]

only thing i can think of is try responsing out your SQL statements, paste them into your DB and query directly, see if you get different results, it could be something in the statements we're not seeing, possibly something like case sensitivity (doubtful though), got me here.

 

[ifeyinwa]

TRIED THAT TOO.
MY OUTPUT SHOULD LOOK SOMETHING LIKE THIS


Test: 140
billno: HB0124
Requireddate : 1/20/2004
area: AUDIT
comments : egfgegad
otherdescription: AUDIT,FINANCE

 

[DreXor]

hmm this is going to seem a little rediculous, but is perchance the HB0124 in the two records not identical as in HB(Zero)124 and HB(o)124?

your code looks great for the logicflow/task @ hand, i'm failing to see why it's failing for you


just for giggles sake try changing this code out :

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
  
 strBillNo=RS("billNo")
 
SQLQuery1= "Select area FROM tblopgaCOm2 WHERE 

billNo = " & strBillNo &"' "
 RS1.Open SQLQuery ,Conn,1,1

and change it to :

 Set Conn = Server.CreateObject("ADODB.Connection")
 Conn.Open "eiwp"

 SQLQuery = "Select * from tblopgaCOm2 WHERE Test=" & Test
 RS = Conn.Execute(SQLQuery)  

 If Not RS.EOF Then strBillNo=RS("billNo")

 SQLQuery1= "Select area FROM tblopgaCOm2 WHERE billNo=" & strBillNo & "'"
 Set RS1 = Conn.Execute(SQLQuery1)