ADO and Stored Procedures - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADO and Stored Procedures

Author

Topic

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-26 : 03:10:05

Salute..
I posted this a week ago..got very nice replys..but my problem still lives!.
I have a stored procedure..it is just a SELECT statement..this stored procedure is also interpreted as a COMMAND in my data environment.
SELECT * FROM myTable
I am assinging the returned recordset to an ADO created recordset object..the lock that I am using is optimistic..cursor location is client...type is static...when execute this statement
myRS = myCMD.execute
the recordset lock becomes READONLY..
why cant I return an updatable recordset from a stored procedure..??
Thanks in Advance..
Thanks ROBVOLK..I always appreciate ur answers..(cuz U answered my first post)

------------------
If I am to do it,
I will, and NOW ..

Nazim
A custom title

1408 Posts

Posted - 2002-02-26 : 03:24:32

Can you post your code here.

About Updating , if i were you . i would do every insert/update/select/delete thru stored procedures only.

something like passing the primarykey value and the value to be updated to the stored procedure and writing a update command in the Sp.

Rob has hit 3000 posts recently(http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13127).

quote:

Thanks ROBVOLK..I always appreciate ur answers..(cuz U answered my first post)

--------------------------------------------------------------

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-26 : 03:29:09

Bad surprise..the same "..." hhappens when I do this..

Dim TestCmd As New ADODB.Command
Dim GrdRs As New ADODB.Recordset

TestCmd.ActiveConnection = G_Cn
TestCmd.CommandType = adCmdText
TestCmd.CommandText = "SELECT GLR_TransD.TransDId, GLR_TransD.TransRemarks, GLR_TransD.ValueDB, GLR_TransD.ValueCR From GLR_TransD WHERE GLR_TransD.TransHId = " + objTransHId.Text

GrdRs.CursorType = adOpenStatic
GrdRs.CursorLocation = adUseServer
GrdRs.LockType = adLockOptimistic
GrdRs.CacheSize = 100
'AFTER the following statement the lockType is changed to adREADONLY
Set GrdRs = TestCmd.Execute
Me.grd.DataSource = GrdRs

what is WROOOOOOONG????
help me dearest YAKS!!!!!!!!!..HELP ME!!!

------------------
If I am to do it,
I will, and NOW ..

Nazim
A custom title

1408 Posts

Posted - 2002-02-26 : 03:40:05

What is grd??
if its a Grid? Check whether AllowUpdate Property is set to True.

and i think you are better off using a stored procedure to retreive records .

Dim TestCmd As New ADODB.Command
Dim GrdRs As New ADODB.Recordset

TestCmd.ActiveConnection = G_Cn
TestCmd.CommandType = adCmdText
TestCmd.CommandText = "exec transsearch " + objTransHId.Text

--------------------------------------------------------------

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-26 : 04:27:43

GrdRs is just a name..
But yes...I am using it as an underlying recordset beneath a TrueDBGrid..
but why is it rpeferrable to use stored procedures?..
I mean if I have a grid..why cant I make my work on the grid..then to the recordset...then to th tables?..
But the question is still WHY it changes to READONLY?..
I mean my case WAS putting the SELECT into a stored procedure..BUT the recordset coming from it was READONLY..this is the issue!...

------------------
If I am to do it,
I will, and NOW ..

Edited by - Da_Retina on 02/26/2002 04:36:26

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-02-26 : 06:57:29

You are creating the recordset twice! Once explicitly and setting values, and then implicitly using SET and .execute, so you only get the default priviledges;

let me explain, no, that'll take too long, let me summarise...

Dim GrdRs As New ADODB.Recordset <-- creates a record set

you then apply some types...

GrdRs.CursorType = adOpenStatic
GrdRs.CursorLocation = adUseServer
GrdRs.LockType = adLockOptimistic
GrdRs.CacheSize = 100

Set GrdRs = TestCmd.Execute <-- problem, this creates a recordset with DEFAULT values (forward only, read only), so effectively blowing away all the work you have done in the previous lines!

What you want to do is use the OPEN method of the recordset instead

grds.open testcmd

GIve that a try instead of the SET line

Edited by - uberbloke on 02/26/2002 07:03:09

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-26 : 06:57:36

You can't edit the results of a stored procedure, any recordset based on an SP will be read-only.

I found this in the ADO documentation under "Static Cursors":

The static cursor always displays the result set as it was when the cursor was 
first opened. Depending on implementation, static cursors are either read-only or 
read/write and provide forward and backward scrolling. The static cursor does not usually 
detect changes made to the membership, order, or values of the result set after the 
cursor is opened. Static cursors may detect their own updates, deletes, and inserts, 
although they are not required to do so.

It appears that you will need to use another cursor type if you need to UPDATE the recordset.

Why not use a stored procedure to retrieve the rows, then use a separate stored procedure to UPDATE? rs.UPDATE and .AddNew are problematic anyway, and using stored procedures lets you use any ADO cursor for the recordset, since it only needs to read rows anyway.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-26 : 07:20:16

You can't set the cursor type when you use the command object in asp to create a recordset from a stored procedure. Try objRS.Count - you'll always get -1

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-26 : 07:57:20

ROBVOLK ,MATE,I think I disagree...

quote:
You can't edit the results of a stored procedure, any recordset based on an SP will be read-only.

uberbloke..U R RIGHT!!!!!!!!!!!!!!!!!!..

THE RECORDSET RETURNED FROM THE SP WAS LOCKED THE WAY I WANTED!!!!..
I am THANKFUL... BUTTTTTT..HOW does a SET STATEMENT creates a new RECORDSET?..what I mean is that I am setting to an already created recordset..!!..I mean how does a set to an already created object create it again!!..
ROBVOLK..the cursot type was not my problem here..it was the lock type!..
Anyways......THANKS AGAIN UBERBLOKE..AND ROB..I will always enjoy ur replys!!..keep them coming mate!..I LOVE U ALLLLLLLL!!..
Big Salute to BIG FELLOWS!!..

------------------
If I am to do it,
I will, and NOW ..

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-02-26 : 09:00:59

Your welcome!

Why does SET create a new recordset?

It doesn't, it is the command (or connection) that is creating the record set.

There are two ways to open a recordset. You can use the OPEN method of a recordset referencing a valid command or connection object.

OR

You can call the EXECUTE method of a command (or connection) object, that may (or may not, in the case of an insert, delete, update) return a recordset.

Using the EXECUTE method means that you dont have to have preexisting defined recordset (it is all done implicitly), as it creates one for you with default properties; but this means that if you use the name of an existing recordset it will get recreated for you.

Ho hum......., serves you right for relying on default behaviour

Have a look at the following link, [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/iisref/html/psdk/asp/iiwadata.asp[/url]
for a detailed look at connection/command/recordset; and use [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcmdexecute.asp[/url] as a start for looking at what the execute method actually does

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources