显示来自消息表的消息,其中包含来自注册表的用户详细信息 [英] show message from message table with user detail from registation table

查看:57
本文介绍了显示来自消息表的消息,其中包含来自注册表的用户详细信息的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想在消息表中显示来自注册表

并使用以下代码的用户详细信息。

请帮助

  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
FirstName VARCHAR ( 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屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