using connection objects 2

[rtgordon]

If I am making multiple queries on a single page (same db), what is the best (most efficient way) to handle the connection objects? Multiple connection objects (1 for each query)? Open/Close the same connection and rs for each query? Anything better?

gordon

 

[shaddow]

U need just one Connectio object...

if u use the queries just for getting values from database u could use one Recordset or u could use as u need

U could use just one Recordset if u optimize your querryes to give u all the info u need...
i recomand to use one Connection and one Recordset...

this is what i used to call a querry or a multiple querry

Set Connection=Server.CreateObject("ADODB.Connection")
dbPath=Server.MapPath("yourdatabase.mdb")
Connection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="&dbPath

Const adOpenStatic = 3
Const adUseClient = 3
Const adLockPessimistic = 2

set rs=server.CreateObject("ADODB.Recordset")

sub ExecuteSQL(byval cmd)
    if rs.State=1 then rs.Close
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockPessimistic
    rs.Source = cmd
    rs.ActiveConnection = Connection   'The record set needs to know what connection to use.
    rs.Open
end sub


querry="select * from table name"
ExecuteSQL(querry)
if rs.RecordCount=0 then
'there is no records
end if

hope this gives u an ideea ________
George, M

 

[rtgordon]

I was looking in the MSDN ADO reference, and I still don't understand a few things. Why would you use a lock to query values? Also, what does setting the cursorLocation property to asUseClient accomplish over using the default? You are calling ExecuteSQL with querry, where do byval and cmd come into the picture?

Sorry for all the questions... just trying to make sense of it. Thank you for the reply.

gordon

 

[link9]

Here's a FAQ on the recordset properties you were asking about... faq333-618 ... hopefully that will clear up your questions there.


(byval cmd)
byval is specifying that he is sending the argument "by value" as opposed to the default in vb, "by reference".

The difference is that if he changes the value of the argument when it's sent 'byval', it doesn't change in the main program -- only in the context of the function (or subroutine in this case) and only for the duration of its execution.

Conversely, if he had passed it 'byref' (default), then if he makes a change to the value of the variable in the function, it also changes it in the main program -- because passing a variable 'byref' does not actually pass the function the value of the variable, it passes it a pointer to the memory location of the actual variable, so when you work on/with it, you are working with the original...


(byval cmd)
the cmd is just an alias that he is going to refer to the variable by in the context of this particular subroutine. Giving the variable a different name will sometimes make your code more readable in that you will always know in what context you are talking about any particular variable.

hope that helps!
Paul Prewett

 

[shaddow]

i think that the asUseClient value tell the ADO send the recordset to the aplication (it is stored on the aplication side)

adLockPessimistic - it is better to use when u make an update to your database or an insert...
otherwise(make a select) u put Const adLockOptimistic = 3 it is mutch faster then adLockPessimistic

ExecuteSQL(byval cmd) is an function witch takes an parameter named cmd(command) that is your querry string and ByValue means that the cmd parameter gets the same value as your call parameter and if i put ByRef the values of cmd is a pionter to that value...
________
George, M

 

[shaddow]

this is for Link9:

How do i make a link to an FAQ as u do?
I dont really stay mutch with this... ________
George, M

 

[link9]

If you go to the FAQ, you'll see in the top left hand corner a little link with the FAQ #....

Just highlight that text, copy it, and paste it into a reply. It will come out as a link.

And btw, you can do the same thing with a thread. Find the link in the top left corner of the thread, right under the title of the thread.

;-)

 

[shaddow]

Ok, thaks... ________
George, M

 

[rtgordon]

Thanks a bunch for the great info!

Mainly I was just creating and closing connections and recordsets as I needed them, and setting the properties based on whether or not the SQL modified the data. I will see how I can use what you have showne me.

gordon