Click here to Skip to main content
15,912,329 members
Search within:


Home / Discussions / Database
   

Database

 
FirstPrevNext
AnswerRe: MySQL - Can't create Visit table Pin
Richard Deeming16-Sep-13 2:25
mveRichard Deeming16-Sep-13 2:25 
QuestionSQL Query Help! Pin
Member 1026694314-Sep-13 16:33
Member 1026694314-Sep-13 16:33 
AnswerRe: SQL Query Help! Pin
PIEBALDconsult14-Sep-13 17:39
mvePIEBALDconsult14-Sep-13 17:39 
AnswerRe: SQL Query Help! Pin
Eddy Vluggen15-Sep-13 2:02
professionalEddy Vluggen15-Sep-13 2:02 
AnswerRe: SQL Query Help! Pin
RedDk15-Sep-13 12:08
RedDk15-Sep-13 12:08 
AnswerRe: SQL Query Help! Pin
Malte Klena16-Sep-13 1:55
Malte Klena16-Sep-13 1:55 
QuestionHelp Me Pin
Member 1025981112-Sep-13 23:58
professionalMember 1025981112-Sep-13 23:58 
AnswerRe: Help Me Pin
Richard MacCutchan12-Sep-13 23:58
mveRichard MacCutchan12-Sep-13 23:58 
AnswerRe: Help Me Pin
Amol_B24-Oct-13 2:29
professionalAmol_B24-Oct-13 2:29 
QuestionObject Oriented Database Pin
fledep12-Sep-13 20:17
fledep12-Sep-13 20:17 
AnswerRe: Object Oriented Database Pin
Mycroft Holmes12-Sep-13 21:54
professionalMycroft Holmes12-Sep-13 21:54 
AnswerRe: Object Oriented Database Pin
jschell13-Sep-13 11:46
jschell13-Sep-13 11:46 
QuestionRetreive Data from sql server along with headers or column name Pin
ashu200112-Sep-13 1:24
ashu200112-Sep-13 1:24 
AnswerRe: Retreive Data from sql server along with headers or column name Pin
GuyThiebaut12-Sep-13 2:39
professionalGuyThiebaut12-Sep-13 2:39 
GeneralRe: Retreive Data from sql server along with headers or column name Pin
jschell12-Sep-13 8:26
jschell12-Sep-13 8:26 
GeneralRe: Retreive Data from sql server along with headers or column name Pin
GuyThiebaut12-Sep-13 8:30
professionalGuyThiebaut12-Sep-13 8:30 
GeneralRe: Retreive Data from sql server along with headers or column name Pin
jschell13-Sep-13 11:23
jschell13-Sep-13 11:23 
GeneralRe: Retreive Data from sql server along with headers or column name Pin
GuyThiebaut13-Sep-13 23:27
professionalGuyThiebaut13-Sep-13 23:27 
AnswerRe: Retreive Data from sql server along with headers or column name Pin
Eddy Vluggen12-Sep-13 2:58
professionalEddy Vluggen12-Sep-13 2:58 
AnswerRe: Retreive Data from sql server along with headers or column name Pin
Corporal Agarn12-Sep-13 3:04
professionalCorporal Agarn12-Sep-13 3:04 
QuestionUser ownership of a specific data row? Pin
Member 914293611-Sep-13 5:27
Member 914293611-Sep-13 5:27 
AnswerRe: User ownership of a specific data row? Pin
David Mujica11-Sep-13 5:32
David Mujica11-Sep-13 5:32 
GeneralRe: User ownership of a specific data row? Pin
Member 914293611-Sep-13 5:55
Member 914293611-Sep-13 5:55 
GeneralRe: User ownership of a specific data row? Pin
Member 914293611-Sep-13 8:48
Member 914293611-Sep-13 8:48 
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 am trying to associate a Member with record that they created through a form that I have on the my site. I want the record to be associated with members profile as well as could be searched and selected by another member, but only a read only to this member who didn't create it but be able to select as part of his membership profile in a read only capacity.

I have two tables, aspnet_Users and aspnet_Genealogy, the code for the two tables are as follows:

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 SP code is for these tables, aspnet_AddCenealogy and aspnet_UserGenealogy are as follows:

SQL
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 have been two webpage that have been created Default.aspx and ResultsMembers.aspx, the code for these pages:

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>

<%@ 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:

C#
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();

        }

    }
}

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 must fill out that form, this is the data I want to associate with member and also allow other members to search the aspnet_Genealogy table and be able to select and update their member profile. Only the member will be able to edit the profile that he created at the time of creation, it will be read only to other members other than selecting it to be added to their profile.

I have also a views code which is as follows:

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 to vw_aspnet_Genealogy and joined aspnet_User, UserName, at which the results are showing all the UserName being associated with the one record that is in the aspnet_Genealogy table. I want to associate only the member that enter the original record and only have the record designated member who selects it through a search as part of a member profile that is read only, this member will not be the originator of the record.

Can any one help?

Thanks Wink | ;) Wink | ;) Wink | ;)
QuestionRe: User ownership of a specific data row? Pin
Eddy Vluggen12-Sep-13 3:01
professionalEddy Vluggen12-Sep-13 3:01 
Last Visit: 31-Dec-99 18:00     Last Update: 5-Jun-24 10:31Refreshᐊ Prev1...245246247248249250251252253254 Next ᐅ

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise
Privacy
Cookies
Terms of Use
Last Updated 13 May 2024
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-05-30:1