Hi.



I have ASP code to execute a stored procedure which does not return a 

record set and I also have ASP code to execute a stored procedure that 

does return a record set.



I need to add adOpenForwardOnly, adLockOptimistic on my code that returns 

a record set. 



I need to add adOpenDynamic, adLockOptimistic on my code that does not 

return a record set.



Here's the code to return a record set:



Dim cmd					

Dim rs						

Dim sConnection				



'Open the connection to database

Set cmd = Server.CreateObject("ADODB.Command")

sConnection = "DSN=MyDSN;UID=MyUID;PWD=MyPWD;"

cmd.ActiveConnection = sConnection



cmd.CommandType = adCmdStoredProc					

cmd.CommandText = "sGROUPSelectAll"					

Set rs = cmd.Execute()							

	

WHERE DO I PUT adOpenForwardOnly, adLockOptimistic ?





Here's the code that does not return a record set:



Dim cn

Dim rs		

Dim sSql	



'Set up the connection to database

Set cn = server.CreateObject("ADODB.Connection")

cn.Open "DSN=MyDSN; uid=MyUID; pwd=MyPWD;"



sSql = "sGROUPUpdate"

sSql = sSql & "'" & sGroupName & "', '" & sBuildPPONetworkID & "'"

cn.Execute(sSql)



WHERE DO I PUT  adOpenDynamic, adLockOptimistic ?



TIA



Rita

Hi Rita



(1)Regarding Set rs = cmd.Execute() as far as i now 

  When u open such a type of recorset then the returned Recordset object 

is always a read-only, forward-only cursor.



If you need a Recordset object with more functionality, first create a 

Recordset object with the desired property settings, then use the 

Recordset object's Open method to execute the query and return the desired 

cursor type





(2)As regards the code cn.Execute(sSql) it has to be noted that-

If the command is not a row-returning query, as in ur case the provider 

returns a closed Recordset object.



THERE IS NO WAY u can  PUT  adOpenDynamic, adLockOptimistic .

If u are using the statement for UPDATE the best option will be-



cn.Execute(sSql), , adExecuteNoRecords



Always use adExecuteNoRecords to improve performance by minimizing 

internal processing.

   

Regards,

Hari

Thanks Hari, for the great advice - I'd never heard of adExecuteNoRecords

before! I'm learning as I go along - no official training.



-----Original Message-----

From: Hariharan [mailto:hariharan_m2001@r...]

Sent: Tuesday, April 17, 2001 1:28 AM

To: ASP Web HowTo

Subject: [asp_web_howto] Re: ASP DataBase Open and Record Locking





Hi Rita



(1)Regarding Set rs = cmd.Execute() as far as i now 

  When u open such a type of recorset then the returned Recordset object 

is always a read-only, forward-only cursor.



If you need a Recordset object with more functionality, first create a 

Recordset object with the desired property settings, then use the 

Recordset object's Open method to execute the query and return the desired 

cursor type





(2)As regards the code cn.Execute(sSql) it has to be noted that-

If the command is not a row-returning query, as in ur case the provider 

returns a closed Recordset object.



THERE IS NO WAY u can  PUT  adOpenDynamic, adLockOptimistic .

If u are using the statement for UPDATE the best option will be-



cn.Execute(sSql), , adExecuteNoRecords



Always use adExecuteNoRecords to improve performance by minimizing 

internal processing.

   

Regards,

Hari

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: I need to add adOpenDynamic, adLockOptimistic on my code

: that does not return a record set.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



adOpenDynamic refers to the cursor type of a recordset. If you're not

returning a recordset, then that option is not applicable.



Likewise, adLockOptimistic is a locktype to use when you return/update a

recordset - the records in the database, that are contained in the

recordset, are locked when you flush changes to the recordset back to the

underlying database table.



If you don't have a recordset, then there is nothing to flush back to the

underlying database, so that option is again not applicable.



When executing something like this:



<%objConn.execute(strSQL)%>



do it like:



<%objConn.execute strSQL,,adCmdText+adExecuteNoRecords %>



because ADO will create a recordset, and then throw it away, if you use the

first option. The optional adExecuteNoRecords tells ADO not to bother

creating the recordset at all.



The adCmdText tells ADO what type of command you are trying to execute,

which also speeds up execution marginally (the default is adCmdUnknown, and

ADO has to work out whether you are calling a stored procedure, executing an

SQL statement etc)



Cheers

Ken