Hi,

I need to check whether an entry exists in a DB before I try inserting it.
I am using a basic Details view and SQLDataSources dragged from the visual studio tool bar to allow users to insert new entries. The problem I have is that I am using a DateTime field that is bound to a SQL DB as a primary key and if the user enters a date that already exist it bombs out with a Violation of PRIMARY KEY constraint error.
 I am new to ASP.net and have used the code that Visual Studio creates to insert to the DB. I really want to do something like:-

    Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating
        If DetailsView1.DataItem("txtDateInsert").exists Then
            Dont enter it!!

        End If

But cannot get the syntax correct. any ideas please?

There is nothing necessary wrong with getting exceptions.

You just attempt to insert it as you do but within a "try {...} catch (SqlException sql) {...}" wrapper. if the exception is a Violation of PRIMARY KEY then, you just let the program flow getting out of your "catch" block, otherwise you will propagate your exception up by throwing it again.

Here is the pseudo code:

bool inserted = false;
try {
   call insert which may cause an exception
   inserted = true;
}
catch (SqlException sqlex) {
    if (Not(error code of sqlex is corresponding to Primary Key Duplicate))
       throw;
}

Hi,

Thank you for the advice, I get the logic of doing it this way now. However I have tried to action this by adding:-

    Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
       

        Try
                        MsgBox("inserted trying")
        Catch ex As System.Data.SqlClient.SqlException
            MsgBox("Caught error")
        Finally
            MsgBox("end of inserted moved on")
        End Try

    End Sub

But it just gives the "inserted trying" then "end of inserted moved on" message and then crashes. I have tried every Action in the detailsview menu i.e inserting, binding, bound etc. I have also tried just having Catch on its own to catch all errors, also tried all the SQLDataSource actions but i dont seem to be picking it up at the correct time. I have placed the error below.

Violation of PRIMARY KEY constraint 'PK_NgADailyCost_1'. Cannot insert duplicate key in object 'dbo.NgADailyCost'.
The statement has been terminated.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_NgADailyCost_1'. Cannot insert duplicate key in object 'dbo.NgADailyCost'.
The statement has been terminated.

Source Error:

[SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_NgADailyCost_1'. Cannot insert duplicate key in object 'dbo.NgADailyCost'.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1953274
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849707
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +227
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +86
   System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +274
   System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +676
   System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +113
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

 in fact that might be different because you work with SqlDataSource

SqlDataSource as you know provide event handlers: selecting, selected, updating, updated, ...

Since you do you insertion through a datacontrol, you are interrested to hook up an event handler of the "inserted" event. The second parameter of this handler is SqlDataSourceStatusEventArgs. This parameter expose the property "Exception"; if it is not null and contains a SqlException having 2627 in its Number property (2627 is the error code I think for duplicate primary key exceptions). When you get a Sql duplicate key exception, you just put "true" in the ExceptionHandled of your SqlDataSourceStatusEventArgs to stop the propagation of the error.

protected void On_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
    SqlException sqlEx = e.Exception as SqlException;
    if (null != sqlEx && 2627 == sqlEx.Number)
        e.ExceptionHandled = true;
}

ref: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasourcestatuseventargs_members.aspx

Hi,
As I mentioned I am new to this so a bit confused. The inbuilt sub for item_inserted is:-

Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted

End Sub

Do I use this or create something like you have done above? If i use the inbuilt one the syntax is wrong if I add SqlDataSourceStatusEventArgs to the variables in the protected line above. I am using Vb.net as the back end language.

WorkOne:

As I mentioned I am new to this so a bit confused. The inbuilt sub for item_inserted is:-

Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted

End Sub

Do I use this or create something like you have done above?

Yes you can

WorkOne:

If i use the inbuilt one the syntax is wrong if I add SqlDataSourceStatusEventArgs to the variables in the protected line above

 not sure I understand this though

The key is to look at the e.Exception and cast it into SqlException (I don't know the syntax in VB). From there, if the sql exception "Number" is 2627 (which means: primary key duplicate error), let the value True into the feild e.ExceptionHandled

Hi, Not sure if this is the best but it worked for me. 

Protected Sub Page_Error(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Error
        Dim message As String = Server.GetLastError().Message
        If message.Contains("Violation of PRIMARY KEY") Then
            Server.ClearError()
            Response.Redirect("DailyDataError.aspx")
        End If
    End Sub

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
    Dim sqlEx as SqlException;
    sqlEx = CType(e.Exception, SqlException)
    If sqlEx <> Nothing And sqlEx.Number = 2627 Then
        ...Do error handling here...

        e.ExceptionHandled = true
    End If
End Sub