Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Retriving data from Database

Reply
Thread Tools

Retriving data from Database

 
 
iffy agbim
Guest
Posts: n/a
 
      05-25-2004


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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
 
 
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      05-25-2004
Have some patience, you just posted after the end of the work day yesterday,
so people really haven't even had an hour to digest this yet. You have a
lot of information here, so it's not going to be a two-word or five-minute
answer. But if you keep repeatedly posting the same question over and over
again in new threads, you will be put on ignore lists quite fast.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/




"iffy agbim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>
> 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"> <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"> <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>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



 
Reply With Quote
 
 
 
 
Robin
Guest
Posts: n/a
 
      05-25-2004
You could do this within your page:
<select name='billDept'>
<%set sql2 = conn.execute("SELECT * FROM tblopgaCOm2 WHERE billNo = '" &
Request.Form("BILLNO") "'")
while not sql2.eof%>
<Option value='<%=sql2("area")%>'> <%=sql2("area")%></option>
<%sql2.MoveNext
wEnd%>
</Select>

"iffy agbim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>
> 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"> <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"> <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>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-25-2004
iffy agbim wrote:
> 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.


No you don't. Without a wildcard, LIKE is the same as =. You may as well use
area='AUDIT'

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


You have really lost me here. I've tried reading it out loud, re-punctuating
it, and meditating on it <grin> and I still can't make any sense out of it.
Care to try again?

And you don't need to show us all the HTML. This is an ASP newsgroup, not an
HTML newsgroup

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
iffy agbim
Guest
Posts: n/a
 
      05-25-2004

Sorry if my question isn't clear.this is a simplified version

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-25-2004
iffy agbim wrote:
> Sorry if my question isn't clear.this is a simplified version
>
> yes I am displaying a form that shows one record with
> SELECT * FROM tblopgaCOm2 WHERE billNo = '" &
> Request.Form("BILLNO") & "' AND area LIKE 'AUDIT'"


Again, the LIKE is not needed and may cause performance to suffer. Use "=".

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

A little. You want to show a list of the areas to which a bill has been
assigned in a textbox. Why not a listbox?

Anyways, you can run two selects in your server-side code. It would look
like this (I will illustrate this with dynamic sql, but you would be better
off using saved parameter queries):

<%
dim cn, rs, , sSQL, sAreas
set cn=server.createobject("adodb.connection")
cn.open "<valid connection string>"
sSQL="Select area FROM tblopgaCOm2 WHERE billNo = '" & _
Request.Form("BILLNO") & "'"
set rs=cn.execute(sSQL,,1)
sArea=rs.GetString(1,,"","; ")
rs.close
sSQL = "SELECT <list of fields> FROM tblopgaCOm2 " & _
"WHERE billNo = '" & Request.Form("BILLNO") & _
"' AND area = 'AUDIT'"
set rs=cn.execute(sSQL,,1)
etc.

The sArea variable will contain the list of areas. Just response.write that
into your read-only textbox

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
TomB
Guest
Posts: n/a
 
      05-25-2004

You can do one of two things.
1) Execute two seperate queries.
SQLQuery="SELECT billno, area, upddate from tblopgaCOm2 WHERE
billNo...AND area='AUDIT'
then
SQLQuery="SELECT billno, area, upddate from tblopgaCOm2 WHERE
billNo...AND NOT area='AUDIT'
2) Execute one query, and just check for which result
SQLQuery="SELECT billno, area, upddate from tblopgaCOm2 WHERE
billNo='whatever'"
if RS.Fields("area")="AUDIT" then
'write out the audit stufff
else
'write out the other stuff
end if


"iffy agbim" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
> Sorry if my question isn't clear.this is a simplified version
>
> 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??
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



 
Reply With Quote
 
iffy agbim
Guest
Posts: n/a
 
      05-25-2004
used your entire code Bob this is the error
I get.
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/eiwp/audit3.asp, line 27


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
iffy agbim
Guest
Posts: n/a
 
      05-25-2004
this is the exact line of code the error references

sArea=rs.GetString(1,,"","; ")



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-25-2004
iffy agbim wrote:
> this is the exact line of code the error references
>
> sArea=rs.GetString(1,,"","; ")
>
>

My mistake. It should be:
sArea=rs.GetString(2,,"","; ")

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
retriving data from memo data type of msaccess to asp.net Aneesh a ASP .Net Web Services 0 05-07-2005 01:11 AM
retriving info from a database and print it ind a form??? jeghers Javascript 2 05-02-2004 02:30 PM
adding and retriving data from the machine.config? george d lake ASP .Net 0 08-29-2003 09:00 PM



Advertisments