adUseServer or adUseClient ???

[Wen Lie]

Dear All,

I would like to ask about ADO connection. I'm using ADO version 2.1 for my Visual Basic application / project.

My question is...
Are there any differences between :

===================================
Connection.CursorLocation = adUseServer

and

Connection.CursorLocation = adUseClient
===================================

What are the differences between two of them... ???
Which one is faster ???
Will it impact the speed of my application while I'm using adUseServer or while I'm using adUseClient ???

Thx in advance nd Need reply / answer....

Regards,
Wille

[Nucleus]

A database server's strength is its ability to process very quickly and efficiently. To take advantage of this server side recordets are usually more efficient, also make sure you only create recordsets with the minimum number of records possible.

To really ram up performance and scalablilty to max, manage concurrency yourself and only ever use serverside, forwards only recordsets with optimistic locking.

You may need client side recordsets if you want to create disconnected recordsets. Other than this stick to server side recordsets (the default for ADO).

[Wen Lie]

Thx...

But, I've just tried to use adUseServer. But, when I goes with begintrans and committrans, it returns with an error... Here is a piece of my coding.... :

Dim myConn as New ADODB.Connection

myConn.CursorLocation = adUserServer
myConn.Mode = adModeReadWrite

myConn.Open "Driver={SQL Server}; server=mySvr; database=myDB; uid=myUID; pwd=myPWD"

The code above is to open my Connection in ADO.

then, at a module, here it is :

....

Do while not myRec1.EOF
     myConn.BeginTrans
     
     myConn.Execute "mysQLstatement update goes here"
     
     myConn.CommitTrans
     
     myRec1.MoveNext
Loop

....

Then, when I run, there'll be an error. The error is Cathastropic Failure .....blablabla.......

hmm...
But, If I put the begintrans before do, and committrans after Loop, my coding will run okay.

Different case if I use adUseClient, the above code will run verywell without any error....

Regards,
Wille

[Nucleus]

I can't help you there, I do all transactions via stored procs sitting on the server rather than in vb code.

[Wen Lie]

sure...
my sql statement, I put them on store procs...
but, to execute the store procedure from visual basic,
we need to use :

Connection.Execute "storeproc_name"

Am I right ?

If I execute from vb using above statement, should I use begintrans and committrans again ?

because, I need begintrans and committrans, so when my program goes an error, I still can rollback the transactions, using rollbacktrans.

???

Wille

[Wen Lie]

helloooo
anybody who knows this ?
experts ?
guru(s) ?

knock knock ???

Wille

[Nucleus]

Well if the transaction is rolledback, then you just get the stored proc to raise and error or you could also use the return value or output parameter to pass back the info.

Then once you have the sp (where all the work really is) you create a command object with ado, use the append method for best performance to pass parameters to and from the stored proc. If the stored proc doesn't raise an error you know it has executed, otherwise the transaction was rolled back.

All the work is done by the server and not via VB. You just use ADO to connect and execute the sp, and return any errors/return values/output parameters.

[Wen Lie]

okay..
but, I don't know how to use OUTPUT parameters in store procedure(s)

do u know how ?

and could I have some easy / simple code to show how to use adUseServer and how to execute SQL Statement using a connection without "Cathastropic Failure" error notice ?

Thx in advanced Nucleus,....
nd I still need help...

Wille

[Nucleus]

Here is an example that returns an output parameter:

