ASP.net C# Dependable Table Query

According to following Diagram i need to data Course,Student and Course Student using C#
I Wrote following code but its not working and any other method to insert to data to dependable table(CourseStudent)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using StudentDB;
public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataClassesDataContext db = new DataClassesDataContext();
        Student st = new Student();
        st.StudentID = "11114";
        st.Studentname = "Test1";
        db.Students.InsertOnSubmit(st);

        Response.Write("Working1!!" + "<br>");

        Course co = new Course();
        co.CourseID = "12314";
        co.Course_Name = "Physics1";
        db.Courses.InsertOnSubmit(co);
        Response.Write("Working2!!" + "<br>");
        
        CourseStudent ct = new CourseStudent();
        ct.CourseID = "12314";
        ct.StudentID = "1111";
        db.CourseStudents.InsertOnSubmit(ct);
        
        //db.ExecuteCommand("Insert into CourseStudent(CourseID,StudentID) values({0},{1})", ct.CourseID, ct.StudentID);
        Response.Write("Working3!!" + "<br>");
db.SubmitChanges();
       

    }

Open in new window


Error is
 Can't perform Create, Update or Delete operations on 'Table(CourseStudent)' because it has no primary key.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Can't perform Create, Update or Delete operations on 'Table(CourseStudent)' because it has no primary key.

Source Error:


Line 27:         ct.CourseID = "12314";
Line 28:         ct.StudentID = "1111";
Line 29:         db.CourseStudents.InsertOnSubmit(ct);
Student.jpg
LVL 10
ukerandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi Ukerandi,
In the back end database, please try to set a composite primary key(a primary key made up of the CourseID and StudentID fields) on the CourseStudent table.
0
PortletPaulfreelancerCommented:
is this homework?

{edited - sorry}
0
ukerandiAuthor Commented:
No I'm self learning data management with c#
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ukerandiAuthor Commented:
I already created primary key for 2 tables          Courseid for course table and student Id for student table
0
Barry CunneyCommented:
Did you set a primary key on the CourseStudent table?
0
PortletPaulfreelancerCommented:
look at 'alter table' at msdn, that what you will use to add the 'composite key'

you will be adding this to the third table, which currently does not have a primary key (the fact that the others are keyed isn't really relevant right now) - look for those little 'key' icons in the image, you want one on each table.

a way, which 'names' it

ALTER TABLE CourseStudent
ADD  CONSTRAINT PK_CourseStudent PRIMARY KEY CLUSTERED ([CourseID], [StudentID])
;
0
ukerandiAuthor Commented:
Bcunney: no why I need to add primary key for that.                                                             Pourtletpaul: coursestudent table courseid is foreign key for course table and studentid is foreign key for student table.
0
Barry CunneyCommented:
The error that your code is returning is because there is no Primary Key on the CourseStudent table

Exception Details: System.InvalidOperationException: Can't perform Create, Update or Delete operations on 'Table(CourseStudent)' because it has no primary key.

So it you add a primary key to CourseStudent table it should resolve the issue
Also it will be good practice because it will avoid duplicates being added - i.e will avoid the same course and student combination being added twice
0
PortletPaulfreelancerCommented:
:)
there are 3 tables
you need 3 primary keys
one for each table
0
Fernando SotoRetiredCommented:
Hi ukerandi;

In Linq to SQL each table that participate in CRUD operations needs to have a primary key. You need to add as primary key the composite key of the two fields in the student course table. This table is called a join or bridge table. So if you want this to work correctly you need to add the primary key to the table.
0
ukerandiAuthor Commented:
I already Added 2 primary key for CourseStudent Table but still same Error
0
Fernando SotoRetiredCommented:
So you originally had this error, " Can't perform Create, Update or Delete operations on 'Table(CourseStudent)' because it has no primary key.", you state that you added the primary key and that you are still getting the same error?
0
PortletPaulfreelancerCommented:
if you look at the Database Diagram now, do you see a key in the upper left corner of every table?
Q-28243733-3-keys.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
@Portletpaul;

The CourseStudent table needs a key icon on both fields, it is a join table used in a many to many relationship.
0
ukerandiAuthor Commented:
it's working thanks for every one
Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using StudentDB;
using System.Data.Linq.Mapping;
public partial class Default3 : System.Web.UI.Page
{
    //[Association(Name="FK_CourseStudent_Students", Storage="_CustomerDemographic", ThisKey="CustomerTypeID", IsForeignKey=true)];
    protected void Page_Load(object sender, EventArgs e)
    {
        DataClassesDataContext db = new DataClassesDataContext();



        Student st = new Student();
        st.StudentID = "11115";
        st.Studentname = "Test2";
        db.Students.InsertOnSubmit(st);

        Response.Write("Working1!!" + "<br>");

        Course co = new Course();
        co.CourseID = "12316";
        co.Course_Name = "Physics2";
        db.Courses.InsertOnSubmit(co);
        Response.Write("Working2!!" + "<br>");



        CourseStudent ct = new CourseStudent();
        ct.CourseID = "12316";
        ct.StudentID = "11115";

        db.CourseStudents.InsertOnSubmit(ct);
        //db.ExecuteCommand("Insert into CourseStudent(CourseID,StudentID) values({0},{1})", ct.CourseID, ct.StudentID);
        
            db.SubmitChanges();
        Response.Write("Working3!!" + "<br>");
       

    }
}

Open in new window


Please let me know stil i'm doing mistake.
Diagramme.jpg
0
ukerandiAuthor Commented:
Great help Thanks
0
PortletPaulfreelancerCommented:
>>CourseStudent table needs a key icon on both fields
yes, I know, sorry  as soon as I posted it I realized my mistake

what I was trying to indicate (in my mind) was one additional index, which doesn't translate to number of icons...

my bad

glad I'm not a graphic artist

Oh, and yippee, well done ukerandi
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.