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)
)
tblUsers
the specifiedUserID
. You need to insert that row before inserting thetblUserRoles
row. If thetblUsers
row should already exist, make sure thestoredUserID
value is correct.storedUserID
andstoredRoleID
defined in your VB code? You need to make sure the values exist intblUsers
andtblRoles
. Also please read this in full.