retriving infor from Access Table to ASP page

[ifeyinwa]

I have a form that displays information selected from an Access Data base. A select statement is used to do this on my asp page
SQLQuery = "SELECT * FROM tblopgaCOm2 WHERE billNo = '"

& Request.Form("BILLNO") & "' AND area LIKE 'AUDIT'"

billNo and area are the 2 fields used together to make the record we need unique. example Bill d456(billNo) can be
assigned to various depts(area). as many depts as it is
assigned rep the many times it is used. ie therefore Bill d456 assigned to the audit, finance and Hresources appears 3 times.
Therefore for me to pull the record rep Bill d456 for the audit dept I have do a select * by billNo and area like "AUDIT" as above.
This infor like I said is displayed in a form so the user also has the ability to add or update this displayed infor.


Now this is what I want to do,
include an input box(readonly) that is also required to capture the various dept(area) where the Billno d456 has been assigned.just one input box that will show me that the same billNo d456 was also assigned to not just the audit dept but also the finance & humanresource dept. so the input box will just show the result "audit, finance & Hresource.
How do I get around this .
Is it to have 2 select statements on a page /form??
If that is the answer then how do I go about that
below is my exsisting script.Thanks as always!!!

<%

Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("adodb.Recordset")
Conn.Open "eiwp"
SQLQuery = "SELECT * FROM tblopgaCOm2 WHERE billNo = '" & Request.Form("BILLNO") & "' AND area LIKE 'AUDIT'"
RS.Open SQLquery, Conn

%>

<table border="0" cellpadding="2" cellspacing="4" width="120%">
<tr>
<td width="100%">

<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="49%" 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="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="28%" 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>
</table>
</form>

 

[CassidyHunt]

OK. I think I understand what you want to do. I think you are trying to access two tables with one select statement. Almost like a join. Then you want to be able to format a page to display the results and show all matches.

SELECT Employees.Department, Supervisors.SupvName
FROM Employees INNER JOIN Supervisors
WHERE Employees.Department = Supervisors.Department;

This is the example in the microsoft help. Basically Employees is the table and department would be a field in that table. Same with Supervisors and SupvName. Join is basically just like Access.

Then you should be able to grab all the data with a single select statement and display it on the page.

Let me know if this helps and if I am on the right track.

Cassidy

 

[ifeyinwa]

you are really not on the right track.
have just one table. The additional input box I want added to the form is meant to pull out(from the table) the other depts(area) where Billno d456 has been assigned.
Do you or anyone understand what I mean??

 

[CassidyHunt]

Ok I have reread it carefully and I think I understand. You are displaying a form that can only show one record, yet you might have 3 or 4 records based on the criteria. You want to show the one record but have someway of displaying that there are really 3 records for that criteria.

try this

<select size="1" name="D1">

<%
rs.movefirst
%>
<option selected><%Response.Write rs("Dept")%></option>
<%
rs.movenext
do until rs.EOF = true
%>
<option><%Response.Write rs("Dept")%></option>
<%
rs.movenext
loop
%>
</select>

I hope this is the right track. This will create a dropdown box that will hold all the departments selected from your SQL statement and enable you to display the rest of the data in a form.

Let me know

Cassidy

 

[ifeyinwa]

You are almost there.
yes I am displaying a form that shows one record with
SELECT * FROM tblopgaCOm2 WHERE billNo = '" & Request.Form("BILLNO") & "' AND area LIKE 'AUDIT'"

The same "billNO"-d456 appears more than once but the "area" makes it unique
On the same form or at least the same page i want to include another Textbox that will capture or extract the other "area" where billNo d456 appears. something like
below,
SELECT area FROM tblopgaCOm2 WHERE billNo = '" & Request.Form("BILLNO") "

but not sure if I can include 2 select statements in one form & if so how that is if this is the way to go??


hope you catch my drift better now??

 

[guitardave78]

I think you need to use 2 record sets make the second one

<%

Set Conn = Server.CreateObject("ADODB.Connection")
Set RsNew = Server.CreateObject("adodb.Recordset")
Conn.Open "eiwp"
SQLQuery = "SELECT area FROM tblopgaCOm2 WHERE billNo = '" & Request.Form("BILLNO") "
RsNew.Open SQLquery, Conn

%>

As long as teh record set name is different you can use as many as you like.

If the new select stament omes after the rest of the info is displayed you can close the RS record set and then reopen it with the new statment so

Rs.close
SQLQuery = "SELECT area FROM tblopgaCOm2 WHERE billNo = '" & Request.Form("BILLNO") "
Rs.Open SQLquery, Conn


But the upshot is use 2 recordset type queries

 

[CassidyHunt]

You can use two recordsets to handle the select statement. just as follows:

<%
dim rs1
dim rs2
dim sSQL

set rs1 = Server.Createobject("ADODB.Recordset")
set rs2 = Server.Createobject("ADODB.Recordset")

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

rs1.open sSQL,Conn

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

rs2.open sSQL,Conn

do until rs1.EOF = true
Response.write rs1("Field") & "<BR>"
rs1.movenext
loop
Response.write "<BR>"

do until rs2.EOF = true
Response.write rs1("Field") & "<BR>"
rs1.movenext
loop

%>

Now of course your wanting to put the values in forms or textboxes so you will need to place the appropriate HTML in there.

HOpefully I have it now.

Cassidy

 

[ifeyinwa]

I am getting errors using cassidy hunt's code

ID#:
ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/eiwp/audit3.asp, line 0

this is my entire asp not sure the problem??


<%
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

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

do until Rs1.EOF = true
Response.write Rs1("area") & "<BR>"
Rs1.movenext
loop
Response.write "<BR>"

do until Rs2.EOF = true
Response.write Rs1("area") & "<BR>"
Rs1.movenext
loop

%>

In html- the form I have ( just included some lines of my html below)

<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>
<input type="text" name="area" style="background-color: #D2D2D2; color: #FF0000; font-weight: bold; text-align: Left" size="8" value="<%=Rs2("area")%>" readonly></font></b></p>

 

[CassidyHunt]

I am sorry. I didn't run the code. It looks like your still using my loop statement to loop through the code. You would need to replace those or add your HTML code in there. I just did that for an example. If you want to see if the code works eliminate the HTML portion and add just under the delcaration on error resume next.

Should look like this.

<%
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

'Place HTML coding or formatting here.
do until Rs1.EOF = true
Response.write Rs1("area") & "<BR>"
Rs1.movenext
loop
Response.write "<BR>"

do until Rs2.EOF = true
Response.write Rs1("area") & "<BR>"
Rs1.movenext
loop

%>

This will already display all the records in the recordset. If you need to start over again you would add a rs1.movefirst or rs1.move (someposition). I would eliminate the do loops before doing any HTML code. I just used those for a quick example.

Sorry about the error. I should had warned you I didn't test the code.

 

[CassidyHunt]

I noticed one more thing. In the second do loop I have rs1.movenext again. that needs to be rs2.movenext

do until Rs1.EOF = true
Response.write Rs1("area") & "<BR>"
Rs1.movenext
loop
Response.write "<BR>"

do until Rs2.EOF = true
Response.write Rs1("area") & "<BR>"
Rs2.movenext
loop

%>

If you need help with the HTML portion let me know.

Cassidy
Again I just wrote it on the fly. I will keep looking to make sure no more errors.

 

[CassidyHunt]

I made up a fake database to try and simulate what you have. I was able to test everything but the select statements in that environment. The code worked to display what I think your needing.

<%
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

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

Rs1.MoveFirst

%>
<input type="text" name="billNo" style="background-color: #D2D2D2" size="9" value="<%Response.Write rs1("billNo")%>" readonly></td>
<input type="text" name="area" style="background-color: #D2D2D2; color: #FF0000; font-weight: bold; text-align: Left" size="8" value="<%Response.Write Rs1("area")%>" readonly>
<textarea rows="8" name="priorcomments" readonly style="background-color: #D2D2D2" cols="50"><%Response.Write Rs1("priorcomments")%></textarea>
<%
do until Rs2.EOF = true
sText = sText & Rs2("area") & ","
Rs2.MoveNext
loop
%>
<input type="text" name="area" style="background-color: #D2D2D2; color: #FF0000; font-weight: bold; text-align: Left" size="8" value="<%Response.Write sText%>" readonly></font></b></p>

This has the HTML and ASP included in one page to format the display. Let me know if you need more or have any errors.

Cassidy

 

[ifeyinwa]

There are no more errors. however The form is being populated by the first select statement But the new textbox(Description)which should be populated with the 2nd select statement is not.it only displays "audit" as the result but there should be more depts displayed in this textarea not just "audit".

my sql is okay cause I tried it in the sql view.

The problem is still with the 2nd select statement.

 

[CassidyHunt]

Instead of using a text box use a text area.

<textarea rows="2" name="S1" cols="20"><%Response.Write & chr(34) & sText & chr(34) %></textarea>

Text boxes sometimes have a problem with spaces in database or formatting. Also I included quotes just to make sure to get all the text.

In addition place at the end of the file this to make sure there is more data to display then the one item

<%Response.Write sText%>

Let me know the results

cassidy

 

[ifeyinwa]

still the same result. No change . My textarea is still not displaying anything.

 

[CassidyHunt]

If you used the code above, with what I had you add, that means there is only one record matching the results.

I would try to open the table in something like Access and make sure the data is formatted the same, spelling and things like that.

If that isn't the case then we need to look at the data from the form. If we are doing a comparison for numerical data we need to change the Request.form("billNO") to be val(Request.form("billNO")).

Let me know what you find.

Cassidy

 

[ifeyinwa]

TO be honest with you at this point I am lost .
not sure what to do next

 

[amorous]

it is better to restate your present position and the problem you are facing in a new thread.....

May be some others can come up with the solution..

-VJ

 

[ifeyinwa]

Thank you I will do that

 

[CassidyHunt]

Amorous has a good point. I think posting in another thread would be good.

I was curious if you could still post in this thread an example of the data from the database. Possibly 5 or 6 sample records. This way I can get a better feel for what you are dealing with.

Cassidy