ConnectionString not correct

Question

I make windows application in Visual Studio 2010. Then i add a database. The database properties is :

Name : C:\USERS\ADMINISTRATOR\DOCUMENTS\VISUAL STUDIO 2010\PROJECTS\WINDOWSAPPLICATION4\WINDOWSAPPLICATION4\DATABASE1.MDF

ConnectionString : Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\Administrator\documents\visual studio 2010\Projects\WindowsApplication4\WindowsApplication4\Database1.mdf";Integrated Security=True;User Instance=True

Provider : .NET Framework Data Provider for SQL Server

Type : Microsoft SQL Server

Version : 10.00.5500

I write this code :

  Dim cn As New ADODB.Connection()
    cn.Open("provider= SQLOLEDB.1;Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Administrator\documents\visual studio 2010\Projects\WindowsApplication4\WindowsApplication4\Database1.mdf;User Instance=True")
    Dim rs As New ADODB.Recordset()
    rs.Open(("Select from TB Where Surname = Stylianou  "), cn)
    While Not rs.EOF
        MsgBox("here")
    End While

And debugger says : Invalid connection string attribute

Andrew Morton Andrew Morton · Accepted Answer · 2014-05-20T19:00:08

A few points:

you're trying to use the old way (i.e. recordset) of reading from a database
you're not using the System.Data.SqlClient class where you should (it's optimised for SQL Server)
you can make connection strings more reliably with the SqlConnectionStringBuilder Class
values should really be passed as parameters (which helps to avoid SQL injection attacks and makes it work if you have, say, an apostrophe in the value) instead of writing them directly in the SQL query string.

You could have something like:

Imports System.Data.SqlClient

' ...

Private Sub LoadData()
    Dim cnsb As New SqlConnectionStringBuilder
    cnsb.DataSource = ".\SQLEXPRESS"
    cnsb.AttachDBFilename = "C:\Users\Administrator\documents\visual studio 2010\Projects\WindowsApplication4\WindowsApplication4\Database1.mdf"
    cnsb.IntegratedSecurity = True
    cnsb.UserInstance = True

    Using conn As New SqlConnection(cnsb.ConnectionString())
        'TODO: Put in the actual column names you want returned
        Dim sql As String = "SELECT [Column1], [Column2] FROM [TB] WHERE [Surname] = @Surname"
        Using sqlCmd As New SqlCommand(sql, conn)
            'TODO: Use the actual .SqlDbType and .Size for the [Surname] column
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Surname", .SqlDbType = SqlDbType.NVarChar, .Size = 50, .Value = "Stylianou"})
            Dim rdr As SqlDataReader = sqlCmd.ExecuteReader()
            While rdr.Read()
                ' do something with the results
                MsgBox(String.Format("Col1={0}, Col2={1}", rdr.GetString(0), rdr.GetString(1)))
            End While
        End Using

    End Using

End Sub

If there are any commands in there which you want to learn more about (I'm sure you do ;) ) then. in Visual Studio, put the cursor in the command name and press F1.

EDIT: The reason your connection string doesn't work may well be that there are spaces in the filename. If you look at the value which the SqlConnectionStringBuilder gives, it automatically encloses the filename in double-quotes. However, I really recommend using code in the style of the above rather than your code.

ConnectionString not correct

1 Answers