Question Update command is not passing change back to database ... sometimes.

[Runescope]

So, this is quite frustrating and I'm not understanding what the problem is.

To start it off, this is the bit of code:

FFoundRow = FDT.Select("[Code1] = '" & Code1 & "'")
FFoundRow(0).Delete()
FDA.Update(FDS, "Info")

FHCIndex = FHDT.Columns.IndexOf(Code1)
FHDS.Tables("History").Columns.RemoveAt(FHCIndex)
FHDA.Update(FHDS, "History")

Now, what happens is that it removes the row from one dataset and then passes that change to the database (all well and good so far), but then while it removes the column(field) from the second dataset(verified), it doesn't pass that change to the database, and I can't figure out why.

Anyone have any ideas? If you need more info about my code, don't hesitate to ask!

Thank you for your time and attention to this post.

 

[Runescope]

Sorry for being away from this thread for so long, I have many hats here at work and there was a holiday in there somewhere too

Right! I had looked at SQL, but I couldn't find a simple enough explanation on how to implement a disconnected database into VB.Net. And I mean I need it simple. I'm transitioning from VB6 to .Net (slowly and painfully) so I need things explained very carefully.

But anyways, even with SQL and changing the database so that each row was an item and each column a new day, I'd still need to know how to add and remove columns, so that's not much help.

Also, my database with 10 years of history is only 25MB, so I'm not too worried about size.

But I do appreciate all the information!

 

[jmcilhinney]

If you are trying to add and remove columns on the fly the you're doing it wrong. It's that simple. If you want to remove a column from any database then you'll need to execute an ALTER TABLE statement so by all means look that up but I'm not prepared to go into any great detail in order to help you do something that you should absolutely, positively not be doing.

 

[Runescope]

There are more things in heaven and earth Horatio than are drempt of in your philosophy :highly_amused:

I need to be able to add and remove not just items from a list, but a daily changing history of those items. If you know of a way to do that without add/removing columns, I am alllllllll ears.

 

[Herman]

Add/remove rows.

 

[Runescope]

If I use the rows for history, then I need to use the columns for items, which is what I'm doing. But the items being tracked aren't static. Sometimes the users will want to add more items in to be tracked, and sometimes remove other from being tracked.

So I could then use the rows for the items, but then I would be using the columns for the history. I am unaware of any way to use rows for both at the same time.

 

[Herman]

No, you use rows for everything... One Items table with the various item specs. Another table Prices with a date field, a foreign item key field and the price listed for that item at that date. Then you query them:

SELECT * FROM [Items] INNER JOIN [Prices] ON [Items].[ItemId] = [Prices].[ExItemId] WHERE [Prices].[DateOfPrice] = CONVERT(DateTime, '05/04/2012')

This makes it completely scalable, and you can add more features in the future simply by adding more relationships to other tables containing whatever additional info you want to add. For example let's say you also wanted to track the source (let's say supplier name) of each daily price, you could add a Sources table with fields for the supplier's contact info, and add an ExSourceId field to your Prices table, and modify your query like so to retrieve the contact info of the price source for one particular item on one particular day:

SELECT *
FROM [Items] INNER JOIN [Prices]
                ON [Items].[ItemId] = [Prices].[ExItemId]
             INNER JOIN [Sources]
                ON [Sources].[SourceId] = [Prices].[ExSourceId]
WHERE [Prices].[DateOfPrice] = CONVERT(DateTime, '05/04/2012')
  AND [Items].[ItemId] LIKE 'PART10293'

 

[Runescope]

Hmmmm, I hadn't thought of it that way. Though with 250 items being tracked (roughly), that would mean 250 rows added into the Prices table every day, that would be roughly 91250 rows a year. I think that's over the Access limit. lol

Maybe I should make another run at MySQL.

 

[Herman]

I have seen SQL tables with over 700K rows, that shouldn't be an issue. (After checking for SQL Express the only limit on rows per table is total available storage. For 10GB in SQL Express, with the maximum row size of a little less that 8K, that means at least 1.2 million rows. Realistically if you store 50 bytes per row it's more in the range of 20 million rows...) .And for the last time, yes, switch away from Access...

And welcome to the wonderful world of actually relational databasing!

 

[jmcilhinney]

You are wasting your time and ours fighting this. I am telling you that there is absolutely never, EVER a need to add and remove columns in the normal running of an application. Stop trying to prove that wrong and start looking for a way to represent your data in such a way you can use your database the way it was designed and intended to be used. If you want to store a list of items then you do it in a table where there is one row per item. If you want to be able to indicate which items are being tracked then you either use a column in that items table that you can set to true or false or else you have another table for tracked items where there is one row per tracked item. If you use a second table then it will contain a foreign key from the first table. If you need to keep a history then you have another table for that where there is one row per history record. Again, there would be a foreign key from one of the other tables.

One of the specific intentions behind relational databases is that you will only ever need to add rows and not columns. I have created many, many applications with many different database schemas and not once have I ever needed to add and remove columns on the fly. I don't know any other developer who has either, because relational databases are specifically designed so that you don't. Do you really think that your scenario is so special that noone who designs RDBMS or the databases in them has considered it? Every time I have encountered someone who believed as you do, i.e. that they needed to add and remove columns on the fly, what was actually needed was to add an extra table with a foreign key.

If you provide a single, full and clear description of the physical system that you want to model, I will tell you how I would design a database to do it. I can guarantee you 100% that it will not involve the need to add or remove columns at run time.