locked
The Insert Statement conflicted with the Foreign Key constraint FK_Landmarks_into_images RRS feed

  • Question

  • User-1352156089 posted

    Hi All,

    I am trying to polulate a listview with the below code. Everything is ok except for the fact that both that the below couple of lines of code get ignored (both labels that are outside the listview are not populated): could you tell me in your opinion what's wrong with the below code:

    landmarknamelbl.Text = myReader("LandmarkName").ToString()
    landmarklbl.Text = myReader("LandmarkId").ToString()

    Full code:

        Private Sub PopulateLandmarksDetails()
            Dim FriendlyLandmarkName As String = Convert.ToString(Page.RouteData.Values("FriendlyLandmarkName"))
            Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
                Using cmd As New SqlCommand("SELECT LandmarkId, LandmarkName, LandmarkDescription, Latitude, Longitude, InfoWindow FROM Landmarks WHERE FriendlyLandmarkName=@FriendlyLandmarkName", con)
                    cmd.Parameters.AddWithValue("@FriendlyLandmarkName", FriendlyLandmarkName)
                    Using adapter As New SqlDataAdapter(cmd)
                        Try
                            con.Open()
                            Dim dt As New DataTable()
                            adapter.Fill(dt)
                            Dim dv As DataView = dt.DefaultView
                            CountryDetails.DataSource = dv
                            ImagesbyLandmark.DataSource = dv
                            LandmarkListviewDetails.DataBind()
                            ImagesbyLandmark.DataBind()
                            Dim myReader As SqlDataReader = cmd.ExecuteReader()
                            While myReader.Read()
                                landmarknamelbl.Text = myReader("LandmarkName").ToString()
                                landmarklbl.Text = myReader("LandmarkId").ToString()
                            End While
                            myReader.Close()
                        Catch
                        End Try
                    End Using
                End Using
            End Using
        End Sub

    I need to reference the LandmarkId to insert this into my Images table and I am not sure if with the above method I am parsing in the right way the LandmarkId.

    My insert statement is:

    (cut code for brevity)
    
     Dim strQuery As String = "INSERT INTO Images (LandmarkId, Name, ContentType, ImageContent) VALUES (@LandmarkId, @Name, @ContentType, @ImageContent)"
                Dim cmd As New SqlCommand(strQuery)
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
                cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contenttype
                cmd.Parameters.Add("@ImageContent", SqlDbType.Binary).Value = bytes
                cmd.Parameters.AddWithValue("LandmarkId", landmarklbl.Text)
    
    (cut code for brevity)

    Thank you very much for looking into this.

    Tuesday, May 26, 2015 10:50 AM

Answers

  • User177399542 posted

    However, after adding the landmarklbl.Text, myReader doesn't read none of the 2 values. I actually need to add the LandmarkId into my Images where LandmarkId is the foreign key in the Images table.

    This means you are not getting any value for LandmarkId. Kind debug step by step and check if you are getting any value in LandMarkId field. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 27, 2015 3:37 AM

