Long ado net question but urgent help required

sql server United States
  • 12 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),
    @Trans
    CodeOrig varchar(6),
    @TransEffDate datetime,
    - Three parameters added here



    @PolicyNbrOld varchar(7),
    @TransCodeOrigOld varchar(6),
    @TransEffDate_Old 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
    --------------- 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 [DF
    TransOverModif719CDDE7] DEFAULT (getdate()),
    [ModifiedUID] [nvarchar] (48) COLLATE SQL
    Latin1GeneralCP1CIAS NULL ,
    [PlanYear] [int] NOT NULL CONSTRAINT [DFTransOverPlan_56757D0D] DEFAULT (2004),
    CONSTRAINT [cnTransOverride
    PK] PRIMARY KEY CLUSTERED
    (
    [PolicyNbr],
    [Trans
    CodeOrig],
    [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 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 _
    ) 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)


    ' The addition of three parameters
    ' The addition of three
    ' add params
    ' parameter for PolicyNbr column
    param = New SqlParameter("@Policy
    NbrOld", System.Data.SqlDbType.VarChar, 7)
    param.Direction = ParameterDirection.Input
    param.Value = strPolicy
    Nbrold
    cmd.Parameters.Add(param)
    ' parameter for Trans
    CodeOrig 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 = dteTrans
    EffDateold
    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


    Any help guys would be a thumbs up!


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

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.

“Debugging is anticipated with distaste, performed with reluctance, and bragged about forever.” - Dan Kaminsky