Insert 2 Parents and a Child

I have an asp.net application that uses a wizard control to collect information from 1 or 2 parents and a child. Whether there is one or two parents is contingent on a yes or no selection in a dropdownlist. I don't know how to set up a sql server stored procedure that will insert the first parent's information, collect the created identity, check for a second parent, insert, capture the created id, and then apply the ids to the foreign key fields of the child's information and insert.

I started a stored proc, but then got stuck. Any help would be greatly welcomed.
CREATE PROCEDURE dbo.sj_AddFamilyInfoFromWebForm 
	-- Add the parameters for the stored procedure here
	@FirstName nvarchar(20), @LastName nvarchar(20), @Gender nvarchar(6), @Birthdate date,
	@Ethnicity nvarchar(50), @MaritalStatus nvarchar(20), @Phone1 nvarchar(14), @Phone2 nvarchar(14), @Phone3 nvarchar(14),
	@Address1 nvarchar(50), @Address2 nvarchar(50), @City nvarchar(50), @State nvarchar(2), @Zip nvarchar(11), @Email nvarchar(175),
	@Education nvarchar(50), @Income nvarchar(50)
	
AS
BEGIN 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [dbo].[sj_ParentGuardian]
           ([FirstName],[LastName],[Gender],[Birthdate],[Ethnicity],[MaritalStatus]
           ,[Phone1],[Phone2],[Phone3],[Address1],[Address2],[City],[State],[Zip]
           ,[Email],[Education],[Income])
     VALUES
           (@FirstName, @LastName, @Gender, @Birthdate, @Ethnicity, @MaritalStatus, @Phone1, @Phone2, @Phone3,
			@Address1, @Address2, @City, @State, @Zip, @Email, @Education, @Income)




END 
GO

Open in new window

wdarnellgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
use the SCOPE_IDENTITY() to get the id value of the newly inserted record... i.e

DECLARE @val1      int

INSERT INTO ......
VALUES (......)
SELECT @val1 = SCOPE_IDENTITY()

modifying your sp as this will solve your problem.
0
wdarnellgAuthor Commented:
Ok, so it works out to look something like this when I try to do it. Obviously there are several syntax errors.

