Results 1 to 10 of 10

Thread: [RESOLVED] Creating a connection string at run time

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2014
    Location
    Sydney
    Posts
    73

    Resolved [RESOLVED] Creating a connection string at run time

    I have an Access database which I want to protect with a password. I then need to be able to create a connection string at run time containing the password to open the database. I have included a connection string including the password but I get the message "Not a valid password". My code is as follows:-
    Code:
    Imports System.Data.OleDb
    
    Public Class frmLogin
      Dim lCancel As Boolean
      Dim lUserName As String
    
      Public Function Login()
        Dim loginadapter As New DataSet1TableAdapters.LoginTableAdapter
        Dim llogindetails As DataSet1.LoginDataTable
        Dim lLoginError As String
        Dim con As New OleDbConnection
    
        Try
    
          ' Open database with password
          con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\@@MYDocuments\Visual Studio 2010\Projects\Encrypted Database 2\Encrypted Database 2\bin\Debug\Members _ 
                                               Database Encrypted.accdb;Jet OLEDB:Database Password=password;"
          con.Open()
    
         'Refresh dataset
          llogindetails = loginadapter.GetDataLoginDetails
    
          For Each loginRow As DataSet1.LoginRow In llogindetails
            If loginRow.UserName = txtUserName.Text Then
              If loginRow.Password = txtPassword.Text Then
                gSecurityLevel = loginRow.SecurityLevel
                gLoginID = loginRow.IDLogin
                gUserName = txtUserName.Text
    
                Return True
              Else
                lLoginError = "Invalid Password"
                MsgBox(lLoginError, , "Password Incorrect")
    
                Return False
              End If
            End If
          Next
          lLoginError = "Invalid User Name"
          MsgBox(lLoginError, , "Incorrect User Name")
    
          Return False
    
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Function
    
    Other Private Subs to do various things with the input
    
    End Class
    Stepping through the code it seems to work until the dataset is refreshed and then the exception is thrown and the error message comes up. Am I barking up the wrong tree or is there something I am not doing right? Any guidance would be appreciated.
    Last edited by Shaggy Hiker; Dec 28th, 2017 at 10:06 AM. Reason: Added CODE tags.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating a connection string at run time

    Please use appropriate formatting tags when posting code snippets.
    vb.net Code:
    1. Imports System.Data.OleDb
    2.  
    3. Public Class frmLogin
    4.   Dim lCancel As Boolean
    5.   Dim lUserName As String
    6.  
    7.   Public Function Login()
    8.     Dim loginadapter As New DataSet1TableAdapters.LoginTableAdapter
    9.     Dim llogindetails As DataSet1.LoginDataTable
    10.     Dim lLoginError As String
    11.     Dim con As New OleDbConnection
    12.  
    13.     Try
    14.  
    15.       ' Open database with password
    16.       con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\@@MYDocuments\Visual Studio 2010\Projects\Encrypted Database 2\Encrypted Database 2\bin\Debug\Members _
    17.                                            Database Encrypted.accdb;Jet OLEDB:Database Password=password;"
    18.       con.Open()
    19.  
    20.      'Refresh dataset
    21.       llogindetails = loginadapter.GetDataLoginDetails
    22.  
    23.       For Each loginRow As DataSet1.LoginRow In llogindetails
    24.         If loginRow.UserName = txtUserName.Text Then
    25.           If loginRow.Password = txtPassword.Text Then
    26.             gSecurityLevel = loginRow.SecurityLevel
    27.             gLoginID = loginRow.IDLogin
    28.             gUserName = txtUserName.Text
    29.  
    30.             Return True
    31.           Else
    32.             lLoginError = "Invalid Password"
    33.             MsgBox(lLoginError, , "Password Incorrect")
    34.  
    35.             Return False
    36.           End If
    37.         End If
    38.       Next
    39.       lLoginError = "Invalid User Name"
    40.       MsgBox(lLoginError, , "Incorrect User Name")
    41.  
    42.       Return False
    43.  
    44.     Catch ex As Exception
    45.       MsgBox(ex.Message)
    46.     End Try
    47.   End Function
    48.  
    49. Other Private Subs to do various things with the input
    50.  
    51. End Class

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Creating a connection string at run time

    You are creating an OleDbConnection and opening it. You seem to be under the impression that that is somehow associated with your typed DataSet. It's not. Each table adapter contains its own connection object. It's that connection object for which you need to change the connection string.

    Open your DataSet in the designer, select a table adapter and open the Properties window. I can't recall the name but there's a property that let's you specify the access level for the connection. You need to make sure that that is set to Public. You can then access the connection object for a table adapter like so:
    vb.net Code:
    1. loginadapter.Connection.ConnectionString = "connection string here"

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Creating a connection string at run time

    Hi Flyboy,

    you can place this Function in a Modul, you will have to adjust the Provider to ACE

    Code:
    Imports System.Data.OleDb
    
    Module modMain
    
        Public Cn As OleDb.OleDbConnection
    
        Public Function ConnectionOpen(ByRef Cn As OleDbConnection, _
                                       ByVal sDataSource As String, _
                                       ByVal sPwd As String) As Boolean
            Try
                If sPwd = Nothing Then
                    sPwd = ""
                End If
                Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                     "Data Source = " & sDataSource & ";"
                If sPwd.Length > 0 Then
                    sCon = sCon & "Jet OLEDB:Database Password=" & sPwd & ";"
                End If
                If Cn Is Nothing Then
                    Cn = New OleDbConnection(sCon)
                End If
    
                If Cn.State <> ConnectionState.Open Then
                    Cn.Open()
                End If
                Return True
            Catch ex As Exception
                Dim Titel As String = "ConnectionOpen"
                MessageBox.Show(ex.Message.ToString, Titel, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Function
    
    End Module
    then you can place this in a Form.

    I choose Form1_Shown, but that's up to you
    Code:
    Private Sub Form1_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
    
           
    
            Dim prompt As String = String.Empty
            Dim title As String = String.Empty
            Dim defaultResponse As String = String.Empty
    
            Dim answer As Object
            ' Set prompt.
            prompt = "please enter Password"
            ' Set title.
            title = "Getting user input"
            ' Set default value.
            defaultResponse = "your Password"
    
            ' Display prompt, title, and default value.
            answer = InputBox(prompt, title, defaultResponse)
    
            'your mdb in the \bin folder
            Dim Mdb As String = Application.StartupPath & "\..\TestAccess.mdb"
    
            Me.Cursor = Cursors.WaitCursor
            'without Password
            'If Not ConnectionOpen(Cn, Mdb, Nothing) Then
            'with Password = mypass
            If Not ConnectionOpen(Cn, Mdb, answer) Then
                'or HardCode the Password like:
                '  If Not ConnectionOpen(Cn, Mdb, "mypass") Then
                Me.Close()
                Exit Sub
            End If
            Me.Cursor = Cursors.Default
            ToolStripStatusLabel1.Text = "Connection to " & Mdb
        End Sub
    this will open a password protected Database, but you still have txtPassword and txtUserName
    so you have to adjust that also.

    I just used an Inputbox to Prompt the user to enter a Password.

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Creating a connection string at run time

    It won't matter, though, unless they also stop using a TableAdapter. As JMC pointed out, the TableAdapter manages its own connection. Just opening a different connection doesn't mean that the TableAdapter will use, or even know anything about, that connection.

    I suspect that the issue is that the OP assumed that a connection was just that: A connection. So, if you have a connection to the database in the program, then you have a connection to the database. That was kind of true at one time a long time back, but it isn't true anymore, nor can it be. The ADO.NET model is much more robust than that. You create as many connections as you need, and there is no particular issue with having several simultaneous connections to a database. It has to be that way, since there could be multiple applications connecting to a database simultaneously, or multiple threads within a single application connecting to a database simultaneously, or even multiple access methods...kind of simultaneously. They've gone with a TableAdapter, so they have to fix up the connection that the TableAdapter uses. Fixing up a different connection is no substitute.
    My usual boring signature: Nothing

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Creating a connection string at run time

    Hi Shaggy,

    the OP want's to open a password protected Database. That's what my sample does

    or am I missing something?

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Creating a connection string at run time

    Yeah, and so did I, but JMC didn't. The OP does indeed want to open said database, but they ALSO are using a TableAdapter. They did okay for a direct connection, which is what you also showed, and what I prefer myself, but since they used a TableAdapter, then they went through the datasource wizard and designer. That will manage connections in its own way, by default, so all you need it to get the connection string right.
    My usual boring signature: Nothing

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Creating a connection string at run time

    ah Ok,

    i used those datasource wizards in VB6 for about 2 months (and gave up),and did not bother looking in .Net

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Creating a connection string at run time

    They're better and MUCH different in .NET. I abandoned them even faster than you did back in VB6. I felt they were pretty much rubbish. In .NET....I'm not completely disparaging of them. They give you a LOT, and if you get to know them, you can do some amazing stuff quite rapidly. However, they are also pretty much a black box. Without knowing much of anything, you can do a bunch of things, but if you want to go beyond those things....it's a whole lot harder, since you have to wade through loads of code you didn't write. So, I understand why people use them. Strongly typed datasets are pretty useful, but I still don't much like them.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Mar 2014
    Location
    Sydney
    Posts
    73

    Re: Creating a connection string at run time

    Quote Originally Posted by jmcilhinney View Post
    You are creating an OleDbConnection and opening it. You seem to be under the impression that that is somehow associated with your typed DataSet. It's not. Each table adapter contains its own connection object. It's that connection object for which you need to change the connection string.

    Open your DataSet in the designer, select a table adapter and open the Properties window. I can't recall the name but there's a property that let's you specify the access level for the connection. You need to make sure that that is set to Public. You can then access the connection object for a table adapter like so:
    vb.net Code:
    1. loginadapter.Connection.ConnectionString = "connection string here"
    JMc you have hit the nail on the head again. I might be able to make all the dials on the instrument panel do all the things that they are supposed to do but I do not have your expertise to make VB do all that it can do. However, I am learning from your contributions and comments to my bumbling attempts to write VB code. I sincerely thank for you for your efforts to help me learn. Your comments and Shaggy Hiker's comments make sense and ChrisE's contribution has helped me understand as well.

    I have inserted your single line of code and ensured the "Modifier " property is set to public and everything works perfectly.

    I apologise for not using appropriate formatting tags when posting code snippets and will promise to remember to do so in future.

    Thank you again guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width