Click here to Skip to main content
15,307,242 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Imgur: The magic of the Internet[^]

Imgur: The magic of the Internet[^]

Imgur: The magic of the Internet[^]

What I have tried:

i have tried using all joins right left inner outer full none has worked every time it says 0 rows affected.
should i be using insert query instead of update for this particular work?so that i can store S_ID and C_ID values from StudentCourse table to Attendance table?
i am not sure how to write proper insert join query to get it to work
Posted
Updated 9-May-19 21:58pm
Comments
Member 12899279 9-May-19 19:45pm
   
insert into Attendance
SELECT
StudentCourse.S_ID,
StudentCourse.C_ID
FROM StudentCourse
left JOIN Student on StudentCourse.S_ID = Student.S_ID
left JOIN Course on StudentCourse.C_ID = Course.C_ID

where StudentCourse.S_ID='2' and StudentCourse.C_ID='2'

this aint working either
Member 12899279 9-May-19 20:31pm
   
Still not able to get what i want now i can select only those rows that match in both tables while i want to updte S_ID,C_ID,Date,Pre_Abs from Attendance table then i want to update it with the values that are selected and showed from StudentCourse table in datagridview in c#
Member 12899279 9-May-19 20:41pm
   
insert into Attendance(S_ID,C_ID,Pre_Abs)
select a.Pre_Abs,sc.S_ID,sc.C_ID from Attendance a,StudentCourse sc
join Student s on s.S_ID=sc.S_ID
join Course c on c.C_ID=sc.C_ID
where sc.S_ID=2 and sc.C_ID=2
this is also not working
CHill60 10-May-19 4:29am
   
For future reference, many of us cannot access image sites if we are looking at CodeProject at work - lots of sites are blocked by corporations. There is very rarely any need to use a picture to describe a coding problem. So you are reducing the number of members who can help you.
Just type or paste your code into the question. It has the added advantage that members can copy your code to try and fix it. Personally I never bother even attempting to help these days if someone posts a link in their question
Member 12899279 10-May-19 6:21am
   
there are two actually four tables Student(S_ID(primary key)),Course(C_ID(Primary key)),StudentCourse((S_ID,C_ID(Foreign keys))Bridge table) and attendance table with (S_ID,C_ID(Foreign keys))
what i am doing is displaying data on datagridview by joining tables and slecting S_ID,S_Name,C_ID and Pre_Abs(Attendance table column)columns from all these tables now i want to insert the info present in datagridview to the attendance table when i click on button..i have done this already with simple insert query to attendance table by using datagrdview.rows[i].cell[2] property
i want to know if there,s any better idea to do this so that i can use JOIN instead of using datagridview property with for loop
for now my attendance table is empty while Student,Course,StudentCourse tables are filled with the data
what i want is to display record(S_ID,C_ID) from studentCourse table and (Pre_Abs) from Attendance table and when i submit the attendance i want it to store Pre_abs record against each S_ID,C_ID in the attendance table
i don't think i can explain it any further

1 solution

Quote:
should i be using insert query instead of update for this particular work?

This kinda says "I have absolutely no idea what I am doing" and that's probably the real problem here.

UPDATE and INSERT queries are very, very different: one changes only existing records, and the other only adds new rows to a a table. That you think they are interchangeable is worrying - and you need to address that before you go much further.

Regardless, the most probable reason it doesn't work is that your WHERE condition is incorrect: if it doesn't select any rows, then nothing gets changed or supplied to INSERT.
But we don't have any access to your data, so we have no idea what it could (or should) do, and no way to test it.

So start by removing the INSERT code completely, and check what the SELECT part does in isolation - when it correctly returns the rows you want, you can start thinking about doing something else with the data!
   
Comments
Member 12899279 10-May-19 6:21am
   
there are two actually four tables Student(S_ID(primary key)),Course(C_ID(Primary key)),StudentCourse((S_ID,C_ID(Foreign keys))Bridge table) and attendance table with (S_ID,C_ID(Foreign keys))
what i am doing is displaying data on datagridview by joining tables and slecting S_ID,S_Name,C_ID and Pre_Abs(Attendance table column)columns from all these tables now i want to insert the info present in datagridview to the attendance table when i click on button..i have done this already with simple insert query to attendance table by using datagrdview.rows[i].cell[2] property
i want to know if there,s any better idea to do this so that i can use JOIN instead of using datagridview property with for loop
for now my attendance table is empty while Student,Course,StudentCourse tables are filled with the data
what i want is to display record(S_ID,C_ID) from studentCourse table and (Pre_Abs) from Attendance table and when i submit the attendance i want it to store Pre_abs record against each S_ID,C_ID in the attendance table
i don't think i can explain it any further
OriginalGriff 10-May-19 6:57am
   
So what happens when you do the SELECT alone?

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

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900