Database-One to many to many

[Wallabie]

Hi

I'm trying to do a database sample and i just can't get this to work!!

Has anyone got a simple one to many to many example of how to display data in ADO in disconnected mode?
Or is there any links to some samples and tutorials.

It has to be one-many-many with at least 3 tables linked

Can anyone help?

[Edneeis]

Are you using a typed dataset? If not I think you have to set the relationships in the dataset or just use queries for updates.

[Pirate]

Edneeis ,are there number of DataSets or what ?
plz I am new to ADO.NET and need to know that .thanx

[Edneeis]

What do you mean by 'number of Datasets'?

[Pirate]

I mean how many datasets in ADO.NET ?
Thanx man.

[Edneeis]

One. Dataset is a type of Object. Typed Datasets are custom datasets created at designtime (usually be the IDE), but they are still just datasets.

[Pirate]

Thanx Edneeis .

[MrGTI]

Are you talking about a SQL statement like this?

SELECT Description, FastTrack, Reboot, aaa.FullName AS ModifiedByFullName, bbb.FullName AS CreatedByFullName, bbb.GROUPID AS CreatedByGroupID FROM ChangeRequest, Users aaa, Users bbb WHERE ModifiedByID = aaa.UserID AND CreatedByID = bbb.UserID

[Edneeis]

Yes Wallabie if you can post your code then we can probably help because a One to Many relationship shouldn't really be a problem.

[MrGTI]

A DataSet can have more than one "set" of data. You can retrieve multiple SQL statements into a single DataSet object.

[Wallabie]

Thanks Edneeis and MRGTI, i'm new to doing database's and ADO thats why i asked if there was a simple example of a one to many to many database.

I have a database setup,........i THINK i've done it right, i have 5 tables in it but they are not linked.

Table 1 has the Farms stored in it
Table 2 has the Paddocks stored in it
Table 3 has the Crop type in it
Table 4 has the Year stored in it
And Table 5 has the data that i need with a farm, paddock, year and crop type

Each farm has many paddocks and each paddock has a new year added and each year has many crop types, does that make sence?

What i need is some example on how these linkor some direction on where to go now. I can't post the database at the moment as the board here is REALLY laggy at the moment.

Thanks for your help

[MrGTI]

This will get you all of the paddocks linked to this farm:

SELECT * FROM tbl_Farms, tbl_Paddocks WHERE tbl_Farms.FarmID = tbl_Paddocks.FarmID

This will get you the same info, but with now with crop info:

SELECT * FROM tbl_Farms, tbl_Paddocks, tbl_Crops WHERE tbl_Farms.FarmID = tbl_Paddocks.FarmID AND tbl_Paddocks.CropID = tbl_Crops.CropID

Hopefully that sends you in the right direction. If you don't understand SQL, then it would be best to buy a book on writing SQL statements. (those are the most basic of SQL statements)

[Wallabie]

Ok thanks MrGTI, i'll try some things out.
I've not done any database programs or worked on anything like this so i'm new to all this.

Anyway here's a quick and dirty sample, the frmMain has the tables displayed in various controls, this is just for us to view nothing more.

The DataForm button goes to a form that the data form wizard made showing something like what i wanted. I want to be able to select a farm (which i can) and have it show the paddocks associated to it(which it does) and then...... this is where i'm lost..... i want to be able to click on a row in the datagrid and show all the details about that paddock, something similar to the datagrid on frmMain (Rotations) which shows the year and crop type.

I'm not sure if i've done the tables right or not or how to get that table to link. Hope you can help, in the mean time i'll try some more things out that you suggested.

P:S
Forgot to ask...are you supposed to use so many oledbDataAdapter's like that, one for each table? is that correct?

[Wallabie]

Narh, i'm still not getting it.

What books would you say would be good to get. I'd need one with lots of good clear advice and samples.

[Wallabie]

Well.....i don't know whats going on here Edneeis! I can't attach the project as it say's it's too big!!

It's only 98kb but it's saying 10000kb????

Something funny going on with the forums, i'll try a later time and send you a PM if i can get it attached.

Ok?

[Hay_Dez]

Start simple in the IDE

Create a connection in the Server Explorer Access or SQL

Then pull in the tables

or

Create Connection to Database link to the data file,

Drop an adapter in then walk thru the SQL select statement for one or all tables which is shown above

Then right click in the IDE on the Adapter and create a data set from that, this will create a table based off of the Select statment so u can use 1 adapter for all 5 or 5 adapters for each Table

With all tables under 1 adapter you need to create command statement to update, insert or delete

If you use 1 table per adapter it will create them for u...

then use the adapter to fill the dataset or datagrid.. with the dataset u can do a databinding, same with the grid

Look at what the system created for u and try to grasp what it did to link it all together and what statements were created thwn edit them to see if you can adjust or change the way it display or updates..

Hope this will give u a starting point so u can start programming it manually to get a better idea of the entire process this is just the simple way to get started


or maybe I am entirely off but it was worth a try

[Wallabie]

Edneeis:

I've hacked a few things out and managed to bring the size down a bit, i was only over by a few bytes.

Hopefully here's the file this time.

[Edneeis]

Here you go I changed more than I needed to so that it would run better. Really what was messing you up is that the selected index changed event was firing when the datasource was set and you hadn't filled the dataset yet.

I changed it so it just looks for the db in the parent folder of the exe. Also I made it load the full Paddock table at the form start and then just filter for the matchs so you don't ahve to go to the database everytime.

[Wallabie]

Hi Edneeis

Thanks, i'll have a look. Hmmmm i didn't think the paddock list could be loaded at start as it required a farm to be selected first.

thanks anyway i'll check it out.

[Edneeis]

Add this to the end of the cboFarms_SelectionChangeCommitted event:

'change lblarea binding
        If Not lblArea.DataBindings("Text").DataSource Is dvFiltered Then
            'remove old binding
            lblArea.DataBindings.Remove(lblArea.DataBindings("Text"))
            'set new one
            lblArea.DataBindings.Add("Text", dvFiltered, "PaddockArea")
        End If

[Wallabie]

Ahhh, thanks Edneeis.

I was trying to use DataBindings.Remove but i couldn't find the correct syntax to use (lblArea.DataBindings()).

I'll give it a go, thank you.