Question Databinding to a specific cell

[keb1965]

I may have painted myself in a corner and I am hoping to code my way out of it, either that or perhaps some kind soul will tell me the proper way to handle this problem.

Background:
I have a front end (in vb.net) that is designed to read various bits of data and display it for editing, viewing etc.
For ease of management I created a user control with several other controls, one of which is a listview (I can make it a DataGridView if it will help). On each control, I can bind data so that when I move through my data, each of the several user controls is updated accordingly. However, the listview will have a varying number of items in a single column. For example "Customer" will have a list of "purchases" that need to be displayed in the listview. Since the number of purchases will vary, I need to list them in a table, rather than have a hundred different fields (and fields that I don't even know about yet I might add), but I still need the "purchases" field to correspond to a specific row item in the "customer" table.

Maybe I just don't know how to ask the question .. but it seems a bit complicated to me.

 

[cjard]

I'd rip out the UI and start again with a DataGridView, leveraging the existing ability to show comboboxes in cells to pick from a list (of e.g. Shipping methods or Payment Types) and just have the grid show a nice, simpler version of what is found in the db.

A post describing your database tables and what info youre trying to display would be most helpful

 

[keb1965]

Ok, maybe I can explain the problem better.

Database structure is a single table with nearly 100 fields. I wanted to use multiple tables but the problems associated with updating fields in multiple tables cannot be resolved using an access database.

I have several custom controls with a datagridview. Each datagridview has one static column (items) that cannot be changed by the user. This column information is obtained from a different database(lets call it DB1). Then I have one dynamic column. While the static column is filled out programmatically from DB1.Table0 (this data is never updated back to the database), the dynamic column is filled out with data from a different database (lets call it DB2) from a column which matches the name in the corresponding row in the datagridview.

So, DB1.Table0 has column "Items", which is read into a dataset and subsequently manually placed in the "Items" column. DB2.Table0 has columns "item_a, item_b, item_c etc...."

I need to bind the data in DB2.Table0 "item_a" to the cell adjacent to "Item a" in the datagridview.

Essentially what I need to do is this, but obviously the code won't work because there is no binding to specific cells

'corresponds to item in row N
RowName = "Item a" 
'replace spaces with underscore and make lowercase
RowItem = Replace(RowName.ToLower, " ", "_")
'bind associated column to cell
Me.DataGridView1.Rows(N).Cells(RowItem).DataBindings(Add(New Binding("text", MyBindingSource, RowItem))

This is about as well as I can explain it

Maybe the picture will help explain it better.

 

[cjard]

I may be misunderstanding you but:

DataGridView is merely for drawing data it finds elsewhere. You should load your data into a datatable. If you want to source data from multiple tables or databases that's fine but only one of them can be updateable (without using stored procedures or very good join queries). Presentational aspects such as a "Frozen column" would be best left out of this discussion

Can you do somehting like Use Excel to create a rendition of what you want, and then send the screenshot? I'm still a bit lost you see; I'm afer you drawing what you want to see, even if its pen and paper and scan (or photo with your mobile phone) up here..

I get the feeling that what you want can be done with DataRelation and Expression, custom datatable fill and a few other tricks. It might also help if you give meaningful names to your columns rather than calling them Field0, ItemA.

 

[keb1965]

Ok, lets try this again ...

The picture in my previous post is exactly what I want.

I have two database files. One is read-only to the user ("toolbox"). It contains information the program uses to dynamically create a custom control defined in my .net project. The second database can be named anything, as the user can browse and select any database of their choosing. When they select the database, if there is not a "client" table, one is created with a field for each specific bit of data defined for each control defined in "toolbox".

So, I have a "client" table with some standard info such as name, address, etc. Then lets suppose I have a "projects" control and a "payments" control in the project, each control has a DataGridView (it could be a listview for all I care) that has a variable number of fields defined.

The attached picture represents a control called "projects" that has 4 fields, one for each row (excluding Total). The "client" table will be:
ID, name, address, date, projects_Paint_Pct, projects_Build_Pct projects_Clean_Pct, projects_Demo_Pct, payments_Grading ....

As you can see, I have 4 columns that correspond to "projects"

I need the value in the record for each field whose name starts with "projects" to be placed in the cell adjacent to the corresponding item in the datagridview.

Hopefully this picture will let you better understand the need.

Now on to the other points you made. This project isn't about displaying data, it is about compiling and editing data, only the questions are dynamic and segregated according to other criteria. However, the data does need to be displayed in way that is easy to understand, and as such display is a huge part of the program.

 

[Tom]

I wanted to use multiple tables but the problems associated with updating fields in multiple tables cannot be resolved using an access database.

I'm not sure what you mean that Access cant update values in multiple tables?

 

[cjard]

I'm not sure what you mean that Access cant update values in multiple tables?

Writing a query that updates joined tables can cause data stupidities because in a 1:M relationship it is possible to update fields in the 1 side with data from the M side which will be different. Most proper DBs disallow updates on views where keys of the 1 table are not also keys of the join. The concept is called Key Preservation and you can read lots about it on the web. Access allows it in some circumstances and it's not a good idea. By default a query that uses a join will NOT have I/U/D generated for it by a CommandBuilder because it cannot work out the necessary detail. You can write them yourself and youre strongly recommended to only update the M side of things

 

[cjard]

Ok, lets try this again ...

The picture in my previous post is exactly what I want.

Yes, but like Who Wants TO Be A Millionaire host Chris Tarrant advises "It's only easy if you know the answer" you know what you want, so of course the picture looks exactly like what you want; it makes no sense to us though!

Having read your post, it sounds like youre trying to pivot a large amount of data, or provide some custom table creation functionality. Esentially you have, say 100 questions the program knows how to ask (or 1000 or whatever) and you want to store the answers in a table, but if the user has chosen [this set of 79 questions] you want to then make a table containing 82 columns, the name, id, whatever, and then 79 columns for the answers.

Fortunately, the datagridview can auto generate columns when you bang data into it, so can the datatable.. So all you need to do is create a custom CREATE TABLE command, with the relevant 82 lines of fields you want in the table, and you're ready to use a commandbuilder to make a set of I/U/D queries to update your rows so you can shuttle your data back and forth.

Ting is.. it's a bit more of a nuisance than just working with data in a vertical fashion, i.e. unpivoted, and then only pivoting it when it comes to display

rather than having one table with 82 columns you have:

id, name, whatever, question, answer
1, a, blah, how much paint?, 80%
1, a, blah, how much wood?, 60%
....
82 rows later
1, a, blah, how much metal?, 20%


and then you can have any number of q and a, and the pivot will make them horizontal ofr display and report purposes.

 

[Tom]

Writing a query that updates joined tables can cause data stupidities because in a 1:M relationship it is possible to update fields in the 1 side with data from the M side which will be different. Most proper DBs disallow updates on views where keys of the 1 table are not also keys of the join. The concept is called Key Preservation and you can read lots about it on the web. Access allows it in some circumstances and it's not a good idea. By default a query that uses a join will NOT have I/U/D generated for it by a CommandBuilder because it cannot work out the necessary detail. You can write them yourself and youre strongly recommended to only update the M side of things

There is no mention of a need to restrict updating to a single update statement. Access most certainly can handle multiple tables and programmers most certainly can update values in multiple tables.

 

[keb1965]

I must admit that database interaction isn't one of my strong skills.

You are pretty close in the analogy of what the requirements are, and I knew going into this thing that it wouldn't be a simple proposition.

The purpose of this software is to create a database of survey responses. As with all surveys, the questions are likely to change on a regular basis. (i.e. after conducting one survey, those results indicate you need to ask different questions)

All questions are grouped by a common theme. Originally it was my idea to maintain a separate table for each question group, but as you know the updating of the data was proving to be a nightmare.

Each question for the survey is presented in a datagridview housed in a user control. Each group of questions has a control for the questions associated. In this manner there can be 1 or 1000 different permutations of surveys dependant upon how many and which of the groups of questions you choose to add to the survey.

The tables are verified (by the survey name) and the fields are verified (and created if neccesary) from the questions in the survey. Each question can have a single answer.

So, yes, there is the potential for huge amounts of data. For now, I have written a function that determines which record is being manipulated and I programmatically update the cells in the datagridview and put the previous data back into the dataset, but it has problems, for example, if a new record is created, it doesn't have an ID field, so I have to use the row number in the dataset, but if there are rows that have already been committed to the database, they have ID numbers. In one method, I simply tagged by ID, but that became a problem when new records were added, so then I tried using the row index, but when a record was deleted that had not been committed, the data became corrupted because the row is deleted from the dataset, whereas if an existing row had been deleted it is marked for delete and is held until the dataset is committed to save.
This is why I wanted to bind to a specific cell, because the updating/adding/deleting of records is managed automatically.

I can see I still have some work to do...

 

[cjard]

There is no mention of a need to restrict updating to a single update statement

That is the wise thing to do

Access most certainly can handle multiple tables and programmers most certainly can update values in multiple tables.

You say that like it's a good thing. To add some clarity, suppose the results of a query are:

Parent.ID, Parent.Value, Child.ParentID, Child.ID, Child.Value
1, PVal, 1, A, CValA
1, PVal, 1, B, CValB

UPDATE (the query result set) SET Parent.Value = Child.Value

Tell me; what would you like to see in the Parent table now?

 

[cjard]

DataColumn has autoincrement (+seed, +step) settings to handle something like that; the thing is youre making a lot of work for yourself trying to represent horizontally something that you would be far better off storing and representing vertically

further, each question should have a name and possibly a group, and that be the PK, not some cooked up ID number (which as you can see, is already giving you troubles)

 

[keb1965]

ID is set by the database as an autoincrement value. You can see that in the picture I posted previously.

I would certainly like to know a better way of handling this, but I don't see how it is possible to store the data vertically without having another table, and that creates problems with updating the database.

Oh how simple it would be if I could bind a datacolumn to a single cell ... heck you can bind a column to a textbox why not a cell in a grid?

 

[cjard]

What are you banging on about? Are you going to answer my questions or not?


I'm trying to help you, not by fixing your broken solution, but by finding a solution that works.. There's a big difference. Compare a man with wings strapped to his arms, wanting to fly. The human muscles are not powerful enough to lift the body weight. Birds get away with having wings that work by flapping because of their strength:weight ratio. To enable a human to fly you employ a completely different method of generating lift. Strapping wings on that you flap, is a broken solution. The working solution is to use a gliding wing, but that's completely different to your solution.

 

[keb1965]

I thought I had answered all of your questions. If I havn't (I have re-read this thread 3 times) I don't know what question it is you want me to answer.

I certainly am willing to look at other solutions, regardless of what they are, but unfortunately the requirement of a vertical display is something I have no control over.

So, if you would please let me know what information you require I'd be more than willing to provide it, particularly if it will allow me to come to a successful completion of this project.

Thanks