Add Conditional Column To Database

Nice tables !

If you're using the data designer I never found a way to change a table design after it was put in place but that was many years ago so I think design time is out which is one of many reasons why I won't use the data designer.

I have some other bad news for you. ADO does not have a way to add a column such that it is persistable in a database.

It's possible but I am not sure that the ADOX objects may use that but please be weary as they are COM Objects.

I believe you may be able to do this using sql statements. I have created entirely new tables with SQL.

What I said above relates to persisting these dataobjects. If there is no need to persist then, you can append all you want but it's my impression that this is not what you want to do.

This can be done thusly:

Dim a as new datacolumn

A.Datatype = system.type.Gettype("System.Integer") ' Case sensitve!!!!

A.ColumnName = "Bob"

Table.columns.add(a)

But I do not think there's anyway to persist this change without using ADOX or SQL.

ReneeC at 2007-10-3 >

I was looking at your tables and i am guessing that you are trying to keep track of the hours on particular parts and perform maintenance or replace at certain intervals.

I don't know your system but it seems as though you could make it easier to keep track of individual parts and their hours if you were to break it up into several tables.

You have the same aircraft listed 9 times with different events, limits and intervals. After a while this is going to get real confusing to keep track of individual items.

Your master table could be the aircraft table for each individual aircraft

Then you would have a part or assembly table that would be a child of the aircraft table

this would give you the ability to keep track of specific parts for each aircraft

Then you have an hours table for each individual part or assembly

this will let you log individual hours for each trip or to make it easier you could just leave it with one record and update the hours as a total after each trip instead of dealing with a bunch of individual ones.

When you have them set up with a foreign key relationship then you can set it up to automatically filter the parts and hours based on which aircraft you select.

When you select aircraft with id - ZK-HQQ in the master table then it will show all the parts for that particular aircraft in the child part table and it will the show the hours for that particular part in the hours table.

Then you can run a report that will be based on a certain total hours for each part or assembly

Then you can check the total hours for each part or assembly and compare to the events and determine what needs to be done to the part or assembly.

I hope this makes sense

js06 at 2007-10-3 >

js06 wrote:

I was looking at your tables and i am guessing that you are trying to keep track of the hours on particular parts and perform maintenance or replace at certain intervals.

Yes Exactly!

I think I do actually have my tables setup similar to what you are saying. I just posted bad examples. I will post some actual examples from my tables below.

• I have a main table (no example shown) with a list of all the aircraft and its details ie manufacturer, type, model...
• I then have another table (Table A) which lists all the Limits that apply to the aircraft ie AC Hours, Engine Hours, Cycles, Landings...etc
• I then have another table (Table B) which lists all the components on the aircraft. This table contains all the component details it does not contain details of the limits because one component may have more than 1 limit assigned to it ie AC Hours and Landings.
• I have another table (Table C) which contains the limit details for each component.

All tables contain the aircraft rego ZK-HQQ. Tables B and C are linked by the "Comp ID".

The problem is I am trying to get the correct values in the "current", "remaining" and "due at" columns of table C. They need to look up the master limit table A to see where the aircraft is at in the "current column". The "remaining" and "due at" columns need to be calculated from this lookup column "current"

I have shown a few examples of the results i am looking for in the "current" "due at" and "remaining" columns.

Thanks you both for your ongoing help and patience.

AC Master Limits (table A)

Limit Type	Aircraft	AC Serial No	Description	Current	Total	Calc Factor	Date Last Updated	TTCW	DateCW
AC Hours	ZK-HQQ	4381	Hourly Limit	100	100	Counts	23/02/01	50	20/06/07
Cycles	ZK-HQQ	4381	Cycle Limit	120	120	Counts	27/02/01	89	20/06/07
Engine 1 Hours	ZK-HQQ	4381	Hourly Limit	100	100	Counts	27/02/01	46	30/05/06
Months	ZK-HQQ	4381	Monthly	0	0	Months	23/02/01
Days	ZK-HQQ	4381	Days	100	100	Days	13/09/01
AC Hours	ZK-HZZ	4234	Hourly Limit	1000	1000	Counts	27/09/07

Component Details(Table B)

Aircraft	Tracking No	TT Installed	Sort Order	Assembly Name	Sub Assembly Name	Component	Part Number	Serial No	ATA	Comp ID	Date Installed
ZK-HQQ	104	1500	1			Seat Belt	AS350-355678	3131	32-10	1	2/04/05
ZK-HQQ	105	1230	2			Tail Rotor Blade	AS350-239984	2134	42-10	2	3/08/06
ZK-HQQ	106	1230	3	Aircraft	Rotor Drive	Main Drive Shaft	AS350-242332	3213	31-43	3	3/08/06
ZK-HQQ	107	1430	4	Aicraft	Fuel	A/F Fuel Filter	AS350-234232	432	43-34	4	2/09/06
ZK-HZZ	108	3450	5	Cockpit	Guages	Altimeter	AS355-323211	1422		5	3/04/05

AC Limit Details(Table C)

