INSERT statement conflicted with COLUMN FOREIGN KEY

Discussion in 'ASP .Net' started by =?Utf-8?B?VGltOjouLg==?=, Dec 21, 2004.

  1. Can someone please tell me why I keep getting the following error from the
    code below!

    Error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint
    'FK_tblOfficePageContent_tblPageContent'. The conflict occurred in database
    'CPNCMS', table 'tblPageContent', column 'pageID'. The statement has been
    terminated.

    I cant seem to see why I keep getting an error when I do the insert!



    ''' CODE

    Private _NewPageID As Long

    Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    DataGridCommandEventArgs)
    If e.CommandName = "Insert" Then
    Dim modDate As String
    Dim dtNow As DateTime = DateTime.Now
    Dim description As String
    Dim txtdescription As TextBox
    Dim title As String
    Dim txtTitle As TextBox
    Dim PageID As Integer
    Me._NewPageID = PageID


    Dim strSQL As String
    modDate = dtNow.Date
    'Read in the values of the TextBoxes


    txtdescription = e.Item.FindControl("add_description")
    description = txtdescription.Text
    txtTitle = e.Item.FindControl("add_Title")
    title = txtTitle.Text


    'Create the appropriate SQL statement
    Dim Myconn As New
    SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    Dim cmd As New SqlCommand("PageAdd", Myconn)
    cmd.CommandType = CommandType.StoredProcedure

    Myconn.Open()

    ' Adds Information from the insert area within the datagrid into
    tblPageContent
    ' and returns the record ID
    Dim objModDate, objDescription, objTitle, objPageID, objOffice
    As SqlParameter
    objModDate = cmd.Parameters.Add("@modDate", SqlDbType.DateTime)
    objDescription = cmd.Parameters.Add("@description",
    SqlDbType.NVarChar)
    objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)

    objModDate.Direction = ParameterDirection.Input
    objDescription.Direction = ParameterDirection.Input
    objTitle.Direction = ParameterDirection.Input
    cmd.Parameters("@PageID").Direction = ParameterDirection.Output

    objModDate.Value = modDate
    objDescription.Value = description
    objTitle.Value = title


    Dim myReader As SqlDataReader = cmd.ExecuteReader()

    myReader.Read()
    myReader.Close()
    PageID = cmd.Parameters("@PageID").Value
    Myconn.Close()
    DoInsert(_NewPageID, LookupOfficeID())

    'Rebind the DataGrid
    DGPages.EditItemIndex = -1
    BindData()

    End If

    End Sub

    Public Sub DoInsert(ByVal _NewPageID As Long, ByVal OfficeId As Long)

    'open a connection and fire the insert sql here
    Dim Myconn As New
    SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    Dim cmd As New SqlCommand("OfficePageAdd", Myconn)
    cmd.CommandType = CommandType.StoredProcedure

    Myconn.Open()

    ' Adds the OfficeID and PageID into tblOfficePageContent
    Dim objOfficeID, objPageID As SqlParameter
    objOfficeID = cmd.Parameters.Add("@officeID", SqlDbType.NVarChar)
    objPageID = cmd.Parameters.Add("@PageID", SqlDbType.NVarChar)

    objOfficeID.Direction = ParameterDirection.Input
    objPageID.Direction = ParameterDirection.Input

    objOfficeID.Value = OfficeId
    objPageID.Value = _NewPageID

    Dim myReader As SqlDataReader = cmd.ExecuteReader()
    myReader.Read()

    myReader.Close()

    Myconn.Close()

    End Sub
    =?Utf-8?B?VGltOjouLg==?=, Dec 21, 2004
    #1
    1. Advertising

  2. =?Utf-8?B?VGltOjouLg==?=

    Marina Guest

    Looks like you have a foreign key on a column, but you are trying to put a
    value in it that does not exist in the corersponding table to which you
    defined the foreign key. The same thing would happen if you tried to run the
    insert in query analyzer. Make sure you are entering valid data, then try
    again.

    "Tim::.." <myatix_at_hotmail.com> wrote in message
    news:...
    > Can someone please tell me why I keep getting the following error from the
    > code below!
    >
    > Error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint
    > 'FK_tblOfficePageContent_tblPageContent'. The conflict occurred in

    database
    > 'CPNCMS', table 'tblPageContent', column 'pageID'. The statement has been
    > terminated.
    >
    > I cant seem to see why I keep getting an error when I do the insert!
    >
    >
    >
    > ''' CODE
    >
    > Private _NewPageID As Long
    >
    > Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    > DataGridCommandEventArgs)
    > If e.CommandName = "Insert" Then
    > Dim modDate As String
    > Dim dtNow As DateTime = DateTime.Now
    > Dim description As String
    > Dim txtdescription As TextBox
    > Dim title As String
    > Dim txtTitle As TextBox
    > Dim PageID As Integer
    > Me._NewPageID = PageID
    >
    >
    > Dim strSQL As String
    > modDate = dtNow.Date
    > 'Read in the values of the TextBoxes
    >
    >
    > txtdescription = e.Item.FindControl("add_description")
    > description = txtdescription.Text
    > txtTitle = e.Item.FindControl("add_Title")
    > title = txtTitle.Text
    >
    >
    > 'Create the appropriate SQL statement
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("PageAdd", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    > ' Adds Information from the insert area within the datagrid

    into
    > tblPageContent
    > ' and returns the record ID
    > Dim objModDate, objDescription, objTitle, objPageID, objOffice
    > As SqlParameter
    > objModDate = cmd.Parameters.Add("@modDate",

    SqlDbType.DateTime)
    > objDescription = cmd.Parameters.Add("@description",
    > SqlDbType.NVarChar)
    > objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)
    >
    > objModDate.Direction = ParameterDirection.Input
    > objDescription.Direction = ParameterDirection.Input
    > objTitle.Direction = ParameterDirection.Input
    > cmd.Parameters("@PageID").Direction =

    ParameterDirection.Output
    >
    > objModDate.Value = modDate
    > objDescription.Value = description
    > objTitle.Value = title
    >
    >
    > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    >
    > myReader.Read()
    > myReader.Close()
    > PageID = cmd.Parameters("@PageID").Value
    > Myconn.Close()
    > DoInsert(_NewPageID, LookupOfficeID())
    >
    > 'Rebind the DataGrid
    > DGPages.EditItemIndex = -1
    > BindData()
    >
    > End If
    >
    > End Sub
    >
    > Public Sub DoInsert(ByVal _NewPageID As Long, ByVal OfficeId As Long)
    >
    > 'open a connection and fire the insert sql here
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("OfficePageAdd", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    > ' Adds the OfficeID and PageID into tblOfficePageContent
    > Dim objOfficeID, objPageID As SqlParameter
    > objOfficeID = cmd.Parameters.Add("@officeID", SqlDbType.NVarChar)
    > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.NVarChar)
    >
    > objOfficeID.Direction = ParameterDirection.Input
    > objPageID.Direction = ParameterDirection.Input
    >
    > objOfficeID.Value = OfficeId
    > objPageID.Value = _NewPageID
    >
    > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    > myReader.Read()
    >
    > myReader.Close()
    >
    > Myconn.Close()
    >
    > End Sub
    >
    >
    Marina, Dec 21, 2004
    #2
    1. Advertising

  3. =?Utf-8?B?VGltOjouLg==?=

    Karl Seguin Guest

    The error message is telling you that you are trying to add a row in
    violation of some constraint (a foreign key). In other words,
    tblPageContent has a column named PageId which is a foreign key to another
    table. To insert a PageId, the value must exist in this other table...and
    it obvioulsy doesn't.

    Karl

    --
    MY ASP.Net tutorials
    http://www.openmymind.net/


    "Tim::.." <myatix_at_hotmail.com> wrote in message
    news:...
    > Can someone please tell me why I keep getting the following error from the
    > code below!
    >
    > Error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint
    > 'FK_tblOfficePageContent_tblPageContent'. The conflict occurred in

    database
    > 'CPNCMS', table 'tblPageContent', column 'pageID'. The statement has been
    > terminated.
    >
    > I cant seem to see why I keep getting an error when I do the insert!
    >
    >
    >
    > ''' CODE
    >
    > Private _NewPageID As Long
    >
    > Public Sub DGPages_Insert(ByVal sender As Object, ByVal e As
    > DataGridCommandEventArgs)
    > If e.CommandName = "Insert" Then
    > Dim modDate As String
    > Dim dtNow As DateTime = DateTime.Now
    > Dim description As String
    > Dim txtdescription As TextBox
    > Dim title As String
    > Dim txtTitle As TextBox
    > Dim PageID As Integer
    > Me._NewPageID = PageID
    >
    >
    > Dim strSQL As String
    > modDate = dtNow.Date
    > 'Read in the values of the TextBoxes
    >
    >
    > txtdescription = e.Item.FindControl("add_description")
    > description = txtdescription.Text
    > txtTitle = e.Item.FindControl("add_Title")
    > title = txtTitle.Text
    >
    >
    > 'Create the appropriate SQL statement
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("PageAdd", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    > ' Adds Information from the insert area within the datagrid

    into
    > tblPageContent
    > ' and returns the record ID
    > Dim objModDate, objDescription, objTitle, objPageID, objOffice
    > As SqlParameter
    > objModDate = cmd.Parameters.Add("@modDate",

    SqlDbType.DateTime)
    > objDescription = cmd.Parameters.Add("@description",
    > SqlDbType.NVarChar)
    > objTitle = cmd.Parameters.Add("@title", SqlDbType.NVarChar)
    > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.Int)
    >
    > objModDate.Direction = ParameterDirection.Input
    > objDescription.Direction = ParameterDirection.Input
    > objTitle.Direction = ParameterDirection.Input
    > cmd.Parameters("@PageID").Direction =

    ParameterDirection.Output
    >
    > objModDate.Value = modDate
    > objDescription.Value = description
    > objTitle.Value = title
    >
    >
    > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    >
    > myReader.Read()
    > myReader.Close()
    > PageID = cmd.Parameters("@PageID").Value
    > Myconn.Close()
    > DoInsert(_NewPageID, LookupOfficeID())
    >
    > 'Rebind the DataGrid
    > DGPages.EditItemIndex = -1
    > BindData()
    >
    > End If
    >
    > End Sub
    >
    > Public Sub DoInsert(ByVal _NewPageID As Long, ByVal OfficeId As Long)
    >
    > 'open a connection and fire the insert sql here
    > Dim Myconn As New
    > SqlConnection(ConfigurationSettings.AppSettings("strConn"))
    > Dim cmd As New SqlCommand("OfficePageAdd", Myconn)
    > cmd.CommandType = CommandType.StoredProcedure
    >
    > Myconn.Open()
    >
    > ' Adds the OfficeID and PageID into tblOfficePageContent
    > Dim objOfficeID, objPageID As SqlParameter
    > objOfficeID = cmd.Parameters.Add("@officeID", SqlDbType.NVarChar)
    > objPageID = cmd.Parameters.Add("@PageID", SqlDbType.NVarChar)
    >
    > objOfficeID.Direction = ParameterDirection.Input
    > objPageID.Direction = ParameterDirection.Input
    >
    > objOfficeID.Value = OfficeId
    > objPageID.Value = _NewPageID
    >
    > Dim myReader As SqlDataReader = cmd.ExecuteReader()
    > myReader.Read()
    >
    > myReader.Close()
    >
    > Myconn.Close()
    >
    > End Sub
    >
    >
    Karl Seguin, Dec 21, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. H5N1
    Replies:
    0
    Views:
    439
  2. Patrick Guio
    Replies:
    6
    Views:
    3,197
    chris
    Oct 20, 2004
  3. Lionel
    Replies:
    9
    Views:
    3,398
  4. J. Muenchbourg
    Replies:
    3
    Views:
    240
    Aaron Bertrand - MVP
    Sep 30, 2003
  5. John
    Replies:
    2
    Views:
    179
    Mike Brind
    Jul 8, 2006
Loading...

Share This Page