GridView on Delete Method problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed Dror - 15 Apr 2008 22:21 GMT

Hi there,

I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro.

I have a Price page (my website require login) with GridView with the
following columns

PriceID, Amount, Approved, CrtdUser and Date
And Edit and Delete buttons

I created a function to retrive the current user

Protected Function GetUserName() As String
Return User.Identity.Name
End Function

And on SQLDatasource1 I added

Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles
SqlDataSource1.Updating
e.Command.Parameters("@CrtdUser").Value = GetUserName()
End Sub

I converted the CrtdUser into a template and changed the Field binding
from crtdUser to GetUserName() function

Also I created a Trigger for Update which basically insert updated records
into a log table from Inserted = NEW and deleted = Old

Everything works fine

I also created a trigger for Delete look like this
CREATE TRIGGER [dbo].[tr_Price_Delete]
ON [dbo].[Price]
AFTER DELETE
AS
BEGIN

SET NOCOUNT ON;
Insert into dbo.PriceArchive
Select
'New','D',
Price_ID,Amount,Store_ID,BSP,ALC_ID,GN_ID,Approved,CrtdUser,GetDate()
From Deleted

END

Because the delete trigger is from deleted every times when user delete
record the log table populated with the original user name that create the
record

I also changed the label on the GridView with GetUserName() so now it show
only the current user name (overrite the acual record) but it dsen't
populate the table with current user name
The crtdUser show old values insted of new.

How do I retreived the current user on delete method whith this GridView
control?

Thanks,
Ed Dror

Reply to this Message

Jialiang Ge [MSFT] - 16 Apr 2008 08:52 GMT

Hello Ed,

I am trying to understand the logic of the Price page. Based on my
understanding, you are composing a Price table (GridView) that allows edit
and delete.
When a user edit an item in the table, the user's name (GetUserName()) will
be filled into its CrtdUser field, and the orginal CtrdUser value will be
backuped into a 'PriceArchive' DB table.
When the user click on the 'Delete' button, the CtrdUser value shows
currently wll be moved to the 'PriceArchive' table, and the original
CtrdUser value which was backuped in 'PriceArchive' DB table will be
restored to the CrtdUser field.
So your question is how to get the current user name that shows in the
Price GridView when users click on the 'Delete' button.
Is this right?

We can add the field CtrdUser into the DataKeyNames property of GridView,
and set the DeleteCommand property of the SqlDataSource as:
"DELETE FROM [PriceTable] WHERE CtrdUser = @CtrdUser"
The SqlDataSource itself knows how to handle @CtrdUser to retrieve its
current value. For more details, see the MSDN article:
http://msdn2.microsoft.com/en-us/library/z72eefad.aspx

Another approach is to remove the DeleteCommand from SqlDataSource,
register the RowDeleting event of the GridView, and in its event handler,
we can get the row item from e.RowIndex, and call the corresponding delete
command.

If you have any other concerns, or questions, feel free to let me know.

Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Ed Dror - 16 Apr 2008 16:38 GMT

Jialiang ,

when you look at the GridView all the record is the current user name so the
statement
Delete from Price where CrtdUser = @CrtdUser will update all the records and
this is somthing we don't want
Also it will not show in the PriceLog table the current user name it will
show the original user name

I'm thinking to convert the delete button to template and OnClick event call
Stored Proc that update the PriceTable before it got deleted

Or in the GridView_RowDeleted ...
Call usp_UpdateCurrentUser
End Sub
And see what happend

What do you think of that?

Thanks,

Ed Dror

> Hello Ed,
>
[quoted text clipped - 56 lines]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.

Reply to this Message

Jialiang Ge [MSFT] - 17 Apr 2008 08:49 GMT

Hello Ed,

Sorry for my misunderstanding of the scenario in my initial response.

I think we can use RowDeleting event, instead RowDeleted which fires after
a delected command is executed.

1. Add a command field in the gridview columns collection:
<asp:CommandField ShowDeleteButton="True">
<ItemStyle HorizontalAlign="Left" />
</asp:CommandField>
2. Register the RowDeleting event of the GridView
3. In the event handler, we get the current selected row in the gridview
with the help of e.RowIndex, retrieve the information we need from the row
then do the delete operation. In the end, we call the bind the gridview to
show the updated data. Here is an example:

protected void tblUser_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int userId = int.Parse(tblUser.DataKeys[e.RowIndex].Value.ToString());
Status result = DAOFactory.GetSysUserDAO().Delete(userId); //our
delete operation
//retrieve the new data
DataTable tb = .....;
// bind to the gridview
tblUser.DataSource = tb;
}

Hope it helps
Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Reply to this Message

Ed Dror - 18 Apr 2008 20:20 GMT

Jialiang,

I fix it
I changed the trigger After Deleted

From

Insert into dbo.StoresArchive
Select
'New','D', Store_ID,Store_Name,CrtdUser,GetDate()
From Deleted

To

Insert into dbo.StoresArchive
Select
'New','D', Store_ID,Store_Name,'DeletedUser',GetDate()
From Deleted

So I Overrite the default name of the CrtdUser to whatever name I want

Now on the ASP.NET VB page I wrote function

Public Function UpdateStoreArchive() As Integer
Dim con As New SqlConnection(conString)
Try
Dim updateString As String = "Update StoresArchive Set CrtdUser
= '" & User.Identity.Name & "' Where CrtdUser='DeletedUser' "
Dim cmd As New SqlCommand(updateString, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Function

And (On Grid view event)

Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.GridViewDeletedEventArgs) Handles
GridView1.RowDeleted
Call UpdateStoreArchive()
End Sub

