How to write a Proper SQL Connection String?

Question

Using VB 6 and Sql Server 2000.

In my code I am using open dialog control for selecting the database file, once I selected the database(Dual_ACS.mdf) file, it will appear in the textbox (path and filename)

Textbox name = databasetext

My code.

Cn.ConnectionString = "Provider=SQLOLEDB.1; Persist Security Info=False;User ID=" & UName & ";Password=" & PWord & ";InitialCatalog=DUAL_ ACS; Data Source=" & databasetext & ""
Cn.Open

But it is showing error. How to write a Proper SQL Connection String?

Need VB 6 code help

Answer 1

If you're sure it's the connection string, check connectionstrings.com - it has the proper formats for hundreds of connection strings. It's a great reference.

Are you sure it's the connection string though? Why don't you tell us what the error message is? And what version of SQL Server are you using?

Answer 2

There is an entire website dedicated to this subject: http://www.connectionstrings.com/

Answer 3

The Data Source in a SQLOLEDB connection needs to point to the SQL Server Server and Instance Name ( or IP but then you need to specify the port). See here for specifics. You can't point directly to the mdf file. This isn't access.

If you want the users to be able to select a data source you should use the Universal Data Link dialog built-in to ADO. Here is an example pulled from a larger class I use. You need to add a reference to the "Microsoft OLE DB Service Component 1.0 Type Library" or C:\Program Files\Common Files\system\ole db\oledb32.dll. If you just need to see what the connection string will look like yourself just create a file called test.udl and then double click it. The interface used there is the same one invoked in this code.

' -----------------------------------------------------------------------------
' Edit
'
' Description:
'    Edits the udl
'
' Arguments:
'
' Dependencies:
'    MSDASC.DataLinks.PromptEdit
'    MSDASC.DataLinks.PromptNew
'
' History:
' 05/23/2003 - WSR : Created
'
Public Function Edit() As Long

Dim dlgEdit       As MSDASC.DataLinks
Dim strConnection As String

   Set dlgEdit = New MSDASC.DataLinks

   ' if there is a connection string
   If Len(m_conSource.ConnectionString) > 0 Then

      ' prompt user to edit the connection
      If Not dlgEdit.PromptEdit(m_conSource) Then

         ' if they didn't edit the connection string
         ' return error code
         Edit = -1

      End If

   ' if there is no connection string
   Else

      ' prompt user to create new connection
      On Error Resume Next
      strConnection = dlgEdit.PromptNew()

      ' if there was a connection string created
      If Len(strConnection) > 0 Then

         ' use it
         m_conSource.ConnectionString = strConnection

      ' if there was no connection string created
      Else

         ' return error code
         Edit = -1

      End If

   End If

   Set dlgEdit = Nothing

End Function
' -----------------------------------------------------------------------------