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

 

[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