Updatecommand

I have a trigger that runs when updating time related data. And to not
allow duplicate entries for a specific date it does the following:

.....

IF @cntoverlappingrows > 0
BEGIN
RAISERROR( 'The new date entry falls into a previous interval.', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

This works great with the following code to show the user where the error
occured by posting that red warning icon:

if (e.Status == UpdateStatus.ErrorsOccurred)
{
e.Row.RowError = e.Errors.Message;
e.Status = UpdateStatus.SkipCurrentRow;
}
else
{
if (e.StatementType == StatementType.Insert)
{
e.Status = UpdateStatus.SkipCurrentRow;
}
}

However the hair pulling begins when I get an error on the insert caused by
the first code section above. But if the user goes back and tries to fix the
error ( the error means we still are trying to insert the new record ) and
click save we get a "concurrency violation: updatecommand ...." error. I
understand why we get the concurrency violation. ADO is trying to update
the entry which was meant to be an insert since it never passed the trigger
which rejects the insert. We are not getting a primary key back because we
still haven't inserted yet. Why is ADO trying to update something that was
never inserted. Does anyone know how to get this record to show up as an
insert? How I can refresh/reload the dataset which does not contain the row
with that insert error? Basically my insert is stuck and can not be updated.
Please help me make my insert an insert!!

By the way if I click refresh obviously the correct data shows again. If I
can't get the insert to be an insert rather than an update I would be open
to any suggestions on handling this.

Thanks to anyone ahead of time. I hope I have some hair left.


.

Relevant Pages

  • Re: ADO + SQL-Server + Defaultwerte
    ... Ich kann ja bei ADO allgemein auch einen String mit einem SQL-Statement ... Der Trigger reagiert auf Updates und die zu ... behandelndeRowwirdanhand einer vorher auf dem Client ...
    (microsoft.public.de.vb.datenbank)
  • Re: Row cannot be located for updating
    ... The insert/update trigger pulls information from other tables and populates ... The solution I was hoping to find was to inform ADO which fieldare the ...
    (borland.public.delphi.database.ado)
  • Re: Write conflict in ADP on updating table including triggers
    ... ADO uses the SQL Server @@IDENTITY variable to determine the key of the record ... The most obvious work-around for this is to make sure that any trigger that ... >FOR UPDATE trigger used for auditing purposes. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Error with Insert/Update stored procedure in VB6
    ... (I use Dataenvironment). ... >> that trigger. ... > I tried to reproduce it by passing a string where the insert/update ... > What ADO version are you using, or what database library if not ADO? ...
    (microsoft.public.vb.bugs)
Loading