question about opening and closing connections on aspfaq.com

Here
http://www.aspfaq.com/show.asp?id=2424
it says

"Open your connection just before needing it, and close it as soon as
you're done with it. Your motto should always be "get in, get/save
data, get out." Always close your ADO objects and set them to nothing.
"

I'm unclear on when that is exactly. Should I open the connection at
the top of the page and close it at the bottom, or should I open it and
close it 4 times?

Should I set rs = nothing as soon as possible after I'm done with rs,
even if I am redefining it again a few lines down?

Do I need to set rs = nothing at the bottom of the page?
Do I need to close the recordset 4 times?

Heres some code from my page:

sql = "select img_url ...
set rs = conn.execute(sql)

response.write ""

sql = "select .."
set rs = conn.execute(sql)
mini_table_name= rs(0)

sql = "select ...

set rs = conn.execute(sql)

mike_CreateList rs

rs.movefirst
response.write "
" & rs("name") & "'s abilities:"

sql = "select ...
set rs = conn.execute(sql)
while not rs.eof
ability_type_desc = rs("ability_type_desc")
response.write "
" & rs("ability_type_name") & "- "
& mike_GetDefinitions(ability_type_desc)
rs.movenext
wend

Re: question about opening and closing connections on aspfaq.com

mike wrote:
> Here
> http://www.aspfaq.com/show.asp?id=2424
> it says
>
> "Open your connection just before needing it, and close it as soon as
> you're done with it. Your motto should always be "get in, get/save
> data, get out." Always close your ADO objects and set them to nothing.
> "
>
> I'm unclear on when that is exactly. Should I open the connection at
> the top of the page and close it at the bottom,

Almost, but no. You should close it immediately after the last time you
use it.

> or should I open it
> and close it 4 times?
>

No. Close it after the last time you use it.
Not at the end of the page: close it immediately after the last time it
is used. You will likely be processing code that has nothing to do with
the connection after the last time you use it. It's a waste to keep the
connection open while that processing is occurring.

This is where the advice given in http://www.aspfaq.com/show.asp?id=2467
to use GetRows and GetString comes into play. Ideally, one should:

1. Perform all preliminary tasks: validate user input, prepare sql
strings, etc.
2. Instantiate and open the connection, immediately before you intend to
use it
3. Open the recordset.
4. Immediately use GetRows to put its data into an array.
5. Close the recordset and connection, unless you still need to use the
connection, in which case:
a. Retrieve any more resultsets you need, immediately moving their
data into arrays
b. Close and destroy the connection immediately after the last time
you use it
6. Process the data


There are rare times when it makes sense to process a recordset rather
than array. In those cases, one should use a disconnected recordset
rather than the default server-side firehose cursor so that the
connection can be closed as quickly as possible. In such a case, the
above sequence can be modified as follows:

1. Perform all preliminary tasks: validate user input, prepare sql
strings, etc.
a.Instantiate a recordset object and set its CursorLocation property
to 3 (adUseClient)
2. Instantiate and open the connection, immediately before you intend to
use it
3. Open the recordset, using rs.open instead of set rs=conn.execute.
4. Disconnect the recordset by setting its ActiveConnection to nothing
(Set rs.ActiveConnection=Nothing).
5. Close the recordset and connection, unless you still need to use the
connection, in which case:
a. Retrieve any more resultsets you need, immediately moving their
data into arrays or disconnecting them
b. Close and destroy the connection immediately after the last time
you use it
6. Process the data

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: question about opening and closing connections on aspfaq.com

Hi Bob,


if you use getrows, what do you do when you need properties:
for i = 0 to (rs.fields.count - 1)
response.write mike_pCase(replace(rs(i).name, "_", " "))

also, do you any functions or subroutines you could paste here that
makes using getrows() and getstring() easier to use?

thanks,
mike

