Relational Data, I'm Stuck!

[ghostrider]

Linking Combo box selection to textbox contents

Hi Guys,
I am currently a third year programmer in college, and am working on experience. I have a good grasp of vb6 but am just beginning with vb.net. Although I am doing well, i have hit a snag. I am currently designing a program that adds, displays and edits an access database. I have three tables named TblTenant, TblResidents, TblAddress. I want the user to be able to select an address from the dropdown box and then have textboxes display the relevant tenants and residents for that address. I can do this in vb6 using recordsets but am unsure of how in vb.net. Can anyone please help me on this. Also Could someone show me how to set the primary id key of the latter two tables so it is the same as the tenant entries as this is the link between the three tables. Thanks in advance.

 

[jmcilhinney]

You can still use the old ADO data access method you used in VB6 to do the same in VB.NET, but I wouldn't recommend it. The preferred, and purpose-built, data access method in .NET is collectively named ADO.NET. I suggest that you do some reading in the help system or on MSDN to get an overall grasp of the technology, and also download the 101 VB Samples mentioned in this thread and examine the data access sample(s).

 

[ghostrider]

Still having problems

Hi,
Thanks for your earlier post, anyway I have the app saving, and retrieving from the database. I would verymuch appreciate if someone could show me some code examples to populate the textboxes i list below based on the circumstances shown below. Also could you tell me how to make the address and resident tables have the same key as TblTenant. I know its alot but I would really appreciate any help you could give me.

database with three tables TblTenant, TblAddress, Tbl Resident.

TblTenant: fldFirstName, fldSurname
TblAddress: fldAddressID, fldAddress1, fldAddress2, fldCounty
TblResident: fldFirstName, fldSurname

A form edit has a combobox that is being populated as below and when the user selects the address i want them to be able to see the other details of that address in the textboxes.

 

[TechGnome]

You can still use the old ADO data access method you used in VB6 to do the same in VB.NET, but I wouldn't recommend it. <snip />

Despide the disqualifier, I'm ashamed and surprised you even said that...

Look through the 101 examples, there's at least one in there that does that. I don't use ADO.NET in this way, so I won't be much help. I do know that there is a way to setup relations within the dataset (DataRelations, I think) but I don't know how to 1) set one of or 2) just how they work to filter down data.

Tg

 

[jmcilhinney]

Despide the disqualifier, I'm ashamed and surprised you even said that...

Look through the 101 examples, there's at least one in there that does that. I don't use ADO.NET in this way, so I won't be much help. I do know that there is a way to setup relations within the dataset (DataRelations, I think) but I don't know how to 1) set one of or 2) just how they work to filter down data.

Tg

I didn't want to tell someone that they couldn't use ADO in .NET and have them find out later that they actually could, and have them think that I was giving bum advice. Better that they know that it's available but that there is a preferred alternative, in my opinion.

The easiest way to create a DataSet with related tables is to do it in the designer. You can add DataSet to your form, which creates a typed DataSet, i.e. it gets its table schema and relationship data directly from your database. You will be able to create a connection and data adapter(s) specifically designed to work with that particular DataSet type. The typed DataSet gets added to your project as an XSD file, so you basically have a new type, which you then create instances of to hold your actual data. Have a look in the help or on MSDN for "typed DataSet" for more info.

Edit:
Tg, notice that there is at least one example in the 101 samples that uses ADO 2.6, so Microsoft obviously aren't trying to hide it.

 

[ManicCW]

Datasets

I'm tired so I will explain you how to do it in designer.

1. Create dataset by dragging dataset icon from toolbox.
2. Drag tables from server explorer. It will create dataadapters for you.
3. Select each dataadapter and click Generate dataset in Properties panel.
4. Put all in designed dataset.
5. Bind combos to specific datamember (properties panel dataset and datamember properties)
6. Bind value and text property of combo's to specific column (Value to ID)

in code:

on load event write:

daTable1.Fill(ds.Table1)
daTable2.Fill(ds.Table2)
daTable3.Fill(ds.Table3)

on selected index event of one combo write:

daTable2.SelectCommand = "SELECT FROM Table2 WHERE ID = " & me.combo1.selectedindex

i surely misted out something couse i wrote this without thinking

hope it helps

oh yeah, fill the dataset again

 

[ghostrider]

Cheers for all the help lads, ill let ye know how i get on.