1

I am wanting to add insert into my uspInsertUserRoles procedure:

CREATE PROCEDURE [dbo].[uspInsertUserRoles]
    @RoleID INT,
    @UserID INT
    AS
    BEGIN
        INSERT INTO tblUserRoles(RoleID, UserID)
        VALUES (@RoleID,@ UserID)
    END
GO

Table:

CREATE TABLE [dbo].[tblUserRoles](
    [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [RoleID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [UserRoleID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblUserRoles]  WITH CHECK ADD FOREIGN KEY([RoleID])
REFERENCES [dbo].[tblRoles] ([RoleID])
GO

ALTER TABLE [dbo].[tblUserRoles]  WITH CHECK ADD FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUsers] ([UserID])
GO

This is my code:

Private Sub btnInsertUserRole_Click(sender As Object, e As EventArgs) Handles btnInsertUserRole.Click
        sqlCon = New SqlConnection(strConn)

        Using (sqlCon)
            Dim sqlComm As New SqlCommand()
            Dim msg As String = "EXEC sp_addrolemember '" & storedRoleDesc & "','" & storedUserName & "'"
            MessageBox.Show(msg)
            sqlComm.Connection = sqlCon
            sqlComm.CommandText = "uspInsertUserRoles"
            sqlComm.CommandType = CommandType.StoredProcedure

            sqlComm.Parameters.AddWithValue("UserID", storedUserID)
            sqlComm.Parameters.AddWithValue("RoleID", storedRoleID)

            sqlCon.Open()
            'sqlComm.ExecuteNonQuery()

            Dim rowsAffected As Integer = sqlComm.ExecuteNonQuery()
            MessageBox.Show(rowsAffected)
            If rowsAffected > 0 Then
                MessageBox.Show("Record has been inserted successfully")
            End If

Gives me error:

System.Data.SqlClient.SqlException: 'The INSERT statement conflicted with the FOREIGN KEY constraint "FK__tblUserRo__UserI__51300E55". The conflict occurred in database "THISONE", table "dbo.tblUsers", column 'UserID'. The statement has been terminated.'

My tblUsers:

CREATE TABLE tblUsers(
        UserID INT IDENTITY(1,1),
        FirstName varchar(50) NOT NULL,
        LastName varchar(50) NOT NULL,
        Age int NOT NULL,
        UserName varchar(50) NOT NULL,
        Password varchar(50) NOT NULL,
        PRIMARY KEY (UserID),
        UNIQUE(UserName)
        )
4
  • 1
    The error means that no row exists in table tblUsers the specified UserID. You need to insert that row before inserting the tblUserRoles row. If the tblUsers row should already exist, make sure the storedUserID value is correct.
    – Dan Guzman
    Commented Nov 18, 2018 at 0:27
  • Where are storedUserID and storedRoleID defined in your VB code? You need to make sure the values exist in tblUsers and tblRoles. Also please read this in full. Commented Nov 18, 2018 at 0:55
  • thanks for that. storedUserID = selectedUserID storedUserName = selectedUserName
    – user165203
    Commented Nov 18, 2018 at 2:26
  • That still doesn't explain where they are defined or where the values come from. Did you try putting them into a message box before executing the query? Were they valid values that exist in their respective tables? You need to be able to do some of this basic debugging yourself because it's very hard for us to do it for you... Commented Nov 18, 2018 at 4:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.