Visual Studio 2010 - which version for SQL connection on server?

[Fedaykin]

I'm trying to figure out which version of Visual Studio 2010 I need to be able to connect to SQL database on a server. Microsoft's website is so overun with 2012 stuff that I can't find a concise answer.

My applications are simple and will just require basic read/write to tables. Can anyone point me in the right direction? Thank you in advance.

 

[IanRyder]

Hi

I'm trying to figure out which version of Visual Studio 2010 I need to be able to connect to SQL database on a server.

All versions of VS2010 are able to connect to a Database on an SQL Server so anything from Express up to Ultimate will work just fine.

Hope that helps.

Cheers,

Ian

 

[Fedaykin]

Thanks for the reply Ian, however Express is telling me this version cannot connect to a SQL database on a server. It will only connect to a local drive.

 

[jmcilhinney]

Thanks for the reply Ian, however Express is telling me this version cannot connect to a SQL database on a server. It will only connect to a local drive.

That is not correct. It will only generate a Data Source from a local SQL Server Express instance but you can connect to any database you like in code. If you need to use a remote database then just reproduce it on a local SQL Server Express instance to generate the Data Source and then change the connection string in the config file to point to the remote instance for testing.

 

[Fedaykin]

You'll have to forgive me, i'm very new to this. I'm not quite following what you're saying. Can I read/write information to a remote database using Visual Basic Express?

 

[jmcilhinney]

You'll have to forgive me, i'm very new to this. I'm not quite following what you're saying. Can I read/write information to a remote database using Visual Basic Express?

I already answered that.

you can connect to any database you like in code

 

[Fedaykin]

According to Microsoft you cannot access a remote database using SQL 2008 Express. Description of support for network database files in SQL Server

The problem with trying to access a remote SQL server instance in code is that you must directly connect to the .mdf file using VB 2010 Express and SQL 2008 Express.

You will be able to directly connect to a SQL Server instance on your local C:\ drive without any problems. But when you attempt to access an instance on a remote drive you will get the following error: ..is on a network path that is not supported for database files.

According to Microsoft this is a very bad idea and it is not supported.

So, in short, the price you pay for the free version of VB 2010 Studio Express and SQL 2008 Express is that you cannot connect remotely through SSMSE AND you cannot connect in code.

Believe me, I've tried exhaustively messing with TCP/IP settings, enabling Server Browser Startup, Configuring remote access blah, blah blah. It doesn't work.

If anyone can SHOW me the code to connect, I'd would love, love love to see it.

here is the code that works beautifully because it is on a local drive:

            Dim sqCon As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\QCGMAIN.mdf;Database=QCGMAIN; Trusted_Connection=Yes;")
            Dim sqCmd As New SqlClient.SqlCommand

            sqCmd.Connection = sqCon            'create the DB connection 
            sqCmd.CommandText = "INSERT dbo.Proj_Setup (ProjectID, PartNum, CustRev, ANotes, QTY, SolderType, CustID, ProjNum, Rev, BOM, FabDate) VALUES ('" & ProjectIDA & "', '" & PartNum & "', '" & CustRev & "', '" & AssyNotes & "', '" & QTY & "', '" & BuildType & "','" & CustID & "', '" & ProjNum & "','" & RevNum & "','" & BOMNum & "', '" & FabDate & "')"

            sqCon.Open()                        'open the connection
            sqCmd.ExecuteNonQuery()             'execute the SQL command
            sqCon.Close()

here is my code that fails miserably because it is on a network drive (remote computer):

            Dim sqCon As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=\\PHYLLIS\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\QCGMAIN.mdf;Database=QCGMAIN; Trusted_Connection=Yes;")
            Dim sqCmd As New SqlClient.SqlCommand

            sqCmd.Connection = sqCon            'create the DB connection 
            sqCmd.CommandText = "INSERT dbo.Proj_Setup (ProjectID, PartNum, CustRev, ANotes, QTY, SolderType, CustID, ProjNum, Rev, BOM, FabDate) VALUES ('" & ProjectIDA & "', '" & PartNum & "', '" & CustRev & "', '" & AssyNotes & "', '" & QTY & "', '" & BuildType & "','" & CustID & "', '" & ProjNum & "','" & RevNum & "','" & BOMNum & "', '" & FabDate & "')"

            sqCon.Open()                        'open the connection
            sqCmd.ExecuteNonQuery()             'execute the SQL command
            sqCon.Close()

Forgive my sarcasm, but telling a motorist who has lost their way that they are in a car is a technically correct.... but thoroughly useless answer.

 

[jmcilhinney]

You are under some misconceptions here. First of all, there are two ways to work with databases with SQL Server and .NET apps.

1. Local data file
2. Attached database

Option 1 is only used when you deploy a loose MDF file with your app. You use the AttachDbFilename attribute in the connection string to specify the file and it gets attached on demand, i.e. when you connect, and detached again when you're done. That is only valid for a local instance, no matter what edition of VS you use.

If you want to use a remote instance then you must use option 2. That means opening SQL Server Management Studio and creating the database there, not in VS. That database will then be permanently attached to the instance and not part of your app. To connect to that in code you use the Initial Catalog attribute in the connection string.

So, basically, you're doing it wrong. If you want to use a remote instance then DO NOT use AttachDbFileName in your connection string. As I said, that has nothing whatsoever to do with the edition of VS you're using. When it comes time to deploy your app, there are various ways you can deploy the database but, regardless of which you choose, it MUST be attached permanently to the SQL Server instance.

 

[Fedaykin]

I appreciate you responding, I really do. However, I'm new to coding for SQL Server so the references you are making are not very helpful. What I was looking for was example code. For anyone just starting out, here is the code that actually works! I tested this in both Visual Basic 2010 Express and Visual Basic 2010 Professional. Both connected to the database in Microsoft SQL Server Management Studio Express (SSMSE). Please note that SSMSE must be open and connected in the background while you are writing/debugging code. Once you've compiled your code and deployed it as an executable you do not need to run SSMSE in the background. This was extremely frustrating to figure out (and from digging through several forums I'm not the only one who found this confusing).

'write a record to the database table
Dim sqCon As New SqlClient.SqlConnection("Server=PHYLLIS\SQLEXPRESS;Database=QCGMAIN; Trusted_Connection=True;")
Dim sqCmd As New SqlClient.SqlCommand

sqCmd.Connection = sqCon            'create the DB connection 
sqCmd.CommandText = "INSERT dbo.Proj_Setup (ProjectID, PartNum, CustRev, ANotes, QTY, SolderType, CustID, ProjNum, Rev, BOM, FabDate) VALUES ('" & ProjectIDA & "', '" & PartNum & "', '" & CustRev & "', '" & AssyNotes & "', '" & QTY & "', '" & BuildType & "','" & CustID & "', '" & ProjNum & "','" & RevNum & "','" & BOMNum & "', '" & FabDate & "')"
sqCon.Open()                        'open the connection
sqCmd.ExecuteNonQuery()             'execute the SQL command
sqCon.Close()

 

[jmcilhinney]

Please note that SSMSE must be open and connected in the background while you are writing/debugging code. Once you've compiled your code and deployed it as an executable you do not need to run SSMSE in the background.

I don't know what was going on on your machine but that is absolutely not true. In fact, you don't even need Management Studio installed. All you need is a running instance with a database attached.