How do I connect to a database in SQL Sever 2008 R2 Express from Visual Basic 2010 Express?

Question

Here is what I have installed:

 

Visual Basic 2010 Express

SQL Server 2008 R2 Express w/ Advanced Services

 

I created a database with SQL Server Management Studio and am trying to connect to it from Visual Basic. From within VB I open up the Database Explorer. I right-click and select Add Connection. From here I select Microsoft SQL Server Database File (SqlClient) for the Data Source. The next box under that asks me for the Database file name (new or existing). If I type in the name of the database that i already created, it just creates a new one in my documents folder. If I browse to the SQL Server install directory and select the .mdf file from there, I get a "this file is in use" error.

All the examples I have looked show that I can enter my server/instance name for the SQL server, however it is only asking me to enter the database file.

How do I connect to a database that has already been created?

Answer 1

You can do it this way, but you won't learn anything useful.

Renee

Answer 2

You can do it this way, but you won't learn anything useful.

Renee

I guess I don't understand your reply. Why will I not learn anything useful? Am I trying to add the database in the wrong way? Do you have any suggestions on how to do it another way?

Answer 3

Microsoft want's to add a database by using canned procedures. There are more basic ways that involve more learning but provide more flexinility. For example, I don't even tell microsoft that I'm using SQL for the duration of a  project.

Renee

Answer 4

Renee, thank you for your help, but I think I am just getting more confused now. VB lets me create a database and modify tables, etc within the database explorer. I want to create the database and its objects with SQL Server Management Studio since it has diagram tools, etc and then bring that database into my project within VB. Is that possible? It seems like it should be so easy, but I am not sure if that is something you cannot do with the express editions.

Answer 5

Here's a step by step approach using VB 2008 (I guess it won't be far from VB 2010):

1. From the DataSources Window, click Add DataSource

2. Select Database Icon and click Next

3. Click New Connection, in the next  dialog box:

4. Click Change and select SQL Server in the dialog box that opens. Click Ok

5. (a). In the Server Name combobox type in your server or select it from the dropdown list (click the refresh button if your database name is not listed)

   (b). In the Select or enter database name combobox, type in your database name or select it from the combobox

   (c). Click Test Connection

6. If the connection is successful

7. Proceed to the next step...

 

---

Only performance counts!

Answer 6

"Renee, thank you for your help, but I think I am just getting more confused now. VB lets me create a database and modify tables, etc within the database explorer. I want to create the database and its objects with SQL Server Management Studio since it has diagram tools, etc and then bring that database into my project within VB. Is that possible? It seems like it should be so easy, but I am not sure if that is something you cannot do with the express editions."

I do that normally for all of my projects.

Do not whatever you do, tell VB that you are creating a database for it.

Create your database with SSMS and connect to it with your project. So it is possible and experienced developers doit this way.

Renee

Answer 7

Sylva, the steps you listed is what I was doing until step 4. When I click change, it offers 3 options: Access Database File, SQL Server Compact 3.5, or SQL Server Database File. When I select SQL Server Database File and click OK, it goes back to the Add Connection window and there is not a combobox to select a server. There is a textbox that asks Database file name (New or existing) and a browse button next to it. If I type in my database name, it creates a new .mdf file in my Documents folder with that name. If I browse to the C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA folder where the database file is I created with SSMS is and select the file, I get a "This file is in use" error.

Am I doing everything correct up until now? I have closed out of SSMS and I still get the same error. I wouldn't have to shut down the SQL Server service to access this file would I?

Answer 8

Well, that's strange although I don't use VB 2010 so I wouldn't know how the connection works. Strange that there is no SQL Server in vb 2010 options. Just keep the thread open, I hope that somebody with vb2010 will step in with a solution soon

Good luck.

---

Only performance counts!

Answer 9

Have you disconnected your database from SQL Management Studio? When you get the error you mention it is Management Studio who is using the file. I had this problem and was very frustrated for days. You might even have to reboot but  then don't open Management Studio.

Mary

Answer 10

I believe the problem is as Mary suggested. The database is already attached to SQL Server and Visual Basic Express will attempt to create a connection string with the Attach statement when generating a new Data Connection. Try detaching the database using SQL Server Management Studio first and then add it as a new connection in Visual Basic Express.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 11

But how does one explain that there is no SQL Server option in the 4th step of my post above? If you're using VB 2010, can you confirm if that's true, please?

---

Only performance counts!

Answer 12

I had the same problem.

This was the solution for VS 2010 B2 and it still works for the VS 2010 Express release.

Make sure you pay attention to the connection. Do not use User Instance!

http://social.msdn.microsoft.com/Forums/en-US/vbide/thread/d52ab30d-0bdb-40da-b951-82d695d78baa

Good Luck

 

Answer 13

But how does one explain that there is no SQL Server option in the 4th step of my post above? If you're using VB 2010, can you confirm if that's true, please?

---

Only performance counts!

Visual Basic Express Edition 2010 does not support connecting directly to SQL Server or SQL Server Express Edition. You can only connect to a local SQL Server by attaching an MDF file.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 14

Hi Paul,

I can confirm that the SQL SERVER Option does NOT appear in VB 2010 Express but does appear in VS 2010 Web Developer. This was my original problem. The work around I proposed has been working since late December of last year even after the recent upgrade.

I connect to the Server via an instantiated SQLdataAdapter.

 

 

Answer 15

Yes, connecting through code is perfectly fine and works for other database systems as well such as MySQL, Oracle, etc. They just limited the Data Connection Wizard to Access and local SQL Server database files in the Express Edition.

---

Paul ~~~~ Microsoft MVP (Visual Basic)