Insert Statement Conflict Column Check Constraint

[macupryk]

This is my insert sub routine.

 

Public Sub DoAddnew()

Dim bm As BindingManagerBase = Me.DataGrid1.BindingContext(Me.DataGrid1.DataSource, Me.DataGrid1.DataMember)

Dim dr As DataRow = CType(bm.Current, DataRowView).Row

Dim addform As New AddTransOverride(dr)

Dim retval As DialogResult = addform.ShowDialog()

If retval = DialogResult.OK Then

bm.EndCurrentEdit()

Try

ExecOnTransOverride.ins(dr.Item(1), dr.Item(2), dr.Item(3), dr.Item(4), dr.Item(5), dr.Item(6), dr.Item(8), dr.Item(7), DateTime.Now, dr.Item(7), dr.Item(9))

SqlDataAdapter1.Update(ds, "DsTransOverride1")

ds.Tables("DsTransOverride1").AcceptChanges()

MsgBox("Data Inserted Successfully !", MsgBoxStyle.Information, Me.Text)

Catch se As SqlException

MessageBox.Show(se.Message)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Else

bm.CancelCurrentEdit()

End If

 

End Sub

 

 

Public Shared Function ins( _

ByVal strPolicy_Nbr As Object, _

ByVal strTrans_CodeOrig As Object, _

ByVal dteTrans_Eff_Date As Object, _

ByVal varOverride_Code As Object, _

ByVal varNR_CodeOvr As Object, _

ByVal varTrans_CodeOvr As Object, _

ByVal dteCreatedDate As Object, _

ByVal strCreatedUID As Object, _

ByVal dteModifiedDate As Object, _

ByVal varModifiedUID As Object, _

ByVal lngPlan_Year As Object _

) As Integer

 

' construct new connection And command objects

Dim conn As SqlConnection = GetConn()

Dim cmd As SqlCommand = GetSprocCmd("prc_TransOverride_ins", conn)

 

Dim param As SqlParameter

 

' Add return value param

param = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

param.Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add(param)

 

' Add params

' parameter for Policy_Nbr column

param = New SqlParameter("@Policy_Nbr", System.Data.SqlDbType.VarChar, 7)

param.Direction = ParameterDirection.Input

param.Value = strPolicy_Nbr

cmd.Parameters.Add(param)

' parameter for Trans_CodeOrig column

param = New SqlParameter("@Trans_CodeOrig", System.Data.SqlDbType.VarChar, 6)

param.Direction = ParameterDirection.Input

param.Value = strTrans_CodeOrig

cmd.Parameters.Add(param)

' parameter for Trans_Eff_Date column

param = New SqlParameter("@Trans_Eff_Date", System.Data.SqlDbType.DateTime, 8)

param.Direction = ParameterDirection.Input

param.Value = dteTrans_Eff_Date

cmd.Parameters.Add(param)

' parameter for Override_Code column

param = New SqlParameter("@Override_Code", System.Data.SqlDbType.VarChar, 2)

param.Direction = ParameterDirection.Input

param.Value = varOverride_Code

cmd.Parameters.Add(param)

' parameter for NR_CodeOvr column

param = New SqlParameter("@NR_CodeOvr", System.Data.SqlDbType.VarChar, 1)

param.Direction = ParameterDirection.Input

param.Value = varNR_CodeOvr

cmd.Parameters.Add(param)

' parameter for Trans_CodeOvr column

param = New SqlParameter("@Trans_CodeOvr", System.Data.SqlDbType.VarChar, 6)

param.Direction = ParameterDirection.Input

param.Value = varTrans_CodeOvr

cmd.Parameters.Add(param)

' parameter for CreatedDate column

param = New SqlParameter("@CreatedDate", System.Data.SqlDbType.DateTime, 8)

param.Direction = ParameterDirection.Input

param.Value = dteCreatedDate

cmd.Parameters.Add(param)

