William (Bill) Vaughn's Musings

The Question
in a network-winforms-application based on a access database I've got the following problem:
user A starts software user B starts software
user A deletes row in db
user B changes data
application of user B makes an update dataadapter.update(datatable).
Now I get an concurrency violation.
Now I try to handle this error - what I want to do is simply refresh user B's datatable with datadapter.fill(datatable). But nothing happens - the datatable is not updated.
I already tried .clear before update - without success. Can you help me with this issue please?

The Answer:
Ah, this is concurrency 101. It's an age old question that has been asked (and answered) since the first time two people tried to sit on the same rock in the garden of Eden. We've discussed this here on this list many times as well.

When you use a disconnected approach to data access (as ADO.NET encourages you to do) you have to understand that the data you've fetched is simply a copy of the data stored in the database. When you do an update (as when you execute the Update method) the data in the database is changed--unless you add rules that say "Ok, if someone else changed the data since I got my copy, don't post my change". This is the default behavior for ADO.NET (as dictated by the CommandBuilder). Yes, there are other rules and ways to determine if a row has changed since you last fetched it but let's just keep it simple for now. So, if someone has changed the row, your Update did not go through. If you requery (run the SELECT again) you'll see the changes made by the other user(s). But there is nothing to stop yet another user from making more changes before you make your change. This is typical of a heavily used database--add more users and you get more collisions.

In my new book, I discuss how to design systems and databases where the users don't share the same rows. I design my data intersections with traffic lights and bridges instead of positioning ambulances nearby to pick up the dead and injured. While I don't know how your database is designed or the problem you're trying to solve with it, there are generally accepted practices we've come up with over the years to deal with concurrency issues.

1. If you have to design a system where more than one user has access to the rows, you have to be prepared for collisions. In ADO.NET these are manifested as the Update method being told that the "rows affected" value was not 1.
2. If your update can fail because you permit multiple access to the rows you must decide (in your design) what to do about it. You can
a. Force through your update. In this approach you might just remove all WHERE clause tests to see if the row has changed. This is called "Last Update wins".
b. You can fetch the changed row and let the user decide what to do. I don't like this approach as the data can change again a dozen times while Betty or Bob figures out what to do.
c. You can make a logical decision about what to do based on business rules. For example, accounts from Texas have a lower priority than those from California but no changes are permitted to accounts from Cleveland.
d. There are other variations on this list... but you get the idea.
3. If possible revisit 1. See if your design can be modified to prevent collisions instead of cleaning up after them.
hth

Posted by William Vaughn at 10:33 AM | Permalink | Comments (0)

I saw this question on a public newsgroup. It's great fodder for the latest question of the day...
__________________________________________________

> Ok I have a VB.NET 2005 application that is connecting to a SQL 2005
> database. The application itself is going to go out and run SP, Views, etc.
> The users will be able to update records.
>
> My main concern is I do not want the user to be able to go out and Access
> the very same SQL server independent of the VB.NET program. What is the
> prefered method to allow users to use the VB application but absolutely no
> direct SQL database access?
>
___________________________________________

My Answer:

Setting up application security is not that hard. First, make sure that the base tables and other objects in the database are not exposed to anyone--just the System Administrator account (SA). Protect the SA password and make sure it's not "password". The following strategy uses SQL Server Mixed-Mode security which is disabled by default--you'll have to enable it using the SQL Server Surface Area Configuration tool.

Next, grant rights on the specific stored procedures you want your applications to use.
I generally create SQL Server logins (after having enabled mixed-mode security) that only the application code uses. I don't try to create individual accounts for each person--it's an administrative nightmare. The User is only granted rights to the application if they can log into windows using Windows SSPI authentication handled by the domain.

