Dataset operations - refresh etc

[Guest]

H
In our application we use stored procedures to insert,update, delete and select rows from the database. I am using a dataset object to manipulate rows returned from the database

If I do an update or delete using the stored procedures, I want the dataset to refresh to see these changes - how do I do this?
In VB6 we recorded the bookmark, did a refresh of the recordset and set the recordset back to the bookmarked position and then redisplayed the record. How do I do this with a dataset? Or is there another way to do this

Thanks is advanc
Siobhan

 

[William \(Bill\) Vaughn]

How are you executing the update SP? Are you using the DataAdapter Update
method or simply doing an ExecuteNonQuery? If the former, this implies
you're doing a row-by-row update so you can fetch the changed row using a
SELECT at the end of your SP. The Update method posts this row to the
DataSet table referenced in the Update method.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi
In our application we use stored procedures to insert,update, delete and

select rows from the database. I am using a dataset object to manipulate
rows returned from the database.

If I do an update or delete using the stored procedures, I want the

dataset to refresh to see these changes - how do I do this?

In VB6 we recorded the bookmark, did a refresh of the recordset and set

the recordset back to the bookmarked position and then redisplayed the
record. How do I do this with a dataset? Or is there another way to do
this?

 

[Guest]

Hi - I am executing the stored procedure using ExecuteNonQuery.

So would I put the select at the end of the Update Stored procedure? How would this work, would this also retrieve any other rows updatedby other users?

I also need to know how to refresh after a delete (again executenonquery) or in the event of a failed update (concurrency failure -we update using timestamp field)

Or perhaps using the DataAdapter update method?

Thanks
Siobhan

 

[Greg Low \(MVP\)]

You need to use ExecuteReader so that you can return whatever you select
after you update. The SELECT will return whatever the current state of the
selected rows is at the time it is executed, so if other users have modified
data, you'll see their mods too (if they have committed them).

You can use the same method to refresh after a DELETE or a concurrency
failure using a timestamp.

HTH,

--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com

Hi - I am executing the stored procedure using ExecuteNonQuery.

So would I put the select at the end of the Update Stored procedure? How

would this work, would this also retrieve any other rows updatedby other
users?

I also need to know how to refresh after a delete (again executenonquery)

or in the event of a failed update (concurrency failure -we update using
timestamp field)

 

[Guest]

Sorry - I wasn't clear - the select Stored procedure is execute ExecuteReader, and the update statement is ExecuteNonQuery

I currently use a DataAdapter and fill a DataSet. What I have done at the minute is execute the update Sp and then clear the dataset and fill again, but I am sure this is not the most efficient

Thanks for your time
Siobhan

 

[Greg Low \(MVP\)]

Hi Siobhan,

What I was suggesting was making your update statement an ExecuteReader
instead of an ExecuteNonQuery and putting a SELECT statement after the
UPDATE in the T-SQL that's sent ie do the work in one round trip. Otherwise,
yes, what you are doing is ok.

HTH,

--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com

Sorry - I wasn't clear - the select Stored procedure is execute

ExecuteReader, and the update statement is ExecuteNonQuery.

I currently use a DataAdapter and fill a DataSet. What I have done at the

minute is execute the update Sp and then clear the dataset and fill again,
but I am sure this is not the most efficient?