Re: To LINQ or not to LINQ
LINQ is an interesting suggestion, here. Presumably, that person meant LINQToSQL, which seems like an intermediate technology, to me. MS introduced that at some point to make queries a bit easier. I was under the impression that MS then released the Entity Framework and deprecated LINQToSQL. If that were the case, then anybody recommending LINQToSQL is neither modern nor old-school.
The problem you are likely to run into is that there are MANY different technologies you can use to do this, and everybody you talk to is likely to be an advocate of different ones. For example, in addition to LINQToSQL, and Entity Framework, you also have datasets, strongly-typed datasets, and some more exotic things. The first was datasets using good ol' ADO.NET. The other technologies were attempts to make a lot of the basic work more automated and easier for a new person to get going with. As far as I'm concerned, none of them really lived up to that promise. You can build things pretty fast with Entity Framework or strongly typed datasets, but once you get away from some basics....you're hosed. The basic stuff is easier, but the non-basic stuff is often harder.
Because of that, I advocate just sticking with the basics: Datasets containing datatables. These bind quite easily to DataGridViews and other types of controls. They aren't as easy to set up, because you do the setup in code. It doesn't take many lines (filling a datatable can take two to a dozen depending on what lines you count), but you don't have a wizard to hold your hand every step of the way. On the other hand, you control it, so if there are changes, YOU make them without trying to figure out how to get the wizard to make them for you, if that's even possible.
Of all that you wrote, it's almost all easy using plain old datasets, with one exception: That bit about getting the new primary key. I would suggest that you include the primary key in the datatable, but then hide the column in the DGV. It's there, it just isn't displayed. However, depending on the data type, a new primary key can still be a challenge to figure out when you want to add a new record. Oddly, for several years now, I've only been using GUID primary keys for this kind of a situation, and GUID primary keys are one of the easiest to use for this kind of thing. However, using a GUID for a primary key is not something that is done all that often because they have some real performance implications. If you are using integer primary keys, there are other considerations, but plenty of people here can answer any questions you have about those.
Re: To LINQ or not to LINQ
Hi Shaggy - thanks for your reply.
Yes it was LINQtoSQL (see, I thought it was all just LINQ!). I didnt even know that Entity Framework existed! I did (quite a few years ago, vb6) have some experience with DAO with access dbs, but I seem to have retained only some pretty spotty memories of the broad concepts.
Im more than happy to do stuff in code rather than have wizards as I find it a lot easy to go back and change/fix things without having to remember what selections where made at each step of each wizard!
Do you have any suggestion for tutorials/videos etc directed at getting started with the basics of datasets & datatables?
As I mentioned I'm not overly a fan of the MSDN site (though I do struggle through it) it makes vb seem like a big ball of tangled spaghetti with no protruding ends - there is no good (efficient) place to start to get to the thread you want without having to unravel nearly all the other threads! While it would most likely be a good thing that I would end up having a fuller understanding by knowing about the whole ball, as Sweet Brown says, ain't nobody got time for that!
Re: To LINQ or not to LINQ
I would definitely recommend using Entity Framework over LINQ to SQL in just about all applications. They are similar in many ways but EF supports lots of different data sources rather than just SQL Server and Microsoft are putting lots of work into EF to make it a genuinely good object relational mapping tool (ORM). When you use EF you write your queries using LINQ, just as you do for LINQ to SQL. LINQ as an overarching technology is basically a template for functionality that gets implemented by specific providers aimed at different data sources, e.g.
LINQ to SQL for SQL Server
LINQ to Objects for enumerable collections
LINQ to XML for XML data
LINQ to Entities for EF models
etc.
L2S is certainly not dead because it is the default data access technology for Windows Phone applications. That's where you should use it but probably not anywhere else.
Both EF and L2S use ADO.NET under the hood, i.e. your LINQ queries are converted to SQL code on the fly. When just starting out though, it's not a bad idea to use ADO.NET yourself to get a reasonable understanding of the principles involved. That will make you more able to handle curly situations that come with EF and other technologies later.
Please don't abandon MSDN. It is the best .NET learning resource you will ever find. Just remember that it is primarily a reference, not a tutorial. When you already know what type or member you need to use but don't know the specifics then it should absolutely be the first place you look. You'll more often than not find the answer to your question and probably quite a bit you weren't looking for as well. I speak from personal experience.
Re: To LINQ or not to LINQ
MSDN is good to get a specific answer about a specific object. As a place to learn about VB, in general, it isn't so good for the reasons you noted. It has it's place, as does this forum. Both should be used in only those ways that they are best suited for.