-
Dec 28th, 2017, 06:35 AM
#1
Thread Starter
Lively Member
[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.
-
Dec 28th, 2017, 07:30 AM
#2
Re: Creating a connection string at run time
Please use appropriate formatting tags when posting code snippets.
vb.net 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
-
Dec 28th, 2017, 07:41 AM
#3
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:
loginadapter.Connection.ConnectionString = "connection string here"
-
Dec 28th, 2017, 10:21 AM
#4
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.
-
Dec 28th, 2017, 11:41 AM
#5
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
-
Dec 28th, 2017, 12:00 PM
#6
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.
-
Dec 28th, 2017, 12:25 PM
#7
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
-
Dec 28th, 2017, 01:42 PM
#8
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.
-
Dec 28th, 2017, 03:05 PM
#9
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
-
Dec 28th, 2017, 08:05 PM
#10
Thread Starter
Lively Member
Re: Creating a connection string at run time
Originally Posted by jmcilhinney
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:
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|