ADO.NET question modification

• • Reply

  15 years ago

  by mcupryk

  Mathieu

  Montreal, Canada

  Joined 16 years ago

  I have the following procedure
  

  
  SET QUOTEDIDENTIFIER ON
  GO
  SET ANSINULLS ON
  GO
  

  
  

  
  /* ------------------------------------------------------------
    PROCEDURE:    dbo.prcTransOverrideupd
  

  
    Description:  Updates a record In table 'dbo.prcTransOverrideupd'
  

  
    ------------------------------------------------------------ */
  

  
  CREATE PROCEDURE dbo.prcTransOverrideupd
  (
  @PolicyNbr varchar(7),
  @TransCodeOrig varchar(6),
  @TransEffDate 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
  

  
      Set @Err = @@Error
  

  
      RETURN @Err
  End
  

  
  GO
  SET QUOTEDIDENTIFIER OFF
  GO
  SET ANSINULLS ON
  GO
  

  
  With this in mind I have, where I have to keed the old values  
         oldPolicyNumber = dr.Item(1)
         oldTransCode = dr.Item(2)
         oldTransEffDate = dr.Item(3)
  before they are modified in a diolog form. Then I must do an update that requires the old values in the where clause.
  Now see the question below.
  

  
  

  
  Public Sub DoModify()
         Dim bm As BindingManagerBase = Me.DataGrid1.BindingContext(Me.DataGrid1.DataSource, Me.DataGrid1.DataMember)
         Dim dr As DataRow = CType(bm.Current, DataRowView).Row
         Dim editform As New EditTransOverride(dr)
         oldPolicyNumber = dr.Item(1)
         oldTransCode = dr.Item(2)
         oldTransEffDate = dr.Item(3)
  

  
         Dim retval As DialogResult = editform.ShowDialog()
         If retval = DialogResult.OK Then
             bm.EndCurrentEdit()
             Try
                 Dim substr As String = dr.Item(4)
                 substr = substr.Substring(0, 2)
                 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)
                 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
  

  
  Now I have the following ado.net code that first I must add the three paramaters in
  strPolicyNbrold As Object, strTransCodeOrigold As Object, dteTransEffDate_old As Object
  

  
  in the subroutine with the appropriate direction and modify the stored procedure to work with this.
  

  
  

  
  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 _
    &nbsp 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)
         ' 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
  

  
  Here is the stored procedure:
  SET QUOTEDIDENTIFIER ON
  GO
  SET ANSINULLS ON
  GO
  

  
  

  
  /* ------------------------------------------------------------
    PROCEDURE:    dbo.prcTransOverrideupd
  

  
    Description:  Updates a record In table 'dbo.prcTransOverrideupd'
  

  
    ------------------------------------------------------------ */
  

  
  CREATE PROCEDURE dbo.prcTransOverrideupd
  (
  @PolicyNbr varchar(7),
  @TransCodeOrig varchar(6),
  @TransEffDate 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
  

  
      Set @Err = @@Error
  

  
      RETURN @Err
  End
  

  
  GO
  SET QUOTEDIDENTIFIER OFF
  GO
  SET ANSINULLS ON
  GO
  

  
  I know it is a long question but any help would be well acknowledge.
  

  Report abuse