locked
View, Pivot or What? Please help Geeks ! RRS feed

  • Question

  • User-1251655565 posted

    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,

    Tuesday, November 1, 2016 4:33 PM

All replies

  • User-1838255255 posted

    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

    Wednesday, November 2, 2016 9:15 AM
  • User-1251655565 posted

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

    Any help?

    Wednesday, November 2, 2016 8:29 PM
  • User-271186128 posted

    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

    Tuesday, December 6, 2016 9:40 AM