At last a working VB 2010 data application linked to a SQL Server 2008 instance. In the end I used two methods.
Method 1: Extreme coding. I used sample data provided by aptly named PsychoCoder in http://www.dreamincode.net/forums/topic/32392-sql-basics-in-vbnet/. He obviously doesn't test his posted code, there were a number of errors in it but not too hard to sort out. I couldn't get his code to work with a connection string embedded in app.config but with a hard-coded string it worked as far as it went and showed me a filled DataGridView. That's about where it ended - I couldn't work out from there how to actually do anything useful like update back to the database (he provides code but doesn't suggest where to hang it in a datagrid). However, that clearly showed that with the appropriate connectiions the two systems could talk.
Method 2: Permissions kludge. In the VB data access wizard there is an option to connect to a SQL Server mdf file, but if you try to do that and point it at your SQL database it can't access the file. So I did a few tweaks. I used the advanced settings to select the appropriate SQL Server instance (the 32 bit one as that was where I'd managed to populate some data) and I set it to not use a user instance (that was suggested by a few forum posts). I then changed the permissions on the actual MDF file (it was in C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS32\MSSQL\DATA\FamilyHistory_Data.mdf, your mileage may vary) so the group Users had the same permissions as the SQL users group. Administrators already have those permissions but for some reason UAC doesn't pop up to ask if you want to use them so you have to access as Users. Bingo! the wizard completed and I can use the full might of Visual studio and Visual Basic to manipulate my data. It would be easier if the connection used SQL Server to access the data instead of piling in as if it was a private file, but at least it works. I suspect if you buy the non-Express versions there's an option to go through SQL Server.
Now to write the application. My Access database has developed over many years, so I don't know how much functionality I can get in before something else arrived to take up my time, but here goes.
First, I probably need to rewrite the database to use text strings for dates and a class to provide date checking and arithmetic based on those strings.
Tuesday, 14 December 2010
Monday, 13 December 2010
More VB and SQLWoes
I promised I'd keep readers up to date with my Visual Basic project issues, here's another one:
Problem #4: MS SQL Server has a base date of 1752!
Why is this a problem you ask? Because I've decided my VB project will be my Access family history database. I can trace my family back to the 17th century, long before 1752.
Why 1752? That is when the UK and US switched from the Julian to the Gregorian calendar, and also moved the legal year end ffrom 31st March to 31sr December. Dates before that can be problematical and SQL Server's progenitors evidently elected to simply not allow earlier dates.
This isn't quite a restrictive as MS Excel which won't handle dates before 1900, but Access happily goes back before 1752.
It wouldn't be hard to permit any date - what do historians and archaeologists do?
Problem #4: MS SQL Server has a base date of 1752!
Why is this a problem you ask? Because I've decided my VB project will be my Access family history database. I can trace my family back to the 17th century, long before 1752.
Why 1752? That is when the UK and US switched from the Julian to the Gregorian calendar, and also moved the legal year end ffrom 31st March to 31sr December. Dates before that can be problematical and SQL Server's progenitors evidently elected to simply not allow earlier dates.
This isn't quite a restrictive as MS Excel which won't handle dates before 1900, but Access happily goes back before 1752.
It wouldn't be hard to permit any date - what do historians and archaeologists do?
Saturday, 11 December 2010
Visual Basic Woes
I've decided to have a real go at mastering Visual Basic 2010. After years of playing with VBA I'd like to be able to write real standalone apps without needing Access as a rather expensive front end. I'm going to post here any issues I've found with getting a "proper" app written.
Starting from scratch with a new laptop (Windows 7 Home Premium) I downloaded (free) all this:
Visual Basic .net 2010 Express
SQL Server 2008 R2 Express x64
Office 2007 Home & Student (I had a spare license)
Adventureworks sample database
VB sample code
I needed to import data from an Access app on another laptop, so decided to use an Excel backup of the tables. Problem #1. The app is a genealogy database and has dates going back to the C17th. Excel won't accept a date before 1901. Had to write queries to export dates as text strings.
Then, Problem #2 (you won't believe it) there's no Excel 2007 connector for SQL Server 2008 x64.
Official MS advice was to install the x86 (32 bit) version and the downloadable Access connector, then reference the data across to the x64 database. So I did that. and then
Problem #3, I can't see any way to establish a data connection to a SQL server database from Visual Studiio. I can connect to a database file (.sdf) but not to a database in a server instance, which is what I am used to working with in SQL Server. So I'll investigate a bit more, and then probably copy the data over to a .sdf file.
Starting from scratch with a new laptop (Windows 7 Home Premium) I downloaded (free) all this:
Visual Basic .net 2010 Express
SQL Server 2008 R2 Express x64
Office 2007 Home & Student (I had a spare license)
Adventureworks sample database
VB sample code
I needed to import data from an Access app on another laptop, so decided to use an Excel backup of the tables. Problem #1. The app is a genealogy database and has dates going back to the C17th. Excel won't accept a date before 1901. Had to write queries to export dates as text strings.
Then, Problem #2 (you won't believe it) there's no Excel 2007 connector for SQL Server 2008 x64.
Official MS advice was to install the x86 (32 bit) version and the downloadable Access connector, then reference the data across to the x64 database. So I did that. and then
Problem #3, I can't see any way to establish a data connection to a SQL server database from Visual Studiio. I can connect to a database file (.sdf) but not to a database in a server instance, which is what I am used to working with in SQL Server. So I'll investigate a bit more, and then probably copy the data over to a .sdf file.
Thursday, 2 December 2010
F1 - Are the New Teams Welcome?
This is the subject of the Sidepodcast Thursday Thought so I'll post something on it here.
Surely no-one would not welcome new blood in F1. Without the three new teams we'd have less action to watch and some great drivers would be without a seat. No Karun Chandhok!
Arguably HRT as a team made little contribution, as much as anything because they didn't "join in". Get a third party to build you a car then not develop it during the season - not a great plan. And pulling in pay drivers to cover the bills isn't going to make you popular with the fans. I do wonder if they'll make the grid in Bahrain.
Virgin make a good showing. There's a good tech story (all CFD design) and worthwhile drivers. Its a shame they ended up last, because they were way better than HRT when they managed to finish. What WAS DiGrassi thinking of with an off on the way to the grid in Japan? Next year, with Marussia money and a year's experience of developing a car they may be challenging for the odd point.
Lotus really deserved to break out of the bottom 3. Mike Gascoyne is a good designer and they hired experienced drivers. I hope they make a real job of it next year, they seem to be at least as committed a team as the STRs of this world.
Overall, without the new teams we'd have had a much less interesting year and the future, with fewer opportunities for new drivers to get into the sport, would be looking shaky. Next year I'd like to see more interest in Q1 - this year it was all about who would get 18th place on the grid.
Surely no-one would not welcome new blood in F1. Without the three new teams we'd have less action to watch and some great drivers would be without a seat. No Karun Chandhok!
Arguably HRT as a team made little contribution, as much as anything because they didn't "join in". Get a third party to build you a car then not develop it during the season - not a great plan. And pulling in pay drivers to cover the bills isn't going to make you popular with the fans. I do wonder if they'll make the grid in Bahrain.
Virgin make a good showing. There's a good tech story (all CFD design) and worthwhile drivers. Its a shame they ended up last, because they were way better than HRT when they managed to finish. What WAS DiGrassi thinking of with an off on the way to the grid in Japan? Next year, with Marussia money and a year's experience of developing a car they may be challenging for the odd point.
Lotus really deserved to break out of the bottom 3. Mike Gascoyne is a good designer and they hired experienced drivers. I hope they make a real job of it next year, they seem to be at least as committed a team as the STRs of this world.
Overall, without the new teams we'd have had a much less interesting year and the future, with fewer opportunities for new drivers to get into the sport, would be looking shaky. Next year I'd like to see more interest in Q1 - this year it was all about who would get 18th place on the grid.
Subscribe to:
Posts (Atom)