Bob Barrows [MVP] wrote:
> mike wrote:
> > Here
> > http://www.aspfaq.com/show.asp?id=2424
> > it says
> >
> > "Open your connection just before needing it, and close it as soon as
> > you're done with it. Your motto should always be "get in, get/save
> > data, get out." Always close your ADO objects and set them to nothing.
> > "
> >
> > I'm unclear on when that is exactly. Should I open the connection at
> > the top of the page and close it at the bottom,
>
> Almost, but no. You should close it immediately after the last time you
> use it.
>
> > or should I open it
> > and close it 4 times?
> >
>
> No. Close it after the last time you use it.
> Not at the end of the page: close it immediately after the last time it
> is used. You will likely be processing code that has nothing to do with
> the connection after the last time you use it. It's a waste to keep the
> connection open while that processing is occurring.
>
> This is where the advice given in http://www.aspfaq.com/show.asp?id=2467
> to use GetRows and GetString comes into play. Ideally, one should:
>
> 1. Perform all preliminary tasks: validate user input, prepare sql
> strings, etc.
> 2. Instantiate and open the connection, immediately before you intend to
> use it
> 3. Open the recordset.
> 4. Immediately use GetRows to put its data into an array.
> 5. Close the recordset and connection, unless you still need to use the
> connection, in which case:
> a. Retrieve any more resultsets you need, immediately moving their
> data into arrays
> b. Close and destroy the connection immediately after the last time
> you use it
> 6. Process the data
>
>
> There are rare times when it makes sense to process a recordset rather
> than array. In those cases, one should use a disconnected recordset
> rather than the default server-side firehose cursor so that the
> connection can be closed as quickly as possible. In such a case, the
> above sequence can be modified as follows:
>
> 1. Perform all preliminary tasks: validate user input, prepare sql
> strings, etc.
> a.Instantiate a recordset object and set its CursorLocation property
> to 3 (adUseClient)
> 2. Instantiate and open the connection, immediately before you intend to
> use it
> 3. Open the recordset, using rs.open instead of set rs=conn.execute.
> 4. Disconnect the recordset by setting its ActiveConnection to nothing
> (Set rs.ActiveConnection=Nothing).
> 5. Close the recordset and connection, unless you still need to use the
> connection, in which case:
> a. Retrieve any more resultsets you need, immediately moving their
> data into arrays or disconnecting them
> b. Close and destroy the connection immediately after the last time
> you use it
> 6. Process the data
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Re: question about opening and closing connections on aspfaq.com

mike wrote:
> Hi Bob,
>
>
> if you use getrows, what do you do when you need properties:
> for i = 0 to (rs.fields.count - 1)
> response.write mike_pCase(replace(rs(i).name, "_", " "))

For field names, either create a separate array containing the names of
the fields* before closing the recordset:

dim fldnames(),fld
redim fldnames(rs.fields.count - 1)
for each fld in rs.fields
fldnames(fld.ordinalposition-1)=fld.name
next

or use a disconnected recordset.

>
> also, do you any functions or subroutines you could paste here that
> makes using getrows() and getstring() easier to use?

Not really. I don't consider them that hard to use.

Hmm. You might want to look at this demo:
http://www.davidpenton.com/testsite/tips/xml.data.islands.as p

One thing that makes using GetRows arrays easier is to create constants
containing the index numbers of the fields in the array:
sql="Select ID,EntryDate,UserName from table"
const ID = 0 'don't forget - array indexes are zero-based
const EntryDate=1
const UserName = 2
....
open the recordset and create the GetRows array
....
'get the username from the 3rd row:
sUserName=arData(UserName,2)

Alternatively, instead of creating the array of field names as above,
you can create a dictionary object:

dim dFldNames
set dFldNames=createobject("scripting.dictionary")
.... open the recordset and
for each fld in rs.fields
dFldNames(fld.Name,fld.ordinalposition-1)
next
....
sUserName=arData(fldNames("UserName"),2)



* Why would you need that? Don't you know what fields you are
retrieving? You're not using selstar are you?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: question about opening and closing connections on aspfaq.com

Hi Bob,

I am trying to do the adUseClient and ActiveConnection = nothing but I
get errors. Can you look at the code below and tell me what I am doing
wrong? The error is commented out with the line that causes it.

strConn = "Provider = SQLNCLI;" & _
"Data Source = .\SQLEXPRESS;" & _
"Initial Catalog = *****;" & _
"User ID = sa;" & _
"Password = ****;"


Set rs = Server.CreateObject("ADODB.recordset")
'rs.CursorLocation=adUseClient
'ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.

sql = "exec MINIGAME_GetGameTypes"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(strConn)
rs.Open sql, conn
'rs.ActiveConnection = Nothing
'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
is open.




