Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2005 > BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 28th, 2008, 07:31 AM
Registered User
 
Join Date: Dec 2008
Location: Northern Ireland
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Wink Creating SQL Server database with Visual Basic 2005 Express

I have been using Thearon Willis's book 'Beginning Visual Basic 2005 Databases' to learn about using SQL Server 2005 Express instead of Access 2000, which database I have used in the past, and as a hobbyist programmer, I have found the book very useful indeed.

At the outset, I have to admit that instead of using the project in the book, I am trying to adapt the methods to an old project of my own which used Access 2000 as its database. At first everything worked fine and I found a whole new way of dealing with Data Access using the WBAccess base class.

However, when I tried to migrate my database to SQL as described in Chapter 8, I ran into a number of problems mainly brought on by the fact that I am using the Express Editions of both Visual Basic 2005 and SQL Server 2005. Some of these problems, such as Windows Authentication, I managed to solve by reading through the threads opened by some of the contributors to this forum but one I can't solve:-

I created an empty SQL Server database using Visual Basic 2005 Express, which works differently from the process descibed in Chapter 1. (This included 14 tables , primary keys and relationships!) Unfortunately VB Express appears to produce something called a User Instance, which I don't really understand, and as such, I can't connect to it through the usual Server - (local)\SQLExpress - in the migration program. I have tried using the Server Management Studion Express to attach the empty SQL database to my Server but without success. No doubt, had I been following the text in your book to the letter and created the ProjectTimeTracker database, the same problem would have arisen.

I would appreciate any help you can offer to allow me to continue to explore the rest of this excellent book.
Reply With Quote
  #2 (permalink)  
Old December 28th, 2008, 04:46 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

I can only tell you what worked for me.

I created the DB using the free "Management Studio Express".

*THEN* I connected to it from VB Express. And then I could use VB to create tables in that DB and the tables are visible quite nicely from Management Studio Express, from other SQL Server clients, and of course from VB.

So it's only the need to create the DB outside of VB that may be hanging you up. Maybe.
Reply With Quote
  #3 (permalink)  
Old December 29th, 2008, 06:58 AM
Registered User
 
Join Date: Dec 2008
Location: Northern Ireland
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Many thanks for your prompt reply, Old Pedant.

What you suggest sounds like a good way of getting round the problem. I haven't tried it yet since, in the meantime, I found another solution - I used the free Microsoft SQL Server Migration Assistant for Access and succeeded in achieving the same end result, except that the resulting SQL database was accessible from both Microsoft SQL Server Management Studio Express and VB Express.

Nevertheless, out of curiosity, I will try your solution in the near future.

Again, many thanks.

I have now tried your suggestion but there are still problems:

I first created a new database in SSMSE but VB wouldn't connect with it, so I detached it from my Server. This allowed VB to connect and I used VB to create, yet again, all the tables and relationships. I then re-attached the database to my Server using SSMSE again. (I had to exit VB before this was possible.)

Then back to the Migration program: It still will not connect - I get the message "Cannot open database 'MyDB' requested by the login. The login failed."

What is wrong?

Last edited by kernan; December 29th, 2008 at 12:33 PM. Reason: Unsuccessful attempt to apply suggestion
Reply With Quote
  #4 (permalink)  
Old December 29th, 2008, 04:20 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Dunno what to tell you. As I said, it *DID* work for me, doing it the way I described.

Granted, it took me a couple of tries to figure out the right connection string to use (VB wouldn't connect using its own defaults), but once I got the connection string right it worked just fine.
Reply With Quote
  #5 (permalink)  
Old December 30th, 2008, 09:45 AM
Registered User
 
Join Date: Dec 2008
Location: Northern Ireland
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Old Pedant

Thanks again for your interest. Good news! I finally got it to work. Bad news! I don't remember how - I made so many changes, moved things around, renamed files, detached and re-attached databases, etc. that I lost track completely. However, the outcome was worth it.

There is one point I'd like to make however. One of the tables I was migrating was empty and this caused an error in the 'Finally' section of the published code due to the fact that because there were no rows in the DataReader, the objData.command didn't exist, so it couldn't be disposed! I changed the code so that the 'cleanup' was contained within the 'If' - 'End If' section as below and removed the 'Finally' section completely:
Code:
If .DataReader.HasRows Then
......
'Process all rows
While .DataReader.Read
.......
'Execute the INSERT statement
objData.Command.ExecuteNonQuery()
EndWhile
'cleanup
objData.Command.Dispose()
objData.Command = Nothing
EndIf
EndWith
'close the DataReader
objAccessDB.DataReader.Close()
.......


This avoids the problem. Maybe someone will find it useful!



Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visual Basic 2005 Express CForce Visual Basic 2005 Basics 1 June 22nd, 2008 10:21 PM
Visual Basic 2005 Express Edition thomasks BOOK: Beginning VB.NET Databases 8 January 3rd, 2007 01:11 AM
SQL Server 2005 Express with Visual Web Developer steffteo SQL Server 2005 1 March 19th, 2006 03:28 PM
Visual Basic Express 2005 SQL error thomas38 Reporting Services 0 February 16th, 2006 01:53 PM



All times are GMT -4. The time now is 07:07 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.