• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

SQL Server 2008R2 Transaction

I have a stored proc that works great. But I need it to rollback if anything goes wrong with any of the inserts. The two tables have constraints that prevent the same name and birthdate from being entered more than once. If that constraint happens, none of the inserts should take place.

Can someone show me how this is done?

Thank you in advance.
/****** Object:  StoredProcedure [dbo].[AddFamilyInfo]    Script Date: 04/25/2011 10:57:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[AddFamilyInfo] 
	-- Add the parameters for the stored procedure here
	-- First Parent
	@pFirstName nvarchar(20),
	@pLastName nvarchar(20),
	@pGender nvarchar(6),
	@pBirthdate date,
	@pEthnicity nvarchar(50),
	@pMaritalStatus nvarchar(20),
	@pPhone1 nvarchar(14),
	@pPhone2 nvarchar(14),
	@pPhone3 nvarchar(14),
	@pAddress1 nvarchar(50),
	@pAddress2 nvarchar(50),
	@pCity nvarchar(50),
	@pState nvarchar(2),
	@pZip nvarchar(11),
	@pEmail nvarchar(175),
	@pEducation nvarchar(50),
	@pIncome nvarchar(50),
	-- Second Parent
	@opFirstName nvarchar(20) = NULL,
	@opLastName nvarchar(20)= NULL,
	@opGender nvarchar(6)= NULL,
	@opBirthdate date = NULL,
	@opEthnicity nvarchar(50) = NULL,
	@opMaritalStatus nvarchar(20) = NULL,
	@opPhone1 nvarchar(14) = NULL,
	@opPhone2 nvarchar(14) = NULL,
	@opPhone3 nvarchar(14) = NULL,
	@opAddress1 nvarchar(50) = NULL,
	@opAddress2 nvarchar(50) = NULL,
	@opCity nvarchar(50) = NULL,
	@opState nvarchar(2) = NULL,
	@opZip nvarchar(11) = NULL,
	@opEmail nvarchar(175) = NULL,
	@opEducation nvarchar(50) = NULL,
	@opIncome nvarchar(50) = NULL,
	-- First Child
	@LocationPref nvarchar(50),
	@JrFirstName nvarchar(20),
	@JrLastName nvarchar(20),
	@JrGender nvarchar(6),
	@JrBirthdate date,
	@JrEthnicity nvarchar(20),
	-- Second Child
	@SecondLocationPref nvarchar(50) = NULL,
	@SecondJrFirstName nvarchar(20) = NULL,
	@SecondJrLastName nvarchar(20) = NULL,
	@SecondJrGender nvarchar(6) = NULL,
	@SecondJrBirthdate date = NULL,
	@SecondJrEthnicity nvarchar(20) = NULL,
	-- Third Child
	@ThirdLocationPref nvarchar(50) = NULL,
	@ThirdJrFirstName nvarchar(20) = NULL,
	@ThirdJrLastName nvarchar(20) = NULL,
	@ThirdJrGender nvarchar(6) = NULL,
	@ThirdJrBirthdate date = NULL,
	@ThirdJrEthnicity nvarchar(20) = NULL
	
AS
BEGIN 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statement for first parent record
	INSERT INTO [dbo].[Parent]
           ([FirstName],[LastName],[Gender],[Birthdate],[Ethnicity],[MaritalStatus]
           ,[Phone1],[Phone2],[Phone3],[Address1],[Address2],[City],[State],[Zip]
           ,[Email],[Education],[Income])
     VALUES
           (@pFirstName, @pLastName, @pGender, @pBirthdate, @pEthnicity, @pMaritalStatus, @pphone1, @pPhone2, @pPhone3,
			@pAddress1, @pAddress2, @pCity, @pState, @pZip, @pEmail, @pEducation, @pIncome)

	DECLARE @JrParentID int
	SELECT @JrParentID = SCOPE_IDENTITY()
	
	    -- Insert statement for second parent record
	DECLARE @JrOtherParentID int
	IF NOT (@opFirstName is null AND @opLastName IS NULL)
	BEGIN
		INSERT INTO [dbo].[Parent]
			([FirstName],[LastName],[Gender],[Birthdate],[Ethnicity],[MaritalStatus]
			,[Phone1],[Phone2],[Phone3],[Address1],[Address2],[City],[State],[Zip]
			,[Email],[Education],[Income])
		VALUES
			(@opFirstName, @opLastName, @opGender, @opBirthdate, @opEthnicity, @opMaritalStatus, @opphone1, @opPhone2, @opPhone3,
			@opAddress1, @opAddress2, @opCity, @opState, @opZip, @opEmail, @opEducation, @opIncome)

		SELECT @JrOtherParentID = SCOPE_IDENTITY()
	END
	
	-- Insert statement for child record
	
		
	
	INSERT INTO [dbo].[Players]
		([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
	VALUES
		(@LocationPref, @JrFirstName, @JrLastName, @JrGender, @JrBirthdate, @JrEthnicity, @JrParentID, @JrOtherParentID) --
	IF @SecondJrFirstName IS NOT NULL OR @SecondJrLastName IS NOT NULL
	BEGIN
		INSERT INTO [dbo].[Players]
			([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
		VALUES
			(@SecondLocationPref, @SecondJrFirstName, @SecondJrLastName, @SecondJrGender, @SecondJrBirthdate, @SecondJrEthnicity, @JrParentID, @JrOtherParentID)
	END
	IF @ThirdJrFirstName IS NOT NULL OR @ThirdJrLastName IS NOT NULL
	BEGIN
		INSERT INTO [dbo].[Players]		
			([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
		VALUES
			(@ThirdLocationPref, @ThirdJrFirstName, @ThirdJrLastName, @ThirdJrGender, @ThirdJrBirthdate, @ThirdJrEthnicity, @JrParentID, @JrOtherParentID)
	END
END

Open in new window

0
wdarnellg
Asked:
wdarnellg
  • 13
  • 10
  • 5
  • +1
2 Solutions
 
Alpesh PatelAssistant ConsultantCommented:
Create main SP as you have created.
Create second one in that write as below

Start Transaction
Execute You SP
IF (@Return IS NULL or @@Error > 0 )
Rollback Transaction

Commit Transaction
0
 
8080_DiverCommented:
Or you could use a TRY...CATCH to handle the COMMIT (as the last statement in the TRY portion) or ROLLBACK (within the CATCH portion.  (After all, the TRY...CATCH is more in line with the SS2008 way of doing things ;-).

TRY CATCH Information
0
 
wdarnellgAuthor Commented:
Everything seems to work fine, except I would like the error to be returned to the application to get a friendly message to the user. How do I get the constraint error back to the app this way?
/****** Object:  StoredProcedure [dbo].[sj_AddFamilyInfo]    Script Date: 04/26/2011 09:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[AddFamilyInfo] 
	-- Add the parameters for the stored procedure here
	-- First Parent
	@pFirstName nvarchar(20),
	@pLastName nvarchar(20),
	@pGender nvarchar(6),
	@pBirthdate date,
	@pEthnicity nvarchar(50),
	@pMaritalStatus nvarchar(20),
	@pPhone1 nvarchar(14),
	@pPhone2 nvarchar(14),
	@pPhone3 nvarchar(14),
	@pAddress1 nvarchar(50),
	@pAddress2 nvarchar(50),
	@pCity nvarchar(50),
	@pState nvarchar(2),
	@pZip nvarchar(11),
	@pEmail nvarchar(175),
	@pEducation nvarchar(50),
	@pIncome nvarchar(50),
	-- Second Parent
	@opFirstName nvarchar(20) = NULL,
	@opLastName nvarchar(20)= NULL,
	@opGender nvarchar(6)= NULL,
	@opBirthdate date = NULL,
	@opEthnicity nvarchar(50) = NULL,
	@opMaritalStatus nvarchar(20) = NULL,
	@opPhone1 nvarchar(14) = NULL,
	@opPhone2 nvarchar(14) = NULL,
	@opPhone3 nvarchar(14) = NULL,
	@opAddress1 nvarchar(50) = NULL,
	@opAddress2 nvarchar(50) = NULL,
	@opCity nvarchar(50) = NULL,
	@opState nvarchar(2) = NULL,
	@opZip nvarchar(11) = NULL,
	@opEmail nvarchar(175) = NULL,
	@opEducation nvarchar(50) = NULL,
	@opIncome nvarchar(50) = NULL,
	-- First Child
	@LocationPref nvarchar(50),
	@JrFirstName nvarchar(20),
	@JrLastName nvarchar(20),
	@JrGender nvarchar(6),
	@JrBirthdate date,
	@JrEthnicity nvarchar(20),
	-- Second Child
	@SecondLocationPref nvarchar(50) = NULL,
	@SecondJrFirstName nvarchar(20) = NULL,
	@SecondJrLastName nvarchar(20) = NULL,
	@SecondJrGender nvarchar(6) = NULL,
	@SecondJrBirthdate date = NULL,
	@SecondJrEthnicity nvarchar(20) = NULL,
	-- Third Child
	@ThirdLocationPref nvarchar(50) = NULL,
	@ThirdJrFirstName nvarchar(20) = NULL,
	@ThirdJrLastName nvarchar(20) = NULL,
	@ThirdJrGender nvarchar(6) = NULL,
	@ThirdJrBirthdate date = NULL,
	@ThirdJrEthnicity nvarchar(20) = NULL
	
AS

BEGIN TRY
BEGIN TRANSACTION
BEGIN 
--All of the sp code

COMMIT TRANSACTION

END TRY

BEGIN CATCH
ROLLBACK
END CATCH

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
8080_DiverCommented:
What about using a couple of OUTPUT parameters (@ErrorCode INT OUTPUT and @ErrorMsg Varchar(1000) OUTPUT)?  Also, as indicated in the web link, you can capture the Error Code and Error Message and pass those back
0
 
wdarnellgAuthor Commented:
Could you show me how to set that up? I am doing something wrong. I am not getting any error output.
0
 
8080_DiverCommented:
See the following:
/****** Object:  StoredProcedure [dbo].[sj_AddFamilyInfo]    Script Date: 04/26/2011 09:16:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[AddFamilyInfo] 
	-- Add the parameters for the stored procedure here
	-- First Parent
	@pFirstName nvarchar(20),
	@pLastName nvarchar(20),
	@pGender nvarchar(6),
	@pBirthdate date,
	@pEthnicity nvarchar(50),
	@pMaritalStatus nvarchar(20),
	@pPhone1 nvarchar(14),
	@pPhone2 nvarchar(14),
	@pPhone3 nvarchar(14),
	@pAddress1 nvarchar(50),
	@pAddress2 nvarchar(50),
	@pCity nvarchar(50),
	@pState nvarchar(2),
	@pZip nvarchar(11),
	@pEmail nvarchar(175),
	@pEducation nvarchar(50),
	@pIncome nvarchar(50),
	-- Second Parent
	@opFirstName nvarchar(20) = NULL,
	@opLastName nvarchar(20)= NULL,
	@opGender nvarchar(6)= NULL,
	@opBirthdate date = NULL,
	@opEthnicity nvarchar(50) = NULL,
	@opMaritalStatus nvarchar(20) = NULL,
	@opPhone1 nvarchar(14) = NULL,
	@opPhone2 nvarchar(14) = NULL,
	@opPhone3 nvarchar(14) = NULL,
	@opAddress1 nvarchar(50) = NULL,
	@opAddress2 nvarchar(50) = NULL,
	@opCity nvarchar(50) = NULL,
	@opState nvarchar(2) = NULL,
	@opZip nvarchar(11) = NULL,
	@opEmail nvarchar(175) = NULL,
	@opEducation nvarchar(50) = NULL,
	@opIncome nvarchar(50) = NULL,
	-- First Child
	@LocationPref nvarchar(50),
	@JrFirstName nvarchar(20),
	@JrLastName nvarchar(20),
	@JrGender nvarchar(6),
	@JrBirthdate date,
	@JrEthnicity nvarchar(20),
	-- Second Child
	@SecondLocationPref nvarchar(50) = NULL,
	@SecondJrFirstName nvarchar(20) = NULL,
	@SecondJrLastName nvarchar(20) = NULL,
	@SecondJrGender nvarchar(6) = NULL,
	@SecondJrBirthdate date = NULL,
	@SecondJrEthnicity nvarchar(20) = NULL,
	-- Third Child
	@ThirdLocationPref nvarchar(50) = NULL,
	@ThirdJrFirstName nvarchar(20) = NULL,
	@ThirdJrLastName nvarchar(20) = NULL,
	@ThirdJrGender nvarchar(6) = NULL,
	@ThirdJrBirthdate date = NULL,
	@ThirdJrEthnicity nvarchar(20) = NULL
-- NEW LINES START
	,@ERRORCODEOUT INT OUTPUT               
	,ERRORSEVERITYOUT INT OUTPUT
	,@ERRORMSGOUT VARCHAR(1000) OUTPUT  -- NOTE: This is based upon information regarding SS2000 error messages 
                                                                                     --            and may need to be nVarChar(1000)
-- NEW LINES END
	
AS

BEGIN TRY
BEGIN TRANSACTION
SELECT  @ERRORCODEOUT = 0
        ,@ERRORSEVERITYOUT = 0
        ,@ERRORMSGOUT = 'Completed with no errors.';

BEGIN 
--All of the sp code

COMMIT TRANSACTION

END TRY

BEGIN CATCH
 SELECT  @ERRORCODEOUT = ERROR_NUMBER()
        ,@ERRORSEVERITYOUT = ERROR_SEVERITY()
        ,@ERRORMSGOUT = ERROR_MESSAGE();


ROLLBACK
END CATCH

Open in new window


Note: You do have t pass some value in for the new OUTPUT parameters.  (I usually pass 0 for integers and '' (i.e. an empty string) for VarChar parameters.
0
 
wdarnellgAuthor Commented:
Thank you. I am reading this away from my work machine at the moment. I will be back at it Monday morning. I am looking forward to trying it out. Please bear with me.
0
 
wdarnellgAuthor Commented:
The sql code works good. It outputs both a message and error code. But I can't get my vb to receive the error message. The sql error received from the app says:
Procedure or function 'AddFamilyInfo' expects parameter '@ERRORCODEOUT', which was not supplied.

The code to try and handle it is below. What am I missing here?
Protected Sub FinishButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles FinishButton.Click
        Try
            'Declare Connection Information
            Dim strConn As String = ConfigurationManager.AppSettings("SiteSqlServer")
            Dim objConn As New SqlConnection(strConn)
            Dim cmd As New SqlCommand
            Dim cs As ClientScriptManager = Page.ClientScript
            Dim PartnerExists As Boolean = False
            Dim SecondChildExists As Boolean = False
            Dim ThirdChildExists As Boolean = False

            ' First, dimension and assign values to all of the variables you need:

            Dim pFirst As String = PFirstNameTextBox.Text
            Dim pLast As String = PLastNameTextBox.Text
            Dim pGender As String = PGenderDropDownList.SelectedValue.ToString
            Dim pBirthdate As Date = PBirthdateTextBox.Text
            Dim pEthnicity As String = PEthnicityDropDownList.SelectedValue.ToString
            Dim pMaritalStatus As String = PMaritalStatusDropDownList.SelectedValue.ToString
            Dim pPhone1 As String = PHomePhoneTextBox.Text
            Dim pPhone2 As String = PCellPhoneTextBox.Text
            Dim pPhone3 As String = PWorkPhoneTextBox.Text
            Dim pAddress1 As String = PAddress1TextBox.Text
            Dim pAddress2 As String = PAddress2TextBox.Text
            Dim pCity As String = PCityTextBox.Text
            Dim pState As String = PStateTextBox.Text
            Dim pZip As String = PZipTextBox.Text
            Dim pEmail As String = PEmailTextBox.Text
            Dim pEducation As String = PEducationDropDownList.SelectedValue.ToString
            Dim pIncome As String = PIncomeDropDownList.SelectedValue.ToString
            If PartnerExistsDropDownList.SelectedValue = "YES" Then
                PartnerExists = True
            End If
            Dim jrLocation As String = JrLocationPreferenceDropDownList.SelectedValue.ToString
            Dim jrFirst As String = JrFirstNameTextBox.Text
            Dim jrLast As String = JrLastNameTextBox.Text
            Dim jrGender As String = JrGenderDropDownList.SelectedValue.ToString
            Dim jrBirthdate As Date = JrBirthdateTextBox.Text
            Dim jrEthnicity As String = JrEthnicityDropDownList.SelectedValue.ToString

            If SecondJrFirstNameTextBox.Text > " " Or SecondJrLastNameTextBox.Text > " " Then SecondChildExists = True
            If ThirdJrFirstNameTextBox.Text > " " Or ThirdJrLastNameTextBox.Text > " " Then ThirdChildExists = True

            ' Now, Build up the stored proc and all of its parameters

            'Open Connection
            cmd.CommandText = "AddFamilyInfo"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = objConn


            cmd.Parameters.AddWithValue("@pFirstName", pFirst)
            cmd.Parameters.AddWithValue("@pLastName", pLast)
            cmd.Parameters.AddWithValue("@pGender", pGender)
            cmd.Parameters.AddWithValue("@pBirthdate", pBirthdate)
            cmd.Parameters.AddWithValue("@pEthnicity", pEthnicity)
            cmd.Parameters.AddWithValue("@pMaritalStatus", pMaritalStatus)
            cmd.Parameters.AddWithValue("@pPhone1", pPhone1)
            cmd.Parameters.AddWithValue("@pPhone2", pPhone2)
            cmd.Parameters.AddWithValue("@pPhone3", pPhone3)
            cmd.Parameters.AddWithValue("@pAddress1", pAddress1)
            cmd.Parameters.AddWithValue("@pAddress2", pAddress2)
            cmd.Parameters.AddWithValue("@pCity", pCity)
            cmd.Parameters.AddWithValue("@pState", pState)
            cmd.Parameters.AddWithValue("@pZip", pZip)
            cmd.Parameters.AddWithValue("@pEmail", pEmail)
            cmd.Parameters.AddWithValue("@pEducation", pEducation)
            cmd.Parameters.AddWithValue("@pIncome", pIncome)
            If PartnerExists Then
                Dim opFirst As String = OPFirstNameTextBox.Text
                Dim opLast As String = OPLastNameTextBox.Text
                Dim opGender As String = OPGenderDropDownList.SelectedValue.ToString
                Dim opBirthdate As Date = OPBirthdateTextBox.Text
                Dim opEthnicity As String = OPEthnicityDropDownList.SelectedValue.ToString
                Dim opMaritalStatus As String = OPMaritalStatusDropDownList.SelectedValue.ToString
                Dim opPhone1 As String = OPHomePhoneTextBox.Text
                Dim opPhone2 As String = OPCellPhoneTextBox.Text
                Dim opPhone3 As String = OPWorkPhoneTextBox.Text
                Dim opAddress1 As String = OPAddress1TextBox.Text
                Dim opAddress2 As String = OPAddress2TextBox.Text
                Dim opCity As String = OPCityTextBox.Text
                Dim opState As String = OPStateTextBox.Text
                Dim opZip As String = OPZipTextBox.Text
                Dim opEmail As String = OPEmailTextBox.Text
                Dim opEducation As String = OPEducationDropDownList.SelectedValue.ToString
                Dim opIncome As String = OPIncomeDropDownList.SelectedValue.ToString

                cmd.Parameters.AddWithValue("@opFirstName", opFirst)
                cmd.Parameters.AddWithValue("@opLastName", opLast)
                cmd.Parameters.AddWithValue("@opGender", opGender)
                cmd.Parameters.AddWithValue("@opBirthdate", opBirthdate)
                cmd.Parameters.AddWithValue("@opEthnicity", opEthnicity)
                cmd.Parameters.AddWithValue("@opMaritalStatus", opMaritalStatus)
                cmd.Parameters.AddWithValue("@opPhone1", opPhone1)
                cmd.Parameters.AddWithValue("@opPhone2", opPhone2)
                cmd.Parameters.AddWithValue("@opPhone3", opPhone3)
                cmd.Parameters.AddWithValue("@opAddress1", opAddress1)
                cmd.Parameters.AddWithValue("@opAddress2", opAddress2)
                cmd.Parameters.AddWithValue("@opCity", opCity)
                cmd.Parameters.AddWithValue("@opState", opState)
                cmd.Parameters.AddWithValue("@opZip", opZip)
                cmd.Parameters.AddWithValue("@opEmail", opEmail)
                cmd.Parameters.AddWithValue("@opEducation", opEducation)
                cmd.Parameters.AddWithValue("@opIncome", opIncome)
            End If
            'Declare the Junior Parameters
            cmd.Parameters.AddWithValue("@LocationPref", jrLocation)
            cmd.Parameters.AddWithValue("@jrFirstName", jrFirst)
            cmd.Parameters.AddWithValue("@jrLastName", jrLast)
            cmd.Parameters.AddWithValue("@jrGender", jrGender)
            cmd.Parameters.AddWithValue("@jrBirthdate", jrBirthdate)
            cmd.Parameters.AddWithValue("@jrEthnicity", jrEthnicity)
            If SecondChildExists Then
                Dim secondJrLocation As String = SecondJrLocationPreferenceDropDownList.SelectedValue.ToString
                Dim secondJrFirst As String = SecondJrFirstNameTextBox.Text
                Dim secondJrLast As String = SecondJrLastNameTextBox.Text
                Dim secondJrGender As String = SecondJrGenderDropDownList.SelectedValue.ToString
                Dim secondJrBirthdate As Date = SecondJrBirthdateTextBox.Text
                Dim secondJrEthnicity As String = SecondJrEthnicityDropDownList.SelectedValue.ToString

                cmd.Parameters.AddWithValue("@SecondLocationPref", secondJrLocation)
                cmd.Parameters.AddWithValue("@SecondjrFirstName", secondJrFirst)
                cmd.Parameters.AddWithValue("@SecondjrLastName", secondJrLast)
                cmd.Parameters.AddWithValue("@SecondjrGender", secondJrGender)
                cmd.Parameters.AddWithValue("@SecondjrBirthdate", secondJrBirthdate)
                cmd.Parameters.AddWithValue("@SecondjrEthnicity", secondJrEthnicity)
                If ThirdChildExists Then
                    Dim thirdJrLocation As String = ThirdJrLocationPreferenceDropDownList.SelectedValue.ToString
                    Dim thirdJrFirst As String = ThirdJrFirstNameTextBox.Text
                    Dim thirdJrLast As String = ThirdJrLastNameTextBox.Text
                    Dim thirdJrGender As String = ThirdJrGenderDropDownList.SelectedValue.ToString
                    Dim thirdJrBirthdate As Date = ThirdJrBirthdateTextBox.Text
                    Dim thirdJrEthnicity As String = ThirdJrEthnicityDropDownList.SelectedValue.ToString

                    cmd.Parameters.AddWithValue("@ThirdLocationPref", thirdJrLocation)
                    cmd.Parameters.AddWithValue("@ThirdjrFirstName", thirdJrFirst)
                    cmd.Parameters.AddWithValue("@ThirdjrLastName", thirdJrLast)
                    cmd.Parameters.AddWithValue("@ThirdjrGender", thirdJrGender)
                    cmd.Parameters.AddWithValue("@ThirdjrBirthdate", thirdJrBirthdate)
                    cmd.Parameters.AddWithValue("@ThirdjrEthnicity", thirdJrEthnicity)
                End If

            End If
            Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int, 4)
            ec.Value = 0
            ec.Direction = ParameterDirection.ReturnValue
            cmd.Parameters.Add(ec)


            'Open the connection
            objConn.Open()

            'Insert the info
            cmd.ExecuteNonQuery()

            If CType(ec.ToString, Integer) = 2627 Then
                cs.RegisterStartupScript(Me.GetType, "zomaar", "alert('One or more members of this family already has a profile. Contact Slam Jammers Site Administrator at 254-493-3116 for assistance.');", True)
                objConn.Close()
                Exit Sub
            End If

            'Close the connection
            objConn.Close()

            cs.RegisterStartupScript(Me.GetType, "zomaar", "alert('The Family Information Form Is Complete.');", True)

        Catch se As SqlException
            ErrorMessageLabel.Text = se.ErrorCode.ToString
            Exit Sub
        Catch ex As Exception
            ErrorMessageLabel.Text = ex.ToString
            ErrorMessageLabel.Visible = True
            Exit Sub
        End Try
        SJ1ApplicationMultiView.ActiveViewIndex = 7
    End Sub

Open in new window

0
 
8080_DiverCommented:
You have to "initialize" the output parameter just like you do the input parameters . . . only, it doesn't really matter what value you pass in for the OUTPUT parameter.
0
 
wdarnellgAuthor Commented:
I seem to be failing to initialize this new parameter. I think I am doing the same as the other parameters, the difference being that the other values come from controls. I am still being told in the sql exception that the @ERRORCODEOUT is not being provided.
Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int, 0)
            ec.Direction = ParameterDirection.InputOutput

            cmd.Parameters.AddWithValue(ec.ToString, 0)


            'Open the connection
            objConn.Open()

            'Insert the info
            cmd.ExecuteNonQuery()

Open in new window

0
 
8080_DiverCommented:
I believe that you will need to specify the "dircection" as OUTPUT, if possible.

Also, why aren't you using the same instructions to add set the uoutput parameter?  Something like the following, maybe?

 cmd.Parameters.AddWithValue("@ERRORCODEOUT", 0)

Open in new window

0
 
wdarnellgAuthor Commented:
Progress. The parameter is at least being embraced. But my error code value is coming up Nothing. It shows in ssms when I test the sp, but not in the vb code. Am I missing a request for the error code somewhere?
cmd.Parameters.AddWithValue("@ERRORCODEOUT", 0)

            Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int, 0)
            ec.Direction = ParameterDirection.Output

            'Open the connection
            objConn.Open()

            'Insert the info
            cmd.ExecuteNonQuery()

            If ec.Value = 2627 Then 'during runtime the ec.value is NOTHING

                cs.RegisterStartupScript(Me.GetType, "zomaar", "alert('One or more members of this family already has a profile. Contact Slam Jammers Site Administrator at 254-493-3116 for assistance.');", True)
                objConn.Close()
                Exit Sub
            End If

Open in new window

0
 
wdarnellgAuthor Commented:
I forgot to attach the code
cmd.Parameters.AddWithValue("@ERRORCODEOUT", 0)

            Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int, 0)
            ec.Direction = ParameterDirection.Output

            'Open the connection
            objConn.Open()

            'Insert the info
            cmd.ExecuteNonQuery()

            If ec.Value = 2627 Then
                cs.RegisterStartupScript(Me.GetType, "zomaar", "alert('One or more members of this family already has a profile. Contact Slam Jammers Site Administrator at 254-493-3116 for assistance.');", True)
                objConn.Close()
                Exit Sub
            End If

Open in new window

0
 
8080_DiverCommented:
Once you execute the SP, you need to assign the value from the parameter to a variable.  (That's the equivalent of reading the results in the parameter.)  You are testing for the RETURN value and not for the OUTPUT parameter value.

I usually set the ErrorCode parameter to a 0 and then test for the results being non-zero.  If you get back a zero, then the stored procedure should have run successfully.
0
 
wdarnellgAuthor Commented:
Can you give me an example?
0
 
8080_DiverCommented:
Not really.  The only VB code I write is involvede with SSIS packages.

That being said, check out this link:
Using Ouput Parameters in Stored Procedures
0
 
wdarnellgAuthor Commented:
Thanks for the article. It makes sense but I am not getting the error code from the stored proc. I should be as I am entering duplicate data. I guess the good news is that the duplicates are not inserting, that is the constraint works on sql server, but my vb still doesn't get a value in the @errorcodeout parameter.

I believe I have followed the example correctly.

Anyway, I think for the sql server part at least, this question has been accurately answered 8080 Diver.


Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int)
            ec.Direction = ParameterDirection.Output
            cmd.Parameters.AddWithValue("@ERRORCODEOUT", 0)
            'Open the connection
            objConn.Open()

            'Insert the info
            cmd.ExecuteNonQuery()
            'Get the error code if it exists
            Dim reader As SqlDataReader = cmd.ExecuteReader
            reader.GetInt32(ec.Value)

            If ec.Value = 2627 Then
                cs.RegisterStartupScript(Me.GetType, "zomaar", "alert('One or more members of this family already has a profile. Contact Slam Jammers Site Administrator at 254-493-3116 for assistance.');", True)
                objConn.Close()
                Exit Sub
            End If

Open in new window

0
 
8080_DiverCommented:
Are you sure that you are setting the ErrorCodePOut to the error code correctly?

I would suggest adding some PRINT statements (e.g. PRINT #ERROR; PRINT @ERRORCODEOUT; within the stored procedure right after the line that is supposed to set the @ERRORCODEOUT parameter's value.  Then execute the SP manually from Visual Studio and check the Output tab to see what printed.
0
 
wdarnellgAuthor Commented:
The Visual Studio output was: (0 row(s) returned)
@ERRORCODEOUT = <NULL>
@RETURN_VALUE =
Finished running [dbo].[sj_AddFamilyInfo].
The thread 'mydarn-pc [63]' (0xd98) has exited with code 0 (0x0).
The program '[2968] [SQL] mydarn-pc: mydarn-pc' has exited with code 0 (0x0).
0
 
wdarnellgAuthor Commented:
I must have something wrong since VStudio is not able to read the error code. I get error code 2627 in ssms.
ALTER PROCEDURE [dbo].[sj_AddFamilyInfo] 
	-- Add the parameters for the stored procedure here
	-- First Parent
	@pFirstName nvarchar(20),
	@pLastName nvarchar(20),
	@pGender nvarchar(6),
	@pBirthdate date,
	@pEthnicity nvarchar(50),
	@pMaritalStatus nvarchar(20),
	@pPhone1 nvarchar(14),
	@pPhone2 nvarchar(14),
	@pPhone3 nvarchar(14),
	@pAddress1 nvarchar(50),
	@pAddress2 nvarchar(50),
	@pCity nvarchar(50),
	@pState nvarchar(2),
	@pZip nvarchar(11),
	@pEmail nvarchar(175),
	@pEducation nvarchar(50),
	@pIncome nvarchar(50),
	-- Second Parent
	@opFirstName nvarchar(20) = NULL,
	@opLastName nvarchar(20)= NULL,
	@opGender nvarchar(6)= NULL,
	@opBirthdate date = NULL,
	@opEthnicity nvarchar(50) = NULL,
	@opMaritalStatus nvarchar(20) = NULL,
	@opPhone1 nvarchar(14) = NULL,
	@opPhone2 nvarchar(14) = NULL,
	@opPhone3 nvarchar(14) = NULL,
	@opAddress1 nvarchar(50) = NULL,
	@opAddress2 nvarchar(50) = NULL,
	@opCity nvarchar(50) = NULL,
	@opState nvarchar(2) = NULL,
	@opZip nvarchar(11) = NULL,
	@opEmail nvarchar(175) = NULL,
	@opEducation nvarchar(50) = NULL,
	@opIncome nvarchar(50) = NULL,
	-- First Child
	@LocationPref nvarchar(50),
	@JrFirstName nvarchar(20),
	@JrLastName nvarchar(20),
	@JrGender nvarchar(6),
	@JrBirthdate date,
	@JrEthnicity nvarchar(20),
	-- Second Child
	@SecondLocationPref nvarchar(50) = NULL,
	@SecondJrFirstName nvarchar(20) = NULL,
	@SecondJrLastName nvarchar(20) = NULL,
	@SecondJrGender nvarchar(6) = NULL,
	@SecondJrBirthdate date = NULL,
	@SecondJrEthnicity nvarchar(20) = NULL,
	-- Third Child
	@ThirdLocationPref nvarchar(50) = NULL,
	@ThirdJrFirstName nvarchar(20) = NULL,
	@ThirdJrLastName nvarchar(20) = NULL,
	@ThirdJrGender nvarchar(6) = NULL,
	@ThirdJrBirthdate date = NULL,
	@ThirdJrEthnicity nvarchar(20) = NULL
-- NEW LINES START
	,@ERRORCODEOUT INT OUTPUT               
	
-- NEW LINES END
	
AS
TRY

--MAIN STORED PROC


END TRY

BEGIN CATCH
 SELECT  @ERRORCODEOUT = ERROR_NUMBER()
 
PRINT @ERRORCODEOUT;


ROLLBACK
END CATCH

Open in new window

0
 
8080_DiverCommented:
@PAQ_Man,

You might probably should have expanded to invlude VB.Net rather than more SQL Server zones.  ;-)

wdarnellg,

I am a bit puzzled by your not getting anything printed in the Output area from the PRINT @ERRORCODEOUT;
line.

Did the Transaction get rolled back?

Since you have so many parameters that you are passing in, it will be hard to execute the stored procedure manually (i.e. open it in VS and then execute it) because you will have to supply all of the parameters' values.  However, you may have to do that so that you can look at the Output tab's contents to see what is happening.

(I had forgotten how many parameters you are passing in when I suggested puting the PRINT statement in to print the contents of the @ERRORCODEOUT variable.

Here is another link that may provide a more workable solution:

Return Error Code and Msg via Raise Error
0
 
srikanthreddyn143Commented:
Try something like this

ALTER SP AS


TRY

--MAIN STORED PROC


END TRY
BEGIN CATCH
 SET @ERRORCODEOUT = ERROR_NUMBER()
     IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;


0
 
srikanthreddyn143Commented:
Small Correction at the top

ALTER SP AS

BEGIN TRANSACTION;
BEGIN TRY

--MAIN STORED PROC


END TRY
BEGIN CATCH
 SET @ERRORCODEOUT = ERROR_NUMBER()
     IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
0
 
wdarnellgAuthor Commented:
@srikanthreddy,
I tried it the way you suggested with the exact same behavior... on ssms the sp processes fine. I get new records when I have fresh data and I get the error code of 2627 when I try to add duplicate data. However, I am not able to get anything to return to the vb sqldatareader variable.
Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int)
            ec.Direction = ParameterDirection.Output
            cmd.Parameters.AddWithValue("@ERRORCODEOUT", 0)
            'Open the connection
            objConn.Open()

            'Insert the info
            cmd.ExecuteNonQuery()
            Dim reader As SqlDataReader = cmd.ExecuteReader
            reader.GetInt32(ec.Value)

            If ec.Value = 2627 Then
               custom error message
                objConn.Close()
                Exit Sub
            End If

            'Close the connection
            objConn.Close()

Open in new window

0
 
8080_DiverCommented:
I am not really a VB.net coder; however, I think that you are trying to read from the datareader when you should be accessing the parameter of the datareader.  Try something more like:
Dim ErrorCodeOut int = cmd.parameters.parameterbyname("@ERRORCODEOUT").value

Open in new window


Note: that is probably not syntactically correct and I know that but reread the first sentence in this post. ;-)
0
 
srikanthreddyn143Commented:
As 8080 Driver said,

Below is the one with data type casting.

Dim avgPrice as Integer= Convert.ToInt32(ec.Value)

And what is the value you are getting in ec.value?
0
 
srikanthreddyn143Commented:
Ok ...Found the issue.

Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int)
            ec.Direction = ParameterDirection.Output
            cmd.Parameters.Add(ec)
            'Open the connection
            objConn.Open()

            'Insert the info
            cmd.ExecuteNonQuery()
            Dim reader As SqlDataReader = cmd.ExecuteReader
            Dim ErrorCode as Integer= Convert.ToInt32(ec.Value)

            If ErrorCode = 2627 Then
               custom error message
                objConn.Close()
                Exit Sub
            End If

            'Close the connection
            objConn.Close()

0
 
srikanthreddyn143Commented:
In the above code, you were adding a new parameter '@ERRORCODEOUT' again instead of using output parameter ec

Dim ec As New SqlParameter("@ERRORCODEOUT", SqlDbType.Int)
            ec.Direction = ParameterDirection.Output

Instead of this-->            cmd.Parameters.AddWithValue("@ERRORCODEOUT", 0)

I wrote this-->cmd.Parameters.Add(ec)

AndAlso you can remove --> Dim reader As SqlDataReader = cmd.ExecuteReader,  As reader is used to get the information.
0
 
wdarnellgAuthor Commented:
Thanks Fellas! This is what I wanted. Everything is well behaved now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 13
  • 10
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now