Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cursor Location/type Clarification

Status
Not open for further replies.
joeisbatman

joeisbatman

Programmer
Oct 8, 2001
39
US
Alright, I'm new with ASP. Let me explain the purpose of the site: Its a site that will have many people VIEWING records at the same time. This number viewing will be VERY high, I'm thinking many many thousands (50 thousand??) per day. A much lower number will be actually editing records/ creating new ones.

This in mind I'm really concerned with the effiecncy of the code- I read the FAQ and in it, link9 wrote:

dim rs
set rs = server.createobject (“ADODB.Recordset”)
rs.activeconnection = con
rs.cursortype = adOpenStatic
rs.cursorlocaton = adUseClient
rs.locktype = adLockOptimistic
rs.open “SELECT * FROM myLittleTable”
set rs.activeconnection = nothing
con.close

See that? I just closed the connection, I still have my data, and I've freed up some server resources. If you use a client side cursor, then you don’t even need to keep your active connection. Can you say, “My web site can now support more visitors”?

Currently my code looks like this "
Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
connectionToDatabase.ConnectionTimeout=60 connectionToDatabase.Open "DSN=MyBuddyInfo1"
Set recordSet=Server.CreateObject("ADODB.RecordSet")

recordSet.Open "SELECT * FROM Users_Accounts_A WHERE ID=" &frmID, connectionToDatabase


THEN AT THE VERY BOTTOM OF MY CODE I CLOSE THE DATABASE
connectionToDatabase.Close
set connectionToDatabase=Nothing

Keep in mind, each there aren't many things being pulled from the database in the page, maybe 5 at most, and they'll all be in the same row. WHat I don't understand is is there a way to store the data in a record set then close my connection up top? WOuld this be a good idea? Please note I am very unclear on cursortype/location, but I have a feeling that I want to use a clientside cursor location and close my connection at the top of my code, and then i should still be able to access the record set later. If this is correct could someone type out that code for me, I can't get it to work. And if that isn't correct, please enlighten. Thanks a million
JOE
 
Sort by date Sort by votes
Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
connectionToDatabase.ConnectionTimeout=60 connectionToDatabase.Open "DSN=MyBuddyInfo1"
Set recordSet=Server.CreateObject("ADODB.RecordSet")

recordSet.cursorLocation = 2 'adUseClient
recordSet.cursorType = 3 'adOpenStatic
recordSet.lockType = 3 'adLockOptimistic


recordSet.Open "SELECT * FROM Users_Accounts_A WHERE ID=" &frmID, connectionToDatabase

connectionToDatabase.close
set connectionToDatabase = nothing

Should do the trick for you, but that connection looks suspiciously like a connection to an Access database (no password or userID). If that's the case, and you are expecting up to 50k visitors per day to this site w/updating, selecting, etc... I fear that I see many headaches in your immediate future. With that amount of traffic accessing your site and its data, the only choice is an enterprise scale database system such as SQL Server or Oracle. No way in the world Access is going to support that many users -- you're bound to have concurrency issues. You could even go w/ something like mySQL (free), but you're going to have to get real friendly w/ SQL (the language) if you plan to do that because of its lack of widgets and such that make managing a database much easier.

I might be wrong about the connection, though. Hope so.

Good luck! :)
Paul Prewett
penny.gif
penny.gif
 
Upvote 0 Downvote
OKay, I tried the code you suggested and get the following error
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/index2.asp, line 25
The line of code is:
<body bgcolor=&quot;<% Response.Write(RecordSet(&quot;BGCOLOR&quot;)) %>&quot; ALINK=&quot;<% Response.Write(RecordSet(&quot;LINK_COLOR&quot;)) %>&quot; VLINK=&quot;<% Response.Write(RecordSet(&quot;LINK_COLOR&quot;)) %>&quot; LINK=&quot;<% Response.Write(RecordSet(&quot;LINK_COLOR&quot;)) %>&quot;>

This is also the first time I access the recordset. You said I might need to go to an SQL server, do you think thats necessary even tho each visitor will only be pulling like ONE single cell out of ONE table in my database? The database is incredably simple...

Thanks again
Joe
 
Upvote 0 Downvote
Usually you get this message if your table(or tables) in a database doesn't have fields with the names you're using. So check all the names; you might have misspelled them.
Shouldn't there be RecordSet(&quot;BG_COLOR&quot;) instead of [red]RecordSet(&quot;BGCOLOR&quot;)[/red]?
 
Upvote 0 Downvote
The names are correct--- and plus, everything worked BEFORE I made the changes

--Joe
 
Upvote 0 Downvote
If you got this error:
Item cannot be found in the collection corresponding to the requested name or ordinal.


Then the name is not correct. You are trying to access a column name incorrectly.

I would suggest looking at the table and comparing all the column names one by one to find the one that has been misspelled somewhere. That's the only thing that error ever means. It does not have to do with the connection.

:)
Paul Prewett
penny.gif
penny.gif
 
Upvote 0 Downvote
even tho each visitor will only be pulling like ONE single cell out of ONE table in my database

Really isn't the issue. The issue is concurrency. If more than one user at a time tries to access the same record, then Access is going to return an error, whereas SQL Server will just make them wait until the record is available. There are ways around this, but it puts alot more load on you as a developer trying to handle these exceptions in your code, rather than relying on the database to handle them automatically.

If there really are going to be that many users, I just don't see how Access could handle it. :-(
penny.gif
penny.gif
 
Upvote 0 Downvote
Okay guys,
I don't think you're exactly correct about that error- My field names are all correct. I double checked each one. I found the immediate problem, but it still doesn't accomplish the goal that Paul had in the first place (closing the connection). When I delete the &quot;connectionToDatabase.close
set connectionToDatabase = nothing
at the top, it works. My code WAS after pauls suggestion:

frmID=Request.QueryString(&quot;ID&quot;)
Set connectionToDatabase=Server.CreateObject(&quot;ADODB.Connection&quot;)
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open &quot;DSN=MyBuddyInfo1&quot;
Set recordSet=Server.CreateObject(&quot;ADODB.RecordSet&quot;)

recordSet.cursorLocation = 2 'adUseClient
recordSet.cursorType = 3 'adOpenStatic
recordSet.lockType = 3 'adLockOptimistic

recordSet.Open &quot;SELECT * FROM Users_Accounts_A WHERE ID=&quot; &frmID, connectionToDatabase

connectionToDatabase.close
set connectionToDatabase = nothing%>
<html>
<body bgcolor=&quot;<% Response.Write(RecordSet(&quot;BGCOLOR&quot;)) %>&quot;
One of my friends thought that when we close the connection so early, the recordset object gets destroyed, which means it cannot find &quot;BGCOLOR&quot; in a recordset that is not there. Maybe I misunderstood the faq by paul about closing the connection so early...
Thanks again
joe
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
420
penguinspeaks
penguinspeaks
DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
331
DiDYRO_
DiDYRO_
leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
262
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
type mismatch error
Replies
2
Views
116
penguinspeaks
penguinspeaks
xrum
  • Locked
  • Question
Transaction cannot have multiple recordsets with this cursor type.
Replies
8
Views
270
BigRed1212
BigRed1212

Part and Inventory Search

Sponsor

Back
Top