Bob Barrows [MVP] wrote:
> mike wrote:
> > Hi Bob,
> >
> >
> > if you use getrows, what do you do when you need properties:
> > for i = 0 to (rs.fields.count - 1)
> > response.write mike_pCase(replace(rs(i).name, "_", " "))
>
> For field names, either create a separate array containing the names of
> the fields* before closing the recordset:
>
> dim fldnames(),fld
> redim fldnames(rs.fields.count - 1)
> for each fld in rs.fields
> fldnames(fld.ordinalposition-1)=fld.name
> next
>
> or use a disconnected recordset.
>
> >
> > also, do you any functions or subroutines you could paste here that
> > makes using getrows() and getstring() easier to use?
>
> Not really. I don't consider them that hard to use.
>
> Hmm. You might want to look at this demo:
> http://www.davidpenton.com/testsite/tips/xml.data.islands.as p
>
> One thing that makes using GetRows arrays easier is to create constants
> containing the index numbers of the fields in the array:
> sql="Select ID,EntryDate,UserName from table"
> const ID = 0 'don't forget - array indexes are zero-based
> const EntryDate=1
> const UserName = 2
> ...
> open the recordset and create the GetRows array
> ...
> 'get the username from the 3rd row:
> sUserName=arData(UserName,2)
>
> Alternatively, instead of creating the array of field names as above,
> you can create a dictionary object:
>
> dim dFldNames
> set dFldNames=createobject("scripting.dictionary")
> ... open the recordset and
> for each fld in rs.fields
> dFldNames(fld.Name,fld.ordinalposition-1)
> next
> ...
> sUserName=arData(fldNames("UserName"),2)
>
>
>
> * Why would you need that? Don't you know what fields you are
> retrieving? You're not using selstar are you?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Re: question about opening and closing connections on aspfaq.com

Hi Bob,

I am trying to do the adUseClient and ActiveConnection = nothing but I
get errors. Can you look at the code below and tell me what I am doing
wrong? The error is commented out with the line that causes it.

strConn = "Provider = SQLNCLI;" & _
"Data Source = .\SQLEXPRESS;" & _
"Initial Catalog = *****;" & _
"User ID = sa;" & _
"Password = ****;"


Set rs = Server.CreateObject("ADODB.recordset")
'rs.CursorLocation=adUseClient
'ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.

sql = "exec MINIGAME_GetGameTypes"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(strConn)
rs.Open sql, conn
'rs.ActiveConnection = Nothing
'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
is open.




Bob Barrows [MVP] wrote:
> mike wrote:
> > Hi Bob,
> >
> >
> > if you use getrows, what do you do when you need properties:
> > for i = 0 to (rs.fields.count - 1)
> > response.write mike_pCase(replace(rs(i).name, "_", " "))
>
> For field names, either create a separate array containing the names of
> the fields* before closing the recordset:
>
> dim fldnames(),fld
> redim fldnames(rs.fields.count - 1)
> for each fld in rs.fields
> fldnames(fld.ordinalposition-1)=fld.name
> next
>
> or use a disconnected recordset.
>
> >
> > also, do you any functions or subroutines you could paste here that
> > makes using getrows() and getstring() easier to use?
>
> Not really. I don't consider them that hard to use.
>
> Hmm. You might want to look at this demo:
> http://www.davidpenton.com/testsite/tips/xml.data.islands.as p
>
> One thing that makes using GetRows arrays easier is to create constants
> containing the index numbers of the fields in the array:
> sql="Select ID,EntryDate,UserName from table"
> const ID = 0 'don't forget - array indexes are zero-based
> const EntryDate=1
> const UserName = 2
> ...
> open the recordset and create the GetRows array
> ...
> 'get the username from the 3rd row:
> sUserName=arData(UserName,2)
>
> Alternatively, instead of creating the array of field names as above,
> you can create a dictionary object:
>
> dim dFldNames
> set dFldNames=createobject("scripting.dictionary")
> ... open the recordset and
> for each fld in rs.fields
> dFldNames(fld.Name,fld.ordinalposition-1)
> next
> ...
> sUserName=arData(fldNames("UserName"),2)
>
>
>
> * Why would you need that? Don't you know what fields you are
> retrieving? You're not using selstar are you?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Re: question about opening and closing connections on aspfaq.com

