Updating a database

[JohnW2]

Hi there,

I'm in the early stages of transitioning from VB6 to VB.NET, and am experimenting to find out how to do things. I suspect I'm missing something obvious, but ....

I've created a small local database and the associated DataSource. I can view Tables in that database using DataGridView, in which I can edit the data and add new rows etc. However, try as I may, I cannot find a way to save those changes/additions to the database.

The documentation seems to indicate that if I drag the DataSource onto a Form (which is one of the approaches I've tried) that that will automatically result in a "BindingNavigator" tool strip (with a 'Save' button) appearing on the Form, but that is not happening.

In any event, I would ideally prefer not have to press a 'Save' button manually - is there not a way of getting the database updated automatically (e.g. on closing the Form)?

Any advice would be much appreciated.

Kind Regards, John

[wes4dbt]

It sounds like you have created a Datasource and dragged a table onto a form from the datasource. In that case, you are using a TableAdapter "Fill" method" to retrieve the data. To push any changes back to the database you would use the TableAdapter "Update" method. We need more information, what type of database, does the database table have a primary key, post the relevant code.

It should add the dataset, TableAdapter, Bindingsource and BindingNavigator to the from when you drag and drop the table. It wont if you have already added other items to the form. I';d suggest adding a new form and trying again.

If you are going to be doing more than just this one app I would suggest looking at using DataAdapters/DataReaders for working with the database. jmc has a tutorial in the code bank http://www.vbforums.com/showthread.p...ses&highlight=

[JohnW2]

It should add the dataset, TableAdapter, Bindingsource and BindingNavigator to the from when you drag and drop the table. It wont if you have already added other items to the form. I';d suggest adding a new form and trying again.

Thanks, and thanks for your interest. That was the problem. I've been doing all sorts of experimenting, so I couldn't tell you what had previously been done with/to the form - but when a tried again with a brand new form, everything worked as indicated in the documentation (although it didn't warn me about the need for a 'virgin' form ).

If you are going to be doing more than just this one app I would suggest looking at using DataAdapters/DataReaders for working with the database. jmc has a tutorial in the code bank http://www.vbforums.com/showthread.p...ses&highlight=

I'll certainly look at that - many thanks. As I said, I'm currently just playing around with experiments to discover/learn how things are done in VB.NET, so it's not a specific application I'm working on.

However, this is the sort of thing that I need to use quite often, and it may be that I ought to be taking a totally different approach. My most common need is just to be able to store (a fairly modest amount of) data locally, and for a user to be able to edit and add to it as transparently as possible (as well, of course, with other parts of the app actually processing the data) - so, ideally, I would prefer a user not to have to press a 'Save' button, or even to be exposed to the BindingNavigator tool strip. I wonder if there is a different approach, perhaps not even involving a database as such, that I should be contemplating? [In VB6 I usually did this sort of thing by creating my own routines for reading, writing, editing and processing a number of (commonly .csv) files].

Kind Regards, John

[wes4dbt]

It sounds like a SQLite database would work well for you, as long as your not planning a multi-user system that requires a server database like Sql Server. You can download SQLite manager system for free. With this you can deploy your system on any local machine and all you have to do is have the SqLite Dll in the same directory as the exe.

You can always delete the bindingnavigator and there are ways to have the system automatically save the data. You could have the data saved when you close the form or when the BindingSource position changes. That's up to you. But with Typed Datasets and TableAdapters, if the table has a primary key, it's as simple as,

yourBindingsource.EndEdit
yourTableAdapter.Update(yourDataset.YourTable

If you look at the code and objects added when you drag the table to the form, you will see it probably added a TableAdapterManager. If you look in the bindingnavigator Save_click you will see the code. Instead of "yourTableAdapter.Update(yourDataset.YourTable" it will use "yourTableAdapterManager.UpdateAll(yourDataset).

[JohnW2]

It sounds like a SQLite database would work well for you, as long as your not planning a multi-user system that requires a server database like Sql Server. You can download SQLite manager system for free. With this you can deploy your system on any local machine and all you have to do is have the SqLite Dll in the same directory as the exe.

Thanks. I'll look into that possibility.

You can always delete the bindingnavigator and there are ways to have the system automatically save the data. You could have the data saved when you close the form or when the BindingSource position changes. That's up to you. But with Typed Datasets and TableAdapters, if the table has a primary key, it's as simple as .... If you look at the code and objects added when you drag the table to the form, you will see it probably added a TableAdapterManager. If you look in the bindingnavigator Save_click you will see the code. Instead of "yourTableAdapter.Update(yourDataset.YourTable" it will use "yourTableAdapterManager.UpdateAll(yourDataset).

That is, indeed, what I see.

However, I spoke too soon. Although dragging the DataSource onto a 'clean' new form does, indeed, result in the BindingNavigator tool strip appearing on the form, neither clicking its 'Save' button nor doing the equivalent programmatically results in the database being updated with changes/additions. I think that I must be doing something silly - any idea what it could be?

Kind Regards, John

[jmcilhinney]

However, I spoke too soon. Although dragging the DataSource onto a 'clean' new form does, indeed, result in the BindingNavigator tool strip appearing on the form, neither clicking its 'Save' button nor doing the equivalent programmatically results in the database being updated with changes/additions. I think that I must be doing something silly - any idea what it could be?

There's a very good chance that you're looking in the wrong database or in the right database at the wrong time when checking whether something was saved. The call to Update or UpdateAll tells you all you need to know. That call will either succeed with a zero result, succeed with a non-zero result or throw an exception. Those are the only three possibilities. If the first option occurs then there were no changes to save, so that's what you need to address. If the second option occurs, everything is working as it should and you need to determine why you can't see the changes as expected. To that end, follow the first link in my signature below. If the third option occurs then you need to look at the specific exception to determine the specific issue that you need to address.

P.S. Actually, that link no longer works. I'll try to track down it's new location. In the mean time, let me know if you need that information and I'll provide it directly.

P.P.S. I've updated the address behind that link to something that addresses the same issue but I'm not completely happy with it. I might look at writing something myself at some point but not right now.

[JohnW2]

There's a very good chance that you're looking in the wrong database or in the right database at the wrong time when checking whether something was saved. The call to Update or UpdateAll tells you all you need to know. That call will either succeed with a zero result, succeed with a non-zero result or throw an exception. Those are the only three possibilities.

Thanks, and apolgioes for the delay - I've been away fro a couple of days.

I've 'started from scratch' again (ne project, new Table etc.), with exactly the same results. I'm starting to wonder whether I maybe have not got some 'required' component of VS installed. Briefly, what I've done is this ...

... starting with the virgin project and form, I have created a database with one table entered a few rows of data (using "Show Table Data" via Server Explorer) essentially per these instructions , and then created a DataSource and dragged it to my clean form per these instructions . Dragging the DataSource onto the form results, as expected in the BindingNavigator tool strip (with a 'Save' button) appearing on the form.

Using "Show Table Data" via Server Explorer, I can view the table. I also can edit and add to the table by that method, and, when I do a 'Save All', the database is updated with those changes/additions.

However, as before, when I run the project, although I can view the table, and change and add to its contents on screen, neither pressing the 'Save' button (of BindingNavigator tool strip) nor doing the equivalent programmatically result in the database being updated.

Any thoughts? As above, is it possible that I'm missing some required component of VS?

Kind Regards, John

[jmcilhinney]

You have quoted my post about testing the result of the call to Update/UpdateAll but you have given no indication that have followed those instructions. You first need to determine whether such a call is being made and then what the result is. We have to know what problem we're trying to solve.

[JohnW2]

You have quoted my post about testing the result of the call to Update/UpdateAll but you have given no indication that have followed those instructions. You first need to determine whether such a call is being made and then what the result is. We have to know what problem we're trying to solve.

Apologies - fortunately I still have a copy of what should have got posted (I composed it 'off-line') from which I somehow, in what got posted, managed to delete the crucial small paragraph which was meant to follow the first one-liner. It read:

"As you know, I am very new to VB.NET. How do I determine the result of such a call and whether any exceptions resulted from the call? In the meantime ...."

Kind Regards, John

[jmcilhinney]

Update and UpdateAll are functions. How do you usually get the result of a function and see what it is? As for exceptions, if you're not already explicitly catching them then your app would be crashing if one was thrown, so that would be a bit of a giveaway.

I suggest that you stop what you're doing and learn two things. First, learn how to debug. That means setting breakpoints, stepping through code and using the tools provided by the IDE to examine the state of the application at each step. That's how you see the values of variables, etc, as your application executes. That's how you see the result of a function that you have assigned to a variable. Second, learn about exception handling. Database access is one of those things that can always go wrong because certain aspects are beyond your control, so you should always use exception handling in case of that.

[JohnW2]

Update and UpdateAll are functions. How do you usually get the result of a function and see what it is?

I confess that I wasn't thinking of them as functions. Now I am, I can tell you that UpdateAll is returning '0' if called when there are no changes/addition to update, and '1' if called when there there are such changes/updates (which is what I would have expected).

As for exceptions, if you're not already explicitly catching them then your app would be crashing if one was thrown, so that would be a bit of a giveaway.

I would ask you to try to bear with me, since I haven't even got used to the VB.NET vocabulary yet - I still think of it as 'error handling' - and, yes, I have implemented it to some extent. Attempting to update the database is not resulting in either crashing, nor are any exceptions being trapped/caught.

So, answering your questions, the UpdateAll is returning '0' if called when there are no changes/additions to update, and '1' if called when there there are such changes/additions, and is not generating any exceptions. Does that get us any closer to ideas about why my database is not getting updated?

I suggest that you stop what you're doing and learn two things. First, learn how to debug. That means setting breakpoints, stepping through code and using the tools provided by the IDE to examine the state of the application at each step. That's how you see the values of variables, etc, as your application executes. That's how you see the result of a function that you have assigned to a variable.

Debugging is second nature to me, and is fortunately conceptually the same as with VB6 (and most other languages with which I have some familiarity). I did try, but there was not really anything much/useful to 'step through' and, since I was not thinking of it as a function, I had not assigned UpdateAll to a variable, so there was no 'result' to see.

Second, learn about exception handling. Database access is one of those things that can always go wrong because certain aspects are beyond your control, so you should always use exception handling in case of that.

See above - I'll try hard to remember and use (and recognise) the correct vocabulary!

Thanks for your ongoing assistance.

Kind Regards, John

[jmcilhinney]

So, answering your questions, the UpdateAll is returning '0' when called if there are no changes/additions to update, and '1' when there there are such changes/additions, and is not generating any exceptions. Does that get us any closer to ideas about why my database is not be getting updated?

Yes and no, because it's actually telling us that your database IS getting updated. That 1 means that one record in the database was affected by your SQL statement, so one record was indeed inserted, updated or deleted. The issue appears to be, as I thought from the outset, that you are either looking in the wrong database or looking in the right database at the wrong time. The link in my signature that I directed you to previously should address the specific issue you're having.

[JohnW2]

Yes and no, because it's actually telling us that your database IS getting updated. That 1 means that one record in the database was affected by your SQL statement, so one record was indeed inserted, updated or deleted. The issue appears to be, as I thought from the outset, that you are either looking in the wrong database or looking in the right database at the wrong time.

Yes, I know that's what you said before, but I couldn't really see how that could be the case at the time, and nor do I now. Put another way if some database is getting updated, goodness only knows what 'database' it is! ...

... throughout my experiments I have ensured that there was only one .mdf file anywhere in my computer (i.e. the one of interest). If I update the table in that database 'directly' (via Server Explorer), the contents of that .mdf file changes, and its datestamp becomes the time of that update - and the updates to the table become visible to my program. However, after the (unsuccessful) attempts to update the database programmatically (utilising the UpdateAll call), both content and timestamp of the .mdf file remain unchanged, despite the return code from the function call - no matter how many times I introduce changes and call UpDateAll.

So, as I see it, either the return codes from UpdateAll are 'not telling the truth' or else something unknown (and not visible to my program) (and which I know is not an .mdf file) is getting 'updated'! ... or can you think of some third possibility?

The link in my signature that I directed you to previously should address the specific issue you're having.

I'll read it again and see if it can help me.

Kind Regards, John

[jmcilhinney]

throughout my experiments I have ensured that there was only one .mdf file anywhere in my computer (i.e. the one of interest).

I have created a database with one table entered a few rows of data (using "Show Table Data" via Server Explorer) essentially per these instructions

Except that is a contradiction because, if you followed those instructions, there will be two MDF files.

can you think of some third possibility?

Yes I can. The one specifically described in the link I directed you to two days ago:

In short, the local database file will be copied to Output directory, and THAT database is the one that will get updated.

If the copy in the output directory is the one that gets updated, what point is there to looking for changes in the original in the project folder? This is something that takes a lot of people unawares but you probably ought to get past it when the reason is pointed out to you.

[wes4dbt]

Usually when you create a datasource it will ask you if you want to save the connection string, if you said yes then you will find it in the Project->Settings. Depending on how you setup the db properties and the connectionstring there may be several mdf files. One in the project folder with the forms and such, one in the debug folder, one in the release folder. It is complicated to understand and there are several different way you can choose to setup your project. One way to get a grasp on it would be to find all the project mdf's and check the date/time. Make a change using Server Explorer tools, check the date/time stamps. Then make a change using code, then check the date/time stamp. To tell you the truth I have to refresh my memory of how it works when I haven't used it in a while.

[jmcilhinney]

It is complicated to understand

It's not really. It's just not obvious to the uninitiated. When you add an MDF file to your project, you're adding a source file, just like when you add a form or a class or anything else. If that was the only MDF file then all your test data would end up in it. When it then came time to deploy your app, you'd have to clean out the database, which you might not do properly, and you'd lose all your test data. By copying the source data file to the output folder, you are able to test against that copy while retaining a pristine source file. Any time you want to refresh your test database, you simply delete the copy and a new one will be created. Any time you update the schema or default data, that will be copied to the output folder. When you build a Release version to deploy, the clean source file is copied to the Release output folder, not affecting the test version in the Debug output folder. There's nothing especially complex about it at all. The only issue I have is that I think that they should have made Copy If Newer the default instead of Copy Always, so that test data was retained by default when building after a code change. That's is what most people should be using and the fact that their data disappears after a code change and build does confuse many people. That's what I was referring to when I said looking in the right database at the wrong time. If you look in the test copy before a build then you'll see the data but it will be gone after a build, unless you use Copy If Newer.

[jmcilhinney]

Note that this all only applies to local data files, i.e. databases where the data file (MDF, MDB, ACCDB, etc) are part of your project. If, for example, you create a database on a SQL Server instance using SSMS, there really is only one database so only one place to look. In such cases, deploying the database is an extra step over and above just deploying the application. It might require executing a script against a database server or something else.

[wes4dbt]

It's not really. It's just not obvious to the uninitiated.

I'll never agree with that statement. To me it's complicated. I'm not trying to speak for anyone else and maybe no one else finds it complicated but I do. I've always have. I don't like that feature, I prefer dealing with just one database file. That's why I don't always add the database to my project. Whether it's a good or bad feature doesn't matter to me, I usually find it more of a hindrance than helpful. but that's just me. If people find it helpful then by all means use it.

That being said, you did provide an excellent description of how the feature works. Going to have to bookmark this so I can come back and read it the next time I forget.

[JohnW2]

Apologies again for the delay in this response. This time it’s because I’ve been without a usable Internet connection for most of the day (and, now, it is still extremely iffy!)…

Except that is a contradiction because, if you followed those instructions, there will be two MDF files.

Fair enough. What I meant was that there were no .mdf files in my computer with a different file name. It did not occur to me that the one (with identical filename) in the ‘debug’ folder was ‘functional' - I thought that it was a copy in some way related to debugging (in the usual sense), not part of ‘normal operation’ within the development environment.

Yes I can. The one specifically described in the link I directed you to two days ago: … If the copy in the output directory is the one that gets updated, what point is there to looking for changes in the original in the project folder?

Again, fair enough. Whatever my views about this functionality may be, it sounds as if re-visiting that link may well enable me to solve the problem - so thanks again.

Thanks for your assistance.

Kind Regards, John

[JohnW2]

Usually when you create a datasource it will ask you if you want to save the connection string, if you said yes then you will find it in the Project->Settings. Depending on how you setup the db properties and the connectionstring there may be several mdf files. One in the project folder with the forms and such, one in the debug folder, one in the release folder.

That I now (but only now!) understand. I currently have just two .mdf files, one in the project folder and the other in the debug folder - and, as I’ve just written to jmc, it did not occur to me that the one in the ‘debug’ folder (which he {and his linked material}, seemingly more appropriately in this context, calls the 'output directory') had anything to do with ‘functioning', even during development (see *** below).

It is complicated to understand and there are several different way you can choose to setup your project. One way to get a grasp on it would be to find all the project .mdf's and check the date/time. Make a change using Server Explorer tools, check the date/time stamps. Then make a change using code, then check the date/time stamp. To tell you the truth I have to refresh my memory of how it works when I haven't used it in a while.

Now that this issue has been brought to my attention, I have done precisely that. If one makes changes using Server Explorer tools, then only the .mdf in the project folder gets updated, and if one makes changes programmatically during development (i.e. updating with a call to UpdateAll), then only the one in the debug folder gets updated.

*** Having read jmc’s most recent post, it seems that this is related to the fact that, unlike the situation with VB6 (and many/most other development environments), behaviour during development is not identical to that during operation of a deployed compiled program. With, say, VB6, RUNing a program in the development environment results is essentially the same as running the corresponding compiled .exe file - so, as jmc implies, one has to clean out any test data etc. which would otherwise still be there after compilation for deployment.

It seems that VB.NET is ‘trying to be helpful’ in that respect (and that, since 'running’ a program during development is called ‘debugging’, I suppose I can see why the ‘development database' file is in the “debug' folder), but (particularly in view of what we are discussing) I would say that there is probably scope for debate as to whether, on balance, that actually is ‘helpful'! Personally, I would probably prefer to have essentially identical functionality in the development/design environment as during run-time of a compiled program, and only one database file in the development environment, even if that meant I had to do that final 'cleaning'.

However, whatever one’s view may be about that, I personally don’t think it forgives the apparent inadequacy (I would say 'misleading') of the documentation, which has already wasted a lot of my time. It seems (to me) to be far from reasonable that the documentation should provide detailed instructions as to how one can create, and create programmatic access to, a local database (inevitably within the development environment) without at least a warning that what one has produced will not actually ‘work' within the development environment (and therefore cannot be tested within that environment) if one simply follows the instructions. However, maybe that's just me, and that others feel that it is reasonable for the documentation to be as it is!

Kind Regards, John

[jmcilhinney]

Personally, I would probably prefer to have essentially identical functionality in the development/design environment as during run-time of a compiled program, and only one database file in the development environment, even if that meant I had to do that final 'cleaning'.

What exactly do you think the benefit of that would be? I would suggest that, while you may not be thinking this consciously, the actual benefit is that it would mean that you wouldn't have to think about changing from what you're used to. Speaking for myself, I'm glad that it's done the way it is because having to clean up a database manually every time you want to refresh in test or release a build is a pain and error-prone. That said, if you want to work with a single database then you can, so have at it.

[JohnW2]

I'll never agree with that statement. To me it's complicated. I'm not trying to speak for anyone else and maybe no one else finds it complicated but I do. I've always have.

For what it's worth, in my present situation I would certainly say the same - and, as I recently wrote, I think that insult is added to injury by the fact that the documentation explaining how to create and access local databases gives no hint that there are any such issues.

I don't like that feature, I prefer dealing with just one database file. That's why I don't always add the database to my project. Whether it's a good or bad feature doesn't matter to me, I usually find it more of a hindrance than helpful. but that's just me. If people find it helpful then by all means use it.

In my present position, I certainly feel the same - since all it appears to be doing is frustrating my attempts to do what I want to do. However, I accept that this could be a consequence of my current ignorance, since (as will probably be apparent from subsequent posts) I am still pretty confused, and am perhaps not fully understanding what is going on, and what I should be doing!

Kind Regards, John

[jmcilhinney]

all it appears to be doing is frustrating my attempts to do what I want to do.

What exactly is it that you want to do that you can't? If we know what that is then we can advise how to resolve the issue. If it's just that you want to be able to see the contents of the working database via the Server Explorer then simply add a connection to the working database to the Server Explorer.

Like I said though, if you want to use a single database then do it. You simply use Never Copy rather than Copy Always or Copy if Newer and change your connection string to point to the one database. 30 seconds and you're done. I'd recommend against it though.

[JohnW2]

What exactly do you think the benefit of that would be?

A good question. As I've recently written, I am currently still pretty confused, and need to do more reading, thinking and playing before I will really understand what is going on and what I should be doing - after which my view might possibly change. With my current level of understanding/knowledge, this 'feature' (the likes of which I don't recall having encountered in any other software development/design environment) is merely making my life difficult! However...

I would suggest that, while you may not be thinking this consciously, the actual benefit is that it would mean that you wouldn't have to think about changing from what you're used to.

No. I'm not one who resists change because I don't like change. My current problem is that, quite possibly through ignorance and poor understanding, the 'change' in question is making life difficult for me - and that is obviously something I don't welcome!

Speaking for myself, I'm glad that it's done the way it is because having to clean up a database manually every time you want to refresh in test or release a build is a pain and error-prone. That said, if you want to work with a single database then you can, so have at it.

Having to clean up a database (and it doesn't necessarily have to be 'manually', does it?) is not really a major issue for me - particularly given that there will inevitably be plenty of 'test code', commented-out lines of code and 'temporary comments' that I would have to 'clean up manually', anyway.

I'll explain some of the practical issues that I need to get to properly understand and address, but it is (again!) well after my (UK) bedtime, so that will probably have to wait!

Kind Regards, John

[JohnW2]

What exactly is it that you want to do that you can't? If we know what that is then we can advise how to resolve the issue. If it's just that you want to be able to see the contents of the working database via the Server Explorer then simply add a connection to the working database to the Server Explorer.

Well, I've just written that I would explain some of that after I've had a spell in bed - but, since you've now asked, very briefly in relation to one issue ...

... I accept that this may, when I understand, come to be a 'non-problem'. It's not that I want to be able to see the contents of the working file via Server Explorer. It is nice to 'see' it within a 'running' (in the design environment) (aka 'being debugged') project, just as a user of the built project would - but most important of all is that procedures within the project should be able to 'see it' (at 'design time') (and then still see it in a final built version, without any modifications) - is that the case? (I should soon know, as the result of experiments!)

As for context, my database tables will usually contain primarily numerical data and many of the procedures will usually be involved in manipulation and processing of that numerical data (mathematically and/or graphically etc.). I therefore need to have a situation in which, during development (i.e. 'at design time') I need to be able to make (and be able to see) repeated ('incremental') changes to the contents of the database and then immediately see the effect of those changes on the behaviour and output of the procedures which handle the data.

Edit: I've just realised that what I wrote is probably not clear enough, and may sound like a complete 'non-problem'! The crucial word in what I just wrote is 'incremental' (changes). If I make changes and, as a result of what then happens find that some procedure code needs to be changed, I need the same 'changed data' to persist the next time I 'run' the program after modifying the procedure code - so that I can see what effect my program changes have had when it runs with the same data as the previous time..

Kind Regards, John

[si_the_geek]

It is fairly standard to have a "test" data file to run development builds against, which differs from the "live" environment that the users will work with. This situation is designed to make that easier, but it isn't perfect.

The idea is that you have a design-time data file, which is the "master copy", and you can decide if/when it gets copied to debug and release.

In simple terms the options are:

• Copy Always - always have a "clean" database, so data etc from previous runs will be erased each time you build.
• Never Copy - always have a "dirty" database, so data etc from previous runs will be kept.
• Copy if Newer - have a "dirty" database until you make any changes the design-time database (such as adding tables, or changing the initial data etc), at which point it overwrites the one your program runs against.

Like jmcilhinney, I think that Copy if Newer should be the default (and I also think Never Copy would be a better default than Copy Always, but that complicates the first run).

I suspect that Copy if Newer would be best for you, but Never Copy is a valid option to consider.

[JohnW2]

It is fairly standard to have a "test" data file to run development builds against, which differs from the "live" environment that the users will work with.

Many thanks for your input. I may be starting to get my head around this, but I still have some uncertainties/confusions - with which you may be able to help me! As you say, it is normal practice to work with a "test" data file during development.

This situation is designed to make that easier, but it isn't perfect. The idea is that you have a design-time data file, which is the "master copy", and you can decide if/when it gets copied to debug and release.

That "master copy" of the test data file presumably has to be the 'main' database' - i.e. the one which resides in the project folder? If so, then the only way of achieving jmc's wish to avoid having to eventually 'clean' (i.e. 'empty') it would be to always leave it empty?

Just to be clear, am I right in saying that (at least under normal circumstances) everything that happens at design time uses the data file in the debug folder? That's certainly what I thought, but some of what you go on to say makes me wonder! ...

In simple terms the options are:

• Copy Always - always have a "clean" database, so data etc from previous runs will be erased each time you build.
• Never Copy - always have a "dirty" database, so data etc from previous runs will be kept.

Again to be clear, do I take it that (as what you write seems to support) we are talking about copying from the "master" database (in the project folder) to the 'design time database' (in the debug folder)?

If so, that seems straightforward enough. It would seem that, in terms of my most common situation (as described in the last paragraph or two of my last post), given those two choices, "Never Copy" would presumably be what I would want - and "Copy Always" would be a disaster (at least, a major inconvenience). If a change in data (at design-time) reveals the need to revise the code, once that revision has been implemented I want to run it using the same data as that which revealed the need for revision.

• Copy if Newer - have a "dirty" database until you make any changes the design-time database (such as adding tables, or changing the initial data etc), at which point it overwrites the one your program runs against.

This is where I get confused, which probably means that I don't understand properly.

I think I need to ask you to clarify exactly what databases you are talking about. Per my current understanding, the "design-time database" is the same one which "my programs run against" (at design-time), namely the one in the debug directory. I presume that there must be something wrong with that belief, since what you've written would then relate to updating/overwriting a database with itself!

Furthermore, if (per above), we are talking about "copying from the "master" database (in the project folder) to the 'design time database' (in the debug folder)", then, on the basis of what you've just written, I'm struggling a bit to understand the practical difference between "Copy Always" and "Copy if Newer" - if the database in the debug folder has not changed since it was last copied from the one in the project folder then it does not matter whether or not copying takes place, whilst if (as you seem to be suggesting) 'copying' happens if the one in the debug folder changes, then that would happen in all other cases.

I suppose one question here in relation to "Copy if Newer" is what we are talking about - i.e. which is newer than which. I had always assumed that we were talking about the "master" database (in the project folder) being newer than the one in the debug folder (a situation which presumably could only arise if the former were somehow changed outside of the development environment), but what you seem to be talking about above is the database in the debug folder being 'newer' than the "master one" (because it has been changed).

Can you help to 'un-confuse' me a bit? I do not consider myself to be particularly dim (and have a string of letters after my name, and decades of experience of all sorts of things, to give some support to that belief) but I have to agree with the person who said that this is 'complicated'

Like jmcilhinney, I think that Copy if Newer should be the default (and I also think Never Copy would be a better default than Copy Always, but that complicates the first run). ... I suspect that Copy if Newer would be best for you, but Never Copy is a valid option to consider.

See above. I'm currently rather too confused to decide! I terms of what I think I do understand, "Never Copy" would sound the safest for me, and "Copy Always" would be inappropriate, but because of the things which I don't yet understand properly, I don't really know about "Copy if Newer".

Kind Regards, John

[jmcilhinney]

Your project contains source files. When you build, different things can happen to those files. Some will be compiled, while some can just be copied "as is" from the source folder to the output folder, the output folder being the folder that your compiled EXE is written to after a build. Database files, images and text files are examples of what might get copied "as is".

Generally speaking, if your deployed app is going to expect a particular file to be in the same folder as the EXE or a subfolder of it, you want the same during development, so you should have those files copied from source folder to the output folder as part of the build. In order to do that, you would set the Copy to Output Directory property of that project item to Copy if Newer or Copy Always. The former means that, on a build, the source file will only be copied to the output folder if there isn't already a copy there or there is a copy there that is older than the source. The source being newer would generally mean that you have made a change to it and you would generally want that change propagated to the output folder for use the next time you run the app. The latter means that the source file will be copied to the output folder on every build, regardless of what's currently there.

If your app is going to expect a file to be elsewhere, e.g. the AppData folder, then you should not have that file copied automatically to the output folder, but rather copy it manually tot he appropriate location. In that case, you'd select Never Copy and the source file will never be copied to the output folder on a build and, in fact, a file with the same name will be deleted if found.

So, simple stuff. You have a source file in the source folder in the default state and you can have it copied automatically to the output folder on every build or only when you make changes to it, or you can copy it manually to another location. No one would say that it's complicated for an image file so, if they think it's complicated because it's a database file, they are manufacturing that complexity themselves. So say I and I have determined that I am correct.

[si_the_geek]

Just to be clear, am I right in saying that (at least under normal circumstances) everything that happens at design time uses the data file in the debug folder? That's certainly what I thought, but some of what you go on to say makes me wonder! ...

Design-time is before anything is running (eg: when you are writing the code, or designing the database structure, or placing controls on a window, etc).

Debug and Release are two different kinds of running, which each have their own sub-folders that are relevant to that build, and those sub-folders are filled as explained by jmcilhinney's latest post. Your experience with VB6 helps cause confusion with this (because VB6 doesn't do a full compile for a debug), and so does the "edit and continue" option in VB6 and VB.Net (which allows you to change code while the debug version is running).


Anything in the project folder (as opposed to the Debug and Release sub-folders) should be considered the "source of truth" (the master-copy), as that is what other developers would use if you gave a copy to them. The Debug folder is your personal copy for testing, and the Release folder is a copy that should be ready to give to users (at the point in time it was compiled).

Any changes to things like database structure should happen in the project folder, so that they can be seen by other developers (and depending on the option you choose, be automatically copied to the Debug and Release folders when you next build). This copy of the database should not contain any test data, only essential data that must always exist.

If you want to be able to change the structure and keep your test data, that means you don't want it automatically copied. In that case the option is "Never Copy", and manually make changes to all copies of the database yourself.

As changes to a database structure tend to be rare, losing test data after a change doesn't tend to matter much - so "Copy if Newer" saves the effort of modifying multiple copies of the database.

[JohnW2]

Design-time is before anything is running (eg: when you are writing the code, or designing the database structure, or placing controls on a window, etc). ...

Right, thanks - there is clearly a vocabulary issue here. As I wrote in another thread, what I have so far not found is a readable introduction to the concepts, structure and basics etc. of VB.NET (any recommendations?). Anyway, when I wrote "running at design-time", it seems that I should have written "debugging time" or somesuch.

Debug and Release are two different kinds of running, which each have their own sub-folders that are relevant to that build, and those sub-folders are filled as explained by jmcilhinney's latest post.

OK - but another vocabulary issue. When he refers to "the output folder", is he talking about the debug or release one, or both?

Although I have not knowingly done anything different to account for this, some of the test projects I have created have a 'release' sub-folder, but some do not (but all have a debug one). When there is a release folder, all the new stuff created development is in that sub-folder (and not the debug one) but, obviously, when there is only a debug folder that's where everything goes. Needless to say, I'm a bit confused by this!

Anything in the project folder (as opposed to the Debug and Release sub-folders) should be considered the "source of truth" (the master-copy), as that is what other developers would use if you gave a copy to them. The Debug folder is your personal copy for testing, and the Release folder is a copy that should be ready to give to users (at the point in time it was compiled).

That's all as I had assumed.

Any changes to things like database structure should happen in the project folder, so that they can be seen by other developers (and depending on the option you choose, be automatically copied to the Debug and Release folders when you next build). This copy of the database should not contain any test data, only essential data that must always exist.

Again, as I had assumed. As I said before, if one wants to avoid cleaning out test data, one has to avoid putting it there in the first place. That presumably means that one has to create that test data (during "debugging time") in the copy of the database in one of the other folders, and (at least, per my wishes) configure things so that the (empty) "master copy" never gets copied into that other folder (hence overwriting one's test data with 'blanks'). As above, I have yet to work out why my changed to data during 'debugging' are sometimes going into the debug folder and sometimes into the release one (f present)!

If you want to be able to change the structure and keep your test data, that means you don't want it automatically copied. In that case the option is "Never Copy", and manually make changes to all copies of the database yourself.

In what I've been saying/asking, I haven't been concerned about the situation in which I wanted to change the database structure. I have been talking only about the test data and, as you say, the way to avoid that getting overwritten with blanks is to use "Never Copy". As you go on to say ...

As changes to a database structure tend to be rare, losing test data after a change doesn't tend to matter much - so "Copy if Newer" saves the effort of modifying multiple copies of the database.

Indeed. If I wanted to change the database structure, I would not worry about (and would not expect to avoid) losing the test data - but, as you say, that's a relatively rare occurrence.

So, I think what you're saying is that if one uses "Copy if Newer" and never puts test data into the 'master copy' of the database, the only situation in which it would change (hence invoking "Copy if Newer" at the time of the next build) would be if one changed the database structure (outside of any form of 'running') - is that correct?

I may be gradually 'getting there'!

Kind Regards, John

[si_the_geek]

OK - but another vocabulary issue. When he refers to "the output folder", is he talking about the debug or release one, or both?

They are both output folders.

Although I have not knowingly done anything different to account for this, some of the test projects I have created have a 'release' sub-folder, but some do not (but all have a debug one).

As far as I recall, the Debug and Release sub-folders only get created when you first build each of them (there isn't really any point creating them before that). Not having a Release folder yet implies that you haven't yet done a Release build of that project.

When there is a release folder, all the new stuff created development is in that sub-folder (and not the debug one) but, obviously, when there is only a debug folder that's where everything goes. Needless to say, I'm a bit confused by this!

The Debug and Release folders should usually be left alone, and automatically get filled as appropriate when you build. However in terms of thing like a test database, you may want to create/edit it yourself rather than having the "clean" version copied across.

That presumably means that one has to create that test data (during "debugging time") in the copy of the database in one of the other folders, and (at least, per my wishes) configure things so that the (empty) "master copy" never gets copied into that other folder (hence overwriting one's test data with 'blanks').

You can create the test data at run-time (in Debug mode), or at design-time if you want.

To ensure that the test version doesn't get overwritten, use the Never Copy option... but whenever you change the database structure etc, you need to update it in all copies of the database yourself.

As above, I have yet to work out why my changed to data during 'debugging' are sometimes going into the debug folder and sometimes into the release one (f present)!

Which of those folders gets used depends on what you did a build on. There should be a drop-down list in the toolbar of Visual Studio that says either Debug or Release, and that is the one you will be running.

So, I think what you're saying is that if one uses "Copy if Newer" and never puts test data into the 'master copy' of the database, the only situation in which it would change (hence invoking "Copy if Newer" at the time of the next build) would be if one changed the database structure (outside of any form of 'running') - is that correct?

I may be gradually 'getting there'!

That is correct

[JohnW2]

They are both output folders.

Fair enough. Thanks.

As far as I recall, the Debug and Release sub-folders only get created when you first build each of them (there isn't really any point creating them before that). Not having a Release folder yet implies that you haven't yet done a Release build of that project.

Which of those folders gets used depends on what you did a build on. There should be a drop-down list in the toolbar of Visual Studio that says either Debug or Release, and that is the one you will be running.

Ah, I hadn't even noticed that dropdown list, which must somehow have got changed for some of my projects. That seems to explain everything I'm seeing!

The Debug and Release folders should usually be left alone, and automatically get filled as appropriate when you build. However in terms of thing like a test database, you may want to create/edit it yourself rather than having the "clean" version copied across.

Fair enough. It doesn't really matter, but I'm not sure how I could create or edit a database in either of those output folders - currently, their structure is (initially) being copied from the 'master' one, and the editing done whilst running in debug mode. I'm not sure what one would do to get the database structure into the output folder if one implemented "Never Copy", but it would probably get copied, just the once (at first build) if one was using "Copy if Newer". Is that correct?

You can create the test data at run-time (in Debug mode), or at design-time if you want.

As above, I'm not sure how one would do it at design time, but that doesn't matter, since I'm happy doing it whilst running in debug mode.

To ensure that the test version doesn't get overwritten, use the Never Copy option... but whenever you change the database structure etc, you need to update it in all copies of the database yourself.

As above, if I did use "Never Copy" (rather than "Copy if Newer"), I wonder how I would initially get the database ('empty', so just structure) into the output folder - just by copying it? However, as I wrote above, wouldn't "Copy if Newer" address that - by resulting in the structure being copied at (but only at) the first build, but never subsequently (assuming that the copy in the output folder)?

That is correct

It's nice to get at least one thing right Very many thanks for all your assistance - as I said, I think I'm gradually 'getting there'!

Kind Regards, John

[wes4dbt]

I don't know if anyone has mentioned this to you (too lazy the read everything) but to avoid having to deal with this feature, when you first create a datasource and select a new database file the IDE will ask you if you want to add the database to the project. If you say NO then none of these other database files are ever created You will be using the one database you selected for everything. I'm not advocating this method, just thought you should know.

[JohnW2]

I don't know if anyone has mentioned this to you (too lazy the read everything) but to avoid having to deal with this feature, when you first create a datasource and select a new database file the IDE will ask you if you want to add the database to the project. If you say NO then none of these other database files are ever created You will be using the one database you selected for everything. I'm not advocating this method, just thought you should know.

Yes, that was mentioned early on. However, my understanding (by no means definitely correct!) is that it would then be necessary to hard-code the location of the DB file - which is not ideal.

Kind Regards, John

[wes4dbt]

Not really, when you create the datasource, and when it saves, you'll see a CheckBox whether to save the connectionstring to the settings (don't remember the exact wording). So you can then go to Settings and modify the connectionstring. I like using "|DataDirectory|" whick means the app will look for the database in the same folder as the exe.

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Recipes.mdf;Integrated Security=True;Connect Timeout=30

[wes4dbt]

I should add that this means you will have to manually copy the database from the the Debug folder to Release folder when you run the app in release mode. Also, if you modify the the database in the Debug/Release folder it will not automatically update the other database. It will be up to you to manually maintain all copies of the database.

[si_the_geek]

Fair enough. It doesn't really matter, but I'm not sure how I could create or edit a database in either of those output folders - currently, their structure is (initially) being copied from the 'master' one, and the editing done whilst running in debug mode.

Using the Solution Explorer window you can copy/paste files (so create the master copy in the project folder, then copy it across).

I'm not sure what one would do to get the database structure into the output folder if one implemented "Never Copy", but it would probably get copied, just the once (at first build) if one was using "Copy if Newer". Is that correct?

I'm not sure, but I don't think so... I think you'd need to manually put it there (using copy+paste, or another method).

However, as I wrote above, wouldn't "Copy if Newer" address that - by resulting in the structure being copied at (but only at) the first build, but never subsequently (assuming that the copy in the output folder)?

It would be copied at the first build... but it would also be copied if you ever change the master somehow (such as adding a new table, or adding/removing any 'essential' data), and that would delete your test data.

[JohnW2]

Using the Solution Explorer window you can copy/paste files (so create the master copy in the project folder, then copy it across).

Yes, as I said, I realised that one could copy the database into an output folder (using Solution Explorer within VS or by any other means), but I've only just discovered that, if one uses Solution Explorer, one can click on the output folder DB, opening a Server Explorer window, and thereby edit the contents of tables using "Show Table Data".

[ one odd thing - when I do that, Server Explorer shows two variants of the DB - e.g. MyDB.mdf and MyDB.mdf1, which appear identical. What's that all about, and would it matter which one I used? ]

I'm not sure, but I don't think so... I think you'd need to manually put it there (using copy+paste, or another method).

It would be copied at the first build...

I'm a bit confused by those two seemingly contradictory statements. I suggested that, using "Copy if Newer", the DB would be copied at the first build. You replied that you "don't think so", but then said (as I had done) that it would be copied at first build. What am I missing?

... but it would also be copied if you ever change the master somehow (such as adding a new table, or adding/removing any 'essential' data), and that would delete your test data.

Indeed - but, as I said, that would not worry me since, as you said yourself, it's a fairly rare situation.

Kind Regards, John

[wes4dbt]

I just remembered one strange thing about the Datasource Wizard (if that's what your using). Even after you change the connectionstring in the Settings to "|DataDirectory|" it will still look in the project folder for the database if want to use the Wizard to edit your dataset. So you must keep a database with the current structure in the project folder. I don't understand why it's like that. I mean the dataset connectionstring doesn't point there but that's what the Wizard uses.

[JohnW2]

I just remembered one strange thing about the Datasource Wizard (if that's what your using). Even after you change the connectionstring in the Settings to "|DataDirectory|" it will still look in the project folder for the database if want to use the Wizard to edit your dataset. So you must keep a database with the current structure in the project folder. I don't understand why it's like that. I mean the dataset connectionstring doesn't point there but that's what the Wizard uses.

Thanks for pointing that out!

Kind Regards, John