Board index Database Programming

Opening/closing RS/DB

Opening/closing RS/DB

Post by t. smit » Fri, 25 Apr 1997 04:00:00



Seeking words of wisdom from VB Gurus.  In my VB code I typically do
things like

  Function GetInfo()
   Dim Db As Database
   Dim Rs As Recordset
    Set Db = DBEngine.Opendatabase(MyDB)
    Set Rs = Db.OpenRecordset("Some Stored Query")
    GetInfo = Rs![field name of what I want]
   Rs.Close
   Db.Close

...in other words, functions that open a Db, open a Rs usually based on
a stored parameter query, and then extract *exactly* the info it wants (
singular or multiple fields ) and pass this result (usually a limited
text string ) back to the caller. I'd love to hear some advice on the
quality of this methodology, and my main concern is:  Is it good to open
and close the database and recordset objects so quicky like this ? Is it
unnecessary to open and close the database so fast ? Is it good VB/Db
practice ?

thanks in advance,
thomas
--

=====================================

 
 
 
Top

Opening/closing RS/DB

Post by JustM » Sun, 27 Apr 1997 04:00:00


Hello Tom,

Whenever I write  Progs in VB using a database I noticed that I NEVER
(except the end of the program) close any Recordsets.
In the beginning I opened and closed the DB/RS like you. But by going on
with this on greater programs you surely will get any error. Besides when
you work with big (and I mean REALLY big (up to 300.000 Records)) Databases
you need all the time you can get. And you will notice that it takes more
time to open/close RS and DBs. The few Bytes for the extra Data needed is
not as bad as spending time and time for waiting. So let the things open
and don't thing of it 'till the program ends. Besides best use the "Reset"
command on shut down, so you can be shure that ALL of your Files are
closed.
No words of wisdom and no guru, but s.o. who works with VB and big
databases.

Besides, what the heck means "Some Stored Query".
Hope you do not write the SQL directly. Use a global string for that.

regards,

Paul

**********************************************************
      happy hearts fall by my stupid hearts

--


Quote:> Seeking words of wisdom from VB Gurus.  In my VB code I typically do
> things like

~~~~~~~~~~CUT ~~~~~~~~~~
...
~~~~~~~~~~CUT ~~~~~~~~~~
 > I'd love to hear some advice on the
Quote:> quality of this methodology, and my main concern is:  Is it good to open
> and close the database and recordset objects so quicky like this ? Is it
> unnecessary to open and close the database so fast ? Is it good VB/Db
> practice ?

> thanks in advance,
> thomas


 
 
 
Top

Opening/closing RS/DB

Post by R » Sun, 27 Apr 1997 04:00:00


That is the way I had to go with in order to have the apps. Netwotk
ready. Sure is a little slower, but I never hit the file open error
message...


>Seeking words of wisdom from VB Gurus.  In my VB code I typically do
>things like
>  Function GetInfo()
>   Dim Db As Database
>   Dim Rs As Recordset
>    Set Db = DBEngine.Opendatabase(MyDB)
>    Set Rs = Db.OpenRecordset("Some Stored Query")
>    GetInfo = Rs![field name of what I want]
>   Rs.Close
>   Db.Close
>...in other words, functions that open a Db, open a Rs usually based on
>a stored parameter query, and then extract *exactly* the info it wants (
>singular or multiple fields ) and pass this result (usually a limited
>text string ) back to the caller. I'd love to hear some advice on the
>quality of this methodology, and my main concern is:  Is it good to open
>and close the database and recordset objects so quicky like this ? Is it
>unnecessary to open and close the database so fast ? Is it good VB/Db
>practice ?
>thanks in advance,
>thomas
>--
>=====================================


 
 
 
Top

Opening/closing RS/DB

Post by Trever M. Shic » Tue, 29 Apr 1997 04:00:00


I'd like to say that it is good that you use Stored Queries.  They do
perform faster than a newly created one.  However
how the query is written is a big thing too, make sure not to pull too much
information.
As for closing the rs and db objects of course it's a good Idea, but you
may also want to

