Jump to content

aswhalen

Members
  • Posts

    1
  • Joined

  • Last visited

aswhalen's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. 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
×
×
  • Create New...