Having received no formal instruction on VB programming, I have been using Thearon's excellent book in my retirement, with considerable success, to try to learn something about programming with databases. However, although I can understand enough to use MS Access, I am having a lot of difficulty understanding the MS SQL Server 2005 database. (On investigation, I find that I am still using the Express Edition!).

After many attempts, I thought I had finally got it sorted when I got to Chapter 10 and everything was working smoothly. However, in Chapter 11, I got as far as typing all the code to cope with Roles and Users and, as instructed, I entered a new Role - 'User' - with its Description - 'Time Tracker User' - and Ranking - '1' - and successfully added it to the database.

At this point I checked that it had indeed been added to the database by opening the Server Explorer window and examining the Roles Table. Everything appeared OK.

Then the problems started: -

Thereafter, I found that I could no longer access the database through the program, which had been set up to use SQL Server authentication. The strange thing is that when I use the same User ID and Password in SQL Server Management Studio Express, I have no problem accessing the database but the program still will not allow me to connect to the database.

I have checked that the program's connection string was decrypting the Registry entries correctly, and found that there was no corruption there.

I then tried connecting through the Server Explorer window using SQL Server authentication and it wouldn't work either.

I can't figure out where the problem might be but obviously it is associated with my lack of understanding of MS SQL Server rather than the Time Tracker program. It is driving me insane since I can't continue without solving it . Information which is within my limited understanding is difficult to come by so I hope someone can point me in the right direction again so that I can continue my belated education.

Kernan,

Please try this. Go to the Start button and then navigate to All Programs>Microsoft SQL Server 2005>Configuration Tools and then click on SQL Server Configuration Manager. In the tree view on the left, find the item Protocols for MSSQLSERVER. Look for TCP/IP on the right side of the window and make sure this is enabled. If it is not then you can right click on it and choose Enable from the context menu. After this you'll need to restart the SQL Server service - the best way is to just restart your computer.

Let me know if this does not help and we can investigate further.

Thearon

Thearon,

Many thanks for your prompt attention to my predicament. I appreciate your interest. However, in the meantime, I did manage to solve the problem - and it was so simple that I now feel very foolish for having taken up your time.

In trying just about everything I could think of, I had detached the database using MS SQL Server Management Studio Express, and then re-attached it. This made it possible to connect through the SSMSE but not through the TimeTracker program because what I had forgotten was that I needed to rename the database to match the Database name stored in the Registry. Once I realised that and made the necessary changes everything clicked into place. There's no fool like an old fool!

Thanks anyway.

Thearon,
I have a problem in trying to retrieving the last entered identity, and although I have looked at a variety of solutions, I can't find one which fits the model you use in your excellent book.

I have used a similar DataAccessBase class to the one used in your book, along with your suggested 3-tier architecture for a project of my own in which I need to insert an Order into an Order table (SQL Server), then retrieve the OrderID for insertion into an OrderDetail table.

I know I have to use "SELECT @@Identity" (or equivalent) immediately after adding the Order and before closing the connection, but I just can't see how to do this in the context of your model. The DataAccess.AddOrder procedure is achieved within a Function which already returns a boolean value. The operation to do so is through an ExecuteNonQuery function in the DataAccessBase class, instead of the required ExecuteScalar function that I need for @@Identity. I would try rewriting some of the code if I knew where to start.

Is there any way of modifying your model to allow me to achieve this?

Any help would be appreciated.

Kernan