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

Question

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

I've successfully been 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)

`

Is it the first connection cnn.Open() that fails or is it the .Fill() methods — Conrad Frix
– Conrad Frix, Commented Mar 27, 2011 at 22:25
the .Fill() method will fail. cnn.Open() will open fine. The thing is, if I remove the whole sql connection bit, the .Fill() will correctly work. They seem to be interfering with each other somehow — Christopher C
– Christopher C, Commented Mar 28, 2011 at 0:09

Christopher C · Accepted Answer · 2011-03-29 01:34:46Z

2

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.

answered Mar 29, 2011 at 1:34

Christopher C

1933 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:12:38Z

0

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.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Mar 28, 2011 at 2:39

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

1 Comment

Christopher C Over a year ago

I opened up SQL Server Management Studio and browsed to Security -> Logins and changed the default database for 'Chris-PC\Chris' from 'master' to 'database_db'. Unfortunately, I was only able to login with sql via code and the .Fill() method still produces the login error. I tried removing the sql code to see if the .Fill() would work(like before) but this time it also produces the error.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related