主键和外键问题 [英] primary and foreign key problem
问题描述
我在项目中使用ASP.NET 4.0和SQL Server 2008 R2.我使用了成员资格提供程序来创建用户.我已将aspnet_Users
表中的主键(UserId
)用作JUWUser
表中的外键(u_id
).
现在的问题是,我必须将用户ID从主键列插入到外键列.我已经尝试使用select和insert语句,但是它不起作用.
我正在从标签中的数据库中检索值,以便可以在正确的位置插入ID.
以下是我的代码:
I am using ASP.NET 4.0 and SQL server 2008 R2 in my project. I have used membership provider for creating user.I have taken the primary key (UserId
) from aspnet_Users
table as a foreign key (u_id
) in JUWUser
table.
Now the problem is that I have to insert the user id from the primary key column to the foreign key column. I have tried using select and insert statement, but it is not working.
I am retrieving the value from the database in my label so that I can insert the id in its right place.
Following is my code:
{
lblname.Text = Convert.ToString(Session["name"]);
string myConnectionString = @"Data Source=(local);Initial Catalog=PortalDB;Integrated Security=True";
SqlCommand cmd2 = new SqlCommand("select UserName from JUWUser", new SqlConnection(myConnectionString));
cmd2.Connection.Open();
lblcmpr.Text = cmd2.ExecuteScalar().ToString();
if (lblname.Text == lblcmpr.Text)
{
SqlCommand cmd = new SqlCommand("select UserId from aspnet_Users where UserName ='" + lblname.Text + "'", new SqlConnection(myConnectionString));
cmd.Connection.Open();
lblID.Text = cmd.ExecuteScalar().ToString();
SqlCommand cmd1 = new SqlCommand("insert into JUWUser(u_id) values ('" + lblID.Text + "')", new SqlConnection(myConnectionString));
cmd1.Connection.Open();
cmd1.ExecuteScalar().ToString();
cmd.Connection.Close();
cmd1.Connection.Close();
}
else
lblID.Text = "error";
}
这是个人资料页面的代码.
在此代码中,lblname
存储会话变量中的值,该会话变量包含已登录用户的Username
.
lbl cmpr
正在从JUWUser
表中的UserName
列中检索所有值.
lblID
正在从aspnet_Users
表中检索ID,其中aspnet_Users
中的UserName
列值与lblname.text
中的值匹配.
现在,我必须在u_id
匹配lblname.text
的u_id
列的JUWUser
表中插入此ID.
插入和where语句不能放在一起.
我该怎么办?
请帮助
This is the code of profile page.
In this code lblname
is storing value from a session variable which contains the Username
of the User that has logged in.
lbl cmpr
is retrieving all the values from UserName
column in the JUWUser
table.
lblID
is retrieving the ID from the aspnet_Users
table where the UserName
column value in aspnet_Users
matches the value in lblname.text
.
Now I have to insert this id in JUWUser
table in u_id
column where the UserName
matches lblname.text
An insert and where statement cannot come together.
How should I do this?
Please Help
推荐答案
您需要测试以查看该表中是否已有该用户的记录.
并使用存储过程.
计算JUW中的记录数,其中用户名=用户名.
如果count == 0,则INSERT
如果count == 1,则UPDATE
http://www.w3schools.com/sql/sql_update.asp [
You need to test to see if there is already a record in that table for that user.
And use a stored procedure.
Count number of records from JUW where username = the user name.
if count == 0 then INSERT
if count == 1 then UPDATE
http://www.w3schools.com/sql/sql_update.asp[^] That shows you how to use the UPDATE, and also browse around for some other useful SQL info.
Good luck!
您可以使用存储过程来代替使代码变得如此复杂的方法
只需将lblname.Text作为参数添加到过程
而您的程序应为:
Instead of getting the code this much complex you can go for stored proceure
simply add lblname.Text as parameter to the procedure
And ur procedure shold be as:
create procedure proc1
@name varchar(50)
as
declare @name1 varchar(50)=null
declare @id1 int=0
begin
select @name1=UserName from JUWUser
if @name=@name1
begin
select @id1=UserId from aspnet_Users where UserName =@name
insert into JUWUser(u_id) values (@id1)
end
end
这篇关于主键和外键问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!