mike wrote:
> Hi Bob,
>
> I am trying to do the adUseClient and ActiveConnection = nothing but I
> get errors. Can you look at the code below and tell me what I am
> doing wrong? The error is commented out with the line that causes it.
>
> strConn = "Provider = SQLNCLI;" & _
> "Data Source = .\SQLEXPRESS;" & _
> "Initial Catalog = *****;" & _
> "User ID = sa;" & _
> "Password = ****;"
>
>
> Set rs = Server.CreateObject("ADODB.recordset")
> 'rs.CursorLocation=adUseClient
> 'ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out
> of acceptable range, or are in conflict with one another.

http://www.aspfaq.com/show.asp?id=2112


>
> sql = "exec MINIGAME_GetGameTypes"
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Open(strConn)
> rs.Open sql, conn
> 'rs.ActiveConnection = Nothing

This needs to be
Set rs.ActiveConnection = Nothing



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: question about opening and closing connections on aspfaq.com

Hi Bob,

Thanks for the continued help. I tried putting Set in front of
rs.cursorlocation=aduseclient, which resulted in the error "'Microsoft
VBScript runtime (0x800A01A8) Object required: 'CursorLocation'
"

I tried Set rs.ActiveConnection = Nothing instead of
rs.ActiveConnection = Nothing but I got the same error,
"'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
is open."

Could this be caused by my provider setting? Am I leaving something
out?

Any help would be greatly appreciated!

-Mike

strConn = "Provider = SQLNCLI;" & _
"Data Source = .\SQLEXPRESS;" & _
"Initial Catalog = ****;" & _
"User ID = sa;" & _
"Password = ****;"

Set rs = Server.CreateObject("ADODB.recordset")
'Set rs.CursorLocation=adUseClient
'Microsoft VBScript runtime (0x800A01A8) Object required:
'CursorLocation'
'rs.CursorLocation=adUseClient
'ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.

sql = "exec MINIGAME_GetGameTypes"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(strConn)
rs.Open sql, conn
'rs.ActiveConnection = Nothing
'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
is open.
'Set rs.ActiveConnection = Nothing
'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
is open.




mike wrote:
> Hi Bob,
>
> I am trying to do the adUseClient and ActiveConnection = nothing but I
> get errors. Can you look at the code below and tell me what I am doing
> wrong? The error is commented out with the line that causes it.
>
> strConn = "Provider = SQLNCLI;" & _
> "Data Source = .\SQLEXPRESS;" & _
> "Initial Catalog = *****;" & _
> "User ID = sa;" & _
> "Password = ****;"
>
>
> Set rs = Server.CreateObject("ADODB.recordset")
> 'rs.CursorLocation=adUseClient
> 'ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out
> of acceptable range, or are in conflict with one another.
>
> sql = "exec MINIGAME_GetGameTypes"
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Open(strConn)
> rs.Open sql, conn
> 'rs.ActiveConnection = Nothing
> 'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
> is open.
>
>
>
>
> Bob Barrows [MVP] wrote:
> > mike wrote:
> > > Hi Bob,
> > >
> > >
> > > if you use getrows, what do you do when you need properties:
> > > for i = 0 to (rs.fields.count - 1)
> > > response.write mike_pCase(replace(rs(i).name, "_", " "))
> >
> > For field names, either create a separate array containing the names of
> > the fields* before closing the recordset:
> >
> > dim fldnames(),fld
> > redim fldnames(rs.fields.count - 1)
> > for each fld in rs.fields
> > fldnames(fld.ordinalposition-1)=fld.name
> > next
> >
> > or use a disconnected recordset.
> >
> > >
> > > also, do you any functions or subroutines you could paste here that
> > > makes using getrows() and getstring() easier to use?
> >
> > Not really. I don't consider them that hard to use.
> >
> > Hmm. You might want to look at this demo:
> > http://www.davidpenton.com/testsite/tips/xml.data.islands.as p
> >
> > One thing that makes using GetRows arrays easier is to create constants
> > containing the index numbers of the fields in the array:
> > sql="Select ID,EntryDate,UserName from table"
> > const ID = 0 'don't forget - array indexes are zero-based
> > const EntryDate=1
> > const UserName = 2
> > ...
> > open the recordset and create the GetRows array
> > ...
> > 'get the username from the 3rd row:
> > sUserName=arData(UserName,2)
> >
> > Alternatively, instead of creating the array of field names as above,
> > you can create a dictionary object:
> >
> > dim dFldNames
> > set dFldNames=createobject("scripting.dictionary")
> > ... open the recordset and
> > for each fld in rs.fields
> > dFldNames(fld.Name,fld.ordinalposition-1)
> > next
> > ...
> > sUserName=arData(fldNames("UserName"),2)
> >
> >
> >
> > * Why would you need that? Don't you know what fields you are
> > retrieving? You're not using selstar are you?
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.

