FK constraints conflict with insert statement

Post your code so that experts can help

Here's the aspx, followed by the code-behind

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
   <h1>Create a new listing</h1>
    <asp:DetailsView ID="DetailsView1"  AutoGenerateRows="False" 
        DataKeyNames="id" DataSourceID="LinqDataSource1" Height="50px" 
        Width="165px" DefaultMode="Insert"  runat="server">
         <Fields>
            <asp:DynamicField DataField="equipmentType" HeaderText="equipmentType" />
            <asp:DynamicField DataField="maker" HeaderText="maker" />
            <asp:BoundField DataField="model" HeaderText="model" />
            <asp:BoundField DataField="serial_num" HeaderText="serial_num" />
            <asp:BoundField DataField="mfg_year" HeaderText="mfg_year" />
            <asp:BoundField DataField="last_pm_date" HeaderText="Last PM date" />
            <asp:BoundField DataField="asking_price" HeaderText="asking_price" />
            <asp:DynamicField DataField="condition" HeaderText="condition" />
            <asp:DynamicField DataField="status" HeaderText="status" />
            <asp:DynamicField DataField="UserId" HeaderText="UserId" ReadOnly="true" Visible="False" />
            <asp:CommandField ShowInsertButton="True" />
         </Fields>
    </asp:DetailsView>
    <asp:LinqDataSource ID="LinqDataSource1"  
        ContextTypeName="lucidequipmentDataContext" EnableInsert="True" 
        EntityTypeName="" TableName="equipment_masters" runat="server">
       <InsertParameters>
           
            <asp:Parameter Name="makersId" DefaultValue="@makersId"  />
            <asp:Parameter Name="equipmentTypeId" DefaultValue="@equipmentTypeId" />
            <asp:Parameter Name="model" DefaultValue="@model" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="serial_num" DefaultValue="@serial_num" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="mfg_year" DefaultValue="@mfg_year" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="last_pm_date" DefaultValue="@last_pm_date" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="conditionsId" DefaultValue="@conditions.Id" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="asking_price" DefaultValue="@asking_price" />
            
            <asp:Parameter Name="statusId" DefaultValue="@statusId" ConvertEmptyStringToNull=true />
        </InsertParameters>
 
    </asp:LinqDataSource>
     <asp:Label ID="ListingCreatedMessage" runat="server" 
          Text="Your listing has been successfully created" EnableViewState="False" 
          Visible="False" AssociatedControlID="EquipmentMasterFields">
  </asp:Label>
</asp:Content>

Select allOpen in new window

and now the code-behind

Imports System.Web.Security
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.DynamicData
Imports System.Web.Routing
Imports System.Web.UI.WebControls.Expressions

