|
 |
asp_databases thread: objRS.RecordCount
Message #1 by "Nick Middleweek" <nickm@t...> on Thu, 10 Aug 2000 18:12:26 +0000
|
|
Hello,
Every time I perform a SELECT on SQL7 with ASP, I check the
objRS.RecordCount property, thinking it will contain the number of records
returned? This isn't the case.
I'm making the connection using
Set objConn = Server.CreateObject("ADODB.Connection")
strConn = "DSN=db"
objConn.Open(strConn)
strQuery = "SELECT * from table1"
Set objRS = obj.Conn.Execute(strQuery)
If objRS > 0 Then
do something
Else
do something else
End if
Also, is the above code, beginners code, i.e. Are there better ways of
connecting to dbs and retrieving data from tables. I'm fairly new to all
this SQL7 and ASP IIS5 stuff so any help will be appreciated.
And yes, I've loads of books:
VBScript programmers reference - wrox
Professional Active Server Pages 3.0 - wrox
Sams teach yourself ASP in 24 hours (supposedly!)
SAMS SQL in 21 days
SAMS SQl Server 7 in 21 days
SAMS e-commerce in 21 days
Windows Scrpit Host - wrox
Does anyone think these are good reads? And good references that I can read
from. The ASP 3.0 wrox book is massive and I'm finding it hard to get into.
Thanks in advance!
Regards
Nick Middleweek
Message #2 by Fredrik Normen <fredrik.normen@s...> on Fri, 11 Aug 2000 10:26:36 +0200
|
|
Hi,
Instead of Using ODBC you should use SQLOLEDB ( Oledb provider for SQL
Server )
This will increase performance. How you create the COnnectionstring with
OLEDB is very simple:
strCOnn = "Provider=SQLOLEDB;Data Source=MYSQLSERVER;Initial
Catalog=MyDataBase;User Id=sa;password=;"
Provider = What provider you want to use. There are alot of provider to
select from.
But if you use SQL7 you can use the SQLOLEDB provider.
Data Source = The name of you SQL Server
Initial Catalog = The database you use for example Northwind.
User Id = The user
Password = The users password.
When you want to check if you have retrieved some data you can check the
.EOF and .BOF Property of the Recordset object like this.
if Not rs.EOF then
'Do something
else
'There is no data
end if
If you want to step through the recordset you can do something like this.
while Not rs.EOF
Response.Write rs("FieldName") & "<BR>" 'FieldName = THe column
name you want to see
rs.MoveNext ' MoveNext will jump to the next row in the recordset.
wend
I think Wrox have a book with name ASP and ADO or something.
I have look at it and it's look like to be a great book if you want to learn
how to use ADO in a ASP Page.
/Fredrik Normen
-----Original Message-----
From: Nick Middleweek
Sent: den 10 augusti 2000 20:12
To: ASP Databases
Subject: [asp_databases] objRS.RecordCount
Hello,
Every time I perform a SELECT on SQL7 with ASP, I check the
objRS.RecordCount property, thinking it will contain the number of records
returned? This isn't the case.
I'm making the connection using
Set objConn = Server.CreateObject("ADODB.Connection")
strConn = "DSN=db"
objConn.Open(strConn)
strQuery = "SELECT * from table1"
Set objRS = obj.Conn.Execute(strQuery)
If objRS > 0 Then
do something
Else
do something else
End if
Also, is the above code, beginners code, i.e. Are there better ways of
connecting to dbs and retrieving data from tables. I'm fairly new to all
this SQL7 and ASP IIS5 stuff so any help will be appreciated.
And yes, I've loads of books:
VBScript programmers reference - wrox
Professional Active Server Pages 3.0 - wrox
Sams teach yourself ASP in 24 hours (supposedly!)
SAMS SQL in 21 days
SAMS SQl Server 7 in 21 days
SAMS e-commerce in 21 days
Windows Scrpit Host - wrox
Does anyone think these are good reads? And good references that I can read
from. The ASP 3.0 wrox book is massive and I'm finding it hard to get into.
Thanks in advance!
Regards
Nick Middleweek
---
You are currently subscribed to asp_databases
Message #3 by "Nick Middleweek" <nickm@t...> on Fri, 11 Aug 2000 12:32:27 +0000
|
|
Fred,
Whats the difference then between the ADODB and SQLOLEDB?
Is SQLOLEDB more up to date than ADODB or does it bypass some other
translators or something?
How come we can't use the RecordCount property of the record set?
I've tried doing the checks for EOF and BOF but when there are no records
returned it in the recordset it gives me an error?
Thanks for your input!
Regards
Nick Middleweek
----------
>From: Fredrik Normen
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] RE: objRS.RecordCount
>Date: Fri, Aug 11, 2000, 08:26
>
> Hi,
>
> Instead of Using ODBC you should use SQLOLEDB ( Oledb provider for SQL
> Server )
> This will increase performance. How you create the COnnectionstring with
> OLEDB is very simple:
>
> strCOnn = "Provider=SQLOLEDB;Data Source=MYSQLSERVER;Initial
> Catalog=MyDataBase;User Id=sa;password=;"
>
> Provider = What provider you want to use. There are alot of provider to
> select from.
> But if you use SQL7 you can use the SQLOLEDB provider.
> Data Source = The name of you SQL Server
> Initial Catalog = The database you use for example Northwind.
> User Id = The user
> Password = The users password.
>
> When you want to check if you have retrieved some data you can check the
> .EOF and .BOF Property of the Recordset object like this.
>
> if Not rs.EOF then
> 'Do something
> else
> 'There is no data
> end if
>
> If you want to step through the recordset you can do something like this.
>
> while Not rs.EOF
> Response.Write rs("FieldName") & "<BR>" 'FieldName = THe column
> name you want to see
> rs.MoveNext ' MoveNext will jump to the next row in the recordset.
> wend
>
> I think Wrox have a book with name ASP and ADO or something.
> I have look at it and it's look like to be a great book if you want to learn
> how to use ADO in a ASP Page.
>
> /Fredrik Normen
>
>
>
> -----Original Message-----
> From: Nick Middleweek
> Sent: den 10 augusti 2000 20:12
> To: ASP Databases
> Subject: [asp_databases] objRS.RecordCount
>
>
> Hello,
>
> Every time I perform a SELECT on SQL7 with ASP, I check the
> objRS.RecordCount property, thinking it will contain the number of records
> returned? This isn't the case.
>
> I'm making the connection using
> Set objConn = Server.CreateObject("ADODB.Connection")
> strConn = "DSN=db"
> objConn.Open(strConn)
>
> strQuery = "SELECT * from table1"
>
> Set objRS = obj.Conn.Execute(strQuery)
>
> If objRS > 0 Then
> do something
> Else
> do something else
> End if
>
> Also, is the above code, beginners code, i.e. Are there better ways of
> connecting to dbs and retrieving data from tables. I'm fairly new to all
> this SQL7 and ASP IIS5 stuff so any help will be appreciated.
>
> And yes, I've loads of books:
> VBScript programmers reference - wrox
> Professional Active Server Pages 3.0 - wrox
> Sams teach yourself ASP in 24 hours (supposedly!)
> SAMS SQL in 21 days
> SAMS SQl Server 7 in 21 days
> SAMS e-commerce in 21 days
> Windows Scrpit Host - wrox
>
> Does anyone think these are good reads? And good references that I can read
> from. The ASP 3.0 wrox book is massive and I'm finding it hard to get into.
>
> Thanks in advance!
>
> Regards
> Nick Middleweek
>
> ---
> You are currently subscribed to asp_databases
>
> ---
> You are currently subscribed to asp_databases
>
Message #4 by Fredrik Normen <fredrik.normen@s...> on Fri, 11 Aug 2000 15:27:04 +0200
|
|
SQLOLEDB is a provider ( A Driver to access SQL Databases with OLEDB ).
ADO is objects that make it easier for developer to access data with the
OLEDB or ODBC and etc.
ADO is an Interface for OLEDB.
So ADO use OLEDB API to access databases.
It's difficult to use OLEDB API so Microsoft create ADO to make it easier
for developers.
The recordcount will not work on a recordset with cusrortype of ForwardOnly
( This is default if you don't change the property cursortype of the
Recordset object.
Try to set the CursorType to adOpenStatic like this:
rs.CursorType = adOpenStatic
You have to set the cursortype before you fill the recordset with records.
When you check for EOF, do you try something like this ?
if rs.EOF and rs.BOF then
' No Record
else
' Records
end if
/Fredrik Normen
-----Original Message-----
From: Nick Middleweek
Sent: den 11 augusti 2000 14:32
To: ASP Databases
Subject: [asp_databases] RE: objRS.RecordCount
Fred,
Whats the difference then between the ADODB and SQLOLEDB?
Is SQLOLEDB more up to date than ADODB or does it bypass some other
translators or something?
How come we can't use the RecordCount property of the record set?
I've tried doing the checks for EOF and BOF but when there are no records
returned it in the recordset it gives me an error?
Thanks for your input!
Regards
Nick Middleweek
----------
>From: Fredrik Normen
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] RE: objRS.RecordCount
>Date: Fri, Aug 11, 2000, 08:26
>
> Hi,
>
> Instead of Using ODBC you should use SQLOLEDB ( Oledb provider for SQL
> Server )
> This will increase performance. How you create the COnnectionstring with
> OLEDB is very simple:
>
> strCOnn = "Provider=SQLOLEDB;Data Source=MYSQLSERVER;Initial
> Catalog=MyDataBase;User Id=sa;password=;"
>
> Provider = What provider you want to use. There are alot of provider to
> select from.
> But if you use SQL7 you can use the SQLOLEDB provider.
> Data Source = The name of you SQL Server
> Initial Catalog = The database you use for example Northwind.
> User Id = The user
> Password = The users password.
>
> When you want to check if you have retrieved some data you can check the
> .EOF and .BOF Property of the Recordset object like this.
>
> if Not rs.EOF then
> 'Do something
> else
> 'There is no data
> end if
>
> If you want to step through the recordset you can do something like this.
>
> while Not rs.EOF
> Response.Write rs("FieldName") & "<BR>" 'FieldName = THe column
> name you want to see
> rs.MoveNext ' MoveNext will jump to the next row in the recordset.
> wend
>
> I think Wrox have a book with name ASP and ADO or something.
> I have look at it and it's look like to be a great book if you want to
learn
> how to use ADO in a ASP Page.
>
> /Fredrik Normen
>
>
>
> -----Original Message-----
> From: Nick Middleweek
> Sent: den 10 augusti 2000 20:12
> To: ASP Databases
> Subject: [asp_databases] objRS.RecordCount
>
>
> Hello,
>
> Every time I perform a SELECT on SQL7 with ASP, I check the
> objRS.RecordCount property, thinking it will contain the number of records
> returned? This isn't the case.
>
> I'm making the connection using
> Set objConn = Server.CreateObject("ADODB.Connection")
> strConn = "DSN=db"
> objConn.Open(strConn)
>
> strQuery = "SELECT * from table1"
>
> Set objRS = obj.Conn.Execute(strQuery)
>
> If objRS > 0 Then
> do something
> Else
> do something else
> End if
>
> Also, is the above code, beginners code, i.e. Are there better ways of
> connecting to dbs and retrieving data from tables. I'm fairly new to all
> this SQL7 and ASP IIS5 stuff so any help will be appreciated.
>
> And yes, I've loads of books:
> VBScript programmers reference - wrox
> Professional Active Server Pages 3.0 - wrox
> Sams teach yourself ASP in 24 hours (supposedly!)
> SAMS SQL in 21 days
> SAMS SQl Server 7 in 21 days
> SAMS e-commerce in 21 days
> Windows Scrpit Host - wrox
>
> Does anyone think these are good reads? And good references that I can
read
> from. The ASP 3.0 wrox book is massive and I'm finding it hard to get
into.
>
> Thanks in advance!
>
> Regards
> Nick Middleweek
Message #5 by "Nick Middleweek" <nickm@t...> on Fri, 11 Aug 2000 14:59:31 +0000
|
|
Thanks Fred,
I'm finding this very valuable! Thankyou!
That's why ADODB is slower then, because of the extra layer it hasd to go
through?!
I get lost with cursor types and I don't understand them so I may have to
read a few chapters before I understand them.
I understand though that I can use the second parameter of Execute when used
witht the Connection object as apposed to the .... can't remember but I know
there's another object that uses it after we call open? Or have I got this
the wrong way round? Mmmm! I'll have to get my book out to know for sure.
Have you used this second parameter?
What is your prefered way of outputting data, it to call it using
objRS("FieldName") or is there a better method that we should use, perhaps
some fancy ADO or OLE thing?
Cheers Fred!
Regards
Nick Middleweek
----------
> SQLOLEDB is a provider ( A Driver to access SQL Databases with OLEDB ).
> ADO is objects that make it easier for developer to access data with the
> OLEDB or ODBC and etc.
> ADO is an Interface for OLEDB.
>
> So ADO use OLEDB API to access databases.
> It's difficult to use OLEDB API so Microsoft create ADO to make it easier
> for developers.
>
> The recordcount will not work on a recordset with cusrortype of ForwardOnly
> ( This is default if you don't change the property cursortype of the
> Recordset object.
>
> Try to set the CursorType to adOpenStatic like this:
>
> rs.CursorType = adOpenStatic
>
> You have to set the cursortype before you fill the recordset with records.
>
> When you check for EOF, do you try something like this ?
>
> if rs.EOF and rs.BOF then
> ' No Record
> else
> ' Records
> end if
>
>
> /Fredrik Normen
>
>
> -----Original Message-----
> From: Nick Middleweek
> Sent: den 11 augusti 2000 14:32
> To: ASP Databases
> Subject: [asp_databases] RE: objRS.RecordCount
>
>
> Fred,
>
> Whats the difference then between the ADODB and SQLOLEDB?
>
> Is SQLOLEDB more up to date than ADODB or does it bypass some other
> translators or something?
>
> How come we can't use the RecordCount property of the record set?
>
> I've tried doing the checks for EOF and BOF but when there are no records
> returned it in the recordset it gives me an error?
>
>
> Thanks for your input!
>
> Regards
> Nick Middleweek
Message #6 by "Fredrik Normen" <fredrik.normen@s...> on Fri, 11 Aug 2000 20:40:15
|
|
On 08/11/00, ""Nick Middleweek" <nickm@t...>" wrote:
> Thanks Fred,
>Have you used this second parameter?
>What is your prefered way of outputting data, it to call it using
>objRS("FieldName") or is there a better method that we should use,
perhaps
>some fancy ADO or OLE thing?
There is thre diffrent way to get data from the recordset in ASP.
rs("FieldName")
rs.Item("FieldName")
rs(ColumnNumber) <- This is the fastest way to access data but is not so
effiecient
I recommand you to use rs("FieldName")
or rs(ColumnNumber) when you only have few columns in the recordset.
The Connection object's second parameter is:
RecordsAffected and it's Optional.
The parameter is a Long variable to which the provider returns the number
of records that the operation affected.
/Fredrik Normen
Message #7 by "Ken Schaefer" <ken@a...> on Sat, 12 Aug 2000 23:00:00 +1000
|
|
a) Difference between ODBC, OLEDB (and where ADO fits in)
http://www.adOpenStatic.com/faq/whyOLEDB.asp
will explain the differences between ODBC and OLEDB. You use ADO to access
both. There is a diagram on the site which will explain things, and make it
much clearer, promise!
b) Why doesn't .recordCount work?
This is because you are using the wrong type of cursor. You are using the
default adOpenForwardOnly cursor. With this type of cursor, ADO doesn't know
how many records are in the recordset until it gets to the end. All it is
worried about it spitting them out as soon as possible,
If you use an adOpenStatic or adOpenKeySet cursor you will be able to
.recordCount. With both of these cursors forward and backward scrolling is
supported. In order to support this, ADO needs to know about the whole
recordset, right from the beginning. In the process it gets to know how many
records are in the recordset
That said, I will show you something which I think is faster and better
programming practice:
strSQL = "SELECT field1 FROM table1"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If objRS.EOF and objRS.BOF then
' There are no records
intNumRecords = 0
Else
arrResults = objRS.GetRows
intNumRecords = UBound(arrResults, 2) + 1
End if
objRS.close
set objRS = nothing
intNumRecords will hold the number of records in your recordset. You also
have your recordset in a VBScript array, and have immediately closed your
connection to the database, allowing your database to be more responsive,
and you page to run faster. (and you can keep the adOpenForwardOnly cursor)
Cheers
Ken
----- Original Message -----
From: "Nick Middleweek"
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, August 12, 2000 12:59 AM
Subject: [asp_databases] RE: objRS.RecordCount
> Thanks Fred,
>
> I'm finding this very valuable! Thankyou!
>
> That's why ADODB is slower then, because of the extra layer it hasd to go
> through?!
>
> I get lost with cursor types and I don't understand them so I may have to
> read a few chapters before I understand them.
>
> I understand though that I can use the second parameter of Execute when
used
> witht the Connection object as apposed to the .... can't remember but I
know
> there's another object that uses it after we call open? Or have I got this
> the wrong way round? Mmmm! I'll have to get my book out to know for sure.
>
> Have you used this second parameter?
>
> What is your prefered way of outputting data, it to call it using
> objRS("FieldName") or is there a better method that we should use, perhaps
> some fancy ADO or OLE thing?
>
>
> Cheers Fred!
>
> Regards
> Nick Middleweek
>
> ----------
> > SQLOLEDB is a provider ( A Driver to access SQL Databases with OLEDB ).
> > ADO is objects that make it easier for developer to access data with the
> > OLEDB or ODBC and etc.
> > ADO is an Interface for OLEDB.
> >
> > So ADO use OLEDB API to access databases.
> > It's difficult to use OLEDB API so Microsoft create ADO to make it
easier
> > for developers.
> >
> > The recordcount will not work on a recordset with cusrortype of
ForwardOnly
> > ( This is default if you don't change the property cursortype of the
> > Recordset object.
> >
> > Try to set the CursorType to adOpenStatic like this:
> >
> > rs.CursorType = adOpenStatic
> >
> > You have to set the cursortype before you fill the recordset with
records.
> >
> > When you check for EOF, do you try something like this ?
> >
> > if rs.EOF and rs.BOF then
> > ' No Record
> > else
> > ' Records
> > end if
> >
> >
> > /Fredrik Normen
> >
> >
> > -----Original Message-----
> > From: Nick Middleweek
> > Sent: den 11 augusti 2000 14:32
> > To: ASP Databases
> > Subject: [asp_databases] RE: objRS.RecordCount
> >
> >
> > Fred,
> >
> > Whats the difference then between the ADODB and SQLOLEDB?
> >
> > Is SQLOLEDB more up to date than ADODB or does it bypass some other
> > translators or something?
> >
> > How come we can't use the RecordCount property of the record set?
> >
> > I've tried doing the checks for EOF and BOF but when there are no
records
> > returned it in the recordset it gives me an error?
> >
> >
> > Thanks for your input!
> >
> > Regards
> > Nick Middleweek
>
> ---
> You are currently subscribed to asp_databases
$subst('Email.Unsub')
>
Message #8 by "Nick Middleweek" <nickm@t...> on Sat, 12 Aug 2000 17:06:51 +0000
|
|
Fred,
If the rs(columnNumber) method is faster than how come it's less efficient?
Thanks again!
Nick
----------
> On 08/11/00, ""Nick Middleweek" <nickm@t...>" wrote:
>> Thanks Fred,
>>Have you used this second parameter?
>
>>What is your prefered way of outputting data, it to call it using
>>objRS("FieldName") or is there a better method that we should use,
> perhaps
>>some fancy ADO or OLE thing?
>
> There is thre diffrent way to get data from the recordset in ASP.
>
> rs("FieldName")
> rs.Item("FieldName")
>
> rs(ColumnNumber) <- This is the fastest way to access data but is not so
> effiecient
>
> I recommand you to use rs("FieldName")
> or rs(ColumnNumber) when you only have few columns in the recordset.
>
> The Connection object's second parameter is:
>
> RecordsAffected and it's Optional.
> The parameter is a Long variable to which the provider returns the number
> of records that the operation affected.
>
> /Fredrik Normen
>
> ---
> You are currently subscribed to asp_databases
>
Message #9 by Fredrik Normen <fredrik.normen@s...> on Mon, 14 Aug 2000 11:00:41 +0200
|
|
>If the rs(columnNumber) method is faster than how come it's less efficient?
If you have many columns like:
Id,Name,Address,Country osv.
If you use rs("Name") the developer can see what column it is.
But if you use rs(1) instead, the developer have no idea what column it is.
They have to run the query and see what is the second ( rs(x), x begins with
0 ) column in the recordset.
/Fredrik Normen
----------
> On 08/11/00, ""Nick Middleweek" wrote:
>> Thanks Fred,
>>Have you used this second parameter?
>
>>What is your prefered way of outputting data, it to call it using
>>objRS("FieldName") or is there a better method that we should use,
> perhaps
>>some fancy ADO or OLE thing?
>
> There is thre diffrent way to get data from the recordset in ASP.
>
> rs("FieldName")
> rs.Item("FieldName")
>
> rs(ColumnNumber) <- This is the fastest way to access data but is not so
> effiecient
>
> I recommand you to use rs("FieldName")
> or rs(ColumnNumber) when you only have few columns in the recordset.
>
> The Connection object's second parameter is:
>
> RecordsAffected and it's Optional.
> The parameter is a Long variable to which the provider returns the number
> of records that the operation affected.
>
> /Fredrik Normen
>
Message #10 by "Nick Middleweek" <nickm@t...> on Mon, 14 Aug 2000 16:07:11 +0000
|
|
I understand! Thanks!
Regards
Nick
----------
>From: Fredrik Normen
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] RE: objRS.RecordCount
>Date: Mon, Aug 14, 2000, 09:00
>
>>If the rs(columnNumber) method is faster than how come it's less efficient?
>
> If you have many columns like:
> Id,Name,Address,Country osv.
>
> If you use rs("Name") the developer can see what column it is.
> But if you use rs(1) instead, the developer have no idea what column it is.
>
> They have to run the query and see what is the second ( rs(x), x begins with
> 0 ) column in the recordset.
>
> /Fredrik Normen
Message #11 by "Nick Middleweek" <nickm@t...> on Mon, 14 Aug 2000 16:52:32 +0000
|
|
Ken,
Could you explain a bit about Cursors and what the difference is between the
?three? different types? What do they alow you to do if I used a different
type of cursor? What's scrolling? In what situations would other cursors be
suited - I can see the answer to this isn't going to be quick and easy?
Are cursors a representation of the selection of records that I've SELECTed,
INSERTed or UPDATEd? If I use another type (which?) is the cursor still
'bound' or 'connected' from the SQL Server 7 to the ASP page? Am I thinking
on the right lines here? What do the other types do and what does the type
do that I've been using (adOpenForwardOnly)?
That Getrows function you used, does it create a 2 dimensional array of all
the columns if I SELECTed more than one? How would I reference them?
Cheers!
Regards
Nick
----------
> a) Difference between ODBC, OLEDB (and where ADO fits in)
> http://www.adOpenStatic.com/faq/whyOLEDB.asp
>
> will explain the differences between ODBC and OLEDB. You use ADO to access
> both. There is a diagram on the site which will explain things, and make it
> much clearer, promise!
>
> b) Why doesn't .recordCount work?
> This is because you are using the wrong type of cursor. You are using the
> default adOpenForwardOnly cursor. With this type of cursor, ADO doesn't know
> how many records are in the recordset until it gets to the end. All it is
> worried about it spitting them out as soon as possible,
>
> If you use an adOpenStatic or adOpenKeySet cursor you will be able to
> .recordCount. With both of these cursors forward and backward scrolling is
> supported. In order to support this, ADO needs to know about the whole
> recordset, right from the beginning. In the process it gets to know how many
> records are in the recordset
>
> That said, I will show you something which I think is faster and better
> programming practice:
>
> strSQL = "SELECT field1 FROM table1"
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
>
> If objRS.EOF and objRS.BOF then
> ' There are no records
> intNumRecords = 0
> Else
> arrResults = objRS.GetRows
> intNumRecords = UBound(arrResults, 2) + 1
> End if
>
> objRS.close
> set objRS = nothing
>
> intNumRecords will hold the number of records in your recordset. You also
> have your recordset in a VBScript array, and have immediately closed your
> connection to the database, allowing your database to be more responsive,
> and you page to run faster. (and you can keep the adOpenForwardOnly cursor)
>
> Cheers
> Ken
Message #12 by "Ken Schaefer" <ken@a...> on Tue, 15 Aug 2000 12:27:34 +1000
|
|
This is fastest:
rs.Fields.Item(0).Value
Is even faster than rs(0)
It's probably less efficient because it takes longer to code - you have to
remember which fields in your recordset correspond to which ordinals. Using
rs.Fields.Item(0).Value is even less efficient to code :-)
Someone on another list recommended something which might be even faster,
but it hasn't been tested yet.
<snip>
Set objMyField = objRS.Fields.Item(0)
Do While Not objRS.EOF
Response.Write "Value = " & objMyField.value & vbCrLf
objRS.MoveNext
Loop
Set objMyField = Nothing
</snip>
Cheers
Ken
----- Original Message -----
From: "Nick Middleweek" <nickm@t...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, August 13, 2000 3:06 AM
Subject: [asp_databases] RE: objRS.RecordCount
> Fred,
>
> If the rs(columnNumber) method is faster than how come it's less
efficient?
>
>
> Thanks again!
>
> Nick
Message #13 by "Ken Schaefer" <ken@a...> on Tue, 15 Aug 2000 12:43:06 +1000
|
|
> Could you explain a bit about Cursors and what the difference is between
the
> ?three? different types? What do they alow you to do if I used a different
> type of cursor? What's scrolling? In what situations would other cursors
be
> suited - I can see the answer to this isn't going to be quick and easy?
>
> Are cursors a representation of the selection of records that I've
SELECTed,
> INSERTed or UPDATEd? If I use another type (which?) is the cursor still
> 'bound' or 'connected' from the SQL Server 7 to the ASP page? Am I
thinking
> on the right lines here? What do the other types do and what does the type
> do that I've been using (adOpenForwardOnly)?
When you get some records from your database, these are stored in a
recordset.
Your recordset has certain properties, included are the CursorType and the
LockType.
So, straight away, we can say that cursors do not have anything to do with
executing INSERT or UPDATE statements, since these, in of themselves, do not
involve creating recordsets since they don't return any records. (Basically
only SELECT statements return recordsets)
Now, a recordset is a representation of the data in your database. Scrolling
allows you to move around in the recordset.
With the simplest, and fastest cursor, adOpenForwardOnly, you can only
scroll forwards through the recordset. This cursor is designed to get
records from the database as quickly as possible. It is not worried about
getting extra information about how many records there are, or keeping track
of records that you have already gone past. Once you have moved past a
record in an adOpenForwardOnly recordset, you can't move backwards (ie you
can't scroll backwards).
A cursor like adOpenStatic allows movement through the recordset. Here, the
recordset takes a snapshot of the data in your database, like
adOpenForwardOnly, but also gets more information, which allows it to
"remember" which records are in your recordset.
Using this cursor is about twice as expensive (in terms of time to output
stuff to the screen), but suppose you wanted to create two drop down lists
with the same data, you could use this recordset to iterate the records
once, then do a:
objRS.movefirst ' move back to the first record
and then iterate the recordset again. However an adOpenStatic cursor is not
aware of change made to the underlying data by other users. Once the
snapshot is taken, the records in the recordset are frozen. Other cursors
(eg adOpenDynamic) allow you to see changes made to the database by other
users, but these cursors are even more expensive, because even more
information has to be retrieved about which records where in the original
recordset, and this needs to be compared to the underlying database to see
if any more have been added or deleted.
Does that explain cursors and scrolling a little better?
For more indepth info, you're better off downloading the Microsoft Platform
SDK:
http://www.microsoft.com/msdownload/platformsdk/setuplauncher.htm
or getting Wrox's Professional ADO 2.5 Programming book
HTH
Cheers
Ken
Message #14 by =?iso-8859-1?Q?Gonzalo_Ruiz_de_Villa_Su=E1rez?= <gonzalo.ruizdevilla@a...> on Mon, 14 Aug 2000 11:14:00 +0200
|
|
Ken, the last solution you comment has been tested. I recommend you a Wayne
Plourde's article where he tests many different solutions and compares
speeds.
You can find it at:
http://www.asptoday.com/articles/20000426.htm
Cheers,
Gonzalo
-----Mensaje original-----
De: Ken Schaefer
Enviado el: martes, 15 de agosto de 2000 4:28
Para: ASP Databases
Asunto: [asp_databases] RE: objRS.RecordCount
This is fastest:
rs.Fields.Item(0).Value
Is even faster than rs(0)
It's probably less efficient because it takes longer to code - you have to
remember which fields in your recordset correspond to which ordinals. Using
rs.Fields.Item(0).Value is even less efficient to code :-)
Someone on another list recommended something which might be even faster,
but it hasn't been tested yet.
<snip>
Set objMyField = objRS.Fields.Item(0)
Do While Not objRS.EOF
Response.Write "Value = " & objMyField.value & vbCrLf
objRS.MoveNext
Loop
Set objMyField = Nothing
</snip>
Cheers
Ken
----- Original Message -----
From: "Nick Middleweek"
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, August 13, 2000 3:06 AM
Subject: [asp_databases] RE: objRS.RecordCount
> Fred,
>
> If the rs(columnNumber) method is faster than how come it's less
efficient?
>
>
> Thanks again!
>
> Nick
Message #15 by "Nick Middleweek" <nickm@t...> on Tue, 15 Aug 2000 10:31:44 +0000
|
|
Thanks Ken!
----------
>> Could you explain a bit about Cursors and what the difference is between
> the
>> ?three? different types? What do they alow you to do if I used a different
>> type of cursor? What's scrolling? In what situations would other cursors
> be
>> suited - I can see the answer to this isn't going to be quick and easy?
>>
>> Are cursors a representation of the selection of records that I've
> SELECTed,
>> INSERTed or UPDATEd? If I use another type (which?) is the cursor still
>> 'bound' or 'connected' from the SQL Server 7 to the ASP page? Am I
> thinking
>> on the right lines here? What do the other types do and what does the type
>> do that I've been using (adOpenForwardOnly)?
>
> When you get some records from your database, these are stored in a
> recordset.
> Your recordset has certain properties, included are the CursorType and the
> LockType.
>
> So, straight away, we can say that cursors do not have anything to do with
> executing INSERT or UPDATE statements, since these, in of themselves, do not
> involve creating recordsets since they don't return any records. (Basically
> only SELECT statements return recordsets)
>
> Now, a recordset is a representation of the data in your database. Scrolling
> allows you to move around in the recordset.
>
> With the simplest, and fastest cursor, adOpenForwardOnly, you can only
> scroll forwards through the recordset. This cursor is designed to get
> records from the database as quickly as possible. It is not worried about
> getting extra information about how many records there are, or keeping track
> of records that you have already gone past. Once you have moved past a
> record in an adOpenForwardOnly recordset, you can't move backwards (ie you
> can't scroll backwards).
>
> A cursor like adOpenStatic allows movement through the recordset. Here, the
> recordset takes a snapshot of the data in your database, like
> adOpenForwardOnly, but also gets more information, which allows it to
> "remember" which records are in your recordset.
>
> Using this cursor is about twice as expensive (in terms of time to output
> stuff to the screen), but suppose you wanted to create two drop down lists
> with the same data, you could use this recordset to iterate the records
> once, then do a:
>
> objRS.movefirst ' move back to the first record
>
> and then iterate the recordset again. However an adOpenStatic cursor is not
> aware of change made to the underlying data by other users. Once the
> snapshot is taken, the records in the recordset are frozen. Other cursors
> (eg adOpenDynamic) allow you to see changes made to the database by other
> users, but these cursors are even more expensive, because even more
> information has to be retrieved about which records where in the original
> recordset, and this needs to be compared to the underlying database to see
> if any more have been added or deleted.
>
> Does that explain cursors and scrolling a little better?
> For more indepth info, you're better off downloading the Microsoft Platform
> SDK:
> http://www.microsoft.com/msdownload/platformsdk/setuplauncher.htm
>
> or getting Wrox's Professional ADO 2.5 Programming book
>
> HTH
>
> Cheers
> Ken
Message #16 by Imar Spaanjaars <Imar@S...> on Tue, 15 Aug 2000 17:40:13 +0200
|
|
In addition to this, check out one of Wrox's own articles on www.asptoday.com:
http://www.asptoday.com/articles/20000426.htm
In that article all these methods (and others) are tested.
Imar
At 12:27 PM 8/15/2000 +1000, you wrote:
>This is fastest:
>
>rs.Fields.Item(0).Value
>
>Is even faster than rs(0)
>
>It's probably less efficient because it takes longer to code - you have to
>remember which fields in your recordset correspond to which ordinals. Using
>rs.Fields.Item(0).Value is even less efficient to code :-)
>
>Someone on another list recommended something which might be even faster,
>but it hasn't been tested yet.
>
><snip>
>Set objMyField = objRS.Fields.Item(0)
>
>Do While Not objRS.EOF
> Response.Write "Value = " & objMyField.value & vbCrLf
>objRS.MoveNext
>Loop
>
>Set objMyField = Nothing
></snip>
>
>Cheers
>Ken
|
|
|
View Cart
