Wrox Programmer Forums
BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5
This is the forum to discuss the Wrox book Beginning Visual Basic 2005 Databases by Thearon Willis; ISBN: 9780764588945
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 31st, 2010, 02:14 PM
Registered User
Join Date: Dec 2008
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Server Authentication

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.
Old April 1st, 2010, 06:26 AM
Thearon's Avatar
Wrox Author
Join Date: Dec 2003
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts


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.

Old April 1st, 2010, 02:34 PM
Registered User
Join Date: Dec 2008
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts


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.
Old October 19th, 2010, 03:46 PM
Registered User
Join Date: Dec 2008
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default How can I use "SELECT @@Identity"

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
WCF Service and SQL Server Authentication jacob .NET Web Services 0 April 2nd, 2009 09:14 AM
Connect to SQL Server using NT Authentication (ADO donnie200 Access VBA 5 July 15th, 2005 06:33 AM
Secure SQL Server authentication sal ASP.NET 1.x and 2.0 Application Design 3 October 17th, 2004 04:02 PM
forms authentication with sql server 2000 Lee8mm ASP.NET 1.0 and 1.1 Professional 0 September 26th, 2003 08:58 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.