GridView on Delete Method problem

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

RE: GridView on Delete Method problem

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/de fault.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.

Re: GridView on Delete Method problem

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:











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:

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

Ed Dror wrote:
> 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

Re: GridView on Delete Method problem

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

"Jialiang Ge [MSFT]" wrote in message
news:IcZMnb5nIHA.9016@TK2MSFTNGHUB02.phx.gbl...
> 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/de fault.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.
>

Re: GridView on Delete Method problem

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



wrote in message
news:d4d019b8-aa44-42f3-aa55-8ba600cb5e44@c65g2000hsa.google groups.com...
> 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:
>
>
>
>
>
>
>
>
>
>
>
> 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:
>
> > 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
>
> Ed Dror wrote:
>> 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

Re: GridView on Delete Method problem

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:



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.
=================================================

Re: GridView on Delete Method problem

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:



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.
=================================================

Re: GridView on Delete Method problem

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

On Apr 16, 5:43 pm, "Ed Dror" wrote:
> 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
>
> wrote in message
>
> news:d4d019b8-aa44-42f3-aa55-8ba600cb5e44@c65g2000hsa.google groups.com...
>
> > 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:
>
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
> > 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:
>
> > > > 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
>
> > Ed Dror wrote:
> >> 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

Re: GridView on Delete Method problem

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


"Jialiang Ge [MSFT]" wrote in message
news:B8gtQ%23FoIHA.10116@TK2MSFTNGHUB02.phx.gbl...
> 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:
>
>
>
> 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.
> =================================================
>

Re: GridView on Delete Method problem

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

wrote in message
news:9aaf2867-07b0-4e2e-9b06-3c0e7562a018@e39g2000hsf.google groups.com...
> 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
>
> On Apr 16, 5:43 pm, "Ed Dror" wrote:
>> 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
>>
>> wrote in message
>>
>> news:d4d019b8-aa44-42f3-aa55-8ba600cb5e44@c65g2000hsa.google groups.com...
>>
>> > 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:
>>
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>> > 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:
>>
>> > >> > 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
>>
>> > Ed Dror wrote:
>> >> 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
>

Re: GridView on Delete Method problem

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
"Jialiang Ge [MSFT]" wrote in message
news:QU8sQ%23FoIHA.9016@TK2MSFTNGHUB02.phx.gbl...
> 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:
>
>
>
> 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.
> =================================================
>

Re: GridView on Delete Method problem

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.
=================================================