Long ado net question but urgent help required

• • Reply

  15 years ago

  by mcupryk

  Mathieu

  Montreal, Canada

  Joined 16 years ago

  I have an sql table that consist of three key constraints
  PolicyNbr, TransCodeOrig, TransEffDate. Now I need to create a stored procedure that will handle
  violations on the table. What I mean here is that the old values(three key constraints) when I do an update on the table should
  be included in the stored procedure. I must add them in. Here is the stored procedure I am working on.
  Now I am need to include three parameters.
  ExecOnTransOverride.upd(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),
  oldPolicyNumber, oldTransCode, oldTransEffDate)
  

  ---

  
  

  
  CREATE PROCEDURE dbo.prcTransOverrideupd
  (
  @PolicyNbr varchar(7),
  @TransCodeOrig varchar(6),
  @TransEffDate datetime,
  - Three parameters added here

  
  

  
  @PolicyNbrOld varchar(7),
  @TransCodeOrigOld varchar(6),
  @TransEffDate_Old datetime,

  
  

  
  @OverrideCode varchar(2),
  @NRCodeOvr varchar(1),
  @TransCodeOvr varchar(6),
  @CreatedDate datetime,
  @CreatedUID nvarchar(48),
  @ModifiedDate datetime,
  @ModifiedUID nvarchar(48),
  @PlanYear int
  )
  As
  BEGIN
  DECLARE @Err Int
  

  
  UPDATE [TransOverride]
  Set
  [PolicyNbr] = @PolicyNbr,
  [TransCodeOrig] = @TransCodeOrig,
  [TransEffDate] = @TransEffDate,
  [OverrideCode] = @OverrideCode,
  [NRCodeOvr] = @NRCodeOvr,
  [TransCodeOvr] = @TransCodeOvr,
  [CreatedDate] = @CreatedDate,
  [CreatedUID] = @CreatedUID,
  [ModifiedDate] = @ModifiedDate,
  [ModifiedUID] = @ModifiedUID,
  [PlanYear] = @PlanYear
  WHERE
  [PolicyNbr] = @PolicyNbr AND
  [TransCodeOrig] = @TransCodeOrig AND
  [TransEffDate] = @TransEffDate
  --------------- Now I must add these three parameters in my where clause so the update does not violate
  --------------- the key constraints on the table. See below table for reference.
  Set @Err = @@Error
  

  
  RETURN @Err
  End
  CREATE TABLE [TransOverride] (
  [PolicyNbr] [varchar] (7) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL ,
  [TransCodeOrig] [varchar] (6) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL ,
  [TransEffDate] [datetime] NOT NULL ,
  [OverrideCode] [varchar] (2) COLLATE SQLLatin1GeneralCP1CIAS NULL ,
  [NRCodeOvr] [varchar] (1) COLLATE SQLLatin1GeneralCP1CIAS NULL ,
  [TransCodeOvr] [varchar] (6) COLLATE SQLLatin1GeneralCP1CIAS NULL ,
  [CreatedDate] [datetime] NOT NULL CONSTRAINT [DFTransOverCreat70A8B9AE] DEFAULT (getdate()),
  [CreatedUID] [nvarchar] (48) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL ,
  [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DFTransOverModif719CDDE7] DEFAULT (getdate()),
  [ModifiedUID] [nvarchar] (48) COLLATE SQLLatin1GeneralCP1CIAS NULL ,
  [PlanYear] [int] NOT NULL CONSTRAINT [DFTransOverPlan_56757D0D] DEFAULT (2004),
  CONSTRAINT [cnTransOverridePK] PRIMARY KEY CLUSTERED
  (
  [PolicyNbr],
  [TransCodeOrig],
  [TransEffDate]
  ) ON [PRIMARY] ,
  CONSTRAINT [cnTransOverrideNRCodeOvrCK] CHECK ([NRCodeOvr] = '' or ([NRCodeOvr] = 'R' or [NRCodeOvr] = 'N')),
  CONSTRAINT [cnTransOverrideOverrideCodeCK] CHECK ([OverrideCode] = '' or ([OverrideCode] = 'OT' or ([OverrideCode] = 'AU' or ([OverrideCode] = 'MS' or ([OverrideCode] = 'EN' or ([OverrideCode] = 'AO' or [OverrideCode] = 'AC'))))))
  ) ON [PRIMARY]
  GO
  

  
  

  
  Now here is my ado.net function update that will call the above stored procedure
  passing the parameters.
  

  
  Public Shared Function upd( _
  ByVal strPolicyNbr As Object, _
  ByVal strTransCodeOrig As Object, _
  ByVal dteTransEffDate As Object, _
  ByVal varOverrideCode As Object, _
  ByVal varNRCodeOvr As Object, _
  ByVal varTransCodeOvr As Object, _
  ByVal dteCreatedDate As Object, _
  ByVal strCreatedUID As Object, _
  ByVal dteModifiedDate As Object, _
  ByVal varModifiedUID As Object, _
  ByVal lngPlanYear As Object, _
  ByVal strPolicyNbrold As Object, _
  ByVal strTransCodeOrigold As Object, _
  ByVal dteTransEffDate_old As Object _
  ) As Object
  

  
  ' construct new connection And command objects
  Dim conn As SqlConnection = GetConn()
  Dim cmd As SqlCommand = GetSprocCmd("prcTransOverrideupd", 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 PolicyNbr column
  param = New SqlParameter("@PolicyNbr", System.Data.SqlDbType.VarChar, 7)
  param.Direction = ParameterDirection.Input
  param.Value = strPolicyNbr
  cmd.Parameters.Add(param)
  ' parameter for TransCodeOrig column
  param = New SqlParameter("@TransCodeOrig", System.Data.SqlDbType.VarChar, 6)
  param.Direction = ParameterDirection.Input
  param.Value = strTransCodeOrig
  cmd.Parameters.Add(param)
  ' parameter for TransEffDate column
  param = New SqlParameter("@TransEffDate", System.Data.SqlDbType.DateTime, 8)
  param.Direction = ParameterDirection.Input
  param.Value = dteTransEffDate
  cmd.Parameters.Add(param)
  

  
  ' The addition of three parameters
  ' The addition of three
  ' add params
  ' parameter for PolicyNbr column
  param = New SqlParameter("@PolicyNbrOld", System.Data.SqlDbType.VarChar, 7)
  param.Direction = ParameterDirection.Input
  param.Value = strPolicyNbrold
  cmd.Parameters.Add(param)
  ' parameter for TransCodeOrig column
  param = New SqlParameter("@TransCodeOrigOld", System.Data.SqlDbType.VarChar, 6)
  param.Direction = ParameterDirection.Input
  param.Value = strTransCodeOrigold
  cmd.Parameters.Add(param)
  ' parameter for TransEffDate column
  param = New SqlParameter("@TransEffDateOld", System.Data.SqlDbType.DateTime, 8)
  param.Direction = ParameterDirection.Input
  param.Value = dteTransEffDateold
  cmd.Parameters.Add(param)
  

  
  

  
  

  
  ' parameter for OverrideCode column
  param = New SqlParameter("@OverrideCode", System.Data.SqlDbType.VarChar, 2)
  param.Direction = ParameterDirection.Input
  param.Value = varOverrideCode
  cmd.Parameters.Add(param)
  ' parameter for NRCodeOvr column
  param = New SqlParameter("@NRCodeOvr", System.Data.SqlDbType.VarChar, 1)
  param.Direction = ParameterDirection.Input
  param.Value = varNRCodeOvr
  cmd.Parameters.Add(param)
  ' parameter for TransCodeOvr column
  param = New SqlParameter("@TransCodeOvr", System.Data.SqlDbType.VarChar, 6)
  param.Direction = ParameterDirection.Input
  param.Value = varTransCodeOvr
  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 PlanYear column
  param = New SqlParameter("@PlanYear", System.Data.SqlDbType.Int, 4)
  param.Direction = ParameterDirection.Input
  param.Value = lngPlanYear
  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
  

  
  Any help guys would be a thumbs up!
  

  
  Let's share our knowledge together!!!
  Matt Cupryk
  514-685-0449
  

  Report abuse