469,948 Members | 2,952 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,948 developers. It's quick & easy.

ASP.Net VB Access DataTable fill changes connection string

My project was initially created without putting passwords in the connection string. When I added the password in the connection string, it disappears when performing a database fill. I have stepped through the code in debug and the connection string is correct up til the Fill command. Once the fill executes, the password disappears from the connection string. This particular Fill is performed to check that the user is valid for this database and are able to proceed further in the app.

I see no reason that the password should drop out of the connection string. Does anyone have any suggestions or reasons why this is happening? Any way of making it NOT happen? I know Access is crap. I have assurances that the company will convert to SQL Server and it can't happen soon enough to suit me.

Before executing the fill, the connection string inside dbConnection is
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\database.mdb;User ID=user;Password=password;Jet OLEDB:System database=C:\DB\Security.mdw"
After, it is
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\database.mdb;User ID=user;Jet OLEDB:System database=C:\DB\Security.mdw"

If I put dbConnection in a Session variable before executing the Fill, the value in the Session is changed after the Fill as well.

Relevant Code:
Expand|Select|Wrap|Line Numbers
  1. Public Class Constants
  2.     Public Class ConfigurationKeys
  3.         Public Const CONNECTION_STRING As String = "ConnectionString"
  4. ...
  5. ' Data Source and System Database are derived from which market a user chooses
  6. ' User ID and Password are entered by the user
  7. <connectionStrings>
  8.     <add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID={1};Password={3};Jet OLEDB:System database={2}" providerName="System.Data.OleDb"/>
  9. </connectionStrings>
  10. ...
  11.     Public Class SelectCommands
  12.         Public Const TEST_DB_ACCESS As String = "SELECT TOP 1 Table_ID FROM tblTable"
  13. ...
  14.     Dim m_dsMKTs As New DataSet()
  15. ...
  17.         Dim currentMKT As New MKTInfo
  18.         Dim iIndex As Integer = ddlMKT.SelectedIndex
  19.         Dim strUserId As String = tbUserID.Text
  20.         Dim strPassword As String = tbPassword.Text
  22.         ' If a MKT has been selected, populate the MKTInfo class
  23.         If iIndex > 0 Then
  24.             Dim dtJDE_ID As New DataTable
  26.             currentMKT.Name = m_dsMKTs.Tables(0).Rows(iIndex - 1)("Name")
  27.             currentMKT.DatabasePath = m_dsMKTs.Tables(0).Rows(iIndex - 1)("DatabasePath")
  28.             currentMKT.SecurityFile = m_dsMKTs.Tables(0).Rows(iIndex - 1)("SecurityFile")
  30.             ' Build the access database connection string based on the MKT specified and current user
  31.             Dim strConnectionFormat As String = ConfigurationManager.ConnectionStrings(Constants.ConfigurationKeys.CONNECTION_STRING).ConnectionString
  32.             Dim strConnectionString As String = String.Format(strConnectionFormat, currentMKT.DatabasePath, strUserId, currentMKT.SecurityFile, strPassword)
  33.             Dim dbConnection As OleDbConnection = New OleDbConnection(strConnectionString)
  34.             Dim strSelectCommand As String = Constants.SelectCommands.TEST_DB_ACCESS
  35.             Dim daJDE_ID As New OleDbDataAdapter(strSelectCommand, dbConnection)
  37.             Try
  38.                 daJDE_ID.Fill(dtJDE_ID) ' this is where I have problems.
  40.                 'Workaround - have to re-establish the connection string because the previous line removes the password for some reason
  41.                 dbConnection.Close()
  42.                 dbConnection = New OleDbConnection(strConnectionString)
Aug 31 '07 #1
1 2437
add "Persist Security Info=True" to your connection string.
Sep 9 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Job Lot | last post: by
4 posts views Thread by George | last post: by
4 posts views Thread by sarabonn | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.