So every time you delete a record the trigger will insert into Archive table
from deleted but will force to change the name column to a dummy name
Then on raw_deleted you call function that will update the raw with the
current user name on dummy name that you just created.

Thanks,
Ed Dror

> Hello Ed,
>
[quoted text clipped - 41 lines]
> rights.
> =================================================

Reply to this Message

Jialiang Ge [MSFT] - 21 Apr 2008 05:31 GMT

Thank you, Ed, for sharing the resolution with us.

Have a good day!

Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Reply to this Message

Jialiang Ge [MSFT] - 17 Apr 2008 08:49 GMT

Reply to this Message

Ed Dror - 18 Apr 2008 18:23 GMT

Jialiang,

It seems that there is no connection between what you see on the Grid and
what you get from
SQL server

How come the code

Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles
SqlDataSource1.Updating
e.Command.Parameters("@CrtdUser").Value = GetUserName()
End Sub

Workd for update but not for delete

Remember I have a simple trigger After Delete that will select from deleted
into PriceLOG table
So no matter how we manipulate the DataGrid (Screen View) it always select
from Deleted

Now I though that befor delete I will call Update procedure but this will
generate an extra record

There is no way to overrite the Original UserName when you delete a raw?

Thanks,
Ed Dror

> Hello Ed,
>
[quoted text clipped - 41 lines]
> rights.
> =================================================

Reply to this Message

wisccal@googlemail.com - 16 Apr 2008 13:30 GMT

Hi Ed Dror,

You're saying that you "converted the CrtdUser into a template and
changed the Field binding from crtdUser to GetUserName() function".

If I understand this correctly, you did something like this:

<asp:GridView ID="gvPrices" runat="server">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblUser" Text="<%#
GetUserName() %>" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Now you expect that the value GetUserName() returns will be saved to
the database. Is my understanding correct?

The problem with this is that you really can't bind to your own
method. Only the Bind() method will automatically load from and save
fields to the DB. And you can only use it with fields in your
DataSource.

What you could do instead is handle the RowUpdating event

in the aspx file:

<asp:GridView ID="gvPrices" OnRowUpdating="gvPrices_RowUpdating"
runat="server">

in your codebehind:

Protected Sub gvPrices_RowUpdating(ByVal sender As Object, ByVal e As
GridViewUpdateEventArgs)
e.NewValues("CrtUser") = GetUserName()
End Sub

=========
Regards,
Steve
www.stkomp.com

> Hi there,
>
[quoted text clipped - 58 lines]
> Thanks,
> Ed Dror

Reply to this Message

Ed Dror - 16 Apr 2008 16:43 GMT

Steve,

The GridView working fine on Update method, the problem is with Delete
method
Even the GridView CrtdUser set as current user when you delete record
It show the original User Name in the PriceLog table
Because the Trigger AfterDelete uses the Select from deleted not from
inserted

Thanks,
Ed Dror

> Hi Ed Dror,
>
[quoted text clipped - 106 lines]
>> Thanks,
>> Ed Dror

Reply to this Message

wisccal@googlemail.com - 17 Apr 2008 08:54 GMT

Hi Ed Dror,

I'm still not 100% sure I understand your problem. You are talking
about a PriceLog table, but your trigger is defined on PriceArchive.
Is the PriceLog table for updated records?

If you need to have some kind of parameter to pass to a trigger, there
are a couple of options.

You could use a global temporary table as follows:

CREATE TABLE ##CurrentUser(UserName varchar(50));

Then, in your RowDeleting event, you can update this table to hold the
current user name. In your trigger, you will be able to say:

Insert into dbo.PriceArchive
Select 'New','D', Price_ID,Amount,Store_ID,BSP,ALC_ID,GN_ID,Approved,
(
select top 1 UserName
from ##CurrentUser
),
GetDate()
From Deleted

You can do the same with SQL Server's context_info(), which is
probably a better option even.

But I believe writing your own stored procs to handle the auditing for
deleted records is the safest way to go.

===========
Regards,
Steve
www.stkomp.com

> Steve,
>
[quoted text clipped - 122 lines]
> >> Thanks,
> >> Ed Dror

Reply to this Message

Ed Dror - 18 Apr 2008 20:17 GMT

Steve,
I fix it
I changed the trigger After Deleted from
Insert into dbo.StoresArchive
Select
'New','D', Store_ID,Store_Name,CrtdUser,GetDate()
From Deleted

To

Insert into dbo.StoresArchive
Select
'New','D', Store_ID,Store_Name,'DeletedUser',GetDate()
From Deleted

So I Overrite the default name of the CrtdUser to whatever name I want

Now on the ASP.NET VB page I wrote function

Public Function UpdateStoreArchive() As Integer
Dim con As New SqlConnection(conString)
Try
Dim updateString As String = "Update StoresArchive Set CrtdUser
= '" & User.Identity.Name & "' Where CrtdUser='DeletedUser' "
Dim cmd As New SqlCommand(updateString, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
ErrorMessage.Text = ex.Message.ToString
End Try
End Function

And
Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.GridViewDeletedEventArgs) Handles
GridView1.RowDeleted
Call UpdateStoreArchive()
End Sub

So every time you delete a record the trigger will insert into Archive table
from deleted but will force to change the name column to a dummy name
Then on raw_deleted you call function that will update the raw with the
current user name on dummy name that you just created.

Thanks,
Ed Dror

> Hi Ed Dror,
>
[quoted text clipped - 161 lines]
>> >> Thanks,
>> >> Ed Dror

Reply to this Message

GridView on Delete Method problem

Free Magazines