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

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

Answer 1

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

Answer 2

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?

Answer 3

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?

Answer 4

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?

Answer 5

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?

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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.

Answer 10

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

Answer 11

User-1847166894 posted

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

Answer 12

User-1847166894 posted

Good news Mr. Naughton?

Answer 13

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/)

Answer 14

User-330204900 posted

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

Answer 15

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)

Answer 16

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.

Answer 17

User-1847166894 posted

Mr. Naughton

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

Carlos

Answer 18

User-330204900 posted

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

Answer 19

User-1847166894 posted

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

Answer 20

User-330204900 posted

yes that is standard in ALL my projects now :)

Answer 21

User-1847166894 posted

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

Answer 22

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.

Answer 23

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

Answer 24

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  it's my pleasure to help.

Answer 25

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

Answer 26

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

Answer 27

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

Answer 28

User-330204900 posted
Yes I do have something but been very busy, I just keep being dragged away from it