Set rs = nothing
and
set db = nothing

it's not necessary, but it's a little more house cleaning that I don't
always like to leave up to VB.

Another thing, if you're going to use the DB alot in your program, you may
just keep the connection open, depending
on how many users, etc, etc.. If you need to use it many times in the
program, there's no sense in opening and closing it
multiple times when you could just declare the db connection as global.  (
Depending of course on the application )



> Seeking words of wisdom from VB Gurus.  In my VB code I typically do
> things like

>   Function GetInfo()
>    Dim Db As Database
>    Dim Rs As Recordset
>     Set Db = DBEngine.Opendatabase(MyDB)
>     Set Rs = Db.OpenRecordset("Some Stored Query")
>     GetInfo = Rs![field name of what I want]
>    Rs.Close
>    Db.Close

> ...in other words, functions that open a Db, open a Rs usually based on
> a stored parameter query, and then extract *exactly* the info it wants (
> singular or multiple fields ) and pass this result (usually a limited
> text string ) back to the caller. I'd love to hear some advice on the
> quality of this methodology, and my main concern is:  Is it good to open
> and close the database and recordset objects so quicky like this ? Is it
> unnecessary to open and close the database so fast ? Is it good VB/Db
> practice ?

> thanks in advance,
> thomas
> --

> =====================================


 
 
 
Top

1. cannot get rs from stored procedure to release user connection without closing rs

I'm having trouble creating a recordset where the SQL Server 7 'user
connection' is freed after opening the recordset.  I'm using VB6 MDAC
2.6 on NT4 SP6a with SQL7 SP3

I am able to use an SQL statement to return a disconnected recordset
where the 'user connection' is freed when I close the connection
object without any problems.

But for my code that uses a stored procedure instead of an SQL
statement (show below) runs, the 'user connection' (as show in the
Performance Monitor of SQL Server viewing the User Connections) only
gets released when the recordset is closed, not when the connection is
closed when disconnecting the recordset.

I am at a loss as I've looked at code examples documenting
disconnected recordset with stored procedures -  and I seem to be on
target.  The code is very much the same as for the SQL statement
version.

Help????

Function RunSPReturnRS(ByVal spName$, params As Variant) As
ADODB.Recordset
    Dim rs As ADODB.Recordset, cmd As ADODB.Command
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command

    Dim cnn As ADODB.Connection
    Set cnn = GetConn

    cmd.ActiveConnection = cnn
    cmd.CommandText = spName$
    cmd.CommandType = adCmdStoredProc

    collectParams cmd, params

    rs.CursorLocation = adUseClient
    rs.cursorType = adOpenKeyset
    rs.locktype = adLockBatchOptimistic

    rs.Open cmd
    Set rs.ActiveConnection = Nothing

    Set RunSPReturnRS = rs ' return the rs as the functions value
    Set cmd = Nothing
    cnn.Close ' user connection SHOULD be release here, is not
    Set cnn = Nothing
    Set rs = Nothing
END Function

MAIN CODE HERE

  Dim rs As ADODB.Recordset, retSqlErr As retSqlErrs
   Set rs = RunSPReturnRS("prGetSysIdFromLineNum",

        ' do something here
    rs.Close ' user connnection IS released here????
   ' WHY is connection only release when close rs???
    Set rs = Nothing

--

2. Urgent - Update in a table being raplicated (Bug????)

3. rs.close and set rs = nothing

4. Problem with ORA Lite ODBC via MS-ACCESS

5. Set rs = nothing and rs.Close

6. Error 161 - Maximum licenses reached

7. cannot get rs from stored procedure to release user connection without closing rs

8. SQL Question in FPW 2.5

9. Closing an ADO db & rs

10. Rs.Open vs. Set Rs = Conn.Execute

11. Bounding Datalist with Rs via Open or Rs via cmd.execute

12. rs.open vs. set rs = conn.execute

13. ADO, rs.AddNew, rs.Update, rs.Filter, rs.EditMode


All times are UTC
Board index