Jump to content

User Ownership of a specific row


aswhalen

Recommended Posts

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

Edited by aswhalen
Link to comment
Share on other sites

It doesn't look like you're adding the user ID when you add the record to the geneology table, you need to include the user ID when you add those records. Once you know which records belong to which users then it should be pretty straight-forward. You can build an editor page to get the current user's records and let them edit those, and on the pages that show the records you just wouldn't have a form or anything to update them, you would just show the data.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...