SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Caesar's Avatar
    Join Date
    May 2000
    Location
    On some harddisk
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Comparing field records in Access Database

    Let's say that I have a row in the table with the following data:

    Row1:

    Field1: 123
    Field2: 555
    Field3: 789
    Field4: 369
    Answer: 123
    Chosen: 123

    How do I code an ASP script so that the fifth field and the sixth field will be compared and if there are the same, I will print a certain message and if not, I will print another message?

    I want to use a loop so that it goes through every single row in the database.

    Any ideas? TIA!
    Go increase web site traffic by using 27 proven strategies!

  2. #2
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    Code:
    <%
    Set compareAnswer = objDBCon.Execute("SELECT Answer, Chosen FROM tblsQuz")
    Do Until compareAnswer.EOF
       If compareAnswer("answer") = compareAnswer("chosen") then
          response.write("Well done, you got it right<br>")
       else
          response.write("Wrong!")
       end if
       compareAnswer.MoveNext
    Loop
    %>
    Edit:


    Forgot to make it loop. lol
    Last edited by dhtmlgod; Oct 5, 2001 at 07:10.

  3. #3
    SitePoint Evangelist Caesar's Avatar
    Join Date
    May 2000
    Location
    On some harddisk
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that.

    With the following code, I get a error message: "Object doesn't support this property or method: 'Execute'
    /stats/stats.asp, line 11"

    Code:

    <% Response.buffer = true %>
    <html>
    <head>
    <title>Stats Page</title>
    </head>
    <body>
    <% Dim rs
    Set rs = Server.CreateObject ("ADODB.Recordset")
    rs.Open "dataentry", "DSN=MyStats"

    Set compareAnswer = rs.Execute("SELECT Answer, Chosen FROM dataentry")
    Do Until compareAnswer.EOF
    If compareAnswer("answer") = compareAnswer("chosen") then
    response.write("Well done, you got it right<br>")
    else
    response.write("Wrong!")
    end if
    compareAnswer.MoveNext
    Loop

    con.Close
    Set con = Nothing

    %>
    </body>
    </html>

    So what's wrong?
    Go increase web site traffic by using 27 proven strategies!

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Caeser,

    In your code you're getting the entire 'dataentry' table into the recordset whereas dhtmlgod simply used an sql query to get just the 'Answer' and 'Chosen' fields from the table, which is more efficient. Having got the entire table into the recordset you then try to execute an sql string which causes the problem because the recordset object doesn't have an Execute method and even if it did it'd be unnecessary as you've already got the data in the recordset.

    Because you've opened the entire table you could actually just remove the following line and I think it'd work.
    Code:
    Set compareAnswer = rs.Execute("SELECT Answer, Chosen FROM dataentry")
    dhtlgod, used the Execute method of the connection object which can (and in this instance does) return a recordset object.

    Code:
    '--So instead of:
    Set rs = Server.CreateObject ("ADODB.Recordset") 
    rs.Open "dataentry", "DSN=MyStats" 
    
    Set compareAnswer = rs.Execute("SELECT Answer, Chosen FROM dataentry")
    
    
    
    '--You can use either open the recordset using the connection execute method or using the recordset open method.
    'Set up sql
    strSQL = "SELECT Answer, Chosen FROM dataentry"
    
    'Create connection object
    Set objConn = Server.CreateObject("ADODB.Connection")
    'Open connection
    objConn.Open "DSN=MyStats" 
    
    'Method 1
    'Open recordset using connection execute method
    Set objRS = objConn.Execute(strSQL)
    
    'Method 2
    'Set the required recordset properties
    With objRS
      .ActiveConnection = objConn
      .Source = strSQL
      .Open
    End With
    The second method does give more flexibilty, if needed, as you can specify different recordset properties which you can't if you use the execute method.

    With ADO there's often a bunch of different ways that something can be done which can lead to some confusion.

    I hope this helps.
    Last edited by shane; Oct 5, 2001 at 14:53.

  5. #5
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that, guess I should have included the connection string.

    Thanks shane for clearing that up while I was away


Bookmarks

Posting Permissions

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