Partial Class SellerPages_RecordInsertTest
    Inherits System.Web.UI.Page

    Dim currentUser As MembershipUser = Membership.GetUser()
    Dim currentUserId As Guid = DirectCast(currentUser.ProviderUserKey, Guid)
    Dim UserId As Guid = currentUserId

    Protected Sub DetailsView1_ItemInserting(sender As Object, e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
        
        Dim CurrentDate As DateTime = Now
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("lucidequipmentConnectionString").ConnectionString
        Dim insertSql As String = "INSERT INTO equipment_master(makersId,equipmentTypeId,model, serial_num, mfg_year,asking_price,statusId, last_pm_date, conditionsId,registration_date, userId) VALUES(@makersId,@equipmentTypeId, @model, @serial_num, @mfg_year, @asking_price, @statusId, @last_pm_date, @conditionsId, @registration_date, @UserId )"

        Using myConnection As New SqlConnection(connectionString)
            myConnection.Open()

            Dim myCommand As New SqlCommand(insertSql, myConnection)

            myCommand.Parameters.AddWithValue("@UserId", currentUserId)
            myCommand.Parameters.AddWithValue("@registration_date", CurrentDate)
            myCommand.Parameters.Add("@makersId", SqlDbType.Int).Value = e.Values("makersId")
            myCommand.Parameters.Add("@equipmentTypeId", SqlDbType.Int).Value = e.Values("equipmentTypeId")
            myCommand.Parameters.Add("@model", SqlDbType.NVarChar).Value = e.Values("model")
            myCommand.Parameters.Add("@serial_num", SqlDbType.NVarChar).Value = e.Values("serial_num")
            myCommand.Parameters.Add("@mfg_year", SqlDbType.NVarChar).Value = e.Values("mfg_year")

            myCommand.Parameters.Add("@asking_price", SqlDbType.NVarChar).Value = e.Values("asking_price")
            myCommand.Parameters.Add("@statusId", SqlDbType.Int).Value = e.Values("statusId")
            myCommand.Parameters.Add("@last_pm_date", SqlDbType.SmallDateTime).Value = e.Values("last_pm_date")
            myCommand.Parameters.Add("@conditionsId", SqlDbType.Int).Value = e.Values("conditionsId")

            myCommand.ExecuteNonQuery()

            myConnection.Close()
        End Using
    End Sub
    Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
        ListingCreatedMessage.Visible = True
    End Sub
End Class

Select allOpen in new window

There is FOREIGN KEY Relationship set between table "aspnet.Users" and "equipment_master", which would not allow you yo make entry in table "equipment_master" with userId that does not exist in table "aspnet.Users".

Can you debug and see what value you receive "UserId" after this line
 Dim UserId As Guid = currentUserId in your code, if it is valid GUID confirm in users table it that GUID does exist.

Yes, I have set up that FK relationship between the tables, and yes, UserID is a valid Guid, and yes, it is in the user's table, the first time through.  I have traced the code in the debugger.  It does the insert, it actually passes through the sub with the success msg in the iteminserted event handler (without posting the message on screen), and then goes back through the iteminserting handler and tries to do it again.  I think! that the UserId value is wiped out after the first pass, so the blank Guid in memory doesn't match the one in the FK aspnet_users table.

The success message doesn't show on screen.  I have another webform with a details view, but for updating not inserting. In this latter form, the success message does happen (as part of itemupdated event).  I have tried to match the structure of these two files, w/o success. The update one works perfectly, the posted code craps out.

For a while I was struggling with duplicate records, often discussed online as coming from browser refresh.  Fixed that. How do I nudge the code out of iteminserting and into iteminserted, and get to the success message?

move these lines of  code

Dim currentUser As MembershipUser = Membership.GetUser()
    Dim currentUserId As Guid = DirectCast(currentUser.ProviderUserKey, Guid)
    Dim UserId As Guid = currentUserId

TO  DetailsView1_ItemInserting event  and check

Please make sure the useid which you are using is exists in the user table,

Moved the declarations to inside the event handler.
Checked that there is a valid userID in the aspnet_users table.
Renamed the FK field in equipment_masters to EMUsersID, to get some clarity of names. Reloaded the data context.
Tried removing, and then restoring the InsertParameters for the LDS in the aspx file, on the theory that I had it covered in the event handler in the code-behind file.

Still failing. Record inserts in the db. Fails right after passing through the iteminserted handler, but the success message doesn't show on screen.  Err msg is
Failed to set one or more properties on type equipment_master.  Cannot convert value of parameter 'EMUserId' from 'System.String' to 'System.Guid'.

Checked during debug that I actually had a valid Guid in currentUserID to load into EMUserID.  Don't know where the string is that the system can't convert. Maybe the Boundfield EMUserID in the detailsview?


Here are the files as they now exist:

Imports System.Web.Security
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.DynamicData
Imports System.Web.Routing
Imports System.Web.UI.WebControls.Expressions

Partial Class SellerPages_RecordInsertTest
    Inherits System.Web.UI.Page

    Protected Sub DetailsView1_ItemInserting(sender As Object, e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
        Dim currentUser As MembershipUser = Membership.GetUser()
        Dim currentUserId As Guid = DirectCast(currentUser.ProviderUserKey, Guid)
        Dim EMUserId As Guid = currentUserId
        Dim CurrentDate As DateTime = Now
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("lucidequipmentConnectionString").ConnectionString
        Dim insertSql As String = "INSERT INTO equipment_master(makersId,equipmentTypeId,model, serial_num, mfg_year,asking_price,statusId, last_pm_date, conditionsId,registration_date, EMuserId) VALUES(@makersId,@equipmentTypeId, @model, @serial_num, @mfg_year, @asking_price, @statusId, @last_pm_date, @conditionsId, @registration_date, @EMUserId )"

        Using myConnection As New SqlConnection(connectionString)
            myConnection.Open()

            Dim myCommand As New SqlCommand(insertSql, myConnection)

            myCommand.Parameters.AddWithValue("@EMUserId", currentUserId)
            myCommand.Parameters.AddWithValue("@registration_date", CurrentDate)
            myCommand.Parameters.Add("@makersId", SqlDbType.Int).Value = e.Values("makersId")
            myCommand.Parameters.Add("@equipmentTypeId", SqlDbType.Int).Value = e.Values("equipmentTypeId")
            myCommand.Parameters.Add("@model", SqlDbType.NVarChar).Value = e.Values("model")
            myCommand.Parameters.Add("@serial_num", SqlDbType.NVarChar).Value = e.Values("serial_num")
            myCommand.Parameters.Add("@mfg_year", SqlDbType.NVarChar).Value = e.Values("mfg_year")

            myCommand.Parameters.Add("@asking_price", SqlDbType.NVarChar).Value = e.Values("asking_price")
            myCommand.Parameters.Add("@statusId", SqlDbType.Int).Value = e.Values("statusId")
            myCommand.Parameters.Add("@last_pm_date", SqlDbType.SmallDateTime).Value = e.Values("last_pm_date")
            myCommand.Parameters.Add("@conditionsId", SqlDbType.Int).Value = e.Values("conditionsId")

            myCommand.ExecuteNonQuery()

            myConnection.Close()
        End Using
    End Sub
    Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
        ListingCreatedMessage.Visible = True
    End Sub
End Class

Select allOpen in new window

<%@ Page Title="Add new listing" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="RecordInsertTest.aspx.vb" Inherits="SellerPages_RecordInsertTest" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
   <h1>Create a new listing</h1>
    <asp:DetailsView ID="DetailsView1"  AutoGenerateRows="False" 
        DataKeyNames="id" DataSourceID="LinqDataSource1" Height="50px" 
        Width="165px" DefaultMode="Insert"  runat="server">
         <Fields>
             <asp:DynamicField DataField="equipmentType" HeaderText="equipmentType" />
             <asp:DynamicField DataField="maker" HeaderText="maker" />
             <asp:BoundField DataField="model" HeaderText="model" />
             <asp:BoundField DataField="serial_num" HeaderText="serial_num" />
             <asp:BoundField DataField="mfg_year" HeaderText="mfg_year" />
             <asp:BoundField DataField="last_pm_date" HeaderText="last_pm_date" />
             <asp:BoundField DataField="asking_price" HeaderText="asking_price" />
             <asp:DynamicField DataField="status" HeaderText="status" />
             <asp:DynamicField DataField="condition" HeaderText="condition" />
             <asp:BoundField DataField="EMUserId" HeaderText="UserId" Visible="False" />
             <asp:CommandField ShowInsertButton="True" />
         </Fields>
    </asp:DetailsView>
    <asp:LinqDataSource ID="LinqDataSource1"  
        ContextTypeName="lucidequipmentDataContext" EnableInsert="True" 
        EntityTypeName="" TableName="equipment_masters" runat="server">
       <InsertParameters>
            <asp:Parameter name="EMUserId" DefaultValue=currentUserId  />
            <asp:Parameter Name="makersId" DefaultValue="@makersId"  />
            <asp:Parameter Name="equipmentTypeId" DefaultValue="@equipmentTypeId" />
            <asp:Parameter Name="model" DefaultValue="@model" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="serial_num" DefaultValue="@serial_num" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="mfg_year" DefaultValue="@mfg_year" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="last_pm_date" DefaultValue="@last_pm_date" Type="String" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="conditionsId" DefaultValue="@conditions.Id" ConvertEmptyStringToNull=true />
            <asp:Parameter Name="asking_price" DefaultValue="@asking_price" />
            <asp:Parameter Name="registration_date" DefaultValue=currentdate ConvertEmptyStringToNull=true />
            <asp:Parameter Name="statusId" DefaultValue="@statusId" ConvertEmptyStringToNull=true />
        </InsertParameters>

    </asp:LinqDataSource>
     <asp:Label ID="ListingCreatedMessage" runat="server" 
          Text="Your listing has been successfully created" EnableViewState="False" 
          Visible="False" AssociatedControlID="EquipmentMasterFields">
  </asp:Label>
</asp:Content>

Select allOpen in new window

Here's the stack trace

[LinqDataSourceValidationException: Failed to set one or more properties on type equipment_master.  Cannot convert value of parameter 'EMUserId' from 'System.String' to 'System.Guid'.]
   System.Web.UI.WebControls.LinqDataSourceView.HandleValidationErrors(IDictionary`2 errors, DataSourceOperation operation) +361286
   System.Web.UI.WebControls.QueryableDataSourceView.ExecuteInsert(IDictionary values) +91
   System.Web.UI.WebControls.ContextDataSourceView.ExecuteInsert(IDictionary values) +94
   System.Web.UI.WebControls.LinqDataSourceView.ExecuteInsert(IDictionary values) +29
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +89
   System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +379
   System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +574
   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) +112
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +125
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +169
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +9
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

Select allOpen in new window

I examine the MS SqlServer db tables after each debug run.  There is a valid Guid that correctly corresponds to the logged-in user in the EMUserID field in the newly added record.  At least once through the code, that is being done correctly.

I continue to think the iteminserting handler executes twice, losing values the second time, but have no evidence in the debug trace to support that.  But remember, I'm a novice. Where is EMUserID a string?

I removed the input parameters from the aspx file for EMUserId and registration_date, the lines causing the failure to convert strings. The program failed with a different msg,

The INSERT statement conflicted with the FOREIGN KEY constraint "equipmentUsers". The conflict occurred in database "lucidequipment", table "dbo.aspnet_Users", column 'UserId'.
The statement has been terminated.

Is a Where clause needed in the handler? or the LDS?

Never got a useful comment