All replies

  • User177399542 posted

    The error states that the value you are trying to insert into "LandmarkId" column does not exists in landmark table. 

    Kindly check if you are getting values from your selected statement by debugging. Do some code alterations like:

    If myReader.HasRows Then
    While myReader.Read()
                                landmarknamelbl.Text = myReader("LandmarkName").ToString()
                                landmarklbl.Text = myReader("LandmarkId").ToString()
                            End While
                            myReader.Close()
    End If
    

    Wednesday, May 27, 2015 1:18 AM
  • User-1352156089 posted

    Hi Anuy,

    thank you for your feedback.

    It's really strange. If I limit my reader to read the landmarknamelbl.Text only, the reader reads the value. However, after adding the landmarklbl.Text, myReader doesn't read none of the 2 values. I actually need to add the LandmarkId into my Images where LandmarkId is the foreign key in the Images table.

    So still getting:

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Landmarks_into_Images". The conflict occurred in database "HPLdata", table "dbo.Landmarks", column 'LandmarkId'. The statement has been terminated.

    When debugging, no error is reported but the above.

    What could be the cause?

    For your reference, this is my tables structure:

    Landmarks:

    USE [HPLdata]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Landmarks](
    	[LandmarkId] [int] IDENTITY(1,1) NOT NULL,
    	[CityId] [int] NOT NULL,
    	[LandmarkName] [nvarchar](50) NOT NULL,
    	[FriendlyLandmarkName]  AS (lower(replace([LandmarkName],' ','-'))),
    	[Latitude] [float] NULL,
    	[Longitude] [float] NULL,
    	[GeoLoc]  AS ([geography]::STPointFromText(((('POINT('+CONVERT([varchar](20),[Longitude]))+' ')+CONVERT([varchar](20),[Latitude]))+')',(4326))),
    	[InfoWindow] [nvarchar](300) NULL,
    	[LandmarkDescription] [nvarchar](max) NULL,
    	[LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_Landmarks_LastUpdate]  DEFAULT (getdate()),
     CONSTRAINT [PK_Landmarks] PRIMARY KEY CLUSTERED 
    (
    	[LandmarkId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Landmarks]  WITH CHECK ADD  CONSTRAINT [FK_Cities_into_Landmarks] FOREIGN KEY([CityId])
    REFERENCES [dbo].[Cities] ([CityId])
    GO
    
    ALTER TABLE [dbo].[Landmarks] CHECK CONSTRAINT [FK_Cities_into_Landmarks]
    GO

    Images:

    USE [HPLdata]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Images](
    	[ImageId] [int] IDENTITY(1,1) NOT NULL,
    	[LandmarkId] [int] NOT NULL,
    	[ImageName] [nvarchar](100) NULL,
    	[FriendlyImageName]  AS (lower(replace([ImageName],' ','-'))),
    	[ImageContent] [varbinary](max) NULL,
    	[Name] [varchar](50) NULL,
    	[ContentType] [varchar](50) NULL,
    	[AltAttribute] [nvarchar](50) NULL,
    	[CourtesyOf] [nvarchar](50) NULL,
    	[LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_Images_LastUpdate]  DEFAULT (getdate()),
     CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED 
    (
    	[ImageId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[Images]  WITH CHECK ADD  CONSTRAINT [FK_Landmarks_into_Images] FOREIGN KEY([LandmarkId])
    REFERENCES [dbo].[Landmarks] ([LandmarkId])
    GO
    
    ALTER TABLE [dbo].[Images] CHECK CONSTRAINT [FK_Landmarks_into_Images]
    GO

    Thank you

    Wednesday, May 27, 2015 3:29 AM
  • User177399542 posted

    However, after adding the landmarklbl.Text, myReader doesn't read none of the 2 values. I actually need to add the LandmarkId into my Images where LandmarkId is the foreign key in the Images table.

    This means you are not getting any value for LandmarkId. Kind debug step by step and check if you are getting any value in LandMarkId field. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 27, 2015 3:37 AM
  • User-1352156089 posted

    Thanks a lot,

    I had to use a different select statement to populate my listview:

      Private Sub PopulateImageDetails()
            Dim FriendlyLandmarkName As String = Convert.ToString(Page.RouteData.Values("FriendlyLandmarkName"))
            Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString)
                Using cmd As New SqlCommand("SELECT Images.ImageId, Images.ImageContent, Images.ImageName, Images.AltAttribute, Landmarks.LandmarkId FROM Images INNER JOIN Landmarks ON Landmarks.LandmarkId=Images.LandmarkId WHERE Landmarks.FriendlyLandmarkName = '" + FriendlyLandmarkName + "'", con)
                    cmd.Parameters.AddWithValue("@FriendlyLandmarkName", FriendlyLandmarkName)
                    Using adapter As New SqlDataAdapter(cmd)
                        Try
                            con.Open()
                            Dim dt As New DataTable()
                            adapter.Fill(dt)
                            Dim dv As DataView = dt.DefaultView
                            ImagesbyLandmark.DataSource = dv
                            ImagesbyLandmark.DataBind()
                        Catch Err As SqlException
                            Using ErrorFile = File.AppendText(System.Web.HttpContext.Current.Request.MapPath("SiteError.txt"))
                                ErrorFile.WriteLine(DateTime.Now.ToString() + " : " + Err.Message)
                            End Using
                        End Try
                    End Using
                End Using
            End Using
        End Sub

    Thanks

    Wednesday, May 27, 2015 10:12 AM