[thelist] ASP & the Find Method

Ken Schaefer ken at adOpenStatic.com
Tue Dec 3 19:35:01 CST 2002


Couple of things:

a) Use a WHERE clause in your SQL statement to bring back only the rows that
match your criteria rather than returning the entire table to ADO:

<%
strSQL = _
    "SELECT a.First_Name, a.Last_Name, " & _
    "a.Building, a.Room_Number, a.[Date], " & _
    "a.[Time], b.[Last Name], b.[Completed_(Y/N)], " & _
    "b.Completed_By, b.Problem, b.Resolution " & _
    "FROM CallLog_Table  AS a " & _
    "INNER JOIN Log_Table AS b " & _
    "ON a.Last_Name = b.[Last Name] " & _
    "WHERE a.First_Name = '" & varFName & "'"

Set objRS = objConn.Execute(strSQL)

If objRS.EOF then
    ' Add New Record
Else
    ' Edit existing record
End If
%>

b) Use aliases for your tablenames - saves a heap of typing :-)

c) Don't use fieldnames like "Date", and "Time" - they are function names
and as such are reserved. This wil come back to bite you in the behind at
some stage :-). Use CallDate, or CallTime, or DateModified or similar (which
is also more descriptive of what the date actually represents).

d) Don't pass a connection string to the Recordset's .Open method. This
creates an ADO connection behind the scenes which you don't have a reference
to, so you can't return it to the connection pool:

[Recommended Reading]:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;191572
INFO: Connection Pool Management by ADO Objects Called From ASP

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
/pooling2.asp
Pooling in the Microsoft Data Access Components

Explicitly create a connection object and open it, and use that to open the
recordset.

e) Using the faster adOpenForwardOnly/adLockReadOnly cursor/lock type will
give you about a 40% increase in performance when using Jet/Access.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <ceverett51 at attbi.com>
Subject: [thelist] ASP & the Find Method


: I am building an ASP page that I want to open a Db and find a record
matching a
: field from a form on a previous ASP page. the problem I am running into is
that
: I keep receiving an error for "Rowset does not support scrolling backward.
"
:
: Here is a copy of the relevant ASP code:
: <%Dim varFName
: varFName = Request ("fname")
: Set oRSc.Server.CreateObject("ADODB.Recordset")
: oRSc.Open "SELECT CallLog_Table.First_Name, CallLog_Table.Last_Name,
: CallLog_Table.Building, CallLog_Table.Room_Number, CallLog_Table.Date,
: CallLog_Table.Time, Log_Table.[Last Name], Log_Table.[Completed_(Y/N)],
: Log_Table.Completed_By, Log_Table.Problem, Log_Table.Resolution FROM
: CallLog_Table INNER JOIN Log_Table ON CallLog_Table.Last_Name =
Log_Table.[Last
: Name];", "DSN=contact"
: oRSc.Find "First_Name = " & chr(39) & varFName & chr(39)
: IF oRSc.EOF Then
: oRSc.AddNew
:         oRSc.Field ....
:         oRSc.update
: Else
:         oRSc.Field ....
:         oRSc.update
: End IF
:
: ....%>
: What do I need to change in this code to get it to work correctly




More information about the thelist mailing list