|
Hi All,
Im sure this is very simple but after googling for the past 4 hours i cannot seem to find the correct syntax.
I currenly have a SQL which amongst other tables has a DateTime Coloum in the folllowing Format:
2013-09-11 21:06:08:970
I am currently running the follow query from within my C# Application:
select * from table where errorcode = 448
What I would like to do is:
select * from table where errorcode = 448, where date = today
and also
select * from table where errorcode = 448, where date is between 2013-09-11 00:00:00:000 and 2013-09-11 23:59:59:999
I just cant figure out the correct syntax for the queries though.
Could anybody possible Help?
Many Thanks
SG
|
|
|
|
|
Ah, see this where to post it, not the C# forum, and now you've cross-posted. Very naughty.
|
|
|
|
|
Member 10266943 wrote: Im sure this is very simple but after googling for the past 4 hours i cannot seem to find the correct syntax.
The syntax is defined in the manual. Google is a general search-engine, and whilst it's helpfull when researching a new topic, it's not the place to be when you simply need a manual.
Member 10266943 wrote: What I would like to do is:
select * from table where errorcode = 448, where date = today
You'd need to pick up a book on SQL. A statement has a single WHERE clause, and we add in more filters using the AND keyword. Something similar to below;
SELECT col1, col2
FROM sometable
WHERE errorcode = 448
AND somedate = GETDATE() There shouldn't be any columns in the table named "date" as it's a non-descriptive name. Also, "table" and "date" are reserved keywords.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm sure I don't understand the question because if you query using "WHERE [date] = '%2013-09-11%'" without any time appendix dangling from it the return will give all strings of the wildcarded nature.
Incidently, noone knows the storage datatype of [date] but the programmer who coded the procedure.
Member 10266943 wrote: the folllowing Format
Really?
Member 10266943 wrote: 2013-09-11 21:06:08:970
Check the format in Object Viewer ...
|
|
|
|
|
|
hello
Im looking for that information on the design and realization of BSC BTS site that can help me
(creates a sql server data or Oracl)
|
|
|
|
|
Member 10259811 wrote: Im looking for that information
www.google.com[^].
Veni, vidi, abiit domum
|
|
|
|
|
|
what do i require to make an object-oriented database which links to a website?
fledep
|
|
|
|
|
If you do just a smidgen of research like typing into Google [^] you will find there is plenty of information easily available without someone having to retype it out for you.
Read the guidelines, do some research and then ask sensible questions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
fledep wrote: object-oriented database which links to a website?
You probably don't. Presumably however that you actually want a website that uses a object oriented database then you do the following
1. Research creating websites
2. Write a website
3. Research object oriented databases.
4. Pick a object oriented database
5. Write a interface layer in 2 to use 4
6. Test, test, test.
|
|
|
|
|
hello everyone,
i am using vb.net2003 ( for a windows application ) along with sql server 2005 i need to display data of a sql procedure whose columns are being generated dynamically. The problem arise when i want to give header of flexgrid as i am not able to find out how many sql column will be retrieved/generated after the execution of the procedure and what their headers will be (i.e column name as i am using pivot to generate column from a temp table in which n number of distinct values can appear as rows, which will be used in pivot for which n number of column fields will be generated accordingly ). As i have to give header of grid and number of column is unknown , i am struck here...Plz help
ashish sharma
|
|
|
|
|
The short answer is if the columns are being generated dynamically and you don't know what columns will be there, guess what neither will .Net be able to work this out!
However if you return the results of the stored procedure into a Datatable you will be able to iterate through the Columns collection -> clickety[^]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: , guess what neither will .Net be able to work this out!
Most modern databases provide a way to query for most or even all of the meta data associated with the database.
SQL Server provides a way to query for table column names.
|
|
|
|
|
The OP says:
"i need to display data of a sql procedure whose columns are being generated dynamically"
Because the columns are being generated "dynamically" there is no way to retrieve the meta data relating to the SP before the SP is run.
Also meta data relating to SPs pretty much only extends to getting the input parameters.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
GuyThiebaut wrote: "i need to display data of a sql procedure whose columns are being generated dynamically"
Misread that. I thought they were using tables which had been created by some runtime process.
|
|
|
|
|
You've got a good point there - as there is some ambiguity in the word dynamic.
I just answered the question based on how I dynamically generate columns in SPs - so yes,. if the columns were generated through the .net code or based on some meta data in a table it would be possible to know information regarding the columns before they were generated.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Please don't crosspost, and don't repeat a question without additional information.
The FlexGrid is part of Visual Studio 6. It should die along with VB6. In VB.NET, one would use the DataGridView. Once you fetch the data, you can get the schema information from the datareader.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
If you have a temporary table you can use
select * from tempdb.sys.columns where object_id = object_id('tempdb..#yourtable');
you can create a header.
From here there are a couple of ways to handle the data and not being very good at VB.NET I will let you handle that.
|
|
|
|
|
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:
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:
CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
(
@FamilyName char(200),
<a href="/Members/firstname">@FirstName</a> char(200),
@MiddleName1 char(200),
@MiddleName2 char(200),
@MiddleName3 char(200),
<a href="/Members/Gender">@Gender</a> char(10),
<a href="/Members/dob">@DOB</a> varchar(20),
<a href="/Members/cob">@COB</a> char(200),
<a href="/Members/sob">@SOB</a> char(200),
<a href="/Members/Coob">@COOB</a> char(200),
<a href="/Members/newsletter">@Newsletter</a> char(200),
@DateTimeGenealogy DateTime
)
AS
BEGIN
INSERT INTO aspnet_Genealogy (FamilyName, FirstName, MiddleName1, MiddleName2, MiddleName3, Gender, DOB, COB, SOB, COOB, Newsletter, DateTimeGenealogy)
VALUES (@FamilyName, <a href="/Members/firstname">@FirstName</a>, @MiddleName1, @MiddleName2, @MiddleName3, <a href="/Members/Gender">@Gender</a>, <a href="/Members/dob">@DOB</a>, <a href="/Members/cob">@COB</a>, <a href="/Members/sob">@SOB</a>, <a href="/Members/Coob">@COOB</a>, <a href="/Members/newsletter">@Newsletter</a>, @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:
<%@ 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:
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("<a href="/Members/firstname">@FirstName</a>", 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("<a href="/Members/Gender">@Gender</a>", SqlDbType.Char).Value = Gender.Text.Trim();
cmd.Parameters.Add("<a href="/Members/dob">@DOB</a>", SqlDbType.VarChar).Value = DOB.Text.Trim();
cmd.Parameters.Add("<a href="/Members/cob">@COB</a>", SqlDbType.Char).Value = COB.Text.Trim();
cmd.Parameters.Add("<a href="/Members/sob">@SOB</a>", SqlDbType.Char).Value = SOB.Text.Trim();
cmd.Parameters.Add("<a href="/Members/Coob">@COOB</a>", SqlDbType.Char).Value = COOB.Text.Trim();
cmd.Parameters.Add("<a href="/Members/newsletter">@Newsletter</a>", 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 
modified 11-Sep-13 14:42pm.
|
|
|
|
|
One way of implementing this would be to create a view with a "where clause" that restricts the rows returned to the specific user. You would then grant "select" access to only those views, not directly to the tables themselves.
Just a thought.
Remember to vote. 
|
|
|
|
|
Do you think this would work:
I have to views, for two tables, aspnet_Users and aspnet_Genealogy
I have the views already connect to the username which would the login, it shows all users with the one test data row which is the only entry at this point in time. Now would this "where clause" work,
UPDATE aspnet_genealogy
SET FamilyName, FirstName, MiddleName1
WHERE UserName=LoginStatus1;
and then a select,
SELECT UserName, FamilyName, FirstName, MiddleName1
FROM aspnet_Genealogy;
Do you think this would work?
Thanks
|
|
|
|
|
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:
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:
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:
<%@ 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:
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
|
|
|
|
|
Member 9142936 wrote: Can any one help?
Does the user need to be the physical owner of the data? That implies that each potential owner is in the AD.
Does the user need to be "owner" in order to "not show" the data to other people besides the user?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
The user/member 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 Users/Members, on a global basis but they can not edit the information, only select the data to become of their profile.
Thanks for your quick response and look forward to hearing from you soon.
Thanks
modified 12-Sep-13 10:01am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
