Wrox Programmer Forums
|
BOOK: Beginning Microsoft Visual Basic 2008 ISBN: 978-0-470-19134-7
This is the forum to discuss the Wrox book Beginning Microsoft Visual Basic 2008 by Thearon Willis, Bryan Newsome; ISBN: 9780470191347
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Microsoft Visual Basic 2008 ISBN: 978-0-470-19134-7 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 August 7th, 2009, 11:41 AM
Authorized User
 
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default Chapter 17 Connection String

When attempting to complete the DataSet example starting on page 571 I get the following error.

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

I am presuming that I have the connection string wrong. The Pubs database is located at C:\SQL Server Databases\PUBS.MDF

The connection string I used in Visual Basic is:
Dim objConnection AsNew SqlConnection("server=localhost\SQL Server Databases;database=pubs;user id=;password=;")

Having been an Access user for quite a while, there is obviously something I missed when setting the SQL connection string. Can anyone help?
 
Old August 10th, 2009, 12:51 AM
Wrox Author
 
Join Date: Mar 2006
Posts: 91
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Where is your sql database? I will assume you have 1 default sql server instance on your local machine. You will need to know a login to the SQL database in the example.

Dim objConnection AsNew SqlConnection("server=localhost;database=pubs;user id=SqlUser;password=mySQLPassword;")

Does this make sense?

Bryan
 
Old August 25th, 2009, 02:51 PM
Authorized User
 
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default

Thanks bnews for trying to help. I have tried for weeks to get this to work with no success. The database is in a folder called "SQL Server Databases" on the C drive.

When I look at the 'SQL Server Management Studio the Pubs database is listed under the server 'SIRMILT-PC\SQLEXPRESS' My connection string now is:

(
"server=localhost\SQL Server Databases;database=PUBS;user id=;password=;")

I have tried to find a simple tutorial that I can follow with little success and am very close to scrapping the idea of moving from Access to SQL Server, unless someone can help me .

Thanks again

Milt
 
Old August 25th, 2009, 03:34 PM
Authorized User
 
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default

There has been some progress. I now get past the OpenConnection . Her's the code as I have it for this example:
Code:
Dim objConnection AsNew SqlConnection("server=localhost\SQLEXPRESS;Integrated Security=TRUE;")
Dim objDataAdapter AsNew SqlDataAdapter
Dim objDataSet AsNew DataSet
PrivateSub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
'Set the SelectCommand properties...
objDataAdapter.SelectCommand = New SqlCommand
objDataAdapter.SelectCommand.Connection = objConnection
objDataAdapter.SelectCommand.CommandText = _
"SELECT au_lname. au_fname,title,price " & _
"FROM authors " & _
"JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
"JOIN titles on titleauthor.title_id = titles.title_id " & _
"ORDER BY au_lname, au_fname"
objDataAdapter.SelectCommand.CommandType = CommandType.Text
'Open the database connection...
objConnection.Open()
'Fill the DataSet object with data...
objDataAdapter.Fill(objDataSet, "authors")
'Close the database connection...
objConnection.Close()
'Set the DataGridView propertiesto bind it to the data...
grdAuthorTitles.AutoGenerateColumns = True
grdAuthorTitles.DataSource = objDataSet
grdAuthorTitles.DataMember = "authors"
'Clean up...
objDataAdapter = Nothing
objConnection = Nothing
EndSub


The problem now is that I get an error message "Invalid object name 'authors'."

I know that "authors" is a valid table name in Pubs. Im also confused about why the database opens when I didn.t specify the database name.

Milt
 
Old August 25th, 2009, 09:54 PM
Wrox Author
 
Join Date: Mar 2006
Posts: 91
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Add database=PUBS to your connection string. Your user has a default DB to open and it is not pubs. Let me know what happens next. Bryan
 
Old August 28th, 2009, 10:01 AM
Authorized User
 
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default Pubs

Quote:
Originally Posted by bnews987 View Post
Add database=PUBS to your connection string. Your user has a default DB to open and it is not pubs. Let me know what happens next. Bryan
Bryan

Thanks, your suggestion worked perfectly.

Milt
 
Old January 30th, 2010, 03:57 PM
Authorized User
 
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default SQL Connection String -Chapter 17

After trying and getting completely frustrated last August, I gave up on SQL Server and continued using the Access database.

Now I'm going to try again. The code I tried today, to complete the execise on page 572 and which results in this error:

An error occurred creating the form. See Exception.InnerException for details. The error is: Keyword not supported: 'sqlserver databases;database'.

Since SQLServer Databases is the name of the folder that contains the Pubs database, I'm lost.

Here's the latest version of my code, which results in the above error:

