Retriving data from Database

Discussion in 'ASP General' started by iffy agbim, May 25, 2004.

  1. iffy agbim

    iffy agbim Guest

    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!
     
    iffy agbim, May 25, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    >
    >
    > 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!
     
    Aaron Bertrand - MVP, May 25, 2004
    #2
    1. Advertising

  3. iffy agbim

    Robin Guest

    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" <> wrote in message
    news:...
    >
    >
    > 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!
     
    Robin, May 25, 2004
    #3
  4. 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.
     
    Bob Barrows [MVP], May 25, 2004
    #4
  5. iffy agbim

    iffy agbim Guest

    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!
     
    iffy agbim, May 25, 2004
    #5
  6. 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.
     
    Bob Barrows [MVP], May 25, 2004
    #6
  7. iffy agbim

    TomB Guest

    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" <> wrote in message
    news:%...
    >
    > 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!
     
    TomB, May 25, 2004
    #7
  8. iffy agbim

    iffy agbim Guest

    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!
     
    iffy agbim, May 25, 2004
    #8
  9. iffy agbim

    iffy agbim Guest

    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!
     
    iffy agbim, May 25, 2004
    #9
  10. 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.
     
    Bob Barrows [MVP], May 25, 2004
    #10
  11. iffy agbim

    iffy agbim Guest

    Have done the correction and now get this error.don,t know what to do
    from hence

    ADODB.Recordset error '800a0bcd'

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

    /eiwp/audit3.asp, line 27



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    iffy agbim, May 26, 2004
    #11
  12. iffy agbim wrote:
    > Have done the correction and now get this error.don,t know what to do
    > from hence
    >
    > ADODB.Recordset error '800a0bcd'
    >
    > Either BOF or EOF is True, or the current record has been deleted.
    > Requested operation requires a current record.
    >
    > /eiwp/audit3.asp, line 27
    >


    Is that the GetString line? Obviously, the query is not returning a record.
    Time for some basic debugging:

    sSQL="Select area FROM tblopgaCOm2 WHERE billNo = '" & _
    Request.Form("BILLNO") & "'"
    Response.Write sSQL
    Response.End

    Run the page and verify that the query is correct. Open the database in
    Access and use the Query Builder to run the sql statement from the browser
    window in order to verify that it has been built correctly.

    My code was for example only. It was not intended to be run as-is. You are
    expected to put some error-handling into the code to handle situations such
    as this. for example, instead of merely saying:

    set rs=cn.execute(sSQL,,1)
    sArea=rs.GetString(1,,"","; ")

    You should be saying:

    set rs=cn.execute(sSQL,,1)
    if not rs.EOF then
    sArea=rs.GetString(1,,"","; ")
    else
    response.write "the query did not return any results"
    rs.close : set rs = nothing
    cn.close : set cn = nothing
    end if


    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.
     
    Bob Barrows [MVP], May 26, 2004
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. george d lake
    Replies:
    0
    Views:
    490
    george d lake
    Aug 29, 2003
  2. simon

    Retriving data

    simon, Nov 26, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    368
    simon
    Nov 26, 2003
  3. sp
    Replies:
    1
    Views:
    433
    Joe Kesselman
    Feb 7, 2006
  4. Aneesh a

    retriving data from memo data type of msaccess to asp.net

    Aneesh a, May 7, 2005, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    112
    Aneesh a
    May 7, 2005
  5. jeghers
    Replies:
    2
    Views:
    87
    Jeff North
    May 2, 2004
Loading...

Share This Page