troubleshooting Question

how to call a stored proc in vb.net.

Avatar of mathieu_cupryk
mathieu_cuprykFlag for Canada asked on
Visual Basic.NET
24 Comments1 Solution303 ViewsLast Modified:
I have the following stored procedure:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
--    DO AN UPDATE OR AN INSERT INTO TRANSACTION OVERRIDE TABLE
/*******************************************************************************
***   Name: dbo.stp_InsUpdTransOverride
***   Creation Date:  08/24/2005
***   Author:       Mathieu Cupryk
***   Modified:
***
***   Input:    Policy_Nbr, Trans_CodeOrig, Trans_Eff_Date, Override_Code, NR_CodeOvr, Trans_CodeOvr,
***                CreatedUID, ModifiedUID, ModifiedDate,Plan_Year
***
***   Output:   ALL Transaction Override Records
***   Order:    Transaction_Eff_Date
***   Database:  CCFinSol
***   Purpose:   Insert/Update TransactionOverride Record
***
***   Copyright:   @2005 - Corporate Customer, Zurich N.A., all rights reserved
********************************************************************************/

CREATE PROCEDURE dbo.stp_InsUpdTransOverride
    @Policy_Nbr      varchar(7),
    @Trans_CodeOrig  varchar(6),
    @Trans_Eff_Date  datetime,
    @Override_Code   varchar(2),
    @NR_CodeOvr      varchar(1),
    @Trans_CodeOvr   varchar(6),
    @CreatedUID      nvarchar(48),
    @ModifiedUID     nvarchar(48),
    @Plan_Year            int
AS
BEGIN
      SET NOCOUNT ON

      UPDATE dbo.TransOverride
           SET Override_Code = @Override_Code,
           NR_CodeOvr    = @NR_CodeOvr,
           Trans_CodeOvr = @Trans_CodeOvr,
           ModifiedUID   = @ModifiedUID,
           ModifiedDate  = GETDATE(),
           Plan_Year = @Plan_Year
      WHERE Policy_Nbr = @Policy_Nbr
           AND Trans_CodeOrig = @Trans_CodeOrig
           AND Trans_Eff_Date = @Trans_Eff_Date

      IF @@ROWCOUNT = 0
      BEGIN
           INSERT INTO dbo.TransOverride (Policy_Nbr, Trans_CodeOrig, Trans_Eff_Date, Override_Code,
                  NR_CodeOvr, Trans_CodeOvr,   CreatedUID, ModifiedUID )
          VALUES (@Policy_Nbr, @Trans_CodeOrig, @Trans_Eff_Date, @Override_Code,
                  @NR_CodeOvr, @Trans_CodeOvr, @CreatedUID, @CreatedUID)

      END

      RETURN 0
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I need to call this stored procedure in my sub routine:
Should I drop a sqladapter, the problem it is a stored proc that can update or do an insert. How should I proceed:

Private Sub DoSave()
        Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("FinSolMainDBConn")
        Dim connfinsol As New System.Data.SqlClient.SqlConnection(ConnectionString)
        Dim strsql As String

        Try
            strsql = "UPDATE [CCFinSol].[dbo].[TransOverride] SET " & "Policy_Nbr='" & txtPolicyNumber.Text & "',Trans_CodeOrig='" & cmbOriginalTransCode.Text & "', Trans_Eff_Date='" & DateEditTransEff.Value & "', Override_Code='" & cmbOverrideAction.Text & "', NR_CodeOvr='" & cmbOverrideNR.Text & "', Trans_CodeOvr='" & cmbOverrideTransactionCode.Text & "', Plan_Year='" & txtPlanYear.Text & "'" & " WHERE Policy_Nbr='" & oldPolicyNumber & "' AND Trans_CodeOrig='" & oldTransCode & "' AND Trans_Eff_Date='" & oldTransEffDate & "'"
            MessageBox.Show(strsql.ToString)
            connfinsol.Open()
            Dim cmd As New SqlCommand(strsql, connfinsol)
            cmd.ExecuteNonQuery()
            connfinsol.Close()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        '      MessageBox.Show(strsql.ToString)
        MsgBox("Data Saved Successfully !", MsgBoxStyle.Information, Me.Text)
    End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 24 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 24 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros