primary and foreign key problem

Question

0.00/5 (No votes)

See more:

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:

C#

{
	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";
}

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~~

Posted 14-Nov-12 22:05pm

workoholic91

Updated 14-Nov-12 23:18pm

André Kraak

v3

Add a Solution

Comments

sahabiswarup 15-Nov-12 4:46am

try to simplify your question so that it will be easy for us to answer.

Sergey Alexandrovich Kryukov 25-Nov-12 17:06pm

Please don't post your comments as "solution". Comment the answers you got.
--SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rob Branaghan · Accepted Answer · 2012-11-14T22:41:00

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!

Divya RS · Accepted Answer · 2012-11-14T23:29:00

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:

SQL

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

primary and foreign key problem

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0