Correct connection string for VB.NET WinForms application connecting to local SQL Server db

I am building a VB.NET WinForms application in VS2013. The program is using a SQL Server 2012 database that is local to the deployed application - both on the development computer and the computer (the only computer) the program will be used on.

In the program, when the user clicks a button, a sql script - an sql file on the computer - needs to execute.

Depending on the type of connection string I try, I am getting different errors and I'm not sure which connection string to use.

Here is my code:

Private Sub btnLoadMasterData_Click(sender As Object, e As EventArgs) Handles btnLoadMasterData.Click
	Dim connString As String = "Server=DHSNET\marky;Database=hours_analysis;User Id=*[USER]*;Password=*[PASSWORD]*"
    'Dim connString As String = "Server=DHSNET\marky;Database=hours_analysis;Integrated Security=True"
    'Dim connString As String = "Server=(LocalDB)\v11.0;Integrated Security=True;AttachDbFilename=C:\*[PATHTOFILE]*\hours_analysis.mdf"
    'Dim connString As String = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\[PATHTOFILE]\hours_analysis.mdf;Integrated Security=True;Connect Timeout=30"
	
    Dim file As New FileInfo("C:\HL_Time_Entry_Reporting\SQL Scripts\stage_employee_hours_load.sql")
	Dim sqlText As String = file.OpenText().ReadToEnd()

	ExecuteCommand(sqlText, connString)
End Sub

Private Sub ExecuteCommand(queryString As String, connectionString As String)
	Using connection As New SqlConnection(connectionString)
		Dim command As New SqlCommand(queryString, connection)
		command.Connection.Open()
		command.ExecuteNonQuery()
	End Using
End Sub

As you can see, I've tried several types of connections. The fourth one is what VS2013 set up to connect to the database for the datagridviews in the application, which load and work fine.

I got the other three connection strings from the ConnectionStrings.com "SQL Server 2012 connection strings" page.

When I use the first and second connection strings there is a pause of about five seconds and then a SqlException error occurs on the Connection.Open() line, saying, "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 use either of the connection strings for the mdf file I get a SqlException error saying "Database 'hours_analysis' does not exist. Make sure that the name is entered correctly." (The path to the file is correct)

What connection string to I need to use?

1 answer