foreign key command? [Resolved]

Posted by Tejamanimala under ASP.NET on 5/13/2013 | Points: 10 | Views : 1598 | Status : [Member] | Replies : 17
hi,i have two tables
1)Results is a table name,(columns are UserID Primerkey,USerNAme,Domain,Marks)here already i gave primery key to user id
2)Student is a table name(columns are UserID,QuestionID,Question,SelectedOption,CorrectOption)here i didnt give foreign key to user id,but i created table,now i want to add foreign key to UserID in Student table,and am using Sql2005,can you please tell me how to add foreign key to student table

manimala


Responses

Posted by: Kmandapalli on: 5/13/2013 [Member] Silver | Points: 50

Up
0
Down

Resolved
Hi,

You need to delete the data in all your tables otherwise you will go on getting this data .

Regards,
Shree M.

Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Macki on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

TRY THIS IT WILL WORK
ALter Table student
add Foreign key(USERID)
references results(USERID)

Do like this or you can do Mapping (DATABASE DIAGRAM ADD TABLES AND MAPP TWO TABLE )
CLICK YOUR DATABASE AND CLICK DATABASE DIAGRAM >ADD NEW >RIGHT CLICK DASHBOARD >ADD TWO TABLES >MAPPED


Regards
Macki

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
hi,am getting this error
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__StudentEx__UserI__44FF419A". The conflict occurred in database "OnlineExam", table "dbo.Resultsforstudents", column 'UserID'.


manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/13/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

FOREIGN KEY in on table points to PRIMARY KEY in another table.
ie., Foreign Key of Students table points to Primary Key of Results table.

CREATE TABLE Results
(
UserID int Primary key identity(1,1),
USerNAme nvarchar(100),
Domain nvarchar(100),
Marks int
)

CREATE TABLE Students
(
UserID int,
QuestionID int Primary Key identity(1,1),
Question nvarchar(300),
SelectedOption nvarchar(100),
CorrectOption nvarchar(100),
FOREIGN KEY (UserID) References Results(UserID)
)

Mark as Answer if satisfied...........

regards,
Shree M.



Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
how to give relationship to the tables

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/13/2013 [Member] Silver | Points: 25

Up
0
Down
hi,

First drop your two tables that you have created and create them i have did.
It will work out...

Mark as Answer if satisfied...

Regards,
Shree M.

Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
hi kmandapalli
already i created t=two tables,now i want to modif it to foreign key,how can i modify the userid column to foreign key,and i gave primerky to userid in results table

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/13/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

Try it..
ALTER TABLE Students
ADD FOREIGN KEY (UserID)
REFERENCES Results(UserID)

Mark as Answer if satisfied...

Regsrds,
Shree M.

Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
hi shree,
when iused ur command i am getting this error
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__StudentEx__UserI__52593CB8". The conflict occurred in database "OnlineExam", table "dbo.Resultsforstudents", column 'UserID'.


manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/13/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

Do you have OnlineExam table in your database.
If so check if you have provided Foreign Key constraint to UserID in that table.
Let me know...

Regards,
Shree M.


Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,OnlinExam is a database name

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/13/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

Got your error now.
I guess you already have some data in your table.
That's y you are unable to add the Foreign Key Constraint.
So you have to delete all your data and first create Foreign Key constraint and then add data..
Then only it will work..

Mark as answer if satisfied....

Regards,
Shree M.

Kavya Shree Mandapalli

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
hi,oh ok ,yes i have already some data in my table,thank you i will try

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
hi,
am getting this error
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__StudentEx__UserI__5441852A". The conflict occurred in database "OnlineExam", table "dbo.Resultsforstudents", column 'UserID'.
The statement has been terminated,what shoul i do?

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
hi shree
am getting error as like above in this code at cmd1.ExecuteNonQuery();
if ((RadioButtonList1.SelectedValue) == dsCurrent.Tables[0].Rows[0]["CorrectOption"].ToString())
{

i++;
SqlCommand cmd1 = new SqlCommand("insert into StudentExamFullResult (UserID,Question,SelectedOption,CorrectOption) values('"+Label32.Text+"','" + dsCurrent.Tables[0].Rows[0]["Question"].ToString() + "','" + RadioButtonList1.SelectedValue + "','" + dsCurrent.Tables[0].Rows[0]["CorrectOption"].ToString() + "')", con);
cmd1.ExecuteNonQuery();
}

else
{
SqlCommand cmd1 = new SqlCommand("insert into StudentExamFullResult (UserID,Question,SelectedOption,CorrectOption) values('" +Label32.Text + "','" + dsCurrent.Tables[0].Rows[0]["Question"].ToString() + "','" + RadioButtonList1.SelectedValue + "','" + dsCurrent.Tables[0].Rows[0]["CorrectOption"].ToString() + "')", con);
cmd1.ExecuteNonQuery();
}

if ((RadioButtonList2.SelectedValue) == dsCurrent.Tables[0].Rows[1]["CorrectOption"].ToString())
{

i++;
SqlCommand cmd2 = new SqlCommand("insert into StudentExamFullResult (UserID,Question,SelectedOption,CorrectOption) values('" + Label32.Text + "','" + dsCurrent.Tables[0].Rows[1]["Question"].ToString() + "','" + RadioButtonList2.SelectedValue + "','" + dsCurrent.Tables[0].Rows[1]["CorrectOption"].ToString() + "')", con);
cmd2.ExecuteNonQuery();
}

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
ya allready i deleted all the data in all tables
but still am getting this error,y?

manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tejamanimala on: 5/13/2013 [Member] Starter | Points: 25

Up
0
Down
HI,it's working now,thank you,and i have one problem,i.e,
I have one online exam paper page,in this when students select the answer,and submit the button,result will be calculated and stores hat results into database.My problem is,when i selecting the questions,when i missed to select one question and click's on the submit button i got a message as please select 3rd question like this,after selecting the 3rd question,i clicked on submit button,then result is storing into database.result in the scense question,correct option,selected option.so here result is stored into database multiple times,beacause,after submit,3rd question missing,selection of 3rd question ,after submiting answers i am getting the multiple times data(i mean if i have 30 questions,it stores 30 questions 2times,i'e,am getting 60 questions,ansers,seelcted options)
what should i do for this?


manimala

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response