Once the application connects, it gathers additional information from the user's windows credentials or it gathers another UID/PWD and revalidates this against a custom SQL Server validation SP. The UID could be "admin16" or "salesperson" or a user's name (but not usually). These credentials coupled with the attributes associated with the "person" or role as determined by the validation SP tells the application what this person is allowed to do with the application. If I validate the user I have the SP generate a dynamic security token and pass that to any SP I call to revalidate the user. Because I designed all of this myself, there is less of a chance that a nationwide-hacking attack can penetrate it. It's probably not any more secure, but it's different enough to make it harder to crack.

If someone fires up Access or VB/VB.NET they won't be able to connect to the server unless they know the SQL Server Login/Password used by the application. Since database objects are not made visible to normal users they can't see views, tables, stored procedures or other objects. Those who have seen (or compromised) the application credentials can try to run one of the exposed SPs , but since the SPs also require valid credential tokens (which are generated dynamically by the application), they can't do anything. If they hack the validation credentials somehow, all they can do is run that specific SP--they also don't know that I log unsuccessful accesses to the database as well as each invocation of the SPs--and who made it. The token validator can also include valid MAC addresses or other means to re-re-re-validate the user--at least I can track them down later...

Yes, there are lots of other things you can do--depending on how secure you want your system to be and what the data is worth. If it's the company payroll you're protecting, you'll do more to hide the data than if you're retrieving a list of employee parking spaces but less than a list of everyone's SSAN. As data becomes more important, it's critical to add layers of protection that makes casual or hacking more difficult. Private data should always be encrypted in place--or not stored at all. Unencrypted data should never leave the physical security of the site--even if it's just the SSANs of 240,000 Veterans.

Posted by William Vaughn at 03:26 PM | Permalink | Comments (2)

Unfortunately, I think Microsoft has stepped off on the wrong foot as far as the marketing of the SQL Server Everywhere edition. For obvious reasons, I expect they wanted to leverage the popularity and reputation of its deadly serious SQL Server DBMS. This makes sense, but technically, SQL Server Everywhere (SQL Ev) is not SQL Server (like SQL Server Express or the other paid editions). I’m of the opinion that if you name a product, it should bear some resemblance to that product. Each product name carries with it the baggage (negative attributes) and benefits (positive attributes) of its namesake. Calling something SQL Server when it’s not is deceptive and can be confusing when one assumes that it’s a clone of the predecessor or parent product. This also means when the customer gets the product and opens the box, they’re often disappointed when it does not look, taste or smell like what they expected. It’s like selling plastic “chocolate-like” bunnies to kids at Easter. As soon as they take their first bite on the left ear, they’ll know the difference—and you’ll regret it. No, I don’t mean to imply that SQL Ev is not edible, er, suitable because it is. It’s a perfectly good DBMS engine that can take on quite a few important responsibilities when it comes to storing, managing, fetching and replicating local data stores. It’s just not SQL Server and should not be expected to have the same feature set.

If Microsoft had simply called it “SQL Everywhere”, your expectations and mine would have been different. If they had left the name “SQL Mobile” we would have understood where it fit and what is was capable of doing. But they didn’t, so authors, trainers and those attempting to explain where SQL Ev fits have to spend their first 800 words telling you what SQL Ev isn’t—instead of what it is. We've already seen this in the blog entries... Steve, please reconsider your choice of names.

Posted by William Vaughn at 05:12 PM | Permalink | Comments (0)

Perhaps this entry should be titled “Driving off the road with Microsoft Streets and Trips” ‘cause I nearly did drive off the road trying to use the 2006 version while on a number of recent trips. I would call myself a self-styled expert on automobile navigation systems. I must have purchased a half-dozen GPS automotive navigation systems since their first introduction. All along the way I’ve passed comments back to the manufacturers in an attempt to make these system work better for me and all of us. This article discusses Microsoft’s contribution to the fray.

