locked
Need to add a TextBox to the Many-To-Many field template (in a editable gridview - excel like) RRS feed

  • Question

  • User-1847166894 posted

    I'd like to have an editable grid for displaying all rows at once belonging to an intersection table

    The many-To-Many works well in a way but it only shows a box for each combination. I'd like to do that but also gathering additional data for each box 

    So I need to display the box plus the textbox and save both values. I am learning how to simulate a kind of excell gridview so I can gather all probable combinations displayed at once (row by row is a good choice too via adding buttons to each row)

    Any idea on this?

    Carlos

    Saturday, March 18, 2017 5:11 AM

Answers

  • User-330204900 posted

    yes we all knew this web forms appear slow and unwieldy compared to client side. That is why I am wrapping my DD stuff up and moving over to working with AngularJS, but my plan is to bring DD to angular, not sure what this will look like or how helpful it will be but I'm looking into it. I'll update my blog when I have something to show.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 7, 2017 3:16 PM

All replies

  • User-271186128 posted

    Hi CPorras,

    I’m not quite understand what’s your requirement.

    Do you want to add a textbox to each row in gridview that you can add something like notes to it?

    Or you want to update the row?

    If you want to update the row ,you could try to use below code.

    In aspx:

    <asp:GridView ID="GridView1" runat="server" DataKeyNames="ID1"
                    OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit"
                    OnRowUpdating="OnRowUpdating" AutoGenerateColumns="false" PageSize="50">
                    <Columns>
                        <asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
                            <ItemTemplate>
                                <asp:Label ID="lblName" runat="server" Text='<%# Eval("col2") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="col2" runat="server" Text='<%# Eval("col2") %>'></asp:TextBox>
                            </EditItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Country" ItemStyle-Width="150">
                            <ItemTemplate>
                                <asp:Label ID="lblCountry" runat="server" Text='<%# Eval("col3") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="col3" runat="server" Text='<%# Eval("col3") %>'></asp:TextBox>
                            </EditItemTemplate>
                        </asp:TemplateField>
                        <asp:CommandField ButtonType="Link" ShowEditButton="true" ItemStyle-Width="150" />
                    </Columns>
                </asp:GridView>
    

    In behind code:

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!this.IsPostBack)
                {
                    this.BindGrid();
                }
            }
    
            protected void BindGrid()
            {
                NorthwindDataContext db = new NorthwindDataContext();
                
                var data = from d in db.Table1s
                           select d;
    
                GridView1.DataSource = data.ToList();
                GridView1.DataBind();
            }
    
            protected void OnRowEditing(object sender, GridViewEditEventArgs e)
            {
                GridView1.EditIndex = e.NewEditIndex;
                this.BindGrid();
            }
    
            protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
            {
                GridViewRow row = GridView1.Rows[e.RowIndex];
                int ID1 = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
                string col2 = (row.FindControl("col2") as TextBox).Text;
                string col3 = (row.FindControl("col3") as TextBox).Text;
                using (NorthwindDataContext ctx = new NorthwindDataContext())
                {
                    var data = (from c in ctx.Table1s
                                         where c.ID1==ID1
                                         select c).FirstOrDefault();
                    data.col2 = col2;
                    data.col3 = col3;
                    ctx.SubmitChanges();
                }
                GridView1.EditIndex = -1;
                this.BindGrid();
            }
            protected void OnRowCancelingEdit(object sender, EventArgs e)
            {
                GridView1.EditIndex = -1;
                this.BindGrid();
            }
    

    And the result is:

    You can refer to below link for a tutorial of updating gridview:

    https://www.aspsnippets.com/Articles/Select-Insert-Edit-Update-and-Delete-in-GridView-using-LINQ-to-SQL-in-ASPNet.aspx

    But if you want to update the relationship between the related entities, I think it is better to use a down drop list than a text box.

    Best regards,
    Dillion

    Wednesday, March 22, 2017 2:18 AM
  • User-1847166894 posted

    thank you ... I'll give it a try

    I really need to do both things. I might have in the combination table a lot of different combinations (depending of course of the entities that keep this table alive, let's say the left and right tables)

    so like in a survey for instance, I'd like to have the set of available questions plus remarks on each one of them. Let's say that the questions are: do you have a favorite color? do you have a favorite brand? do you remember the day of your first kiss? ... and so on. Then I'd like to have an extra explanation of the answer: name of the colour, name of the brand, what day it was?

    don't know if that makes my question clearer or darker?

    Wednesday, March 22, 2017 3:46 AM
  • User-1847166894 posted

    thing is that for gathering the data in a intersection table you must use data coming from its conforming ends (left and right table to be simplistic)

    but let's say that lef table is "SurveyNames" and rihght table is "SurveyQuestions". Intersection table is "Answers"
    you should create the grid for gathering the data not from "Answers" itself but from the other two, isn't it?

    but you must also consider that you don't want neither you need to display all the questions for every Survey. You need to display all those pertaining to some kind of logical or filtering condition that make each question had its own weight

    for e.g.: you wouldn't like to display questions belonging to the category "girls" to a boy; neither will you want to display any other data which doesn't fit the profile of the person being interviewed

    so, assuming you do all filtering first, then you'd like to display in a grid all questions belonging to the filtered profile
    ... AND ANSWERING THOSE QUESTIONS AT THE SAME TIME

    sound this like it makes sense?

    Thursday, March 23, 2017 12:05 AM
  • User-1847166894 posted

    Just to make my case a little clear:

    My intersection table is "seen".  It has more than the 2 traditional FK columns as its PKs and it also has more fields inits structure and many other FK from its reference tables (to do the filtering)

    BUT I need to have it as "unseen" and work instead with its wing (left and right) tables

    Does that make sense at all? Should I try instead working with a traditional ASP.Net Web Form page?

    Sunday, March 26, 2017 3:28 AM
  • User-330204900 posted

    OK carlos, you may have to do this in a customer page I thought it was the intersection table that had the extra columns

    Table A

    Table B

    Table C

    PKA

    PKA

     

     

    PKB

    PKB

    So it's Table C you want to display under Table A with Table B as the hidden intersection table?

    Monday, March 27, 2017 9:22 AM
  • User-1847166894 posted

    Sample for demonstrating that table answers must be build not from itself but from its wings

    So YES, answers must be saved in the intersection table but meanwhile you try to gather the answers ... there will be .... (not yet) .... any records stored in this table as to be shown.

    So the problem is that I can't just display something which isn't already there

    So the many-to-many dynamic data control does just fine in displaying and saving ... it does what I need to do ... and it does it right ... it works simply just fine

    I just need to have available an extra TextBox in the same control

    Type of Table   Table   Interview Type of   Table Type of
    Survey Surveys   Answers   Number Survey   Questions Survey
       
    1 Survey about Religion   Do you eat fruits No 325324 3   Religion Professed 1
    2 Survey about Chimneys   Do you eat Vegetables Yes 325324 3   Since what age 1
    3 Survey about Foods   Do you eat Junk Food No 325324 3   How you learned it 1
      Your age 54 325324 3   Are you married 1
      Currently On a Diet No 325324 3   Do you have children 1
      Have you mad diets before No 325324 3   Plan to teach same religion to your kids 1
       
      Do you eat fruits Yes 565655 3   Do you have a chimney 2
      Do you eat Vegetables No 565655 3   Is real or just resembles one 2
      Do you eat Junk Food Yes 565655 3   Use logs or fuel 2
      Your age 34 565655 3   Type of fuel 2
      Currently On a Diet Yes 565655 3   Date build approximately 2
      Have you mad diets before Yes 565655 3  
        Do you eat fruits 3
      Do you have a chimney No 8888 2   Do you eat Vegetables 3
        Do you eat Junk Food 3
      Do you have a chimney Yes 77777 2   Your age 3
      Is real or just resembles one Real 77777 2   Currently On a Diet 3
      Use logs or fuel Logs 77777 2   Have you mad diets before 3
      Date build approximately 1935 77777 2  

    P.S. 

    Problem with Northwind samples is that you always think as if you have only that information already available ... it's like a game of just reproducing what it is now in Northwind tables but in real life you must gather new date all the time not just reproducing what is already there

    Monday, March 27, 2017 5:04 PM
  • User-330204900 posted

    OK I believe the image i sent you with the child table will do what you want. so you will have it when I get this sample finished.

    Monday, March 27, 2017 6:20 PM
  • User-1847166894 posted

    I've been thinking a lot about the man-to-many stuff

    It seems all I need could be as well
    a grid displaying the data from the "Questions" table
    (with all its filtering)
    and then adding an extra column to it 
    as to gather all the answers 
    and by clicking the save button
    answers will be saved in the "Answers" table
    keeping of course
    all required FK and referential integrity
    as the CRUD-Insert operation
    won't fail

    Tuesday, March 28, 2017 10:26 PM
  • User-330204900 posted

    OK, I've been here before and my solution was not a good one I will have a think and come back to you, this my need to be custom, but an updatable view may do

    Questions -> Answers

    Can you give the SQL to create the two tables and their join please and I will have a look.

    Wednesday, March 29, 2017 10:07 AM
  • User-1847166894 posted

    Below requested SQl Script

    Added column "Answers" in the "Questions" table for one reason: "it won't be used to store any answer there"  BUT to simulating in a grid that I am gathering data for the "SurveyAnswers" table while ASP.Net Dynamic Data will be really displaying data from "Questions" table BUT when I click "Save" button I will be really saving in the "SurveyAnswers" table, NOT in the "Questions" table

    Does that make sense at all? Is my idea clear enough? Am I really saying what I meant to be saying? Is pretty straight forward to me as if I still don't have any answer saved in my "SurveyAnswers" table then it will make no sense at all creating a display interface just to display nothing: am I right?

    I have also included a "Question" extra column in the "SurveyAnswers" table as to save there the question that was answered because it might be possible that somehow while CRUDing the database, some of all formerly used questions might be deleted/erased/modified .... so there will be no way of knowing what questions had been answered

    Hope my english might be clear enough so my idea had been clearly exposed ... I am trying to make the question easier not more complex

    HERE IS THE SCRIPT

    /*
    Project       Surveys
    Author       Carlos Porras
    Version      1.0
    Database   SQL-Server
    */

    if exists (select * from sys.foreign_keys where object_id = object_id('[TypeOfSurvey]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [Questions] drop constraint [TypeOfSurvey]

    if exists (select * from sys.foreign_keys where object_id = object_id('[Relationship3]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [Surveys] drop constraint [Relationship3]

    if exists (select * from sys.foreign_keys where object_id = object_id('[SurveyType]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [SurveyAnswers] drop constraint [SurveyType]

    if exists (select * from sys.foreign_keys where object_id = object_id('[SurveyQuestions]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [SurveyAnswers] drop constraint [SurveyQuestions]

    if exists (select * from sys.foreign_keys where object_id = object_id('[Survey]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [SurveyAnswers] drop constraint [Survey]

    if exists (select * from sys.foreign_keys where object_id = object_id('[Gender]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [Surveys] drop constraint [Gender]

    if exists (select * from sys.foreign_keys where object_id = object_id('[AgeGroup]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [Surveys] drop constraint [AgeGroup]

    if exists (select * from sys.foreign_keys where object_id = object_id('[Ethnicity]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [Surveys] drop constraint [Ethnicity]

    if exists (select * from sys.foreign_keys where object_id = object_id('[Surveyor]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [Surveys] drop constraint [Surveyor]

    if exists (select * from sys.foreign_keys where object_id = object_id('[Typist]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [SurveyAnswers] drop constraint [Typist]

    if exists (select * from sys.tables where object_id = object_id('[DataEntryPeople]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [DataEntryPeople]

    if exists (select * from sys.tables where object_id = object_id('[Surveyors]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [Surveyors]

    if exists (select * from sys.tables where object_id = object_id('[Ethnics]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [Ethnics]

    if exists (select * from sys.tables where object_id = object_id('[Ages]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [Ages]

    if exists (select * from sys.tables where object_id = object_id('[Genders]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [Genders]

    if exists (select * from sys.tables where object_id = object_id('[Surveys]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [Surveys]

    if exists (select * from sys.tables where object_id = object_id('[SurveyAnswers]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [SurveyAnswers]

    if exists (select * from sys.tables where object_id = object_id('[Questions]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [Questions]

    if exists (select * from sys.tables where object_id = object_id('[TypesOfSurveys]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
    Drop table [TypesOfSurveys]

    Create table [TypesOfSurveys]
    (
    [TypeOfSurveyID] Integer Identity NOT NULL,
    [TypeOfSurvey] Nvarchar(120) NULL
    )


    Create table [Questions]
    (
    [TypeOfSurveyID] Integer NOT NULL,
    [QuestionID] Integer Identity NOT NULL,
    [Question] Nvarchar(180) NOT NULL,
    [Answer] Nvarchar(120) NULL
    )


    Create table [SurveyAnswers]
    (
    [QuestionID] Integer NOT NULL,
    [DataEntryPersonID] Integer NOT NULL,
    [SurveyID] Integer NOT NULL,
    [TypeOfSurveyID] Integer NOT NULL,
    [SurveyAnswerID] Integer Identity NOT NULL,
    [Question] Nvarchar(180) NOT NULL,
    [Answer] Nvarchar(120) NOT NULL
    )


    Create table [Surveys]
    (
    [TypeOfSurveyID] Integer NOT NULL,
    [GenderID] Integer NOT NULL,
    [AgeID] Integer NOT NULL,
    [EthnicID] Integer NOT NULL,
    [SurveyorID] Integer NOT NULL,
    [SurveyID] Integer Identity NOT NULL,
    [SurveyNumber] Datetime NULL,
    [SurveyDate] Nvarchar(1) NULL
    )


    Create table [Genders]
    (
    [GenderID] Integer Identity NOT NULL,
    [Gender] Nvarchar(40) NOT NULL
    )


    Create table [Ages]
    (
    [AgeID] Integer Identity NOT NULL,
    [Age] Integer NULL,
    [AgeFrom] Integer NULL,
    [AgeTo] Integer NULL
    )


    Create table [Ethnics]
    (
    [EthnicID] Integer Identity NOT NULL,
    [Ethnicity] Nvarchar(120) NULL
    )


    Create table [Surveyors]
    (
    [SurveyorID] Integer Identity NOT NULL,
    [Surveyor] Nvarchar(120) NOT NULL
    )


    Create table [DataEntryPeople]
    (
    [DataEntryPersonID] Integer Identity NOT NULL,
    [DataEntryPerson] Nvarchar(120) NOT NULL
    )

    Alter table [TypesOfSurveys] add Constraint [pk_TypesOfSurveys] Primary Key ([TypeOfSurveyID])

    Alter table [Questions] add Constraint [pk_Questions] Primary Key ([QuestionID])

    Alter table [SurveyAnswers] add Constraint [pk_SurveyAnswers] Primary Key ([SurveyAnswerID])

    Alter table [Surveys] add Constraint [pk_Surveys] Primary Key ([SurveyID])

    Alter table [Genders] add Constraint [pk_Genders] Primary Key ([GenderID])

    Alter table [Ages] add Constraint [pk_Ages] Primary Key ([AgeID])

    Alter table [Ethnics] add Constraint [pk_Ethnics] Primary Key ([EthnicID])

    Alter table [Surveyors] add Constraint [pk_Surveyors] Primary Key ([SurveyorID])

    Alter table [DataEntryPeople] add Constraint [pk_DataEntryPeople] Primary Key ([DataEntryPersonID])

    Alter table [Questions] add Constraint [TypeOfSurvey] foreign key([TypeOfSurveyID]) references [TypesOfSurveys] ([TypeOfSurveyID]) on update no action on delete no action

    Alter table [Surveys] add Constraint [Relationship3] foreign key([TypeOfSurveyID]) references [TypesOfSurveys] ([TypeOfSurveyID]) on update no action on delete no action

    Alter table [SurveyAnswers] add Constraint [SurveyType] foreign key([TypeOfSurveyID]) references [TypesOfSurveys] ([TypeOfSurveyID]) on update no action on delete no action

    Alter table [SurveyAnswers] add Constraint [SurveyQuestions] foreign key([QuestionID]) references [Questions] ([QuestionID]) on update no action on delete no action

    Alter table [SurveyAnswers] add Constraint [Survey] foreign key([SurveyID]) references [Surveys] ([SurveyID]) on update no action on delete no action

    Alter table [Surveys] add Constraint [Gender] foreign key([GenderID]) references [Genders] ([GenderID]) on update no action on delete no action

    Alter table [Surveys] add Constraint [AgeGroup] foreign key([AgeID]) references [Ages] ([AgeID]) on update no action on delete no action

    Alter table [Surveys] add Constraint [Ethnicity] foreign key([EthnicID]) references [Ethnics] ([EthnicID]) on update no action on delete no action

    Alter table [Surveys] add Constraint [Surveyor] foreign key([SurveyorID]) references [Surveyors] ([SurveyorID]) on update no action on delete no action

    Alter table [SurveyAnswers] add Constraint [Typist] foreign key([DataEntryPersonID]) references [DataEntryPeople] ([DataEntryPersonID]) on update no action on delete no action

    go

    Wednesday, March 29, 2017 7:27 PM
  • User-1847166894 posted

    maybe you already have new advances on this ? ... do you?

    Friday, March 31, 2017 10:22 PM
  • User-1847166894 posted

    Good news Mr. Naughton?

    Tuesday, April 4, 2017 3:50 AM
  • User-1847166894 posted

    Mr. Naughton,

    You have already seen Mr. Ebbo response of today ... I retwitted you
    So it seems I am the last person on earth working with the fabulous deprecated scaffolding ASP.Net Dynamic Data technology

    It seems I'll have to go on my own if I still decide to go any step further .. lucklily I still have you in this forums

    You'll be like my Navajo WindTalker (a Nicolas Cage movie ... http://www.imdb.com/title/tt0245562/)

    Thursday, April 6, 2017 5:26 AM
  • User-330204900 posted

    Sorry didn't see David's tweet, what did he say?

    Friday, April 7, 2017 11:21 AM
  • User-1847166894 posted

    I retweeted you all of them a few adays ago Steve,

    (lower one was the first one ... and upwards ... top one was the last one)

    Friday, April 7, 2017 2:46 PM
  • User-330204900 posted

    yes we all knew this web forms appear slow and unwieldy compared to client side. That is why I am wrapping my DD stuff up and moving over to working with AngularJS, but my plan is to bring DD to angular, not sure what this will look like or how helpful it will be but I'm looking into it. I'll update my blog when I have something to show.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 7, 2017 3:16 PM
  • User-1847166894 posted

    Mr. Naughton

    I posted the code as you instructed me ... maybe you can see it

    Carlos

    Monday, April 10, 2017 6:24 AM
  • User-330204900 posted

    OK I have the database now I will build a sample of my application for you and send it to you :)

    Monday, April 10, 2017 10:53 AM
  • User-1847166894 posted

    maybe you can include in it as well the bootstrapping feature?

    Monday, April 10, 2017 3:43 PM
  • User-330204900 posted

    yes that is standard in ALL my projects now :)

    Monday, April 10, 2017 4:33 PM
  • User-1847166894 posted

    Hope you might have something already worked on this ...

    Tuesday, April 18, 2017 2:43 PM
  • User-330204900 posted

    I have and I am nearly there stripping all the security code out, but was ill over the weekend so did not get as much done as I had hoped :) sorry for the delay.

    Tuesday, April 18, 2017 3:40 PM
  • User-1847166894 posted

    sorry ... didn't mean to look like I am demanding
    it is ok with me if you can't go on longer with this

    I know for sure that your work load may be too high so you don't need to have more duties than those you can deal with

    It's ok, I understand. I know you are really good at this but I also understand your time availability

    Thanks ... and I appreciate to be again in touch with you

    Tuesday, April 18, 2017 5:31 PM
  • User-330204900 posted

    Your not it's been weeks when I started work was slack now it's speeded up I'll get there by the weekend Laughing it's my pleasure to help.

    Tuesday, April 18, 2017 5:41 PM
  • User-1847166894 posted

    Thank you. I really wish you may have the availability in building this
    (so long awaited magical and kind of difficult of getting)
    source code written for the ASP.Net Dynamic Data way of doing things

    Thanks again

    Thursday, April 20, 2017 5:57 AM
  • User-1847166894 posted

    Will you really do it?

    Do I wait for you or do I really look for an alternative non-Dynamic Data solution?

    I am not complaining neither am I demanding but it is taking too long so I just need how doable is really this

    Maybe is just too complex and will take too long to develop

    Friday, April 21, 2017 4:39 AM
  • User-1847166894 posted

    sorry but I don't know how it is more adequate in your culture to make this kind of question
    but I just need to know if yo have something developed
    or you ha decided not going any further in doing this or maybe will do it sometime later

    I just need to know and I don't know what word do I have to use in order to ask you in the most respectful way

    please believe me that I am not by any means trying t put you in a difficult position. I am just asking ... nothing else

    This is quite a cornerstone for what I am trying to achieve

    Tuesday, May 2, 2017 11:06 PM
  • User-330204900 posted
    Yes I do have something but been very busy, I just keep being dragged away from it
    Wednesday, May 3, 2017 12:22 AM