databindings with 4 tables - getting nightmare !
Hello everybody ! Here is my terrible problem :
VB.NET 2003 / Access 2003
I must write a program who can managed a timetable for several companies.
The tables are : Companies (Key = ID), Persons (Key = ID from Company + ID from Person), TimeTable (...), TimeTableDetails (...).
My program must work with forms in which datas are coming from more than one table.
For instance, the form "FrmPerson" has a combobox where you select the Company, then the persons from the company selected (linked by a foreign key in the table Person) must fill a ListBox (Name + Surname). I must be able to navigate through the companies, and the listbox must refresh with the persons from that company. The datas of a person must then be placed in several TextBox (adress, phone, ...).
I must be able to modify a person (name, adress, ...), delete a person or add one.
The others forms are based on 3 tables (Company + Person + TimTable), but i think if i can do one form, it should be ok.
Ma bigbigbig problem is that i'm learning vb.net, and i have a very small time for doing this code.... i tried the assistant, but then i dont understand what to do with my combobox linked with my listbox linked with my textboxes.
What i need is a small sample of code which shows how to link these 3 controls, all based on the same Access connection.
Many many thanks for your help !
Fred
VB.NET 2003 / Access 2003
I must write a program who can managed a timetable for several companies.
The tables are : Companies (Key = ID), Persons (Key = ID from Company + ID from Person), TimeTable (...), TimeTableDetails (...).
My program must work with forms in which datas are coming from more than one table.
For instance, the form "FrmPerson" has a combobox where you select the Company, then the persons from the company selected (linked by a foreign key in the table Person) must fill a ListBox (Name + Surname). I must be able to navigate through the companies, and the listbox must refresh with the persons from that company. The datas of a person must then be placed in several TextBox (adress, phone, ...).
I must be able to modify a person (name, adress, ...), delete a person or add one.
The others forms are based on 3 tables (Company + Person + TimTable), but i think if i can do one form, it should be ok.
Ma bigbigbig problem is that i'm learning vb.net, and i have a very small time for doing this code.... i tried the assistant, but then i dont understand what to do with my combobox linked with my listbox linked with my textboxes.
What i need is a small sample of code which shows how to link these 3 controls, all based on the same Access connection.
Many many thanks for your help !
Fred
ASKER
Hello Baan,
In fact, i have already written a lot of code ;), and i use exactly what you're talking about. I think i wont use the wizard, because i dont understand what does the generated code do...
Ok, my dataset is filled on a query which returns the persons from an ID company. So first question : how do i link the listbox (which contains the person's name+surname, so 2 fields) to the combobox (which contains the Companies) ?
Second question : how do i link the Textboxes (adresse for instance) to the person selected in the listbox ? I dont know if i have to use the ValueMember property or else ?!?
As u say... there are TOO many pages written, i just didn't find exactly what i want to do...
Thx, Fred
In fact, i have already written a lot of code ;), and i use exactly what you're talking about. I think i wont use the wizard, because i dont understand what does the generated code do...
Ok, my dataset is filled on a query which returns the persons from an ID company. So first question : how do i link the listbox (which contains the person's name+surname, so 2 fields) to the combobox (which contains the Companies) ?
Second question : how do i link the Textboxes (adresse for instance) to the person selected in the listbox ? I dont know if i have to use the ValueMember property or else ?!?
As u say... there are TOO many pages written, i just didn't find exactly what i want to do...
Thx, Fred
ok.. for binding the ListBox with full name ( name+surname), modify the SELECT statement. eg:
SELECT CONCAT(name,' ',surname) AS FullName,...
Then after filleng the dataset, bind the FullName field to the ListBox
ListBox1.DisplayMember = "FieldName"
ListBox1.DataSource = DS.Tables(0)
-Baan
SELECT CONCAT(name,' ',surname) AS FullName,...
Then after filleng the dataset, bind the FullName field to the ListBox
ListBox1.DisplayMember = "FieldName"
ListBox1.DataSource = DS.Tables(0)
-Baan
ASKER
Ok, i have a dataset on the combobox "Companies", filled on the Form_Load.
Then i have another dataset on the listbox "Persons", filled on the ComboBox1_SelectedIndexCha
I wrote "Me.LstPersons.Items.Clear
How to delete the items in the listbox when the combox is refreshed ?
Then i have another dataset on the listbox "Persons", filled on the ComboBox1_SelectedIndexCha
I wrote "Me.LstPersons.Items.Clear
How to delete the items in the listbox when the combox is refreshed ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thx, that's what i did...
But i feel like basically my dataadapters and dataset are wrong created...
What is the best in this situation :
A combobox filled with the Company Names = a dataadapter + a dataset on the query
rqt = "SELECT T_Entite.Ent_ID, T_Entite.Ent_Nom"
rqt &= " FROM T_Entite;"
A listbox filled with the Persons Name = another dataadapter + a dataset on the query
rqt = "SELECT T_Personne.Per_ID, T_Personne.Per_Nom, T_Personne.Per_Prenom"
rqt &= " FROM(T_Personne)"
rqt &= " WHERE (((T_Personne.Per_Lien)=" & Me.CmbEntite.SelectedValue
And then a third dataadapter and a third dataset for all the textboxes filled with personnal datas ?
OR
should i have ONE datadapter for the 3 tables (how to write the query with the relations ?) and 3 datasets or only one ?
Thank you for your help !!!
Fred
But i feel like basically my dataadapters and dataset are wrong created...
What is the best in this situation :
A combobox filled with the Company Names = a dataadapter + a dataset on the query
rqt = "SELECT T_Entite.Ent_ID, T_Entite.Ent_Nom"
rqt &= " FROM T_Entite;"
A listbox filled with the Persons Name = another dataadapter + a dataset on the query
rqt = "SELECT T_Personne.Per_ID, T_Personne.Per_Nom, T_Personne.Per_Prenom"
rqt &= " FROM(T_Personne)"
rqt &= " WHERE (((T_Personne.Per_Lien)=" & Me.CmbEntite.SelectedValue
And then a third dataadapter and a third dataset for all the textboxes filled with personnal datas ?
OR
should i have ONE datadapter for the 3 tables (how to write the query with the relations ?) and 3 datasets or only one ?
Thank you for your help !!!
Fred
ASKER
Hi,
All right, i knew my question not a good one ;) I've read a lot and now my soft is "living".
I used one dataadapter which source change everytime i want to put a table in my dataset. My comboboxes and listboxes are dynamically built, using a dataview (RowFilter property => ValueMember / SelectedIndexChanged).
My problem is now :
How to refresh the combo or listboxes, when an entry is deleted for instance. I tried with a .reset on the dataset, or a .refresh on the combox, but everytime i have a splendid error like "Index 0 is not positive and below the total numberof lines"... what to do ?!?
For the rest of the soft... everything's fine (lot of theory before work !). Only this refresh thing, which makes me very angry :(
All right, i knew my question not a good one ;) I've read a lot and now my soft is "living".
I used one dataadapter which source change everytime i want to put a table in my dataset. My comboboxes and listboxes are dynamically built, using a dataview (RowFilter property => ValueMember / SelectedIndexChanged).
My problem is now :
How to refresh the combo or listboxes, when an entry is deleted for instance. I tried with a .reset on the dataset, or a .refresh on the combox, but everytime i have a splendid error like "Index 0 is not positive and below the total numberof lines"... what to do ?!?
For the rest of the soft... everything's fine (lot of theory before work !). Only this refresh thing, which makes me very angry :(
ASKER
To Admin : Can u close this question, I have resolved it myself (with a little help from DotNetLover_Baan, thank u !)
Briefly what you have to do is:
Create and fill a dataset using DataAdapter with proper sql SELECT query.
Bind all the controls to the coresponding fields of the table with the help of Dataset.
This is the basic idea. I suggest you NOT to go for drag and drop data controls. Create them through code. You will find thousands of help pages on "OleDb" Ado.Net. Start working on it... we are here to help you. :))
-Baan