Hi everyone,
I have a form with one combo box and text box and buttons. please refer the snapshot with i have attached.

I have the following code:

My Stored Procedure for inserting the data:

/*
Name: usp_InsertBranchSetup
Description: Insert the Record to dbo.hrBranchSetup table
Author: Tashi
Modification: Insert

Description				Date			Changed By
Created Procedure		25/02/2011		Tashi
*/
CREATE PROCEDURE [dbo].[usp_InsertBranchSetup]
(
	@BranchCode CHAR(4),
	@OsID CHAR(4),
	@BranchName VARCHAR(75),
	@OnLineBrYN BIT,
	@SetDate DATETIME,
	@UserName VARCHAR(20),
	@CMD_Flag CHAR(1)
)
WITH ENCRYPTION
AS
INSERT INTO [dbo].[hrBranchSetup] VALUES
(
	@BranchCode,
	@OsID,
	@BranchName,
	@OnLineBrYN,
	@SetDate,
	@UserName
)
INSERT INTO [dbo].[hrBranchSetup_Audit] VALUES
(
	@BranchCode,
	@OsID,
	@BranchName,
	@OnLineBrYN,
	@CMD_Flag,
	@SetDate,
	@UserName
)
#Region "InsertCommand"
    Private Sub InsertCMD()
        Try
            Dim sDate As String = CType(DateAndTime.Now, String)
            Dim sIFlag As String = "C"
            Dim cmd As New OleDbCommand("usp_InsertBranchSetup", SQLConn)
            'ds.Clear() 'Clear Data Set
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Transaction = trns
            cmd.Parameters.Add("@BranchCode", OleDbType.Char).Value = brCode.Text
            cmd.Parameters.Add("@OsID", OleDbType.Char).Value = cboOrganization.ValueMember
            cmd.Parameters.Add("@BranchName", OleDbType.VarChar).Value = brName.Text
            cmd.Parameters.Add("@OnLineBrYN", OleDbType.BigInt).Value = bsOnlineYNCheckBox.CheckState
            cmd.Parameters.Add("@SetDate", OleDbType.Date).Value = sDate
            cmd.Parameters.Add("@UserName", OleDbType.VarChar).Value = UCase(frmMainPanel.ShowCurrentUserStatusLabel.Text)
            cmd.Parameters.Add("@CMD_Flag", OleDbType.Char).Value = sIFlag
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show("Error No : " & Err.Number & vbCrLf _
                            & "Error : " & ex.Message & vbCrLf _
                            & "Source : " & Err.Source & vbCrLf _
                            , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
#End Region

Form Load Code:

Dim cmd As New OleDbCommand("select * from dbo.hrorganizationsetup where IsDefault = 1", SQLConn)
            cmd.CommandType = CommandType.Text
            cmd.Transaction = trns
            cmd.Parameters.Add("@OsID", OleDbType.Char).Value = 0
            Dim oDa As New OleDbDataAdapter(cmd) 'SQL Data Adapter object
            oDa.Fill(ds, "dbo.hrorganizationsetup")
            With cboOrganization
                .DataSource = ds.Tables("dbo.hrorganizationsetup")
                .ValueMember = "OsID"
                .DisplayMember = "OsName"
            End With

            'cboOrganization.SelectedValue = intSelectedBranch

            'Remove parameters
            cmd.Parameters.Clear()

Save button code:

Try
                If bNewData Then ' TRY WITH 'OR' Operator
                    If vilBranch.BranchCodeExists(brCode.Text.Trim) Then
                        MessageBox.Show("Branch Code [" & brCode.Text.Trim & "] already exists. Please Check.", "Data Validation", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                        brCode.Focus()
                        Exit Sub
                    ElseIf vilBranch.BranchNameExists(brName.Text.Trim) Then
                        MessageBox.Show("Branch Name [" & brName.Text.Trim & "] already exists. Please Check.", "Data Validation", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                        brName.Focus()
                        Exit Sub
                    Else
                        btnbsSave.Focus()
                    End If
                    SQLConn.Open()
                    trns = SQLConn.BeginTransaction
                    InsertCMD()
                    trns.Commit()
                    SQLConn.Close()
                    GetData()
                    bNewData = False
                    bEditData = False
                    Count()
                    MessageBox.Show("Record Saved Successfully", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    EnableControlsLoadMode(True)
                ElseIf bEditData Then
                    If vilBranch.BranchNameExists(brName.Text.Trim) Then
                        MessageBox.Show("Branch Name [" & brName.Text.Trim & "] already exists. Please Check.", "Data Validation", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                        brName.Focus()
                        Exit Sub
                    Else
                        btnbsSave.Focus()
                    End If
                    SQLConn.Open()
                    trns = SQLConn.BeginTransaction
                    UpdateCMD()
                    trns.Commit()
                    SQLConn.Close()
                    GetData()
                    bNewData = False
                    bEditData = False
                    Count()
                    MessageBox.Show("Record Updated Successfully", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    EnableControlsLoadMode(True)
                End If
            Catch ex As Exception
                trns.Rollback()
                MessageBox.Show("Critical Error!" & ex.Message, "Critical Error.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

I can populate the combo box but When i click on SAVE button i get this error message:

Error No: 5
Error: The statement has been terminated.
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_hrBranchSetup_hrOrganizationSetup". The conflict occurred in
database "BDFCLHR", table "dbo.hrOrganizationSetup", column 'OsID'.

Source: Microsoft OLE DB Provider for SQL Server

When i debug this line under "Private Sub InsertCMD()"

cmd.Parameters.Add("@OsID", OleDbType.Char).Value = cboOrganization.ValueMember

i get value as "OsID" not the selected value.


PLEASE HELP ME, I AM STRUCK IN THIS AREA.


TasheeDuks

this error occurs only when we try to entered the foreign key which is not in its parent table ,sorry for my bad english. first try to look at your relations between hrBranchSetup and hrBranchSetup_Audit , hrBranchSetup has a PK OsID and hrBranchSetup_Audit has FK OsID .you should have PK in hrBranchSetup before you insert record in hrBranchSetup_Audit .
Hope this will helps you

Regards

Thanks for your help. Acutally 'OsID' is foreign key in hrBranchSetup table. I am adding my SQL script for creating table to make you understand about the table structure.

hrOgranizationSetup ---- OsID as PK
hrBranchSetup --- BranchCode as PK and OsID as FK
hrBranchSetup_Audit -- This table is created for auditing the transaction of hrBranchSetup table. Here AutoPrimaryKey is PK

Relationship: I have created relation between hrOrganization and hrBranchSetup (1:M). hrBranchSetup_Audit is just a copy of hrBranchSetup table.

I have added my SQL script to make you more understandable about my problem.

USE [BDFCLHR]
GO
/****** Object:  Table [dbo].[hrBranchSetup]    Script Date: 04/09/2010 15:57:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrBranchSetup](
	[BranchCode] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OsID] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BranchName] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OnLineBrYN] [bit] NOT NULL CONSTRAINT [DF_hrBranchSetup_OnLineBrYN]  DEFAULT ((0)),
	[SetDate] [datetime] NOT NULL CONSTRAINT [DF_hrBranchSetup_SetDate]  DEFAULT (getdate()),
	[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_hrBranchSetup] PRIMARY KEY NONCLUSTERED 
(
	[BranchCode] ASC,
	[OsID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Tasbl [dbo].[hrBranchSetup]	Script Date: 04/09/2010 15:55:36 ******/
CREATE TABLE [dbo].[hrBranchSetup_Audit](
	[AutoPrimaryKey] [bigint] IDENTITY(1,1) NOT NULL,
	[BranchCode] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OsID] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BranchName] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OnLineBrYN] [bit] NOT NULL CONSTRAINT [DF_hrBranchSetup_OnLineBrYN_1]  DEFAULT ((0)),
	[CMD_Flag] [char](1) NOT NULL,
	[SetDate] [datetime] NOT NULL CONSTRAINT [DF_hrBranchSetup_Audit_SetDate]  DEFAULT (getdate()),
	[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT [PK_CS_BranchSetup_Audit] PRIMARY KEY CLUSTERED 
(
	[AutoPrimaryKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE BDFCLHR
GO
ALTER TABLE [dbo].[hrBranchSetup]  WITH NOCHECK ADD
	CONSTRAINT [FK_hrBranchSetup_hrOrganizationSetup]
		FOREIGN KEY([OsID])
REFERENCES [dbo].[hrOrganizationSetup] ([OsID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[hrBranchSetup] CHECK CONSTRAINT [FK_hrBranchSetup_hrOrganizationSetup]
/*
select * from hrBranchSetup
select * from hrBranchSetup_Audit
select * from hrOrganizationSetup
*/

PLEASE HELP ME....

this error occurs only when we try to entered the foreign key which is not in its parent table ,sorry for my bad english. first try to look at your relations between hrBranchSetup and hrBranchSetup_Audit , hrBranchSetup has a PK OsID and hrBranchSetup_Audit has FK OsID .you should have PK in hrBranchSetup before you insert record in hrBranchSetup_Audit .
Hope this will helps you

Regards

Edited 4 Years Ago by tashee2007: n/a

hello !
i tried to use your given script but its not working.you should take a look in your table name hrOgranizationSetup , for example , if there is two records having OsID 100 and 101 , and you are trying to insert records in table name hrBranchSetup_Audit with Osid 102 then foreign key conflict error come .
Hope this time you can better understand .and sorry for my bad english

Regards
M.Waqas Aslam

Hi,

Please see this SQL Script for hrOrganizationSetup table.

USE [BDFCLHR]
GO
/****** Object:  Table [dbo].[hrOrganizationSetup]    Script Date: 04/09/2010 15:55:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrOrganizationSetup](
	[OsID] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OsName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OsShortName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IsDefault] [bit] NOT NULL CONSTRAINT [DF_hrOrganizationSetup_IsDefault]  DEFAULT ((0)),
	[SetDate] [datetime] NOT NULL CONSTRAINT [DF_hrOrganizationSetup_SetupDate]  DEFAULT (getdate()),
	[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_CS_OrganizationSetup] PRIMARY KEY CLUSTERED 
(
	[OsID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Tasbl [dbo].[hrOrganizationSetup_Audit]	Script Date: 04/09/2010 15:55:36 ******/
CREATE TABLE [dbo].[hrOrganizationSetup_Audit](
	[AutoPrimaryKey] [bigint] IDENTITY(1,1) NOT NULL,
	[OsID] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OsName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[OsShortName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[IsDefault] [bit] NOT NULL CONSTRAINT [DF_CS_hrOrganizationSetup_Audit_IsDefault]  DEFAULT ((0)),
	[CMD_Flag] [char](1) NOT NULL,
	[SetDate] [datetime] NOT NULL CONSTRAINT [DF_hrOrganizationSetup_Audit_SetupDate]  DEFAULT (getdate()),
	[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT [PK_CS_OrganizationSetup_Audit] PRIMARY KEY CLUSTERED 
(
	[AutoPrimaryKey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

This is the script which i have used to create a hrORganizationSetup table.

Hope this will make you more clear about my TABLES.

hello !
i tried to use your given script but its not working.you should take a look in your table name hrOgranizationSetup , for example , if there is two records having OsID 100 and 101 , and you are trying to insert records in table name hrBranchSetup_Audit with Osid 102 then foreign key conflict error come .
Hope this time you can better understand .and sorry for my bad english

Regards
M.Waqas Aslam

This article has been dead for over six months. Start a new discussion instead.