Can not connect to SQLExpress in VB but can from SSMS (1 Viewer)

essaytee

Need a good one-liner.
Joined
Oct 20, 2008
Messages
512
I've taken the plunge, now trialling SQLExpress along with VB. I'm following an online tutorial but have got to a point where I can't connect to an MSSQL database.

I've installed SQLExpress 2017 and the SSMS tools program. They were both installed to their default locations on my C: drive.

From SSMS I created a database, "Test.mdf", created one table, "Contacts" and added some dummy data. The "Test.mdf" is located here:
Code:
"C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA"
The Server Name = "MyComputerName\sqlexpress"

The following is the connection string:

Code:
MyCn.ConnectionString = "Data Source=MyComputerName\SQLEXPRESS; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf; " &
            “User Instance=True;Integrated Security=SSPI”
that I'm using, as suggested by the tutorial, but when the code gets to MyCn.Open() it errors out as follows:

Code:
System.Data.SqlClient.SqlException:
'Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf". 
Operating system error 5: "5(Access is denied.)".
An attempt to attach an auto-named database for file
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf failed.
A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.'
My user account has Administrator Rights. Any assistance would be appreciated.

Steve.

ps. For completeness, the following is the entire code snippet:

Code:
Public Class Form1
    Private MyDatAdp As New SqlDataAdapter
    Private MyCmdBld As New SqlCommandBuilder
    Private MyDataTbl As New DataTable
    Private MyCn As New SqlConnection
    Private MyRowPosition As Integer = 0

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        MyCn.ConnectionString = "Data Source=MyComputerName\SQLEXPRESS; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf; " &
            “User Instance=True;Integrated Security=SSPI”

        MyCn.Open()    ' errors out here

        MyDatAdp = New SqlDataAdapter("Select* from Contacts", MyCn)
        MyCmdBld = New SqlCommandBuilder(MyDatAdp)
        MyDatAdp.Fill(MyDataTbl)

        Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
        Dim strName As String
        Dim strState As String
        strName = MyDataRow("ContactName")
        strState = MyDataRow("State")
        TxtName.Text = strName.ToString
        TxtState.Text = strState.ToString()
        Me.showRecords()
    End Sub

    Private Sub showRecords()
        If MyDataTbl.Rows.Count = 0 Then
            txtName.Text = ""
            txtState.Text = ""
            Exit Sub
        End If
        txtName.Text = MyDataTbl.Rows(MyRowPosition)("ContactName").ToString()
        TxtState.Text = MyDataTbl.Rows(MyRowPosition)("State").ToString()
    End Sub
   
End Class
 
Last edited:

essaytee

Need a good one-liner.
Joined
Oct 20, 2008
Messages
512
Thanks Galaxiom for the link and advice, all sorted now. After a few failed attempts it finally worked. The following connection string did the trick:

Code:
"Server=.\SQLExpress; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf; Database=Test; Trusted_Connection=True;"
 

essaytee

Need a good one-liner.
Joined
Oct 20, 2008
Messages
512
And finally, after reading your advice once again, I removed the AttachDbFilename reference, and a connection is made.

Connection string is now:

Code:
"Server=.\SQLExpress; Database=Test; Trusted_Connection=True;"
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom