?
Solved

SQL Server 2008R2 Transaction

Posted on 2011-04-25
31
Medium Priority
?
305 Views
Last Modified: 2012-06-21
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
Comment
Question by:wdarnellg
  • 13
  • 10
  • 5
  • +1
29 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35464740
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
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 1000 total points
ID: 35467028
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
 

Author Comment

by:wdarnellg
ID: 35467844
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 35468779
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
 

Author Comment

by:wdarnellg
ID: 35472832
Could you show me how to set that up? I am doing something wrong. I am not getting any error output.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35488047
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
 

Author Comment

by:wdarnellg
ID: 35495968
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
 

Author Comment

by:wdarnellg
ID: 35506959
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35506995
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
 

Author Comment

by:wdarnellg
ID: 35507328
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35507480
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
 

Author Comment

by:wdarnellg
ID: 35508033
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
 

Author Comment

by:wdarnellg
ID: 35508040
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35508091
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
 

Author Comment

by:wdarnellg
ID: 35508125
Can you give me an example?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35508638
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
 

Author Comment

by:wdarnellg
ID: 35510401
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35513619
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
 

Author Comment

by:wdarnellg
ID: 35515678
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
 

Author Comment

by:wdarnellg
ID: 35515771
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35517493
@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
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 35693413
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
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 35693420
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
 

Author Comment

by:wdarnellg
ID: 35695882
@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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35698209
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
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 35698270
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
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 35698298
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
 
LVL 11

Accepted Solution

by:
srikanthreddyn143 earned 1000 total points
ID: 35698336
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
 

Author Closing Comment

by:wdarnellg
ID: 35698615
Thanks Fellas! This is what I wanted. Everything is well behaved now.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question