I have the following procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* ------------------------------------------------------------
PROCEDURE: dbo.prc_TransOverride_upd
Description: Updates a record In table 'dbo.prc_TransOverride_upd'
------------------------------------------------------------ */
CREATE PROCEDURE dbo.prc_TransOverride_upd
(
@Policy_Nbr varchar(7),
@Trans_CodeOrig varchar(6),
@Trans_Eff_Date datetime,
@Override_Code varchar(2),
@NR_CodeOvr varchar(1),
@Trans_CodeOvr varchar(6),
@CreatedDate datetime,
@CreatedUID nvarchar(48),
@ModifiedDate datetime,
@ModifiedUID nvarchar(48),
@Plan_Year int
)
As
BEGIN
DECLARE @Err Int
UPDATE [TransOverride]
Set
[Policy_Nbr] = @Policy_Nbr,
[Trans_CodeOrig] = @Trans_CodeOrig,
[Trans_Eff_Date] = @Trans_Eff_Date,
[Override_Code] = @Override_Code,
[NR_CodeOvr] = @NR_CodeOvr,
[Trans_CodeOvr] = @Trans_CodeOvr,
[CreatedDate] = @CreatedDate,
[CreatedUID] = @CreatedUID,
[ModifiedDate] = @ModifiedDate,
[ModifiedUID] = @ModifiedUID,
[Plan_Year] = @Plan_Year
WHERE
[Policy_Nbr] = @Policy_Nbr AND
[Trans_CodeOrig] = @Trans_CodeOrig AND
[Trans_Eff_Date] = @Trans_Eff_Date
Set @Err = @@Error
RETURN @Err
End
GO
SET QUOTED_IDENTIFIER 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
strPolicy_Nbr_old As Object, strTrans_CodeOrig_old As Object, dteTrans_Eff_Date_old As Object
in the subroutine with the appropriate direction and modify the stored procedure to work with this.
Public Shared Function upd( _
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, _
ByVal strPolicy_Nbr_old As Object, _
ByVal strTrans_CodeOrig_old As Object, _
ByVal dteTrans_Eff_Date_old As Object _
) As Object
' construct new connection And command objects
Dim conn As SqlConnection = GetConn()
Dim cmd As SqlCommand = GetSprocCmd("prc_TransOverride_upd", 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
Here is the stored procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* ------------------------------------------------------------
PROCEDURE: dbo.prc_TransOverride_upd
Description: Updates a record In table 'dbo.prc_TransOverride_upd'
------------------------------------------------------------ */
CREATE PROCEDURE dbo.prc_TransOverride_upd
(
@Policy_Nbr varchar(7),
@Trans_CodeOrig varchar(6),
@Trans_Eff_Date datetime,
@Override_Code varchar(2),
@NR_CodeOvr varchar(1),
@Trans_CodeOvr varchar(6),
@CreatedDate datetime,
@CreatedUID nvarchar(48),
@ModifiedDate datetime,
@ModifiedUID nvarchar(48),
@Plan_Year int
)
As
BEGIN
DECLARE @Err Int
UPDATE [TransOverride]
Set
[Policy_Nbr] = @Policy_Nbr,
[Trans_CodeOrig] = @Trans_CodeOrig,
[Trans_Eff_Date] = @Trans_Eff_Date,
[Override_Code] = @Override_Code,
[NR_CodeOvr] = @NR_CodeOvr,
[Trans_CodeOvr] = @Trans_CodeOvr,
[CreatedDate] = @CreatedDate,
[CreatedUID] = @CreatedUID,
[ModifiedDate] = @ModifiedDate,
[ModifiedUID] = @ModifiedUID,
[Plan_Year] = @Plan_Year
WHERE
[Policy_Nbr] = @Policy_Nbr AND
[Trans_CodeOrig] = @Trans_CodeOrig AND
[Trans_Eff_Date] = @Trans_Eff_Date
Set @Err = @@Error
RETURN @Err
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I think I should add something like this in my ado.net
' The addition of three
' add params
' parameter for Policy_Nbr column
param = New SqlParameter("@Policy_Nbr_Old", System.Data.SqlDbType.VarChar, 7)
param.Direction = ParameterDirection.Input
param.Value = strPolicy_Nbr_old
cmd.Parameters.Add(param)
' parameter for Trans_CodeOrig column
param = New SqlParameter("@Trans_CodeOrig_Old", System.Data.SqlDbType.VarChar, 6)
param.Direction = ParameterDirection.Input
param.Value = strTrans_CodeOrig_old
cmd.Parameters.Add(param)
' parameter for Trans_Eff_Date column
param = New SqlParameter("@Trans_Eff_Date_Old", System.Data.SqlDbType.DateTime, 8)
param.Direction = ParameterDirection.Input
param.Value = dteTrans_Eff_Date_old
cmd.Parameters.Add(param)
and then I will have to add these to my stored procedure.
I know it is a long question but any help would be well acknowledge
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* ------------------------------------------------------------
PROCEDURE: dbo.prc_TransOverride_upd
Description: Updates a record In table 'dbo.prc_TransOverride_upd'
------------------------------------------------------------ */
CREATE PROCEDURE dbo.prc_TransOverride_upd
(
@Policy_Nbr varchar(7),
@Trans_CodeOrig varchar(6),
@Trans_Eff_Date datetime,
@Override_Code varchar(2),
@NR_CodeOvr varchar(1),
@Trans_CodeOvr varchar(6),
@CreatedDate datetime,
@CreatedUID nvarchar(48),
@ModifiedDate datetime,
@ModifiedUID nvarchar(48),
@Plan_Year int
)
As
BEGIN
DECLARE @Err Int
UPDATE [TransOverride]
Set
[Policy_Nbr] = @Policy_Nbr,
[Trans_CodeOrig] = @Trans_CodeOrig,
[Trans_Eff_Date] = @Trans_Eff_Date,
[Override_Code] = @Override_Code,
[NR_CodeOvr] = @NR_CodeOvr,
[Trans_CodeOvr] = @Trans_CodeOvr,
[CreatedDate] = @CreatedDate,
[CreatedUID] = @CreatedUID,
[ModifiedDate] = @ModifiedDate,
[ModifiedUID] = @ModifiedUID,
[Plan_Year] = @Plan_Year
WHERE
[Policy_Nbr] = @Policy_Nbr AND
[Trans_CodeOrig] = @Trans_CodeOrig AND
[Trans_Eff_Date] = @Trans_Eff_Date
Set @Err = @@Error
RETURN @Err
End
GO
SET QUOTED_IDENTIFIER 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
strPolicy_Nbr_old As Object, strTrans_CodeOrig_old As Object, dteTrans_Eff_Date_old As Object
in the subroutine with the appropriate direction and modify the stored procedure to work with this.
Public Shared Function upd( _
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, _
ByVal strPolicy_Nbr_old As Object, _
ByVal strTrans_CodeOrig_old As Object, _
ByVal dteTrans_Eff_Date_old As Object _
) As Object
' construct new connection And command objects
Dim conn As SqlConnection = GetConn()
Dim cmd As SqlCommand = GetSprocCmd("prc_TransOverride_upd", 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
Here is the stored procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* ------------------------------------------------------------
PROCEDURE: dbo.prc_TransOverride_upd
Description: Updates a record In table 'dbo.prc_TransOverride_upd'
------------------------------------------------------------ */
CREATE PROCEDURE dbo.prc_TransOverride_upd
(
@Policy_Nbr varchar(7),
@Trans_CodeOrig varchar(6),
@Trans_Eff_Date datetime,
@Override_Code varchar(2),
@NR_CodeOvr varchar(1),
@Trans_CodeOvr varchar(6),
@CreatedDate datetime,
@CreatedUID nvarchar(48),
@ModifiedDate datetime,
@ModifiedUID nvarchar(48),
@Plan_Year int
)
As
BEGIN
DECLARE @Err Int
UPDATE [TransOverride]
Set
[Policy_Nbr] = @Policy_Nbr,
[Trans_CodeOrig] = @Trans_CodeOrig,
[Trans_Eff_Date] = @Trans_Eff_Date,
[Override_Code] = @Override_Code,
[NR_CodeOvr] = @NR_CodeOvr,
[Trans_CodeOvr] = @Trans_CodeOvr,
[CreatedDate] = @CreatedDate,
[CreatedUID] = @CreatedUID,
[ModifiedDate] = @ModifiedDate,
[ModifiedUID] = @ModifiedUID,
[Plan_Year] = @Plan_Year
WHERE
[Policy_Nbr] = @Policy_Nbr AND
[Trans_CodeOrig] = @Trans_CodeOrig AND
[Trans_Eff_Date] = @Trans_Eff_Date
Set @Err = @@Error
RETURN @Err
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I think I should add something like this in my ado.net
' The addition of three
' add params
' parameter for Policy_Nbr column
param = New SqlParameter("@Policy_Nbr_Old", System.Data.SqlDbType.VarChar, 7)
param.Direction = ParameterDirection.Input
param.Value = strPolicy_Nbr_old
cmd.Parameters.Add(param)
' parameter for Trans_CodeOrig column
param = New SqlParameter("@Trans_CodeOrig_Old", System.Data.SqlDbType.VarChar, 6)
param.Direction = ParameterDirection.Input
param.Value = strTrans_CodeOrig_old
cmd.Parameters.Add(param)
' parameter for Trans_Eff_Date column
param = New SqlParameter("@Trans_Eff_Date_Old", System.Data.SqlDbType.DateTime, 8)
param.Direction = ParameterDirection.Input
param.Value = dteTrans_Eff_Date_old
cmd.Parameters.Add(param)
and then I will have to add these to my stored procedure.
I know it is a long question but any help would be well acknowledge
