Changing the connection string

I'm trying to change my connection string to w/e the user wants. After a few tips here and there, this is what I've come up:

1: In my settings I've added - Name(login), Type(String), Scope(User), Value(Nothing)

2: Here is the code I'm using

Code:

---

Private Sub LoginForm1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Here I'm checking if the login in my.settings is there
        'If not, I'm going to let the user choose what database
        'He/she wants
        strLoginPath = My.Settings.login
        Dim provider As String = "Microsoft.Jet.OLEDB.4.0"
        Dim security As String = "False"
        If My.Computer.FileSystem.FileExists(strLoginPath) = False Then
            MessageBox.Show("Stored Database path is invalid. Click OK to choose a valid Database", "System Message", MessageBoxButtons.OK)
            OpenFileDialog1.Filter = "Access Documents|*.mdb"
            OpenFileDialog1.FileName = ""
            OpenFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                strLoginPath = OpenFileDialog1.FileName
                My.Settings.login = "Provider=" & provider & _
            ";Data Source =" & strLoginPath & ";Persist Security Info=" & security
                My.Settings.Save()
            Else
                MessageBox.Show("No Database Path selected, exiting", "Fatal Error", MessageBoxButtons.OK)
                End
            End If
        End If
    End Sub

---

Code:

---

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        'Here is my code for the login
        Dim con As New OleDbConnection(My.Settings.login)
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT [First Name],password FROM login where [First Name]=? and password=?", con)
        cmd.Parameters.AddWithValue("username", UsernameTextBox.Text)
        cmd.Parameters.AddWithValue("password", PasswordTextBox.Text)
        Try
            con.Open()
            Dim read As OleDbDataReader = cmd.ExecuteReader()
            If read.HasRows Then
                read.Read()
                If UsernameTextBox.Text = read.Item("First Name").ToString And PasswordTextBox.Text = read.Item("password").ToString Then
                    MsgBox("Login successful")
                    SplashScreen1.Show()
                    Me.Close()
                Else
                    MsgBox("Login unsuccessful, username and passwords are case sensitive")
                End If
            Else
                MsgBox("Login unsuccessful")
            End If
            read.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

---

The error I get is: Format of the initialization string does not conform to specification starting at index 0.
here -

Code:

---

Dim con As New OleDbConnection(My.Settings.login)

---

When I have my con equal to when I added the datasource from it works. Any help?

Do you have any spaces in your login path? I've run into errors opening a connection to Excel when that was the case - ie the file is in C:\App Docs\Database as opposed to C:\AppDocs\Database.

If so, throw some quotes around the datasource and the driver won't think you've ended the connection string in the middle:

Data Source =" & strLoginPath & ";

becomes:

Data Source =""" & strLoginPath & """;

Well see, here is what I don't get. Even if I change my value in my.settings.login to c:/login it doesn't open my openfiledialog.

The following utilizes OleDbConnectionStringBuilder to assist in creating your connection string. I would suggest making sure the file name (which most likely includes the path) be trimmed as shown. If at any time you might move to Acces 2007+ then note how I did the provider.

Code:

---

Dim cb As New OleDb.OleDbConnectionStringBuilder With
    {
        .DataSource = strLoginPath.Trim,
        .PersistSecurityInfo = False,
        .Provider = IIf(strLoginPath.ToLower.EndsWith("mdb"),
                        "Microsoft.Jet.OLEDB.4.0",
                        "Microsoft.ACE.OLEDB.12.0").ToString
    }


Console.WriteLine(cb.ToString)

Using cn As New OleDb.OleDbConnection With
    {
        .ConnectionString = cb.ConnectionString
    }
    cn.Open()
    Console.WriteLine("Open")
End Using

---

99% of the time my Access files have neither spaces in the folder or file name but tried the above with both and it still worked fine. That is not to say it will be that way every time.

You could convert the long name i.e.

Code:

---