' parameter for CreatedUID column

param = New SqlParameter("@CreatedUID", System.Data.SqlDbType.NVarChar, 48)

param.Direction = ParameterDirection.Input

param.Value = strCreatedUID

cmd.Parameters.Add(param)

' parameter for ModifiedDate column

param = New SqlParameter("@ModifiedDate", System.Data.SqlDbType.DateTime, 8)

param.Direction = ParameterDirection.Input

param.Value = dteModifiedDate

cmd.Parameters.Add(param)

' parameter for ModifiedUID column

param = New SqlParameter("@ModifiedUID", System.Data.SqlDbType.NVarChar, 48)

param.Direction = ParameterDirection.Input

param.Value = varModifiedUID

cmd.Parameters.Add(param)

' parameter for Plan_Year column

param = New SqlParameter("@Plan_Year", System.Data.SqlDbType.Int, 4)

param.Direction = ParameterDirection.Input

param.Value = lngPlan_Year

cmd.Parameters.Add(param)

 

' open connection

conn.Open()

' Execute command

cmd.ExecuteNonQuery()

' get return value

Dim result As Integer = GetSProcReturnValue(cmd)

' close connection

conn.Close()

 

Return result

 

End Function

 

 

 

Can someone help me out with this

 

 

Thanks,

Matt

Edited September 22, 2005 by PlausiblyDamp

[macupryk]

Pass a two character

 

How do I pass a two character for dr.Item(4) in the following:

ExecOnTransOverride.ins(dr.Item(1), dr.Item(2), dr.Item(3), dr.Item(4), dr.Item(5), dr.Item(6), dr.Item(8), dr.Item(7), DateTime.Now, dr.Item(7), dr.Item(9))

 

 

+ dr.Item(1) "1234555" {String} Object

+ dr.Item(2) "CANADJ" {String} Object

+ dr.Item(3) #9/22/2005# {Date} Object

+ dr.Item(4) "ACCOUNT" {String} Object

+ dr.Item(5) "N" {String} Object

+ dr.Item(6) "COMMCH" {String} Object

+ dr.Item(7) "za\mmmmmm" {String} Object

+ dr.Item(8) #8/28/2005 10:58:13 PM# {Date} Object

+ dr.Item(9) 2222 {Integer} Object

 

I am getting this error

http://www.vbcity.com/forums/topic.asp?tid=111180

Insert Statement Conflict Column Check Constraint

 

 

CREATE TABLE [TransOverride] (

[Policy_Nbr] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Trans_CodeOrig] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Trans_Eff_Date] [datetime] NOT NULL ,

[Override_Code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[NR_CodeOvr] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Trans_CodeOvr] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF__TransOver__Creat__70A8B9AE] DEFAULT (getdate()),

[CreatedUID] [nvarchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF__TransOver__Modif__719CDDE7] DEFAULT (getdate()),

[ModifiedUID] [nvarchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Plan_Year] [int] NOT NULL CONSTRAINT [DF__TransOver__Plan___56757D0D] DEFAULT (2004),

CONSTRAINT [cnTransOverride_PK] PRIMARY KEY CLUSTERED

(

[Policy_Nbr],

[Trans_CodeOrig],

[Trans_Eff_Date]

) ON [PRIMARY] ,

CONSTRAINT [cnTransOverride_NR_CodeOvr_CK] CHECK ([NR_CodeOvr] = '' or ([NR_CodeOvr] = 'R' or [NR_CodeOvr] = 'N')),

CONSTRAINT [cnTransOverride_Override_Code_CK] CHECK ([Override_Code] = '' or ([Override_Code] = 'OT' or ([Override_Code] = 'AU' or ([Override_Code] = 'MS' or ([Override_Code] = 'EN' or ([Override_Code] = 'AO' or [Override_Code] = 'AC'))))))

) ON [PRIMARY]

GO

Edited September 22, 2005 by PlausiblyDamp