Public Function AddNewMember() As Long
 ' Function adds a new member to the database
 ' Returns the new member Id on success, or 0 if not successful
 Dim cn         As ADODB.Connection
 Dim cmd        As ADODB.Command
 Dim prm        As ADODB.Parameter
 Dim er         As ADODB.Error
 Dim lRecs      As Long
 Dim NewId      As Long
 
 Set cn = New ADODB.Connection
 cn.Open sConnection
 
 If cn.state = adStateOpen Then
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandText = "spnewmember"
    cmd.CommandType = adCmdStoredProc
    
    '@ipMemberDateJoined datetime
    Set prm = cmd.CreateParameter("MemDateJoined", adDate, adParamInput, , Date)
    cmd.Parameters.Append prm
    
    '@ipMemberFirstName nvarchar(200),
    Set prm = cmd.CreateParameter("MemFirstName", adVarWChar, adParamInput, 200, Me.MemberFirstName)
    cmd.Parameters.Append prm
    
    '@ipMemberLastName nvarchar(200),
    Set prm = cmd.CreateParameter("MemLastName", adVarWChar, adParamInput, 200, Me.MemberLastName)
    cmd.Parameters.Append prm
    
    '@ipMemberMiddleName nvarchar(200),
    Set prm = cmd.CreateParameter("MemMiddleName", adVarWChar, adParamInput, 200, Me.MemberMiddleName)
    cmd.Parameters.Append prm
    
    '@ipMemberAddress nvarchar(1000),
    Set prm = cmd.CreateParameter("MemAddress", adVarWChar, adParamInput, 1000, Me.MemberAddress)
    cmd.Parameters.Append prm
    
    '@ipMemberPostCode nchar(4),
    Set prm = cmd.CreateParameter("MemPostCode", adWChar, adParamInput, 4, Me.MemberPostcode)
    cmd.Parameters.Append prm
    
    '@ipMemberPhone nvarchar(50),
    Set prm = cmd.CreateParameter("MemPhoneHome", adVarWChar, adParamInput, 50, Me.MemberPhoneHome)
    cmd.Parameters.Append prm
    
    Set prm = cmd.CreateParameter("MemPhoneWork", adVarWChar, adParamInput, 50, Me.MemberPhoneWork)
    cmd.Parameters.Append prm
    
    Set prm = cmd.CreateParameter("MemPhoneMobile", adVarWChar, adParamInput, 50, Me.MemberPhoneMobile)
    cmd.Parameters.Append prm
    
    '@ipMemberSex bit,
    Set prm = cmd.CreateParameter("MemSex", adBoolean, adParamInput, , Me.MemberSex)
    cmd.Parameters.Append prm
    
    '@ipMemberSessions int,
    Set prm = cmd.CreateParameter("MemSessions", adInteger, adParamInput, , Me.MemberSessions)
    cmd.Parameters.Append prm
    
    '@ipMemberConcurrencyId int,
    Set prm = cmd.CreateParameter("MemConcurrencyId", adInteger, adParamInput, , 0)
    cmd.Parameters.Append prm
    
    ' @opMemberId int OUTPUT
    ' Important note output parameter doesn't require size or value
    Set prm = cmd.CreateParameter("opMemberId", adInteger, adParamOutput)
    cmd.Parameters.Append prm
    
    'Make sure you tell it no records returned by stored proc
    cmd.Execute lRecs, , adExecuteNoRecords
    
    If lRecs Then
        Me.MemberId = cmd.Parameters("opMemberId")
        AddNewMember = cmd.Parameters("opMemberId")
    Else
        AddNewMember = 0
    End If
    
    cn.Close
    
    Set prm = Nothing
    Set cmd = Nothing
    Set cn = Nothing
 
 Else
    ' If the connection didn't open A-OK, then
    ' Display all the errors in the connection object
    For Each er In cn.Errors
        MsgBox er.Description
    Next
 End If
End Function

[Wen Lie]

Well,
I will try for it...
but....
Didn't your coding makes an output ?

why did you say (in your code), I should make sure I tell it no records returned by stored proc ???

Is it just work out when we want to add / append / insert, delete, and update our recordset ?

How about if I want to query the recordset ?
Then, what is the real function of OUTPUT parameter ?
coz I can't figure out the function of that statement.

Sorry to bother,
but I still do not understand....
anybody could help me with this kinda problem ?
Nucleus ? Serge ? anybody ?


Regards,
Wille

[Nucleus]

Here is where the output parameter is created:
' @opMemberId int OUTPUT
' Important note output parameter doesn't require size or value
Set prm = cmd.CreateParameter("opMemberId", adInteger, adParamOutput)
cmd.Parameters.Append prm


In this case the ouput parameter returns the id of the new record created by the stored proc.

why did you say (in your code), I should make sure I tell it no records returned by stored proc ???

In this case the sp inserts a new record into a table, so it doesn't return any records. By telling ado that no records are returned it makes the process faster.

[Wen Lie]

okay....
I got the point of OUTPUT parameters....
Thx...

And I want to ask a question again due to your coding....

What is lRec ?
is it a recordset ? because, I see that you define it as Long....
and I see that u use it at cmd.execute

I don't quite understand about below statement :

cmd.execute lRecs, , adExecuteNoRecords

Thx in reply again....
Regards,
Wille