N
NotchersMine
Guest
I have been working on a VB.NET/Visual Studio 2019/WinForms project (targeting .Net Framework 4), where I have set up a My.Settings string value to persist a SQLite filepath.
Within the program I have a Shared property that Gets/Sets the value (effectively, a wrapper for the property generated by My.Settings that executes My.Settings.Save() at the end of the Set routine - I don't know if this is germane or not, but I wanted to mention it just in case).
I overrode the startup form's OnLoad() method to call a routine that opens the database within a Try/Catch construct. I coded for an anticipated SQLiteException error (that the file did not exist, since I had set connStr.FailIfMissing to True), but did not include a generic exception catcher (which, in hindsight, I should have - but then we might not have this riddle to solve):
Friend Class MyGlobals
Public Shared Property DBFilePath As String
Get
Return My.Settings.DBFilePath
End Get
Set(value As String)
My.Settings.DBFilePath = value
My.Settings.Save()
If _DBConn IsNot Nothing Then SetDBConnection()
End Set
End Property
Public Shared Sub SetDBConnection()
Dim holdDBFilePath As String = Nothing
Try
If _DBConn IsNot Nothing Then 'Connection is currently in use
holdDBFilePath = _DBConn.FileName 'Store current connection's filepath
_DBConn.Close() 'And close it
End If
'Build the new connection string...
Dim connStr As New SQLiteConnectionStringBuilder With {
.DataSource = DBFilePath,
.FailIfMissing = True,
.ForeignKeys = True,
.JournalMode = SQLiteJournalModeEnum.Persist,
.Version = 3
}
_DBConn = New SQLiteConnection(connStr.ToString)
_DBConn.Open() 'Attempt to open the database
Catch ex As SQLiteException 'Intercept SQLite exceptions
'Prompt for user action...
Select Case MsgBox(ex.Message.ToSentenceCase, MsgBoxStyle.AbortRetryIgnore, "Database Connection Error")
Case MsgBoxResult.Abort : Application.Exit() : End 'Abort the program
Case MsgBoxResult.Ignore : DBFilePath = holdDBFilePath 'Ignore and reload the original path
End Select 'Maintain status quo if Retry selected
_DBConn = Nothing 'Nullify the (invalid) connection
SetDBConnection() 'And recursively retry...
End Try
End Sub
Public Shared _DBConn As SQLiteConnection = Nothing
End Class
I ran a test with a valid filepath, and everything worked as expected. Then I changed the filepath (appended an "x" to the filetype) - via the My.Settings IDE - and was greeted by the MsgBox ("Unable to open database file"). Again, as expected...so I played around with the options (Abort, Retry and Ignore) and at some point I got an unhandled exception:
System.TypeInitializationException: 'The type initializer for 'WindowsApp1.MyGlobals' threw an exception.'
Inner Exception: ArgumentException: 'Data Source cannot be empty...'
This was unexpected, so I began to dig. I checked the My.Settings IDE and it still showed the (invalid) filepath string. I changed it to the valid filepath, but got the same (empty) result on execution. I set a breakpoint and debugged the program, and discovered that the string that was being returned by My.Settings.DBFilePath was empty. (Aside: I had encountered a similar problem a month or two back, where My.Settings was returning an empty-string property value unexpectedly, after an unexpected exception - my only redress at that point was to copy all my code to a new project solution and recompile, which corrected the situation but did not solve the conundrum of the overriding primary store issue.)
In any case, I wondered if I could reset the empty value through code. So I added code in the OnLoad overload to set the DBFilePath value to the valid path string before calling SetDBConnection(). To my surprise, it worked without any exception! So I went back and checked the My.Settings.DBFilePath string via the IDE: it still referenced the "...x" (invalid) filepath. Next test, I removed the OnLoad DBFilePath-setting code, and got the same result: it worked without exception. Therefore I hypothesized that there must be a hidden data store that persists values and overrides getters/setters for any My.Settings data that somehow gets injected into it.
I started Googling for help. I found a lot of similar situations that did or didn't have accepted solutions, and tried many of them, including:
- Clicking the Synchronize button in the My.Settings IDE. This resulted in a message box telling me that "No user.config files were found in any of the following locations:" with no locations given.
- Cleaning and Rebuilding the solution.
- Checking solution .config files for the phantom filepath (I found all kinds of references to the "...x" file, but not the path to the valid file).
- Searching for the valid filename string in all files in the solution folder/subfolders (including binary/.exe files), to see if one file might actually hold the value and override the other storage spots; all I found were references to the invalid filename.
- Checking to see if there might be some information in C:\Program Files\WindowsApps (a hidden file), but I was unable to gain access to its contents. However, the tooltip for the File Explorer item told me that it is an "Empty folder" (for whatever that's worth).
- Searching for all .config files on my C: drive (via File Explorer search), which resulted in too many options to manually validate them all.
I tried creating a different My.Settings property (via the IDE) to return the filepath, and set it to the invalid ("...x") string, and then referenced that property name (instead of the original) in code; I also deleted the original My.Settings.DBFilePath entry at that time. When testing, this new reference returned the normally-expected "Unable to open database file" popup. So I renamed the new My.Settings property back to the original (phantom) property name and reran the program, and the original result recurred (the program somehow referenced the valid filepath string even though the My.Settings IDE held the "...x" filepath). I had thought that by deleting the original reference I might make the phantom value disappear, but it remained and hooked itself back in as soon as I reused the original property name.
A note of interest: I built a Release version, and it worked the same way (but apparently with a different hidden data store) - that is, the bin/Debug version can be set to one value and the bin/Release version to another (via code), and they each reference the value assigned to the corresponding version.
==========
UPDATE: I found an MSDN Forum that suggested calling My.Settings.Reset() before referencing any of the settings values. It worked - in that the program was finally referencing the (invalid) filepath as defined in My.Settings. I removed the Reset call, and the program worked again (with the invalid filepath). So, apparently, Reset() is the way to correct my problem...EXCEPT...it does not solve the conundrum of the hidden data store! Once again, I played with the MsgBox buttons and recreated the original situation.
These are the steps that recreate the situation:
- I add a My.Settings.Reset() call in the startup form's OnLoad override
- I set a debugger breakpoint at the first line of MyGlobals.SetDBConnection(), run the debugger, and step into (F11) until the DataSource assignment in the SQLiteConnectionStringBuilder (connStr) constructor, which causes the MsgBox to appear (Unable to open database file)
- I choose the Ignore option, which (at least visually) causes the debugger stepper to immediately go to the top of the method
- This time, at the first line of the Try/Catch block, _DBConn is not Nothing, and therefore the holdDBFilePath = _DBConn.FileName assignment is attempted, which causes an unhandled exception:
System.TypeInitializationException: 'The type initializer for 'WindowsApp1.MyGlobals' threw an exception.'
Inner Exception: InvalidOperationException: 'Database connection not valid for getting file name.'
At this point, the original situation (the unexplained persistence) has begun. But the order of execution is confusing to me: after choosing Ignore, the expected sequence of events is to set the DBFilePath property to holdDBFilePath (which is Nothing), then drop through to set _DBConn to Nothing and recursively call the same method. However, the debugger stepper skips to the top of the method after clicking the Ignore button - perhaps the steps execute but the MsgBox call causes a debugger step-out? Then on the second iteration the _DBConn variable is a SQLiteConnection instance even though the debugger never indicated passing through the variable's initialization and .Open() call, and should have been reset to Nothing in the Catch block drop-through anyway! In any case, since the _DBConn variable is not Nothing on the second iteration (but is still not valid), the unhandled exception occurs, and after that the original (mysterious) persistence recurs.
Another note of interest: the mystery data store resets to an empty value each time the unhandled InvalidOperationException (Database connection not valid for getting file name) is thrown. I tested this by setting the mystery store to the valid filepath in code, which caused the now-expected (normal) execution, after which I removed the hard-set code and reran the steps to recreate the problem (as above). The next execution of the program threw the ArgumentException (Data Source cannot be empty...) error.
==========
Bottom Line: There appears to be an unknown/undocumented persisted My.Settings primary key-value store (which might only be targeted once an abortive exception has occurred) that will override any My.Settings-coded reference to that key's value (both Get and Set methods). I can call My.Settings.Reset() to correct the situation, but an unhandled exception can cause it to recur.
Does anyone have any insights that may help?
My system info is as follows:
Windows 10 Home
Version 1909
Build 18363.657
Microsoft Visual Studio Community 2019
Version 16.4.4
VisualStudio.16.Release/16.4.4+29728.190
Microsoft .NET Framework
Version 4.8.03752
Thank you in advance for any assistance you can give!
Continue reading...
Within the program I have a Shared property that Gets/Sets the value (effectively, a wrapper for the property generated by My.Settings that executes My.Settings.Save() at the end of the Set routine - I don't know if this is germane or not, but I wanted to mention it just in case).
I overrode the startup form's OnLoad() method to call a routine that opens the database within a Try/Catch construct. I coded for an anticipated SQLiteException error (that the file did not exist, since I had set connStr.FailIfMissing to True), but did not include a generic exception catcher (which, in hindsight, I should have - but then we might not have this riddle to solve):
Friend Class MyGlobals
Public Shared Property DBFilePath As String
Get
Return My.Settings.DBFilePath
End Get
Set(value As String)
My.Settings.DBFilePath = value
My.Settings.Save()
If _DBConn IsNot Nothing Then SetDBConnection()
End Set
End Property
Public Shared Sub SetDBConnection()
Dim holdDBFilePath As String = Nothing
Try
If _DBConn IsNot Nothing Then 'Connection is currently in use
holdDBFilePath = _DBConn.FileName 'Store current connection's filepath
_DBConn.Close() 'And close it
End If
'Build the new connection string...
Dim connStr As New SQLiteConnectionStringBuilder With {
.DataSource = DBFilePath,
.FailIfMissing = True,
.ForeignKeys = True,
.JournalMode = SQLiteJournalModeEnum.Persist,
.Version = 3
}
_DBConn = New SQLiteConnection(connStr.ToString)
_DBConn.Open() 'Attempt to open the database
Catch ex As SQLiteException 'Intercept SQLite exceptions
'Prompt for user action...
Select Case MsgBox(ex.Message.ToSentenceCase, MsgBoxStyle.AbortRetryIgnore, "Database Connection Error")
Case MsgBoxResult.Abort : Application.Exit() : End 'Abort the program
Case MsgBoxResult.Ignore : DBFilePath = holdDBFilePath 'Ignore and reload the original path
End Select 'Maintain status quo if Retry selected
_DBConn = Nothing 'Nullify the (invalid) connection
SetDBConnection() 'And recursively retry...
End Try
End Sub
Public Shared _DBConn As SQLiteConnection = Nothing
End Class
I ran a test with a valid filepath, and everything worked as expected. Then I changed the filepath (appended an "x" to the filetype) - via the My.Settings IDE - and was greeted by the MsgBox ("Unable to open database file"). Again, as expected...so I played around with the options (Abort, Retry and Ignore) and at some point I got an unhandled exception:
System.TypeInitializationException: 'The type initializer for 'WindowsApp1.MyGlobals' threw an exception.'
Inner Exception: ArgumentException: 'Data Source cannot be empty...'
This was unexpected, so I began to dig. I checked the My.Settings IDE and it still showed the (invalid) filepath string. I changed it to the valid filepath, but got the same (empty) result on execution. I set a breakpoint and debugged the program, and discovered that the string that was being returned by My.Settings.DBFilePath was empty. (Aside: I had encountered a similar problem a month or two back, where My.Settings was returning an empty-string property value unexpectedly, after an unexpected exception - my only redress at that point was to copy all my code to a new project solution and recompile, which corrected the situation but did not solve the conundrum of the overriding primary store issue.)
In any case, I wondered if I could reset the empty value through code. So I added code in the OnLoad overload to set the DBFilePath value to the valid path string before calling SetDBConnection(). To my surprise, it worked without any exception! So I went back and checked the My.Settings.DBFilePath string via the IDE: it still referenced the "...x" (invalid) filepath. Next test, I removed the OnLoad DBFilePath-setting code, and got the same result: it worked without exception. Therefore I hypothesized that there must be a hidden data store that persists values and overrides getters/setters for any My.Settings data that somehow gets injected into it.
I started Googling for help. I found a lot of similar situations that did or didn't have accepted solutions, and tried many of them, including:
- Clicking the Synchronize button in the My.Settings IDE. This resulted in a message box telling me that "No user.config files were found in any of the following locations:" with no locations given.
- Cleaning and Rebuilding the solution.
- Checking solution .config files for the phantom filepath (I found all kinds of references to the "...x" file, but not the path to the valid file).
- Searching for the valid filename string in all files in the solution folder/subfolders (including binary/.exe files), to see if one file might actually hold the value and override the other storage spots; all I found were references to the invalid filename.
- Checking to see if there might be some information in C:\Program Files\WindowsApps (a hidden file), but I was unable to gain access to its contents. However, the tooltip for the File Explorer item told me that it is an "Empty folder" (for whatever that's worth).
- Searching for all .config files on my C: drive (via File Explorer search), which resulted in too many options to manually validate them all.
I tried creating a different My.Settings property (via the IDE) to return the filepath, and set it to the invalid ("...x") string, and then referenced that property name (instead of the original) in code; I also deleted the original My.Settings.DBFilePath entry at that time. When testing, this new reference returned the normally-expected "Unable to open database file" popup. So I renamed the new My.Settings property back to the original (phantom) property name and reran the program, and the original result recurred (the program somehow referenced the valid filepath string even though the My.Settings IDE held the "...x" filepath). I had thought that by deleting the original reference I might make the phantom value disappear, but it remained and hooked itself back in as soon as I reused the original property name.
A note of interest: I built a Release version, and it worked the same way (but apparently with a different hidden data store) - that is, the bin/Debug version can be set to one value and the bin/Release version to another (via code), and they each reference the value assigned to the corresponding version.
==========
UPDATE: I found an MSDN Forum that suggested calling My.Settings.Reset() before referencing any of the settings values. It worked - in that the program was finally referencing the (invalid) filepath as defined in My.Settings. I removed the Reset call, and the program worked again (with the invalid filepath). So, apparently, Reset() is the way to correct my problem...EXCEPT...it does not solve the conundrum of the hidden data store! Once again, I played with the MsgBox buttons and recreated the original situation.
These are the steps that recreate the situation:
- I add a My.Settings.Reset() call in the startup form's OnLoad override
- I set a debugger breakpoint at the first line of MyGlobals.SetDBConnection(), run the debugger, and step into (F11) until the DataSource assignment in the SQLiteConnectionStringBuilder (connStr) constructor, which causes the MsgBox to appear (Unable to open database file)
- I choose the Ignore option, which (at least visually) causes the debugger stepper to immediately go to the top of the method
- This time, at the first line of the Try/Catch block, _DBConn is not Nothing, and therefore the holdDBFilePath = _DBConn.FileName assignment is attempted, which causes an unhandled exception:
System.TypeInitializationException: 'The type initializer for 'WindowsApp1.MyGlobals' threw an exception.'
Inner Exception: InvalidOperationException: 'Database connection not valid for getting file name.'
At this point, the original situation (the unexplained persistence) has begun. But the order of execution is confusing to me: after choosing Ignore, the expected sequence of events is to set the DBFilePath property to holdDBFilePath (which is Nothing), then drop through to set _DBConn to Nothing and recursively call the same method. However, the debugger stepper skips to the top of the method after clicking the Ignore button - perhaps the steps execute but the MsgBox call causes a debugger step-out? Then on the second iteration the _DBConn variable is a SQLiteConnection instance even though the debugger never indicated passing through the variable's initialization and .Open() call, and should have been reset to Nothing in the Catch block drop-through anyway! In any case, since the _DBConn variable is not Nothing on the second iteration (but is still not valid), the unhandled exception occurs, and after that the original (mysterious) persistence recurs.
Another note of interest: the mystery data store resets to an empty value each time the unhandled InvalidOperationException (Database connection not valid for getting file name) is thrown. I tested this by setting the mystery store to the valid filepath in code, which caused the now-expected (normal) execution, after which I removed the hard-set code and reran the steps to recreate the problem (as above). The next execution of the program threw the ArgumentException (Data Source cannot be empty...) error.
==========
Bottom Line: There appears to be an unknown/undocumented persisted My.Settings primary key-value store (which might only be targeted once an abortive exception has occurred) that will override any My.Settings-coded reference to that key's value (both Get and Set methods). I can call My.Settings.Reset() to correct the situation, but an unhandled exception can cause it to recur.
Does anyone have any insights that may help?
My system info is as follows:
Windows 10 Home
Version 1909
Build 18363.657
Microsoft Visual Studio Community 2019
Version 16.4.4
VisualStudio.16.Release/16.4.4+29728.190
Microsoft .NET Framework
Version 4.8.03752
Thank you in advance for any assistance you can give!
Continue reading...