Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

return a set, print it to screen, delete records. 1

Status
Not open for further replies.

RestonDeveloper

Programmer
Aug 1, 2007
23
US
i am creating a winform that reads in an access database and changes the data and puts it into a mediator table, then i call i have 3 sql procedures: sp_create, sp_update, sp_delete

procedures compare mediator and main table, for records in mediator that aren't in main, i update a [actiontaken]='added' and run insert into statement on that group. if records are in both [actiontaken]='updated' and run update main statement. but deleted i just run delete statement nothing in mediator table to flag.

how do i let user know which events were deleted, for simplicity i'll just let user know how many were deleted for now.

thanks any pointers.


 
Its' not entirely clear to me what you're doing here. Are these SQL Server stored procedures we are talking about now? (if so, you really shouldn't use sp_ as your naming convention - It causes SQL Server to first look in MASTER database for the proc, because this is the naming convention for system stored procedures link)

If so, you might try capturing @@ROWCOUNT in a variable somewhere round the delete from your table (preferably after ;-) ), and getting this value passed back to the app for notification.

Here is an example, using an output parameter to pass back the value:

Code:
[COLOR=blue]create[/color] [COLOR=blue]proc[/color] restonTest(@deleted [COLOR=blue]int[/color] [COLOR=blue]output[/color]) [COLOR=blue]as[/color]

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #t (n [COLOR=blue]int[/color])

[COLOR=blue]insert[/color] #t
[COLOR=blue]select[/color] 1
union all [COLOR=blue]select[/color] 2
union all [COLOR=blue]select[/color] 3
union all [COLOR=blue]select[/color] 4

[COLOR=green]--run a delete
[/color][COLOR=blue]delete[/color] [COLOR=blue]from[/color] #t [COLOR=blue]where[/color] n > 3

[COLOR=green]--capture rowcount
[/color][COLOR=blue]set[/color] @deleted = @@rowcount

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t

And to call it:

Code:
[COLOR=green]---declare variable to hold output
[/color][COLOR=blue]declare[/color] @del [COLOR=blue]int[/color]

[COLOR=blue]exec[/color] restonTest @del [COLOR=blue]OUTPUT[/color]

[COLOR=blue]select[/color] @del

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!
 
I don't get it, why would you want to delete the round table?

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett
 
Alex,

thanks. I suppose I could modify code above to return a string instead, and pass the data back, to alert user what rows were deleted instead. thanks this was very helpful.

you the man!
 
What kind of notification do you want to sent back? An array of row ID's or something like that?

I thought you only wanted to notify the user of the count, but re-reading your post I see that was just the starting point. If you want to provide additional information, you probably want to return a resultset, not an output parameter. This will need to be done slightly differently, but it is still not hard.

If you show me the delete statement, I'm sure I'll be able to show you how to return the list of events affected. You just need to capture the info you need somehow before the delete.

Glad you got it (partially) working!

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top