显示来自消息表的消息,其中包含来自注册表的用户详细信息 [英] show message from message table with user detail from registation table
本文介绍了显示来自消息表的消息,其中包含来自注册表的用户详细信息的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在消息表中显示来自注册表
并使用以下代码的用户详细信息。
请帮助
protected void Page_Load( object sender,EventArgs e)
{
string friend = Request.QueryString [ friend]。ToString();
string me =会话[ userid 跨度>]的ToString();
string S = ConfigurationManager.ConnectionStrings [ A1\" 跨度>]的ConnectionString。
con = new SqlConnection(S);
// 以下quiery显示userdetail和message但显示agian和agian 7time因为我在注册表中有7个用户
ad = new SqlDataAdapter( 从Message1中选择*作为a,注册为b where(b.userid = a.friend或b.userid = a.me)和(friend = + friend + and me = + me + )OR(me = + friend + and friend = + me + ),con);
ds = new DataSet();
// 以及下面的静音工作,但没有用户详细信息
// ad = new SqlDataAdapter(select * from Message1 where(friend =+ friend +and me = + me +)OR(me =+ friend +and friend =+ me +),con);
// ds = new DataSet();
ad.Fill(ds);
listview1.DataSource = ds.Tables [ 0 ];
listview1.DataBind();
解决方案
这个解决方案是在黑暗中完全刺伤,因为我不知道是什么你的项目正在尝试。
创建 TABLE dbo.Users(
UserId INT IDENTITY NOT NULL ,
用户名 VARCHAR ( 50 ) NOT NULL ,
[密码] VARCHAR ( 50 ) NOT NULL ,
NickName VARCHAR ( 50 ) NULL ,
FirstNameVARCHAR ( 50 ) NULL ,
LastName VARCHAR ( 50 ) NULL ,
[日期] DATE NULL ,
City VARCHAR ( 50 ) NULL ,
[状态] VARCHAR ( 10 ) NULL ,
CONSTRAINT PK_Users PRIMARY KEY (UserId)
)
CREATE TABLE dbo。[Messages](
MessageId INT IDENTITY NOT NULL ,
[ DateTime ] DATETIME NOT NULL ,
FromUserId INT NOT NULL ,
ToUserId INT NOT NULL ,
[消息] VARCHAR (MAX) NOT NULL ,
[状态] VARCHAR ( 50 ) NOT NULL ,
CONSTRAINT PK_Messages PRIMARY KEY (MessageId),
CONSTRAINT FK_Messages_Users_FromUserId FOREIGN KEY (FromUserId) REFERENCES dbo.Users(UserId),
CONSTRAINT FK_Messages_Users_ToUserId FOREIGN KEY (ToUserId) REFERENCES dbo.Users(UserId)
)
SELECT *
FROM
(
- 这将获取发送给用户的所有消息
SELECT
*
FROM dbo.Users
INNER JOIN dbo。[Messages]
ON Users.UserId = [Messages] .ToUserId
UNION
- 这将获取用户的所有消息
SELECT
*
FROM dbo.Users
INNER JOIN dbo。[Messages]
ON Users.UserId = [Messages] .FromUserId
) AS AllMessages
WHERE
UserId = @ userId
我强烈建议您在Microsoft Virtual Academy网站上查看此演示文稿,以了解数据库设计:
http://www.microsoftvirtualacademy.com/training-courses/database-基础知识 [ ^ ]
i want to show message from message table with user detail from registation table
and using following code.
pls help
protected void Page_Load(object sender, EventArgs e)
{
string friend = Request.QueryString["friend"].ToString();
string me = Session["userid"].ToString();
string S = ConfigurationManager.ConnectionStrings["A1"].ConnectionString;
con = new SqlConnection(S);
//following quiery showing userdetail and message but showing agian and agian 7time because i have 7 users in Registration table
ad = new SqlDataAdapter("select * from Message1 as a,Registration as b where( b.userid=a.friend or b.userid=a.me) and (friend=" + friend + " and me=" +me+") OR (me= " +friend + " and friend=" +me+ ")", con);
ds = new DataSet();
// and this following quiery work but no user detail
// ad = new SqlDataAdapter("select * from Message1 where (friend=" + friend + " and me=" + me + ") OR (me= " + friend + " and friend=" + me + ")", con);
// ds = new DataSet();
ad.Fill(ds);
listview1.DataSource = ds.Tables[0];
listview1.DataBind();
解决方案
This solution is a total stab in the dark as I don't know what your project is trying to do.
CREATE TABLE dbo.Users ( UserId INT IDENTITY NOT NULL, Username VARCHAR(50) NOT NULL, [Password] VARCHAR(50) NOT NULL, NickName VARCHAR(50) NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL, [Date] DATE NULL, City VARCHAR(50) NULL, [State] VARCHAR(10) NULL, CONSTRAINT PK_Users PRIMARY KEY(UserId) ) CREATE TABLE dbo.[Messages] ( MessageId INT IDENTITY NOT NULL, [DateTime] DATETIME NOT NULL, FromUserId INT NOT NULL, ToUserId INT NOT NULL, [Message] VARCHAR(MAX) NOT NULL, [Status] VARCHAR(50) NOT NULL, CONSTRAINT PK_Messages PRIMARY KEY (MessageId), CONSTRAINT FK_Messages_Users_FromUserId FOREIGN KEY (FromUserId) REFERENCES dbo.Users(UserId), CONSTRAINT FK_Messages_Users_ToUserId FOREIGN KEY (ToUserId) REFERENCES dbo.Users(UserId) ) SELECT * FROM ( --This will get all of the messages addressed to the user SELECT * FROM dbo.Users INNER JOIN dbo.[Messages] ON Users.UserId = [Messages].ToUserId UNION --This will get all of the messages from the user SELECT * FROM dbo.Users INNER JOIN dbo.[Messages] ON Users.UserId = [Messages].FromUserId ) AS AllMessages WHERE UserId = @userId
I highly recommend taking a look at this presentation on the Microsoft Virtual Academy web site to learn about database design:
http://www.microsoftvirtualacademy.com/training-courses/database-fundamentals[^]
这篇关于显示来自消息表的消息,其中包含来自注册表的用户详细信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文