Unable to connect to database using both tableadapters.fill & sql code in VB.NET

Running off my laptop: Visual Basic 2010 Express & SQL Server 2008

I've successfully been 开发者_Python百科able to use the connection wizard to connect to my database in vb.net and read/write data using datasets/tableadapters/binding_source etc.

Now I am trying to create an SQL connection via code to connect to my database to do some more complicated queries. I was successful in being able to connect to my database with SQL, but by doing so it produces an error when it tries to run any .Fill() methods from my TableAdapters below.

The error produced is the following: "SqlException was unhandled. Cannot open user default database. Login failed. Login failed for user 'Chris-PC\Chris'".

If I remove my SQL connection code, the TableAdapter.Fill() methods will work fine.

How can I make both simultaneously access the database? Any suggestions?

Here is the code: `

  Dim connetionString As String
    Dim cnn As SqlConnection
    connetionString = "Data Source=CHRIS-PC\SQLEXPRESS;Initial Catalog=database_db;Integrated Security=SSPI;Trusted_Connection=True;"
    cnn = New SqlConnection(connetionString)
    Try
        cnn.Open()
        MsgBox("Connection Open ! ")
        cnn.Close()
    Catch ex As Exception
        MsgBox("Can not open connection ! ")
    End Try


    Me.JobsTableAdapter.Fill(Me.database_dbDataSet.jobs)
    Me.ApplicationsTableAdapter.Fill(Me.database_dbDataSet.applications)
    Me.ApplicantsTableAdapter.Fill(Me.database_dbDataSet.applicants)

`

So after hours of more searching and testing, I finally was able to fix my problem.

The issue was with the connection string that gets created with the Configuration Wizard for my DataSets. The key problem was that it had "User Instance = True"

When "User Instance = True", you remove the ability for any other connections to be able to connect to the server. Therefore, I opened my app.config and change the connection string to the one I have been using for the SQL connect in my code. After that, all worked fine.

Typically when I've seen the error Cannot open user default database it's because the user has not been granted access to the default database. The quick work around would be to set the default to something you're sure the user has access to.The answers to this question show you how to set the default database using TSQL.

The accepted answer from Stephen Wrighton has this code.

sp_defaultdb [@loginame =] 'login' , [@defdb =] 'database'

That said I have no idea why opening a connection would cause this error in a subsequent Fill.