In the early days auto GPS nav systems required users to download maps from an ever-changing repository on the web or from disks or memory modules provided by the map vendors or unit manufacturer. The newest systems contain maps for the entire US (and other countries) that don’t require you to pull to the side of the road as you cross the South Dakota border and completely reprogram the system.
I think that Garmin has produced some of the best of these units. Their maps were probably as accurate as any, but their user interface was far more suitable for use from a moving car. Their display was not nearly as large as the units built into a car’s dash, but what they display is more what I needed to get where I was going. I’ll talk about programming in a minute. The problem with these “unintegrated” units is that they were not tied to the car’s fuel system. Units that did measure RPM and fuel flow were, well… pretty crude and unreliable. They also required quite a bit of mechanical knowledge and cutting the fuel line—scary.
GPS units generally take quite some time to sync with their satellites so they don’t really know where you are until they do. Most units remember where they were and need very few satellites in view to make a good guess as to your current location. That’s the key—all GPS systems need to be able to “see” the satellites so the antenna needs to be outside the car or with a relatively clear view of the sky. This means they won’t work in an airplane unless you can stick the antenna to the window (without the flight attendant calling the Federal Marshal). In a car you must place the GPS receiver on the dash or outside the car. These units don’t do well at all in stone or concrete canyons or deep in a forest. Keep that in mind if you expect to use a hand-held device to find Times Square.
Over the last five years or so I’ve purchased two cars with (supposedly top-line) GPS systems built into the dash. While both of these units (in the Infinity QX4 and the Acura MDX) had displays that were larger and easier to read than dash-top units like the Garmin Street Pilot, they lacked many of the Garmin features like real-time speed, altitude and time-to-destination read-outs. For example, the Garmin unit knew that I was traveling at 38 mph and I had 38 miles to go so it said it would take one hour to get there. As I got through the traffic and picked up speed, it would constantly update the time-to-distance (TTD) value in real time. Both of the car systems based all TTD calculations on 55mph—relatively useless.
The best of these units have a UI designed for drivers who don’t have more than a moment to retrieve information from the display or select an option. They can zoom in or out with a single keystroke or provide audible and visible queues that a change in course is due. All of these units give warning that a turn is approaching—usually 60, 30 and 10 seconds before the turn. Since the built-in units are tied to the car’s audio system, the driver can clearly hear the announcements and (importantly) the volume is adjustable and mutes the car’s audio system. Yes, these announcements can be quite irritating, but when driving in an unfamiliar city, it’s important for the driver to keep his or her eyes on the road and not on a constantly changing map.
Now, as to the Microsoft Streets and Trips 2006. This package is a Windows application designed to create turn-by-turn maps for someone in the right seat to read. That’s not the driver’s seat in the US. Generally, IMHO the printed maps are too hard to read and drive at the same time. The application can also be used with a GPS receiver plugged into the system’s UPS port but the Windows metaphor is not suitable for this type of application. I’ve just used Streets and Trips 2006 on several trips that took over 3 hours each—most through the bowels of large cities. Each time I got lost despite being used to GPS navigation systems and being (somewhat) familiar with the cities in question. I had reviewed the route beforehand but I found that the instructions given by Streets and Trips was less than adequate. I was also plagued by several irresolvable problems:
• The computer generated voice announcements which were required as the display was too difficult to see at 50 mph) were (to be kind) pitiful. They speech was unclear, spoken too quickly and sometimes layered on top of other announcements. Compared to the in-car voice systems, Microsoft’s offering was sad. I know Microsoft has better speech generation technology—it’s apparently not in use here.
• The voice announcements were also not loud enough to hear over normal car background noise. With my laptop’s amplifiers turned up all the way, the sounds were still too quiet to hear. I had to resort to using a headset in one ear to get any intelligible sense out of the messages. The application had no option to increase the volume.
• As I approached a waypoint, the voice commands were increasingly confusing as it cobbled the message with other “reaching waypoint” messages.
• It seemed to me that on several occasions, the application did not properly anticipate upcoming turns—it would say “Turn-left in point one miles” as the exit passed by on the right.
• All of the automobile units automatically recalculated the route when I drove off the route (which happened all too often), however, Streets and Trips did not. I had to press F3 to get the application to recalculate. However, if the focus was not set on the map the F3 key would not work.
• I found that I had to regularly zoom in or out on the map to get a feel for the distance yet to travel. The Microsoft application did not provide this data—only the distance to the next segment or turn. Not knowing how much longer the trip was to take is a big problem. The problem is that unless the windows focus was set on the map, the + and – keys did not work. Again, the Windows metaphor got in the way.
• Driving / turn instructions were shown at the bottom of the map in another window (and voiced by the application), and these were in a large font so they were easier to read, but the distance to travel was shown at the far right—something like this:
Turn left on RT 66 ...................................................................................................................................... 1 mile.
It would be easier to read if it said:
In 1 mile, turn left on RT 66.
• When the application was restarted after going on standby or restarting in the morning, the left-hand GPS window had to be completely reprogrammed. These settings should have been persisted between shutdown and standby states.
• The application gave no indication of time-to-destination. It did provide a real-time speed display but it would have been better to have this critical information displayed in real time—if only to give the kids in the back seat the answer to their question “Are we there yet daddy?”
• All of the other automotive units were designed to assist drivers as they are trying to find gas or food along the way. The Microsoft product could display nearby food and fuel locations, but there did not seem to be an easy way to make an impromptu stop. There needs to be an easy way to interface with the application using pre-canned commands like “find nearest MacDonald’s” or “find nearest gas station”. These must be activated without the mouse (through the keyboard) or via voice response as used on all of the newest automotive systems.
IMHO, Microsoft Streets and Trips 2006 needs a new user-interface—one that does not depend on the mouse at all or on the user having to switch focus to gain access to critical functionality. Some of the basic functionality is there, but since the user is expected to use a laptop on the passenger seat, Microsoft needs to be critically aware of safety issues and how the application must perform in a noisy environment—having to use a headset is not an option. It’s against the law in most states.