ACLD ID	Aircraft	Limit Type	Life Limit	Event	Current	Prior Count	Transacted At	Remaining	Due At	Transacted Date	Comp ID
1001	ZK-HQQ	AC Hours	2025	Retire	100	0	50	1975	2125	1/01/01	1
1002	ZK-HQQ	Cycles	3000	Retire	120	0	60	2940	3060	1/01/01	1
1003	ZK-HQQ	Engine Hours 1	2025	Retire		0	1146.00			1/01/01	1
1004	ZK-HQQ	AC Hours	4550	Retire		926	1146			1/01/01	2
1005	ZK-HQQ	Cycles	6000	Retire		2999	2998.00			27/02/01	2
1006	Zk-HZZ	Engine Hours 1	4550	Retire		926	1146.00			27/02/01	2

Reafidy at 2007-10-3 >

js06,

Thanks for your reply.

I currently have 2 datagridviews on my userform one which shows the component list table B. and when the users clicks on one of the components in that datagridview the second datagridview only show the limit details that apply to that component. I really like this for our particular situation it is very clear to the user. This is because one component might have a limit imposed by the manufacturer of 1000 AC hours and another limit of 3 Months and may be even another limit of 5000 Landing cycles just for that one component!

I dont have a need to filter by assembly or sub assembly yet. But I like your clever thinking and will definately think it over in fact I probably will use that idea. It would be good to like you say select an aircraft then have the 1st datagridview filtered to show only assemblies for that aircraft then filter again to show sub assemblies.

However I can still not see how that will help with my underlying problem. And that is getting the correct data for the calculations for the limits of each component. I only want to update the hours of the aircraft in the limit table I dont want to have to update the limits for each component individually.

Can you see away to do the calculations.

Thanks again for your help, its great, this is a big project for me and I am well aware of the complex nature and are more than prepared to put the work in. But I do appreciate all the help I can get!!

Reafidy at 2007-10-3 >

Ok i have some code for you.

This is based on the your first post where it shows tableA and tableB

How it works is to get the forst record from tableB and find the limit name and the interval

then it loops through the entire tableA untill it finds the match and then gets the current value

it adds the interval to the current value and then displays the total in the new column next to the record in tableB

It loops through every record in tableB repeating the same process

Basically

Get first record values in tableB

loop through tableA to find match then get value

add value and display

get next record and repeat again

etc...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim totcol As New DataGridViewTextBoxColumn

totcol.HeaderText = "Total"

totcol.ValueType = System.Type.GetType(DbType.Int32)

Me.TableBDataGridView.Columns.Add(totcol)

Dim limitB As String = Nothing

Dim intervalB As Integer = 0

Dim limitA As String = Nothing

Dim currentA As Integer = 0

Dim total As Integer = 0

Dim tableBrow As Integer = 0

For Each rowB As DataRow In Me.DB2DataSet.TableB.Rows()

If Not TypeOf rowB("Limit") Is DBNull Then

limitB = rowB("Limit")

intervalB = rowB("Interval")

'MsgBox(r("Limit"))

End If

For Each rowA As DataRow In Me.DB2DataSet.TableA.Rows

If Not TypeOf rowA("Limits") Is DBNull Then

If rowA("Limits") = limitB Then

If Not TypeOf rowA("Current") Is DBNull Then

currentA = rowA("Current")

Exit For

End If

End If

End If

Next rowA

total = intervalB + currentA

Me.TableBDataGridView.Rows(tableBrow).Cells(9).Value = total

tableBrow = tableBrow + 1

Next rowB

End Sub

in this line Me.TableBDataGridView.Rows(tableBrow).Cells(9).Value = total

the cells(9) is the column index of the added total column

so if you have 5 columns and you add the new one - the index will be 5 because they start at 0

Now the problem you are going to have to face is if your database isn't setup properly it will never work correctly.

As you progress with this you will have to match up the aircraft, the assembly, sub assembly and then the component and then get the values.

I'm afraid though that as you move further into this project it will become very complex this is why you should set up your database correctly from the beginning. Once you get to a certain point you will realize that you may have to start over and redo the whole thing. But sometimes that may be unavoidable when working on a project like this. The whole idea is to keep everything in table groups to access the data easily and not have to find it in different places in the same table.

Let me know if you have any questions.

Good luck with it.

js06 at 2007-10-3 >

I'm not quite sure about populating the data in the table directly at the program load. I don't know how to do it with the loops. And i don't know how to modify the existing rows in the table directly.

If you are wanting to load the data and show it quickly when ready, another alternative might be to load the form and populate the datagridview at the program start and simply keep the form hidden untill you are ready for it. Instead of closing the form you can just hide it and then show it as needed.

Also, once you get the tables setup correctly, if you were to have the tables all on one form then you would not have to switch between forms. Or you can link the tables to work together on seperate forms and then you can just switch between the forms without closing them.

I would suggest starting a new thread for this question about populating the values directly in the table itself. This is just beyond my knowledge at the moment.

js06 at 2007-10-3 >