macupryk Posted November 12, 2005 Posted November 12, 2005 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. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.