Arggggggghhhhhhhhhhhh. Connection string(s)

[MichaelRed]

How do i start (the question).

I have attempted ever so many approaches and encountered ever so many objections (e.g. Errors).

Perhaps the best help would be a pointer to the kinds of things which belong in the varions terms.

the immediate issue seems to be ye olde:

[Microsoft][ODBC Driver Manager] _
DataSource not found and no default driver specified

this is in concert with:

Public Function basDToolsSample()

    '<add name="DTools.SystemIntegrator.SDK.Properties.Settings.DToolsMasterTableConnectionString1" _
     connectionString="Data Source= LOCAL\DTOOLSV5;Database=DToolsProducts50; _
     User Id=DToolsData;Password=d7uL8Da7a;Persist Security Info=True;" _
          providerName="System.Data.SqlClient" />

    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As ADODB.Recordset
    Dim strCnn As String
    Dim iCtr As Integer

    strCnn = "Data Source=C:My Documents\DToolSV5;" _
           & "DataBase=DToolsProducts50;" _
           & "User ID=\[i]UserId[/i];" _
           & "Password=[i]pwd[/i];" _
           & "Persist Security Info=True;" _
           & "ProviderName=System.Data.SQLClient;" _
           & "ServerName:= (Local)\SQLEXPRESS;"

''' _
           & "Driver={DTools};"
           ' _
           & "Initial Catalog=C:\My Documents\D-Tools_SDK\SI5;" _
           & "Data Source=Local\DToolsV5;" _
           & "Use Procedure for Prepare=1;" _
           & "Auto Translate=True;" _
           & "Packet Size=4096"

    cnn.Open strCnn
    '[Microsoft][ODBC Driver Manager] _
     DataSource not found and no default driver specified

    cmd.CommandText = "SELECT * FROM Products"
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute

    Do While Not rs.EOF
       For iCtr = 0 To rs.Fields.Count - 1
          'OutPuts Name and Value of each field
          Debug.Print rs.Fields(iCtr).Name & ": " & _
             rs.Fields(iCtr).Value
        Next
    rs.MoveNext
    Loop

    Set rs = Nothing
    Set cmd = Nothing
    cnn.Close
    Set cnn = Nothing

End Function

What I really think i need is a brain transplant, but would gladly settle for just what the (*&^(*&%^*&^ belong on the right of the "=" signs - not the specifics, because they are obviously specific to the app, but where does it say that the datasource is one of the .mdf files or an instance of the version of sql server or the name of the ODBC thing ...

Next, given that Only a few of these are actually known (at least to me), where/ how does one ferrett them out of the system?

lost in the land of new beginnings!




MichaelRed

 

[SQLDenis]

http://www.connectionstrings.com/

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

I was going to post that exact link, Denis!

Another trick is to help you with connection strings is to create an empty .udl file (do File->New->Text Document and change the extension) on your computer and double-click it. Click around the options and you'll find that 80% of the connection strings you need are easily built here. When you're done, open the file in notepad.

Or use the following VB6 form to programmatically do it. You can use this in your programs. Add a reference to "Microsoft OLE DB Service Component 1.0 Type Library." I have this program compiled and use it on a regular basis.

VERSION 5.00
Begin VB.Form FMain 
   Caption         =   "Erik's Connection String Builder"
   ClientHeight    =   2100
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   8280
   BeginProperty Font 
      Name            =   "Tahoma"
      Size            =   8.25
      Charset         =   0
      Weight          =   400
      Underline       =   0   'False
      Italic          =   0   'False
      Strikethrough   =   0   'False
   EndProperty
   LinkTopic       =   "Form1"
   ScaleHeight     =   2100
   ScaleWidth      =   8280
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton btnClose 
      Cancel          =   -1  'True
      Caption         =   "&Close"
      Height          =   375
      Left            =   7080
      TabIndex        =   2
      Top             =   1680
      Width           =   1095
   End
   Begin VB.CommandButton btnBuild 
      Caption         =   "&Build..."
      Default         =   -1  'True
      Height          =   375
      Left            =   5760
      TabIndex        =   1
      Top             =   1680
      Width           =   1095
   End
   Begin VB.TextBox txtConnectionString 
      Height          =   1215
      Left            =   120
      MultiLine       =   -1  'True
      TabIndex        =   0
      Top             =   360
      Width           =   8055
   End
   Begin VB.Label Label1 
      Caption         =   "Connection String:"
      Height          =   255
      Left            =   120
      TabIndex        =   3
      Top             =   120
      Width           =   5895
   End
End
Attribute VB_Name = "FMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub btnBuild_Click()
    
    btnBuild.Enabled = False
    On Error GoTo btnBuild_Click_Error
    Dim oDataLink As MSDASC.DataLinks
    Dim oConn As ADODB.Connection
    
    Set oConn = New ADODB.Connection
    Set oDataLink = New MSDASC.DataLinks
    
    oConn.ConnectionString = txtConnectionString.Text
    oDataLink.PromptEdit oConn
    
    txtConnectionString.Text = oConn.ConnectionString

btnBuild_Click_Exit:
    btnBuild.Enabled = True
    Exit Sub
    
btnBuild_Click_Error:
    Select Case Err.Number
        Case -2147217805
            MsgBox "The existing connection string is invalid. Please clear or correct it and try again.", vbExclamation Or vbOKOnly, "Cannot Build Connection String"
        Case Else
            On Error GoTo 0
            Resume
    End Select
    Resume btnBuild_Click_Exit
End Sub

Private Sub btnClose_Click()
    Unload Me
End Sub

Private Sub txtConnectionString_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = 65 And Shift = vbCtrlMask Then
        txtConnectionString.SelStart = 0
        txtConnectionString.SelLength = Len(txtConnectionString.Text)
    End If
End Sub

 

[ca8msm]

There's a couple of other options here as well:

faq855-5780


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ESquared]

ca8msm,

FYI, the only new information in that FAQ is a .Net version of the DataLinks code and a late-bound example of the same VB code as above.