C:\Documents and Settings\gallaghe\Local Settings\Database.accdb

---

To a shorten name

Code:

---

C:\DOCUME~1\gallaghe\LOCALS~1\DATABA~1.ACC

---

Code to do shortnames
Shorty.vb

Code:

---

Module Shorty
    ''' <summary>
    ''' Provides the short path for long path of a file
    ''' </summary>
    ''' <param name="Path">Long path to shorten</param>
    ''' <returns>Short path</returns>
    ''' <remarks></remarks>
    Public Function ShortPathName(ByVal Path As String) As String
        Dim sb As New System.Text.StringBuilder(1024)

        Dim Result As Integer = Interop.GetShortPathName(Path, sb, 1024)
        If Result <> 0 Then
            Return sb.ToString()
        Else
            Throw New Exception("Failed to return a short path")
        End If
    End Function
End Module

---

unManaged.vb

Code:

---

Imports System
Module unManaged
    <Runtime.InteropServices.DllImport("Kernel32.dll")>
    Function GetModuleFileName(
        ByVal hModule As IntPtr,
        ByVal lpFilename As Text.StringBuilder,
        ByVal nSize As Integer) As Integer
    End Function
    Public Class Interop
        <Runtime.InteropServices.DllImport("kernel32.dll",
            SetLastError:=True, CharSet:=Runtime.InteropServices.CharSet.Auto)> _
        Public Shared Function GetShortPathName(ByVal longPath As String, _
      <Runtime.InteropServices.MarshalAs(Runtime.InteropServices.UnmanagedType.LPTStr)> _
      ByVal ShortPath As System.Text.StringBuilder, _
      <Runtime.InteropServices.MarshalAs(Runtime.InteropServices.UnmanagedType.U4)> _
      ByVal bufferSize As Integer) As Integer
        End Function
    End Class
End Module

---

Using it

Code:

---

Dim ShortPath As String = ShortPathName("Some path and file name that exists")

---

Just one small thing KI:

Code:

---

Dim cb As New OleDb.OleDbConnectionStringBuilder With
    {
        .DataSource = strLoginPath.Trim,
        .PersistSecurityInfo = False,
        .Provider = IIf(strLoginPath.ToLower.EndsWith("mdb"),
                        "Microsoft.Jet.OLEDB.4.0",
                        "Microsoft.ACE.OLEDB.12.0").ToString
    }

---

That code will only work in VB 2010 so you should definitely not be using IIf for anything anymore. You should be using the If operator.

Thanks y'all, I'm not to familar with connectionstring builder. I'm going to tinker around with it today and post what I've come up with. Also, I had absolutely no idea about the IIf. Imma do some research on that, cause I am using 2010.

Quote:

---

Originally Posted by dday9

Thanks y'all, I'm not to familar with connectionstring builder. I'm going to tinker around with it today and post what I've come up with. Also, I had absolutely no idea about the IIf. Imma do some research on that, cause I am using 2010.

---

Here is the better way as per jmc in regards to IIF vs IF

Code:

---

Dim cb As New OleDb.OleDbConnectionStringBuilder With
    {
        .DataSource = strLoginPath.Trim,
        .PersistSecurityInfo = False,
        .Provider = If(strLoginPath.ToLower.EndsWith("mdb"),
                        "Microsoft.Jet.OLEDB.4.0",
                        "Microsoft.ACE.OLEDB.12.0").ToString
    }


Console.WriteLine(cb.ToString)

Using cn As New OleDb.OleDbConnection With
    {
        .ConnectionString = cb.ConnectionString
    }
    cn.Open()
    Console.WriteLine("Open")
End Using

---

This is the proper way for the above code If Operator instead of IIf

I suggested the OleDbConnectionStringBuilder class because for you it gets away from string concatenation and ensures your connection string is properly setup. As connections get more complex many times a new developer will mess up the syntax of the connection string and will fail but not realize they missed say a semi-colon etc.