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.
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
GOASKER
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.
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
GOASKER
Thank you so much.
ASKER
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?
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
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.
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.
ASKER
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)
ENDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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!!
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!!
ASKER
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.
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.
ASKER
This is the code I wound up using in VB. The sql code was unchanged.
Thanks so much!
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 IfASKER
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.
ASKER
Thanks for making this as simple as possible.
DECLARE @val1 int
INSERT INTO ......
VALUES (......)
SELECT @val1 = SCOPE_IDENTITY()
modifying your sp as this will solve your problem.