I am trying to give my user ownership of specific data row that they enter information into, but as well as allowing other users to view and select from. I mean the information will in the database will be able to be seen globally but can also have the information designated to that user. I am using asp.net C# and MS SQL Management Studios, can anyone help, I believe this will have to be in SQL..
Thanks Smile | :)
I have a User logging into my website, the User fills out a form and gets a result, I have two tables that are related to a aspnet_Users and aspnet_Genealogy, I trying to have the UserName from aspnet_Users related to the Genealogy data enter in the table aspnet_Genealogy, but at the same time the data in the aspnet_Genealogy table being to be access by other Users. This means that the data will be own by the User and well as being able to be viewed and selected by other Users but be able to edited only the originated Users.
My tables are as follows:
<pre lang="SQL">
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 Stored Procs are as follows:
The Insert SP:
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
The Join SP is:
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
The Default.aspx is the page that Users/Members enters there data. The code for this is:
<pre lang="c#">
<%@ 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 FREE:<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>
The code for Default.aspx.cs is:
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();
}
}
}
I used the code for the results page, ResultsMembers.aspx, which is:
<%@ 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 behind the resultsMembers.aspx.cs which is:
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 resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record.
I am fairly new to asp.net and sql, can anyone help?
Thanks
Submit an article or tip