ADO.NET question modification

  • 12 years ago

    I have the following procedure


    SET QUOTEDIDENTIFIER ON
    GO
    SET ANSI
    NULLS ON
    GO



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


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


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


    CREATE PROCEDURE dbo.prcTransOverrideupd
    (
    @PolicyNbr varchar(7),
    @Trans
    CodeOrig varchar(6),
    @TransEffDate datetime,
    @OverrideCode varchar(2),
    @NR
    CodeOvr varchar(1),
    @TransCodeOvr varchar(6),
    @CreatedDate datetime,
    @CreatedUID nvarchar(48),
    @ModifiedDate datetime,
    @ModifiedUID nvarchar(48),
    @Plan
    Year 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 ANSI
    NULLS 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 strTrans
    CodeOrig As Object, _
              ByVal dteTransEffDate As Object, _
              ByVal varOverrideCode As Object, _
              ByVal varNR
    CodeOvr As Object, _
              ByVal varTransCodeOvr As Object, _
              ByVal dteCreatedDate As Object, _
              ByVal strCreatedUID As Object, _
              ByVal dteModifiedDate As Object, _
              ByVal varModifiedUID As Object, _
              ByVal lngPlan
    Year 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("@Policy
    Nbr", System.Data.SqlDbType.VarChar, 7)
           param.Direction = ParameterDirection.Input
           param.Value = strPolicyNbr
           cmd.Parameters.Add(param)
           ' parameter for Trans
    CodeOrig column
           param = New SqlParameter("@TransCodeOrig", System.Data.SqlDbType.VarChar, 6)
           param.Direction = ParameterDirection.Input
           param.Value = strTrans
    CodeOrig
           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("@Override
    Code", System.Data.SqlDbType.VarChar, 2)
           param.Direction = ParameterDirection.Input
           param.Value = varOverrideCode
           cmd.Parameters.Add(param)
           ' parameter for NR
    CodeOvr column
           param = New SqlParameter("@NRCodeOvr", System.Data.SqlDbType.VarChar, 1)
           param.Direction = ParameterDirection.Input
           param.Value = varNR
    CodeOvr
           cmd.Parameters.Add(param)
           ' parameter for TransCodeOvr column
           param = New SqlParameter("@Trans
    CodeOvr", 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 Plan
    Year column
           param = New SqlParameter("@PlanYear", 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


    Here is the stored procedure:
    SET QUOTEDIDENTIFIER ON
    GO
    SET ANSI
    NULLS ON
    GO



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


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


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


    CREATE PROCEDURE dbo.prcTransOverrideupd
    (
    @PolicyNbr varchar(7),
    @Trans
    CodeOrig varchar(6),
    @TransEffDate datetime,
    @OverrideCode varchar(2),
    @NR
    CodeOvr varchar(1),
    @TransCodeOvr varchar(6),
    @CreatedDate datetime,
    @CreatedUID nvarchar(48),
    @ModifiedDate datetime,
    @ModifiedUID nvarchar(48),
    @Plan
    Year 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 ANSI
    NULLS ON
    GO


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

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The question of whether computers can think is just like the question of whether submarines can swim.” - Edsger W. Dijkstra