Another thing is that there won't always be a second parent record, just sometimes when there are two parents or guardians instead of a single parent. I need some help checking that there is just one record to add and only inserting a single @Val1 = SCOPE_IDENTITY.
CREATE PROCEDURE dbo.sj_AddFamilyInfoFromWebForm 
	-- Add the parameters for the stored procedure here
	@FirstName nvarchar(20), @LastName nvarchar(20), @Gender nvarchar(6), @Birthdate date,
	@Ethnicity nvarchar(50), @MaritalStatus nvarchar(20), @Phone1 nvarchar(14), @Phone2 nvarchar(14), @Phone3 nvarchar(14),
	@Address1 nvarchar(50), @Address2 nvarchar(50), @City nvarchar(50), @State nvarchar(2), @Zip nvarchar(11), @Email nvarchar(175),
	@Education nvarchar(50), @Income nvarchar(50), @Val1 int, @Val2 int
	
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].[sj_ParentGuardian]
           ([FirstName],[LastName],[Gender],[Birthdate],[Ethnicity],[MaritalStatus]
           ,[Phone1],[Phone2],[Phone3],[Address1],[Address2],[City],[State],[Zip]
           ,[Email],[Education],[Income])
     VALUES
           (@FirstName, @LastName, @Gender, @Birthdate, @Ethnicity, @MaritalStatus, @Phone1, @Phone2, @Phone3,
			@Address1, @Address2, @City, @State, @Zip, @Email, @Education, @Income)

	SELECT @Val1 = SCOPE_IDENTITY()
	
	GO
	    -- Insert statement for second parent record
	INSERT INTO [dbo].[sj_ParentGuardian]
           ([FirstName],[LastName],[Gender],[Birthdate],[Ethnicity],[MaritalStatus]
           ,[Phone1],[Phone2],[Phone3],[Address1],[Address2],[City],[State],[Zip]
           ,[Email],[Education],[Income])
     VALUES
           (@FirstName, @LastName, @Gender, @Birthdate, @Ethnicity, @MaritalStatus, @Phone1, @Phone2, @Phone3,
			@Address1, @Address2, @City, @State, @Zip, @Email, @Education, @Income)

	SELECT @Val2 = SCOPE_IDENTITY()
	
	GO
	-- Insert statement for child record
	DECLARE 
		@LocationPref nvarchar(50), @FirstName nvarchar(20), @LastName nvarchar(20), @Gender nvarchar(6), @Birthdate date,
		@Ethnicity nvarchar(20),@ParentID int, @OtherParentID int
		
		SET 
		@ParentID = @Val1, @OtherParentID = @Val2
				
	INSERT INTO [dbo].[sj_Players]
		([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
	VALUES
		(@FirstName, @LastName, @Gender, @Birthdate, @Ethnicity, @ParentID, @OtherParentID)
		
END 
GO

Open in new window

0
wdarnellgAuthor Commented:
Thank you so much.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

wdarnellgAuthor Commented:
I figured it out! I used two different stored procedures and call the appropriate one based on the selection made by the user. My vb and sql code seems to be working well.

On successfully completing this bit of coding I encountered a problem. I don't have a way for the same parent to add more children using the same application. If anyone can help with this I am considering it part of the same issue, or should I ask this as a different question?
SQL CODE------------------------------------
	-- Add the parameters for the stored procedure here
	@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), @opFirstName nvarchar(20), @opLastName nvarchar(20), @opGender nvarchar(6), @opBirthdate date,
	@opEthnicity nvarchar(50), @opMaritalStatus nvarchar(20), @opPhone1 nvarchar(14), @opPhone2 nvarchar(14), @opPhone3 nvarchar(14),
	@opAddress1 nvarchar(50), @opAddress2 nvarchar(50), @opCity nvarchar(50), @opState nvarchar(2), @opZip nvarchar(11), @opEmail nvarchar(175),
	@opEducation nvarchar(50), @opIncome nvarchar(50), @LocationPref nvarchar(50), @JrFirstName nvarchar(20), @JrLastName nvarchar(20), @JrGender nvarchar(6), @JrBirthdate date,
	@JrEthnicity nvarchar(20)
	
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].[sj_ParentGuardian]
           ([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
	INSERT INTO [dbo].[sj_ParentGuardian]
           ([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)

	DECLARE @JrOtherParentID int
	SELECT @JrOtherParentID = SCOPE_IDENTITY()	
	
	-- Insert statement for child record		
				
	INSERT INTO [dbo].[sj_Players]
		([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
	VALUES
		(@LocationPref, @JrFirstName, @JrLastName, @JrGender, @JrBirthdate, @JrEthnicity, @JrParentID, @JrOtherParentID)
		
END 



VB CODE--------------------------

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
            'Declare the Family Info variables
            If PartnerExistsDropDownList.SelectedValue = "No" Then
                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 = PEmailTextBox.Text
                Dim pIncome As String = PIncomeDropDownList.SelectedValue.ToString
                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

                'Insert the Single Parent Family Information
                If PartnerExistsDropDownList.SelectedValue = "No" Then
                    'Open Connection
                    cmd.CommandText = "sj_AddSingleParentFamilyInfoFromWebForm"
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Connection = objConn

                    'Declare the Parent parameters
                    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)

                    '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)

                    'Open the connection
                    objConn.Open()

                    'Insert the info
                    cmd.ExecuteNonQuery()

                    'Close the connection
                    objConn.Close()


                    System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">alert('The Family Info Has been Recorded')</SCRIPT>")

                End If

                'Insert the Parent Guardian - Other Parent Guardian and Junior Player information
            ElseIf PartnerExistsDropDownList.SelectedValue = "Yes" Then
                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 = PEmailTextBox.Text
                Dim pIncome As String = PIncomeDropDownList.SelectedValue.ToString
                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 = OPEmailTextBox.Text
                Dim opIncome As String = OPIncomeDropDownList.SelectedValue.ToString
                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



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

                'Declare the Parent parameters
                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)
                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)

                '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)

                'Open the connection
                objConn.Open()

                'Insert the info
                cmd.ExecuteNonQuery()

                'Close the connection
                objConn.Close()


                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">alert('The Family Info Has been Recorded')</SCRIPT>")


            End If


        Catch ex As Exception
            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">alert(" + ex.ToString + ")</SCRIPT>")
        End Try
    End Sub

Open in new window

0
Brendt HessSenior DBACommented:
Hmmm.... conceptually, you might need a "FamilyGroup" record level, saying that these N parents apply  to a specified family grouping, then the parents, then the children that apply to those parents.  I'm specifically thinking of complex cases where a single parent is a co-parent with two or more different former spouses.  However, I'll address your questions directly as you posed them.

