Chapter 17 Connection String

sirmilt · August 7th, 2009, 11:41 AM

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?

bnews987 · August 10th, 2009, 12:51 AM

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

sirmilt · August 25th, 2009, 02:51 PM

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

sirmilt · August 25th, 2009, 03:34 PM

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

bnews987 · August 25th, 2009, 09:54 PM

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

sirmilt · August 28th, 2009, 10:01 AM

Quote:

Originally Posted by bnews987

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

sirmilt · January 30th, 2010, 03:57 PM

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

bnews987 · January 31st, 2010, 01:54 AM

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.

sirmilt · February 5th, 2010, 02:12 PM

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?

bnews987 · February 6th, 2010, 01:27 AM

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