View, Pivot or What? Please help Geeks ! RSS

• • Reply
  Asp.net king

  Member

  15 Points

  146 Posts

  View, Pivot or What? Please help Geeks !

  Nov 01, 2016 04:33 PM|Asp.net king|LINK

  Dears,

  I have three tables ( Students, Skills, SkillsElements). Each skill has pass score, the total of skill elements =Total skill mark.

  and every element has a weight (Mark).  for example element 1 for skill 2 must be evaluated from 12 marks.

  Skill table have the following columns : (SkillID,SpecializationID,SkillName,SkillPassCore,TeacherID)

  Students have columns (ID, Name,SpecializationID)

  SkillsElements have columns (ElementID,SkillID,ElementName,ElementWeight)

  taking to your consideration that every skill has dynamic number of elements, for example skill 1 has 4 skill elements while skill 2 has 6 skill elements.

  how i can do this in grid view depending on a query and save it Separate table called ElementsMarks Containing the following Columns (StudentID,ElementID,ElementMark) :

  Skill Name: ____________________________

  Skill Pass Score: 75

  ID	Student	Element 1 Mark	Element 2 Mark	Element 3 Mark	Total
  454357	a	Input textbox	Input textbox	Input textbox	Total must be bigger than skill pass score
  235345	b	Input textbox	Input textbox	Input textbox	Total must be bigger than skill pass score
  436456	c	Input textbox	Input textbox	Input textbox	Total must be bigger than skill pass score

   I ask many questions on this site for this issue. i didn't find a logical and easy solution, I appreciate your help.

  ASP.NET C#

  Regards,

  “First, solve the problem. Then, write the code.”
  (John Johnson)
• • Reply
  Eric Du

  Contributor

  6730 Points

  2715 Posts

  Re: View, Pivot or What? Please help Geeks !

  Nov 02, 2016 09:15 AM|Eric Du|LINK

  Hi asp.net king,

  According to your description, you want query data by skill name, then you want add skill element in multiple students, after updated, saved added data .

  1. you can query data by skill name (skill element value is null in original database), then through edit command fill skill element  single by single !

  here is command button in gridview:

  <Columns>
  
  <asp:BoundField DataField="9/02" HeaderText="9/02" SortExpression="9/02" />
  
  <asp:BoundField DataField="9/03" HeaderText="9/03" SortExpression="9/03" />
  
  <asp:CommandField HeaderText="Command" ShowDeleteButton="True" ShowEditButton="True" ShowHeader="True" />
  
  </Columns>
  

  screen shot:

  

         2. You could write logical code of save data in code behind!

            You can reference this link for how to update data and save data!

            http://www.c-sharpcorner.com/uploadfile/9f0ae2/gridview-edit-delete-and-update-in-asp-net/

  If my reply not solve your problem , please let me know !

  Best Regards,

  Eric Du

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  Asp.net king

  Member

  15 Points

  146 Posts

  Re: View, Pivot or What? Please help Geeks !

  Nov 02, 2016 08:29 PM|Asp.net king|LINK

  All i want is the SQL query to create the grid view.

  Any help?

  “First, solve the problem. Then, write the code.”
  (John Johnson)
• • Reply
  Zhi Lv - MSF...

  All-Star

  45489 Points

  7008 Posts

  Microsoft

  Re: View, Pivot or What? Please help Geeks !

  Dec 06, 2016 09:40 AM|Zhi Lv - MSFT|LINK

  Hi asp.net King,

  Based on your description, first I suppose you need to use Join keyword to join the table and get all student's element mark, Then using PIVOT to change a table-valued expression into another table. Finally, Join the original tables and get the student name or other fields.

  Here are some relevant articles, you could refer to them:

  Using Pivot:

  https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

  https://msdn.microsoft.com/en-us/library/ms177410.aspx

  https://www.codeproject.com/tips/500811/simple-way-to-use-pivot-in-sql-query

  Using Join:

  http://www.w3schools.com/sql/sql_join.asp

  Best regards,
  Dillion

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.