Hi There,
I am trying to associate a Member with a specific record that they created through a form that I have on my site. I want the record to be associated with members profile, UserName, as well as can be searched and selected by another member, UserName, but only at a read only capacity.
I have two tables, aspnet_Users and aspnet_Genealogy, the code for the two tables is as follows:
CREATE TABLE [dbo].[aspnet_Users] (
[ApplicationId] UNIQUEIDENTIFIER NOT NULL,
[userId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[userName] NVARCHAR (256) NOT NULL,
[LoweredUserName] NVARCHAR (256) NOT NULL,
[MobileAlias] NVARCHAR (16) DEFAULT (NULL) NULL,
[isAnonymous] BIT DEFAULT ((0)) NOT NULL,
[LastActivityDate] DATETIME NOT NULL,
CONSTRAINT [PK__aspnet_U__1788CC4D0BC6C43E] PRIMARY KEY NONCLUSTERED ([userId] ASC)
);
GO
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index]
ON [dbo].[aspnet_Users]([ApplicationId] ASC, [LoweredUserName] ASC);
GO
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2]
ON [dbo].[aspnet_Users]([ApplicationId] ASC, [LastActivityDate] ASC);
CREATE TABLE [dbo].[aspnet_Genealogy] (
[GenealogyId] INT IDENTITY (1, 1) NOT NULL,
[FamilyName] CHAR (200) NOT NULL,
[FirstName] CHAR (200) NULL,
[MiddleName1] CHAR (200) NULL,
[MiddleName2] CHAR (200) NULL,
[MiddleName3] CHAR (200) NULL,
[Gender] CHAR (10) NULL,
[DOB] VARCHAR (20) NOT NULL,
[COB] CHAR (200) NULL,
[sOB] CHAR (200) NULL,
[COOB] CHAR (200) NULL,
[Newsletter] CHAR (10) NULL,
[DateTimeGenealogy] DATETIME NOT NULL,
[userId] UNIQUEIDENTIFIER NULL,
PRIMARY KEY CLUSTERED ([GenealogyId] ASC),
CONSTRAINT [FK_aspnet_Genealogy_aspnet_Users] FOREIGN KEY ([userId]) REFERENCES [dbo].[aspnet_Users] ([userId])
);
The SP code is for these tables, aspnet_AddCenealogy and aspnet_UserGenealogy is as follows:
CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
(
@FamilyName char(200),
@FirstName char(200),
@MiddleName1 char(200),
@MiddleName2 char(200),
@MiddleName3 char(200),
@Gender char(10),
@DOB varchar(20),
@COB char(200),
@SOB char(200),
@COOB char(200),
@Newsletter char(200),
@DateTimeGenealogy DateTime
)
AS
BEGIN
INSERT INTO aspnet_Genealogy (FamilyName, FirstName, MiddleName1, MiddleName2, MiddleName3, Gender, DOB, COB, SOB, COOB, Newsletter, DateTimeGenealogy)
VALUES (@FamilyName, @FirstName, @MiddleName1, @MiddleName2, @MiddleName3, @Gender, @DOB, @COB, @SOB, @COOB, @Newsletter, @DateTimeGenealogy)
END
CREATE PROCEDURE [dbo].[aspnet_UserGenealogy]
AS
SELECT aspnet_Users.UserName, aspnet_Genealogy.GenealogyId
FROM aspnet_Users
INNER JOIN aspnet_Genealogy
ON aspnet_Users.UserId=aspnet_Genealogy.UserId
ORDER BY aspnet_Users.UserName;
RETURN 0
There are two webpage that have been created Default.aspx and ResultsMembers.aspx, the code for these pages is:
<%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<link href="Styles/Content1.css" rel="stylesheet" />
<div id="content">
<br />
<span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: large; text-transform: uppercase"><center>Genealogy Membership Profile</center></span><br />
<br />
Please complete your "<span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold">Genealogy Membership Profile</span>" to take full advantage of your free membership, please fillout the following information below and its <b>FREE</b>:<br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: medium">Family Name:
<asp:TextBox ID="FamilyName" runat="server" Width="200px"></asp:TextBox>
<br />
First Name:
<asp:TextBox ID="FirstName" runat="server" Width="200px"></asp:TextBox>
<br />
Middle Name:
<asp:TextBox ID="MiddleName1" runat="server" Width="200px"></asp:TextBox>
<br />
Middle Name:
<asp:TextBox ID="MiddleName2" runat="server" Width="200px"></asp:TextBox>
<br />
Middle Name:
<asp:TextBox ID="MiddleName3" runat="server" Width="200px"></asp:TextBox>
<br />
Gender:
<asp:DropDownList ID="Gender" runat="server" Width="100px">
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
<br />
Date of Birth:
<asp:TextBox ID="DOB" runat="server" Width="200px"></asp:TextBox>
<br />
City of Birth:
<asp:TextBox ID="COB" runat="server" Width="200px"></asp:TextBox>
<br />
Prov. or State Birth:
<asp:TextBox ID="SOB" runat="server" Width="200px"></asp:TextBox>
<br />
Country of Birth:
<asp:TextBox ID="COOB" runat="server" Width="200px"></asp:TextBox>
<br />
Newsletter:
<asp:DropDownList ID="Newsletter" runat="server" Width="100px">
<asp:ListItem>Yes</asp:ListItem>
<asp:ListItem>No</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:Button ID="UpdateButton" runat="server" OnClick="UpdateButton_Click" Text="Update" />
<br />
<br />
</span></div>
<div id="ad"></div>
</asp:Content>
and
<%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeFile="ResultsMembers.aspx.cs" Inherits="ResultsMembers" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
<asp:BoundField DataField="FamilyName" HeaderText="FamilyName" SortExpression="FamilyName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="MiddleName1" HeaderText="MiddleName1" SortExpression="MiddleName1" />
<asp:BoundField DataField="MiddleName2" HeaderText="MiddleName2" SortExpression="MiddleName2" />
<asp:BoundField DataField="MiddleName3" HeaderText="MiddleName3" SortExpression="MiddleName3" />
<asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
<asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" />
<asp:BoundField DataField="COB" HeaderText="COB" SortExpression="COB" />
<asp:BoundField DataField="SOB" HeaderText="SOB" SortExpression="SOB" />
<asp:BoundField DataField="COOB" HeaderText="COOB" SortExpression="COOB" />
<asp:BoundField DataField="Newsletter" HeaderText="Newsletter" SortExpression="Newsletter" />
<asp:BoundField DataField="DateTimeGenealogy" HeaderText="DateTimeGenealogy" SortExpression="DateTimeGenealogy" />
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<SortedAscendingCellStyle BackColor="#FDF5AC" />
<SortedAscendingHeaderStyle BackColor="#4D0000" />
<SortedDescendingCellStyle BackColor="#FCF6C0" />
<SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" SelectCommand="SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users"></asp:SqlDataSource>
</asp:Content>
The code for the two pages, Default.aspx.cs and ResultsMembers.aspx.cs, is as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void UpdateButton_Click(object sender, EventArgs e)
{
String strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "aspnet_AddGenealogy";
cmd.Parameters.Add("@FamilyName", SqlDbType.Char).Value = FamilyName.Text.Trim();
cmd.Parameters.Add("@FirstName", SqlDbType.Char).Value = FirstName.Text.Trim();
cmd.Parameters.Add("@MiddleName1", SqlDbType.Char).Value = MiddleName1.Text.Trim();
cmd.Parameters.Add("@MiddleName2", SqlDbType.Char).Value = MiddleName2.Text.Trim();
cmd.Parameters.Add("@MiddleName3", SqlDbType.Char).Value = MiddleName3.Text.Trim();
cmd.Parameters.Add("@Gender", SqlDbType.Char).Value = Gender.Text.Trim();
cmd.Parameters.Add("@DOB", SqlDbType.VarChar).Value = DOB.Text.Trim();
cmd.Parameters.Add("@COB", SqlDbType.Char).Value = COB.Text.Trim();
cmd.Parameters.Add("@SOB", SqlDbType.Char).Value = SOB.Text.Trim();
cmd.Parameters.Add("@COOB", SqlDbType.Char).Value = COOB.Text.Trim();
cmd.Parameters.Add("@Newsletter", SqlDbType.Char).Value = Newsletter.Text.Trim();
cmd.Parameters.Add("@DateTimeGenealogy", SqlDbType.DateTime).Value = DateTime.Now.ToString();
cmd.Connection = con;
try
{
con.Open();
cmd.ExecuteNonQuery();
Server.Transfer("ResultsMembers.aspx");
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
}
and
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class ResultsMembers : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
The Default.aspx is the first page that member goes to after the new member registers, he/she must fill out that form, this is the data I want to associate with a member and also allow other members to search the aspnet_Genealogy table and be able to select and update their member profile with this role from the table, aspnet_Genealogy. The members will be able to edit the data row that he/she created at the time of creation, it will be read only to other members/UserNames other than selecting it to be added to their profile.
I have also a views file, vw_aspnet_Genealogy, the code is as follows:
1
2
CREATE VIEW [dbo].[vw_aspnet_Genealogy]
AS SELECT [FamilyName], [dbo].[aspnet_Genealogy].[FirstName], [dbo].[aspnet_Genealogy].[MiddleName1], [dbo].[aspnet_Genealogy].[MiddleName2], [dbo].[aspnet_Genealogy].[MiddleName3], [dbo].[aspnet_Genealogy].[Gender], [dbo].[aspnet_Genealogy].[DOB], [dbo].[aspnet_Genealogy].[COB], [dbo].[aspnet_Genealogy].[sOB], [dbo].[aspnet_Genealogy].[COOB], [dbo].[aspnet_Genealogy].[Newsletter], [dbo].[aspnet_Genealogy].[DateTimeGenealogy] FROM [aspnet_Genealogy]
The ResultsMembers.aspx is a gridveiw of which I assign my views table, vw_aspnet_Genealogy and joined vw_aspnet_User, UserName, at which the results are showing all the UserName being associated with the one data record that is in the aspnet_Genealogy table. I want to associate only the member that enter the original record and have all other Members/UserNames search and select from the aspnet_Genealogy table at which they will be select it to be part of their Membership/UserName Profile.
The Membership/UserName that enter the data thru the Default.aspx will be the "Owner", that means it automatically becomes part of his profile. I want that same data to be shown to other Membership/UserName, on a global basis but they can not edit the information, only select the data to become of their profile.
I am new to this, Can any one help?
Thanks