Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003

    Post Unanswered: Retrieve specific records from database

    I'm pulling my hair out. Been working 1 solid week on this. Can anyone help?

    I have an access database. ASP. I want to allow visitors to edit changes to entries they previously made. No password necessary. If visitor types in his or her name, want to be able to match the name typed in with names already found in the database, and pull up a list of all those names/records to allow person to edit.

    Database table (named Calendar) looks like this:

    1 Picnic Fun Outing 12 1 2003 John Doe 12/22/02
    2 Circus Cirque du 13 1 2003 Frank York 12/26/02
    3 Football Kings vs. 10 2 2003 John Doe 1/3/03
    4 Movie Triple XXX 22 2 2003 Mary Smith 1/5/03

    Want to be able to retrieve all records under ADDEDBY by John Doe so that he can edit anything he's already added.

    Have tried MANY variations of SELECT.

    None worked. Is there something about my connection that makes the database retrieval a problem????

    Here's my code:
    'Create and open connection
    set db_conn = Server.CreateObject("ADODB.Connection")
    db_conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    db_conn.Open Server.Mappath("/dbases/users.mdb")

    'Create and open recordset object
    Set objRecordset = Server.CreateObject("ADODB.Recordset")
    objRecordset.Open "calendar", db_conn, adOpenStatic, adLockPessimistic, adCmdTable

    'get the name entered by visitor in referring page

    'write the name to top of form to make sure name captured
    '(by the way, the name is being written to top of page)

    'create variable to hold the name

    'query the database
    sql = "SELECT * FROM [Calendar] WHERE AddedBy = "AddedBy"

    'if the name entered does not match any name in the ADDEDBY column,
    'tell visitor cannot edit unless has previously entered something


    <table border="1" width="100%">
    <tr bgcolor="#b0c4de">

    'display the database listing for editing
    'this works fine, but it retrieves ALL of the records, instead of the ones
    'that match the name of the visitor. I don't want visitors to be able to
    'edit anyone else's entries

    for each x in objRecordset.Fields
    response.write("<th>" & ucase( & "</th>")

    <%do until objRecordset.EOF%>
    <tr bgcolor="#f0f0f0">
    <form method="post" action="db_edit.asp" target="_blank">
    for each x in objRecordset.Fields
    if"ID" AND"AddedBy" then%>
    <td><input type="submit" name="ID" value="<%=x.value%>"></td>
    <td><%Response.Write(x.value)%> </td>
    <%end if

    set objRecordset=nothing
    set db_conn=nothing

    Thanks in advance for any help you can give.
    (Might be simple to you, but it's got me going in circles!!)
    Last edited by seraaj; 01-06-03 at 22:17.

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Retrieve specific records from database

    You never actually execute your select statement. You do this:

    sql = "SELECT * FROM [Calendar] WHERE AddedBy = "AddedBy"

    All that does is assign a character string to a variable, it doesn't execute anything. You then never refer to the sql variable again. It also has a syntax error - I think it should be something like:

    sql = "SELECT * FROM [Calendar] WHERE AddedBy = '" & AddedBy & "'"

    What does work is this line:

    objRecordset.Open "calendar", db_conn, adOpenStatic, adLockPessimistic, adCmdTable

    ... which I guess automatically does a "SELECT * FROM calendar" without a WHERE clause, hence you get all records returned.

  3. #3
    Join Date
    Jan 2003

    I changed the syntax of the response.form variable from "ADDEDBY" to ' " & AddedBy & " ' " and voila!!

    I'll be more attentive to the small details from now on.

    Thanks, again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts