R
Roald
I am working on an application that needs to implement logical deletes
instead of removing the records permanently. The logical delete works
by setting 3 fields (deleted flag, date and user). I have been trying
to replace the DeleteCommand query text of the FormView with an UPDATE
statement, but keep getting an error 'Must declare scalar variable
@delete_user'.
The update statement looks like this:
UPDATE [tablename] SET [delete_logical] = N''Y'', [delete_date] =
GETDATE(), [delete_user] = @delete_user WHERE (urn = @original_urn)
The field urn is the key field of the table, and is set correctly. The
field delete_user is available (but invisible) on the FormView, and
gets set to User.Identity.Name.ToUpper() in the FormView_ItemDeleting
trigger. However, when examining the query executed on the database
through Profiler there is no mention at all of the @delete_user
variable, no definition and no values. This causes the error to occur.
When replacing the variable @delete_user with static text the query
works fine.
Browsing on the net for a solution I came across a two-liner in the
documentation of FormView on DataKeyNames. I tried adding the
delete_user field to the DataKeyNames, but this only results in adding
a definition for original_delete_user to the query for the delete
statement (again via Profiler).
As the (real) update statement in UpdateCommand works fine I suspect
this error has to do with the way the DeleteCommand gets parsed for
variables, and it looks like any variables before the WHERE clause are
ignored.
Does anyone have a solution to this (apart from hand-coding the logical
delete) and/or can anyone confirm that my suspicions on the parsing are
correct.
Many thanks.
Roald.
instead of removing the records permanently. The logical delete works
by setting 3 fields (deleted flag, date and user). I have been trying
to replace the DeleteCommand query text of the FormView with an UPDATE
statement, but keep getting an error 'Must declare scalar variable
@delete_user'.
The update statement looks like this:
UPDATE [tablename] SET [delete_logical] = N''Y'', [delete_date] =
GETDATE(), [delete_user] = @delete_user WHERE (urn = @original_urn)
The field urn is the key field of the table, and is set correctly. The
field delete_user is available (but invisible) on the FormView, and
gets set to User.Identity.Name.ToUpper() in the FormView_ItemDeleting
trigger. However, when examining the query executed on the database
through Profiler there is no mention at all of the @delete_user
variable, no definition and no values. This causes the error to occur.
When replacing the variable @delete_user with static text the query
works fine.
Browsing on the net for a solution I came across a two-liner in the
documentation of FormView on DataKeyNames. I tried adding the
delete_user field to the DataKeyNames, but this only results in adding
a definition for original_delete_user to the query for the delete
statement (again via Profiler).
As the (real) update statement in UpdateCommand works fine I suspect
this error has to do with the way the DeleteCommand gets parsed for
variables, and it looks like any variables before the WHERE clause are
ignored.
Does anyone have a solution to this (apart from hand-coding the logical
delete) and/or can anyone confirm that my suspicions on the parsing are
correct.
Many thanks.
Roald.