Changing the connection string

[dday9]

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

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

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 -

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?

[Tom Sawyer]

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 & """;

[dday9]

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.

[kareninstructor]

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.

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.

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

To a shorten name

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

Code to do shortnames
Shorty.vb

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

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

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

[jmcilhinney]

Just one small thing KI:

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.

[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.

[kareninstructor]

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

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.