Posted by William Vaughn at 08:59 PM | Permalink | Comments (2)

A developer asked the newsgroup this week about a problem he was having with the CommandBuilder and its inability to generate SQL to fetch the newly created Identity value post Insert. His suggestion was to run the query, call RefreshSchema and subsequently fetch the new Identity.

My Answer:
Nary a week goes by without a reference to the CommandBuilder. Keep in mind that Mike P at Microsoft renamed this the “CommandDon’tUseBuilder”. This class is called by the DataAdapter and TableAdapter Configuration wizards to generate the source code that’s added to your project—at design time. That’s the critical point—this generated code is folded into your project as source code. Can it be used at runtime to generate action commands? Sure. Does it make sense for a serious application? Rarely.

I’ve written any number of articles on the CommandBuilder and included a volume of information about it in my books. These detail the cornucopia of issues raised by the CommandBuilder including the inability to return a generated Identity column value. Did your solution address this issue? Well, I expect that it has not. Consider that if you don’t fetch the Identity value just as it’s created, another user can step in and create another Identity value so the one you fetch an instant later is not correct.
Ideally, as I describe in my article on managing identity columns (see http://www.betav.com/Files/Content/whitepapers.htm) you need to combine the INSERT query with a SELECT query to insert the row and return the latest Identity value. In my new book (still in edit), I discuss a number of techniques that eliminate the need for this approach or any approach that requires clients to fetch newly created Identity value after an insert. Basically, these techniques are patterned after age-old paper-based systems. One technique that I call the “waitress order-book” system has clients fetch “blank” order forms that have the Identity values “pre-printed” (already imbedded in the row). This means that the client requests a block of pre-populated rows and simply updates these rows with new customer data. Because these rows are “owned” by a specific client, updates can’t collide with other parallel operations.
As I said, these techniques are discussed in more detail in my new book.

Posted by William Vaughn at 07:18 PM | Permalink | Comments (1)

I saw this question in the microsoft.public.vb.database newsgroup this morning and since I wanted to start a “question of the day” series of blog entries, I thought this was as good a candidate as any.
The question:
I am having some problems with Visual Basic 2005 Express Edition and databases. I have tried three different pieces of code (one I made that executes an INSERT command on a SQL database, another sample project from Microsoft also using SQL, and finally one from VB 2005 EE for Dummies using an Access database) on two different computers but none of the databases saved. I have never worked with databases before, but I followed all the instructions exactly for the samples I used. I could see the information was in the dataset when I ran one of the programs. It had multiple forms that displayed the information in the database in two different ways. When I modified the data in on form and clicked the save button and then went to the other form I could see the updated data. When I closed the forms and started the program all the changes were gone, however in the video that went along with the code I saw the code run just as it should, the data came back when he restarted the program. Am I missing something here? I don't see what I could have possibly done wrong. It does not make sense to me at all. HELP ME!!!,

My response:
Let's walk through the steps used to create a database and get it to keep your data. There are a few issues that pop up when working with VB Express that don't occur with other versions (as they have more options). In VB Express, a common option when working with databases is to add a new database to your project. It sounds like you've done that and added one or more tables to the database. This leverages the instance of SQL Server Express installed with VB Express. Once you create your database it should appear as a Data Connection in the Database Explorer. As you've probably already discovered, this is where you can add and configure your database tables. Once the table is added (again, using the Database Explorer against your Data Connection), return to the Data Sources window and add that table using the Data Source Configuration Wizard (it ran once before when the database was first created) to the existing DataSet (or create a new one). Drag the DataSet from the Data Sources window to a Form [design] surface. This constructs a number of plumbing controls that bind the data to the database table and expose user interface elements like the DataGridView and toolbar to manage it. I expect you've reached this point already, but I wanted to make sure we're on the same page.
Okay, here is where I think your problem lies: by default, when you execute your application (click the "Debug" button), Visual Studio takes the .MDF database file from your project and copies it to the Bin\Debug directory associated with your project. It's this version that you're writing to. When you execute the program the next time, the process is repeated so the version containing your data is overlaid with the original version of the database. Microsoft seems to think this is a "feature"--to start with a virgin database each time. I don't agree, but there are a couple of ways to get around this issue.
In your Solution Explorer, select the .MDF database file and examine the properties. Note that the "Copy to Output" property defaults to "Copy Always". If you change that to "Do Not Copy", Visual Studio won't copy the project database to Bin (again). I suggest that you choose this option once the database is fully configured as changes you make to the project database schema (using the Database Explorer) won't be seen when you test your application. The other option "Copy if newer" tells Visual Studio to copy the database (and clear out the data) if the schema changes. Again, this can delete the data when you click F5 to debug the application.
So, what's the "real" solution? Well, there are really two viable solutions. First, you can create a stand-alone database with the (free) SQL Server Management Studio Express and use this tool to configure it. Here you'll find similar tools and wizard-like dialogs to create a new database and add tables, views and whatever you need. However, you'll have to "detach" the database from the SQL Server Express instance before you'll be able to create a Data Connection in Visual Studio Express--I expect due to the licensing restrictions in VS (or a bug).
Another approach is to use Visual Studio's Database Explorer to create a Data Connection and create the database in the process. This (IMHO) is what folks should do instead of adding a database to their projects. Warning, after you create this standalone database, it's attached to the local instance (SQLEXPRESS) of SQL Server Express--it's not added to your project UNLESS you click "Yes" when asked "Do you want to copy the database to your project?". Unless you want to get in the same trouble as before, click "No".
The downside to this standalone strategy is deployment. In this case you'll have to figure out how to get your standalone database installed on the target systems yourself. One approach might be to add it to the project at the last minute--just before you build your deployment configuration CAB files. But that's fodder for another answer...
In other editions of Visual Studio you can Attach and Detach databases and are afforded far more options when working with SQL Server (and other DBMS engines).
Hth

Posted by William Vaughn at 09:52 AM | Permalink | Comments (1)

According to this article Microsoft plans to further erode acceptance of Vista by not “supporting” any version of SQL Server other than 2005 SP1. This means existing SQL Server customers running older versions (the vast majority) will have a choice. Upgrade to SQL Server 2005 SKUs and bear the expense to redesign, recode, retest, and redeploy or not move to Vista. This is not that easy (as some might lead you to believe). Take MSDE for example. It supports replication subscriptions—SQL Server Express does not. The question is, what does “support” mean? Does it mean that Microsoft won’t let MSDE or SQL Server 2000 install on Vista? I don’t know—but I’m going to find out.
If you add to this the inability of many existing XP systems to upgrade at all (they require a new OS and complete reinstall of installed software) I foresee a dismal beginning to the Vista OS. Perhaps it’s really just a games platform designed for Betty and Bob consumer and the OEMs who want an OS that makes their existing platforms obsolete so they can sell replacement iron.

Posted by William Vaughn at 08:41 AM | Permalink | Comments (5)

I met with Microsoft last week over dim sun where we chatted about what’s right and what’s wrong with the documentation and help system. I said that the search engine is just busted. When I ask for an in-context topic I still get flooded with irrelevant topics. If I add more information to narrow the search, it seems to widen the search. I guess I’m just spoiled by Google that seems to get it right far more often.
So, I revert to the index trying to find something that’s close to what I want. This works better but still there are topics where the work selected takes me to Cleveland when I wanted Boston—and who would want Cleveland anyway?
When in the IDE, I make the mistake of selecting the term to search for—this works sometimes, but other times I just get “Form” or a suburb of Cleveland again.
After the talk, Microsoft wanted me to give them specific cases where I get sent off into the ozone. I’m building a log of all of these hits in an attempt to get them to see what’s going wrong.
Incidentally, they said that all of those little comments we put at the bottom of help topics are being kept by the Visual Studio people—and not passed to the help writers. Sigh.

If you have problems finding stuff with help in Visual Studio 2005, could you post these issues here? I expect that everyone is working with Visual Studio in ways that are far different than what I’m doing. Let’s see if we can help them help us (so to speak).
Bill

Posted by William Vaughn at 09:24 AM | Permalink | Comments (5)

We were conversing this morning about change in developer tools since the demise of VB6 Someone made the point that it's not the intelligence or experience of a developer that makes them sucessful, it's how long can they go before giving up in frustration...

My response was...
____________________
I expect that I’m as frustrated as anyone—perhaps more so. I agree with the assessment, but as I see it, experience can also be a hindrance and further contribute to my frustration. When an experienced developer attacks a problem with a new tool they first try the techniques learned when using the old tool. The problem is, the new tool (while it might have the same name as the old tool—or close—might not work in the “old” ways. After all, it’s “new and improved”—at least in someone’s estimation. People who have never seen the old version can be at an advantage. They don’t carry along the baggage of knowing how stuff used to work.

This problem won’t get any better until Microsoft stops churning just for the sake of churn—and they’re promising to do it even more often. Are a thousand little changes better than one humongous change less often? I’ll leave that up to you to debate. IMHO the answer is no, none of the above--especially when there are still outstanding issues with the last set of changes.

When someone blindfolds me and says they’re handing me a “coffee cup”, I expect to be able to reach out and be handed a vessel with specific properties. As of late, when it’s Microsoft handing me the cup, all too often I get burned and spill hot stuff on my lap.

As far as the books, instructions and training (and Microsoft documentation), these take time to create. My new book (in edit) talks about reporting and Windows forms, and data and SQL Server and how they connect and interact but it’s taken almost two years to write—only for it to be (at least in part) made obsolete by the next “new and improved” version.
___
Another comment echoed this: http://www.theaustralian.news.com.au/story/0,20867,19509645-36375,00.html

Posted by William Vaughn at 01:58 PM | Permalink | Comments (5)