Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Da_Retina
Posting Yak Master
109 Posts |
 Posted - 2002-02-20 : 09:11:41
|
| Salute my pals..I am using a stored procedure to retun a RECORDSET..to a ADO GrdRs that I created..I am setting the GrdRs as follows :GrdRs.CursorType = adOpenStaticGrdRs.CursorLocation = adUseClientGrdRs.LockType = adLockOptimisticwhen the recordsets comes to my baby GrdRs its lock type is READONLY..WHY?...ALTHOUGH I am setting it to adLockOptimistic??????when I change my command to a an ordinary select statement it works fine..WHAT IS WRONG IN using stored procedures to return recordsets?..and how can I change its properties..!!Thanks In Advance..Salute..------------------If I am to do it,I will, and NOW .. |
|
|
Da_Retina
Posting Yak Master
109 Posts |
Posted - 2002-02-20 : 09:17:56
|
| by the way..here is my stored procedure..CREATE PROCEDURE spGLR_DailyJournalGrd (@TransHId as INT) ASSELECT GLR_TransD.TransDId, GLR_TransD.TransHId, GLR_TransD.AccountTotalId, GLR_TransD.TransRemarks, GLR_TransD.ValueDB, GLR_TransD.ValueCR, GLR_TransD.BBF, GLR_TransD.LastUser, GLR_TransD.LastUpdate, GLR_Accounts.MainAccountNo, GLR_Accounts.SubAccountNo, LTRIM(RTRIM(GLR_Accounts.MainAccountNo))+'.'+ LTRIM(RTRIM(STR(GLR_Accounts.SubAccountNo))) as AccountNo, GLR_Accounts.AccountName, GLR_TransD.SystemTransId FROM GLR_TransD INNER JOIN GLR_AccountsTotals ON GLR_TransD.AccountTotalId = GLR_AccountsTotals.AccountTotalId INNER JOIN GLR_Accounts ON GLR_AccountsTotals.AccountId = GLR_Accounts.AccountId WHERE GLR_TransD.TransHId = @TransHIdBY THE WAY.............EVEN is I replaced the whol query (select statement with select * from test IT FAILS!!)..Salute..------------------If I am to do it,I will, and NOW .. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-20 : 09:18:46
|
| Because a stored procedure, even one that returns rows, is not an updateable object. You can't SELECT FROM stored_procedure, nor UPDATE stored_procedure. ADO can't perform an operation that the underlying data source can't support.Have you considered using stored procedures to perform UPDATEs and INSERTs, instead of using ADO's .AddNew and .Update methods? It will be A LOT more efficient over the ADO methods....And having just looked at the code you posted, having JOINs in the results could affect whether the statement CAN be updated or not. You would only be able to UPDATE columns in one table of the JOIN anyway, and only those rows that can be uniquely identified (the one side of a one-to-many relationship).Edited by - robvolk on 02/20/2002 09:21:10 |
|
|
|
Da_Retina
Posting Yak Master
109 Posts |
Posted - 2002-02-20 : 09:33:49
|
| Salute..Thanks for the speedy reply..First...I am SELECTING in the STORED PROCEDURE..NOT FROM IT!..as u wrote..if I understood U right..that is my stored procedure IS ONLY ONE SELECT STATEMENT..Second..I change the stored procedure to SELECT * FROM TESTTABLE..that is a straight forward select..NOT a JOINED statement..and set my ADO cursor's lock to optimistic..it stays like this till it EXECUTES the command and return the RS to my baby rs..the lock changes to READONLY!------------------If I am to do it,I will, and NOW .. |
|
|
|
|
|
|
|
|
Returning Recordsets from SPs..Help