open recordset for update with order by 1

[p27br]

hi

here is my script

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

' These values were copied from the ADOVBS.INC file.
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004


'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

dim intCounter

' Instantiate the ADO objects.
set mySourceConn = CreateObject("ADODB.Connection")
set mySourceRecordset = CreateObject("ADODB.Recordset")

mySourceConn ="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=true;Initial Catalog=Boehringer;Data Source=BRUSSQL03"

'mySQLCmdText = "Select * from tbltempmkt order by total_values"
 
mySourceRecordset.Open "Select * from tbltempmkt", mySourceConn, adOpenKeyset,adLockOptimistic

 mySourceRecordset.MoveFirst
 intCounter = 1
    do while not mySourceRecordset.eof
       if mySourceRecordset.Fields("total_values") = 0 then
mySourceRecordset.Fields("rank") = -1
else
        mySourceRecordset.Fields("rank") = intCounter
end if
        mySourceRecordset.update
        intCounter = intCounter + 1
        mySourceRecordset.MoveNext
    loop
mySourceRecordset.Close
set mySourceRecordset = nothing
Main = DTSTaskExecResult_Success


End Function

this is the problematic part : I want to open an ordered recordset with the group by clause, if I do the following, i get a message : The recordset is not updatable"

mySQLCmdText = "Select * from tbltempmkt order by total_values"
mySourceRecordset.Open mySQLCmdText,

this works but i can't control the order of records

mySourceRecordset.Open "Select * from tbltempmkt",

please tell me if it's the right forum for this post

 

[PHV]

Have you tried with a client side cursor ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fosterr2000]

Try adOpenStatic instead of adOpenKeyset as such maybe.

mySourceRecordset.Open "Select * from tbltempmkt", mySourceConn, adOpenStatic, adLockOptimistic

Good luck.

 

[p27br]

adOpenStatic doesn't work :
Error Description : Current Recordset does not support updating.This may be a limitation of the provider or by the selected locktype


PHV : what do you mean by Client Side cursor ? This script is used in a DTS Package in an ActiveX Script

thanks for your help

 

[PHV]

Before opening the recordset:
mySourceRecordset.CursorLocation = 3 ' adUseClient

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[p27br]

PHV

that works ! have a star
please could you explain why ?

thanks

paul

 

[PHV]

Search your local drives for files named ado*.chm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886