In the original case, your second set of parental parameters should have had the " = NULL" option on the @variables.  Then, a simple IF @opFirstName IS NULL check allows you to use the same procedure for entering one or two parents.

As for adding additional children to a given parent - that implies a ParentGuardianID ... which I see you have called @JrParentID and @JrOtherParentID in your procedure.  You will need to return one or both of these IDs to the application for reuse.  Pass it in to your proc if you have it (pass NULL if you do not), and have the proc check that the parent's info matches.  If it does not, update the record.  If it does, ignore it.  If there is no @jrParentID passed in, add the record and get the ID value.
0
wdarnellgAuthor Commented:
Thank you for the response. It comes just a few minutes after I spent a couple of hours typing, copying-pasting a solution that seems to work perfectly. The NULL values were not a problem, but the empty textbox for the date data types were. I had to copy the parameters into various if statements and write 5 different Stored procs. I call the appropriate sp depending on the number of parents and children records to be inserted. I'll post the code because if I can be shown a simpler way to do it, I would love to learn how to use less code to get the same results.

SQL CODE 
=====================================================

    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
            'Declare the Family Info variables
            If PartnerExistsDropDownList.SelectedValue = "No" Then
                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.SelectedItem.ToString
                Dim pIncome As String = PIncomeDropDownList.SelectedValue.ToString
                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

                'Insert the Single Parent Family Information
                If PartnerExistsDropDownList.SelectedValue = "No" Then

                    If SecondJrFirstNameTextBox.Text = "" And SecondJrLastNameTextBox.Text = "" And ThirdJrFirstNameTextBox.Text = "" And ThirdJrLastNameTextBox.Text = "" Then
                        'Open Connection
                        cmd.CommandText = "sj_AddSingleParentFamilyInfoFromWebForm"
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.Connection = objConn
                        'Declare the Parent parameters
                        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)


                        '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)



                        'Open the connection
                        objConn.Open()

                        'Insert the info
                        cmd.ExecuteNonQuery()

                        'Close the connection
                        objConn.Close()

                        SJ1ApplicationMultiView.ActiveViewIndex = 7

                    ElseIf ThirdJrFirstNameTextBox.Text = "" And ThirdJrLastNameTextBox.Text = "" 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


                        'Open Connection
                        cmd.CommandText = "sj_AddSingleParentTwoChildInfoFromWebForm"
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.Connection = objConn
                        'Declare the Parent parameters
                        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)


                        '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)
                        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)

                        'Open the connection
                        objConn.Open()

                        'Insert the info
                        cmd.ExecuteNonQuery()

                        'Close the connection
                        objConn.Close()

                        SJ1ApplicationMultiView.ActiveViewIndex = 7

                    ElseIf ThirdJrFirstNameTextBox.Text = "" And ThirdJrLastNameTextBox.Text = "" 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
                        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

                        'Open Connection
                        cmd.CommandText = "TwoParentsThreeChildrenStoredProc"
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.Connection = objConn
                        'Declare the Parent parameters
                        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)


                        '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)
                        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)
                        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)

                        'Open the connection
                        objConn.Open()

                        'Insert the info
                        cmd.ExecuteNonQuery()

                        'Close the connection
                        objConn.Close()

                        SJ1ApplicationMultiView.ActiveViewIndex = 7

                    End If
                End If

                'Insert the Parent Guardian - Other Parent Guardian and Junior Player information
            ElseIf PartnerExistsDropDownList.SelectedValue = "Yes" Then
                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
                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
                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 = "" And SecondJrLastNameTextBox.Text = "" And ThirdJrFirstNameTextBox.Text = "" And ThirdJrLastNameTextBox.Text = "" Then

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

                    'Declare the Parent parameters
                    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)
                    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)

                    '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)

                    'Open the connection
                    objConn.Open()

                    'Insert the info
                    cmd.ExecuteNonQuery()

                    'Close the connection
                    objConn.Close()

                    SJ1ApplicationMultiView.ActiveViewIndex = 7

                ElseIf Not SecondJrFirstNameTextBox.Text = "" And Not SecondJrLastNameTextBox.Text = "" And ThirdJrFirstNameTextBox.Text = "" And ThirdJrLastNameTextBox.Text = "" 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

                    'Open Connection
                    cmd.CommandText = "sj_AddTwoParentFamilyTwoChildInfoFromWebForm"
                    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)
                    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)

                    '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)
                    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)

                    'Open the connection
                    objConn.Open()

                    'Insert the info
                    cmd.ExecuteNonQuery()

                    'Close the connection
                    objConn.Close()

                    SJ1ApplicationMultiView.ActiveViewIndex = 7

                ElseIf Not SecondJrFirstNameTextBox.Text = "" And Not SecondJrLastNameTextBox.Text = "" And Not ThirdJrFirstNameTextBox.Text = "" And Not ThirdJrLastNameTextBox.Text = "" 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
                    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

                    'Open Connection
                    cmd.CommandText = "TwoParentFamilyStoredProc"
                    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)
                    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)

                    '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)
                    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)
                    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)

                    'Open the connection
                    objConn.Open()

                    'Insert the info
                    cmd.ExecuteNonQuery()

                    'Close the connection
                    objConn.Close()

                    SJ1ApplicationMultiView.ActiveViewIndex = 7
                End If
            End If


        Catch ex As Exception
            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">alert(" + ex.ToString + ")</SCRIPT>")
        End Try

    End Sub