Code:
Public Class Form1
    Dim objConnection As New SqlConnection _
    ("server=localhost\SQLEXPRESS;Integrated Security=TRUE;SQLServer Databases;database=PUBS")
    Dim objDataAdapter As New SqlDataAdapter
    Dim objDataSet As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Set the  SelectCommand properties...
        objDataAdapter.SelectCommand = New SqlCommand
        objDataAdapter.SelectCommand.Connection = objConnection
        objDataAdapter.SelectCommand.CommandText = _
            "Select au_lname, au_fname, title, price " & _
            "FROM authors " & _
            "JOIN titleauthor ON authors.au.id = titleauthor.id " & _
            "JOIN titles ON titleauthor.title_id = titles.title_id " & _
            "ORDER BY au_lname, au_fname"
        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        'Open the database connection...
        objConnection.Open()

        'Fill the DataSEt object with data...
        objDataAdapter.Fill(objDataSet, "authors")

        'Close the database connection...
        objConnection.Close() '

        'Set theDataGridView properties to bind it to our data...
        grdAuthorTitles.AutoGenerateColumns = True
        grdAuthorTitles.DataSource = objDataSet
        grdAuthorTitles.DataMember = "authors"

        'Clean up
        objDataAdapter = Nothing
        objConnection = Nothing

    End Sub
Can anyone point me in the right direction so that I can understand how the programs( both in development and the users)?

Milt
 
Old January 31st, 2010, 01:54 AM
Wrox Author
 
Join Date: Mar 2006
Posts: 91
Thanks: 0
Thanked 5 Times in 5 Posts
Default

take a look at http://www.connectionstrings.com/ for more in depth detail on the connection string. Yours is not correct.

Try this.

"server=localhost\SQLEXPRESS;Trusted_Connection=Tr ue;database=PUBS" There is no space in true.

Check the website above for many, many options on the connection string and you should be able to make it work.

Last edited by bnews987; January 31st, 2010 at 02:08 AM..
The Following User Says Thank You to bnews987 For This Useful Post:
sirmilt (February 5th, 2010)
 
Old February 5th, 2010, 02:12 PM
Authorized User
 
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default SQL Connection String -Chapter 17

After numerous suggestions, attempts and reading articles and books I still have the same problem.

The connection string is shown as follows:
Code:
Dim objConnection As SqlConnection = New SqlConnection("server=localhost\SIRMILT-PC\SQLEXPRESS\PUBS; Integrated Security=TRUE;")
The error I get is:
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

When I run the SQL Server Management Studio the opening screen shows:
Server Type: Database Engine
Server Name: SIRMILT-PC\SQLEXPRESS
Authentication: Windows Authentication

The User Name and password are greyed out.

When I click the "Connect" button:
The left column is headed "SIRMILT-PC\SQLEXPRESS(SQL Server 10.0.2531 - sirmilt-PC\sirmilt)"

As A Result:
I am completely frustrated and ready to un-install SQL Server and continue using Access as my database of choice.

Can anyone point out where I have gone wrong?
 
Old February 6th, 2010, 01:27 AM
Wrox Author
 
Join Date: Mar 2006
Posts: 91
Thanks: 0
Thanked 5 Times in 5 Posts
Default

This is the syntax Server=myServerName\theInstanceName; Database=myDataBase; Trusted_Connection=True;
You should have this as a connection string

Server=SIRMILT-PC\SQLEXPRESS;Database=PUBS;Trusted_Connection=True;


That will work for a connection string. You can get more info here http://www.connectionstrings.com/sql-server-2005

Also, I do not have the chapter in front of me and I believe it uses the sa account. You may want to go into properties and turn on mixed mode authentication as I think it is outlined in the chapter if that does not work. Then set the sa user password and use a connection string like this.

Server=SIRMILT-PC\SQLEXPRESS;Database=PUBS;;User Id=sa;Password=yourpassword;

Hope this helps. Bryan





Similar Threads
Thread Thread Starter Forum Replies Last Post
Errata Chapter 17 sahyouni ASP.NET 2.0 Professional 1 July 9th, 2009 06:07 AM
Chapter 17 HenzFast BOOK: Beginning Microsoft Visual Basic 2008 ISBN: 978-0-470-19134-7 2 April 14th, 2009 03:28 AM
Chapter 17 First Try It Out paloyme BOOK: Beginning Visual Basic 2005 ISBN: 978-0-7645-7401-6 7 August 23rd, 2006 11:14 AM
Chapter 17 first Try It Out paloyme Visual Basic 2005 Basics 0 April 17th, 2006 04:34 AM
Chapter 17 Gaby BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 3 September 8th, 2005 11:50 AM





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