Comparing field records in Access Database

**Caesar** · Oct 5, 2001 07:01

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!

**dhtmlgod** · Oct 5, 2001 07:07

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

**Caesar** · Oct 5, 2001 09:35

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?

**shane** · Oct 5, 2001 14:49

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.

**dhtmlgod** · Oct 8, 2001 03:05

Sorry about that, guess I should have included the connection string.

Thanks shane for clearing that up while I was away

User Tag List

Thread: Comparing field records in Access Database

Thread Tools

Display

Comparing field records in Access Database

Bookmarks

Bookmarks

Posting Permissions