SQL CODE
=================================================================
ALTER PROCEDURE [dbo].[StoredProc] 
	-- Add the parameters for the stored procedure here
	@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), @LocationPref nvarchar(50), @JrFirstName nvarchar(20), @JrLastName nvarchar(20), @JrGender nvarchar(6), @JrBirthdate date,
	@JrEthnicity nvarchar(20), @SecondLocationPref nvarchar(50), @SecondJrFirstName nvarchar(20), @SecondJrLastName nvarchar(20), @SecondJrGender nvarchar(6), @SecondJrBirthdate date,
	@SecondJrEthnicity nvarchar(20)
	
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].[ParentTable]
           ([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
			,@JrOtherParentID int
			SET @JrOtherParentID = NULL
	SELECT @JrParentID = SCOPE_IDENTITY()	
	
	-- Insert statement for child record
	
		
				
	INSERT INTO [dbo].[sj_Players]
		([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
	VALUES
		(@LocationPref, @JrFirstName, @JrLastName, @JrGender, @JrBirthdate, @JrEthnicity, @JrParentID, @JrOtherParentID)
		
		INSERT INTO [dbo].[sj_Players]
		([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
	VALUES
		(@SecondLocationPref, @SecondJrFirstName, @SecondJrLastName, @SecondJrGender, @SecondJrBirthdate, @SecondJrEthnicity, @JrParentID, @JrOtherParentID)

END


ALTER PROCEDURE [dbo].[Stored Proc] 
	-- Add the parameters for the stored procedure here
	@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), @opFirstName nvarchar(20), @opLastName nvarchar(20), @opGender nvarchar(6), @opBirthdate date,
	@opEthnicity nvarchar(50), @opMaritalStatus nvarchar(20), @opPhone1 nvarchar(14), @opPhone2 nvarchar(14), @opPhone3 nvarchar(14),
	@opAddress1 nvarchar(50), @opAddress2 nvarchar(50), @opCity nvarchar(50), @opState nvarchar(2), @opZip nvarchar(11), @opEmail nvarchar(175),
	@opEducation nvarchar(50), @opIncome nvarchar(50), @LocationPref nvarchar(50), @JrFirstName nvarchar(20), @JrLastName nvarchar(20), @JrGender nvarchar(6), @JrBirthdate date,
	@JrEthnicity nvarchar(20), @SecondLocationPref nvarchar(50), @SecondJrFirstName nvarchar(20), @SecondJrLastName nvarchar(20), @SecondJrGender nvarchar(6), @SecondJrBirthdate date,
	@SecondJrEthnicity nvarchar(20)
	
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].[ParentTable]
           ([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
	INSERT INTO [dbo].[ParentTable]
           ([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)

	DECLARE @JrOtherParentID int
	SELECT @JrOtherParentID = SCOPE_IDENTITY()	
	
	-- Insert statement for child record
	
		
				
	INSERT INTO [dbo].[ChildTable]
		([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
	VALUES
		(@LocationPref, @JrFirstName, @JrLastName, @JrGender, @JrBirthdate, @JrEthnicity, @JrParentID, @JrOtherParentID)
		
	INSERT INTO [dbo].[ChildTable]
		([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
	VALUES
		(@SecondLocationPref, @SecondJrFirstName, @SecondJrLastName, @SecondJrGender, @SecondJrBirthdate, @SecondJrEthnicity, @JrParentID, @JrOtherParentID)
		
END

Open in new window

0
Brendt HessSenior DBACommented:
Hmmm.... Let's start with creating only one stored procedure for your chosen coding method.  That proc sample is attached as code below, and will be demonstrated in my next comment:


ALTER PROCEDURE [dbo].[Stored Proc] 
	-- 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].[ParentTable]
           ([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].[ParentTable]
			([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
	
		
	/*(
	NOTE ASSUMPTION :  IT IS ASSUMED THAT THE OtherParentID field can handle a NULL value, based on your other sample sproc.  
		If this is not true, use an ISNULL(@jjOtherParentID, <DefaultValue>) in the Insert statement below.
	*/
	INSERT INTO [dbo].[ChildTable]
		([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].[ChildTable]
			([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].[ChildTable]
			([LocationPref], [FirstName], [LastName], [Gender], [Birthdate], [Ethnicity], [ParentID], [OtherParentID])
		VALUES
			(@ThirdLocationPref, @ThirdJrFirstName, @ThirdJrLastName, @ThirdJrGender, @ThirdJrBirthdate, @ThirdJrEthnicity, @JrParentID, @JrOtherParentID)
	END
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brendt HessSenior DBACommented:
The code below should correctly perform all of the preparation you need to correctly execute the stored procedure above.  It removes much redundant code from your procedure, reducing code length from 485 to 167 lines.


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 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
			
			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
		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
		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
		ELSE IF ThirdChildExists Then
			Dim secondJrLocation As String = ThirdJrLocationPreferenceDropDownList.SelectedValue.ToString
			Dim secondJrFirst As String = ThirdJrFirstNameTextBox.Text
			Dim secondJrLast As String = ThirdJrLastNameTextBox.Text
			Dim secondJrGender As String = ThirdJrGenderDropDownList.SelectedValue.ToString
			Dim secondJrBirthdate As Date = ThirdJrBirthdateTextBox.Text
			Dim secondJrEthnicity As String = ThirdJrEthnicityDropDownList.SelectedValue.ToString
			SecondChildExists = True
			ThirdChildExists = False
		End If
		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
		End If
		
		' Now, Build up the stored proc and all of its parameters
		
                'Open Connection
                cmd.CommandText = "AddFamily"
                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
			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
			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
				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

                'Open the connection
                objConn.Open()

                'Insert the info
                cmd.ExecuteNonQuery()

                'Close the connection
                objConn.Close()

                SJ1ApplicationMultiView.ActiveViewIndex = 7
        Catch ex As Exception
            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE=""JavaScript"">alert(" + ex.ToString + ")</SCRIPT>")
        End Try

End Sub

Open in new window

0
Brendt HessSenior DBACommented:
Note that I still believe that a proc for adding one parent and a proc for adding one child would be more versatile, and would handle P parents and C children (where P and C are the number of parents and children involved) much more efficiently.  Basic structure would be

Parent Table - One parent per row
Child Table - One Child per row
ParentChildRelationship - Pointer between one parent and one child

With this structure, if there are three effective parents and seven children, no changes to the underlying data structure is needed.
0
wdarnellgAuthor Commented:
I am looking forward to testing your suggestion when I return to my dev machine later tonight.

As for adding one parent and one child, I have a form for the administrators to do exactly that. They can enter the parent and then add a child using a separate form for each. (if I understand you correctly)

The particular form we are addressing is for a user to provide their personal information and their children's from a wizard which is basically one form for them. Since the user is not 'joining' the site, I don't know any other way to keep the basic structure you have mentioned above. It would be easier if they joined the site establishing a UserID and then they could enter all of the children they wanted. But that is not how the customer wants to do things, so this is the best I could come up with, and I am looking forward to greatly reducing the code as you have shown.
I will let you know how it all flows asap!!
0
wdarnellgAuthor Commented:
Ok, the test is done and ALL SYSTEMS ARE GO!!
The only thing I needed to alter was moving the diminsion of the additional family members variables to within the IF statements of the Parameters since Visual Studio couldn't see them within the IF statements above them.
I am not sure how to get you the points... but I will try.
0
wdarnellgAuthor Commented:
This is the code I wound up using in VB. The sql code was unchanged.

Thanks so much!
Try
            'Declare Connection Information
            Dim strConn As String = ConfigurationManager.AppSettings("SiteSqlServer")
            Dim objConn As New SqlConnection(strConn)
            Dim cmd As New SqlCommand
            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 = "sj_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

Open in new window

0
wdarnellgAuthor Commented:
Upon further conversation, I was given a better option than the one I created. I think the more effecient use of code deserves the points. Thank you.
0
wdarnellgAuthor Commented:
Thanks for making this as simple as possible.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.