Re: question about opening and closing connections on aspfaq.com

mike wrote:
> Hi Bob,
>
> Thanks for the continued help. I tried putting Set in front of
> rs.cursorlocation=aduseclient,

Why did you do that? Where did I say to do that? The cursorlocation property
is not an object, so you don't use "Set" when assigning a value to it.
Read my previous reply a little more carefully please.
For the error that resulted from using aduseclient, I referred you to this
article:

http://www.aspfaq.com/show.asp?id=2112

Please go read it. It will show you a good way to import the declarations
for the ADO constantsm one of which is aduseclient, into your application.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: question about opening and closing connections on aspfaq.com

Hi Bob,

Ah, I'm sorry I simply missed that link, I apologize for that, I will
research it.

Do you know why these give me the commented error message? I was
asking about the provider earlier because I did a search and someone
had mentioned something about that in a similar error. The provider I
am using right now is:
strConn = "Provider = SQLNCLI;" & _
"Data Source = .\SQLEXPRESS;" & _

I tried using the Microsoft Jet 4.0 provider but I got an error. I am
using sql server express 2005.

Any ideas?

Set rs = Server.CreateObject("ADODB.recordset")
sql = "exec MINIGAME_GetGameTypes"
Set conn = Server.CreateObject("ADODB.Connection")
rs.Open sql, conn
'rs.ActiveConnection = Nothing
'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
is open.
'Set rs.ActiveConnection = Nothing
'ADODB.Recordset (0x800A0E79) Operation is not allowed when the object
is open.


Bob Barrows [MVP] wrote:
> mike wrote:
> > Hi Bob,
> >
> > Thanks for the continued help. I tried putting Set in front of
> > rs.cursorlocation=aduseclient,
>
> Why did you do that? Where did I say to do that? The cursorlocation property
> is not an object, so you don't use "Set" when assigning a value to it.
> Read my previous reply a little more carefully please.
> For the error that resulted from using aduseclient, I referred you to this
> article:
>
> http://www.aspfaq.com/show.asp?id=2112
>
> Please go read it. It will show you a good way to import the declarations
> for the ADO constantsm one of which is aduseclient, into your application.
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Re: question about opening and closing connections on aspfaq.com

Ack I found out why I missed that link, google hid the quoted text
above it that you referring to :( sorry

> Why did you do that? Where did I say to do that? The cursorlocation property
> is not an object, so you don't use "Set" when assigning a value to it.
> Read my previous reply a little more carefully please.
> For the error that resulted from using aduseclient, I referred you to this
> article:
>
> http://www.aspfaq.com/show.asp?id=2112
>
> Please go read it. It will show you a good way to import the declarations
> for the ADO constantsm one of which is aduseclient, into your application.
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Re: question about opening and closing connections on aspfaq.com

mike wrote:
> Hi Bob,
>
> Ah, I'm sorry I simply missed that link, I apologize for that, I will
> research it.
>
> Do you know why these give me the commented error message? I was
> asking about the provider earlier because I did a search and someone
> had mentioned something about that in a similar error. The provider I
> am using right now is:
> strConn = "Provider = SQLNCLI;" & _
> "Data Source = .\SQLEXPRESS;" & _
>
> I tried using the Microsoft Jet 4.0 provider but I got an error.

Why would you use a jet provider for sql server?

> I am
> using sql server express 2005.

Unless you need SQL 2005 features, you should use the SQLOLEDB provider.

Also, do not use the sa account for your applications. Create an account
with limited permissions (the least privileges needed to perform the
activities required by your application) and use that for your applications.
SA is for server administration ... ONLY.
>

If you have On Error Resume Next in your code anywhere, comment it out so
you can detect errors from opening the connection.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"