[RESOLVED] Query returns value from wrong column of the table

I really have no idea what my problem is or where (the database or Visual Basic) the problem actually is.

Symptoms

I have a function that queries a table and obtains the value from a single defined field then assigns the value to a variable, and finally returns the variable. What occurs is that the query yields the value from a different column than the one requested in the query.

Code:

---

    Private Function GetFilePath(FileCabinet As String, MyUser As String) As Object
        'Creates FilePath directory for non revision controlled articles.
        MyError = "Failed to create the file path."
        MasterBase.MasterBaseQuery("SELECT colFilePath FROM defMasterBase WHERE colName = '" & MyApp & "'")
        If ErrorReport.NoErrors(True) = False OrElse RecordCount < 1 Then MyDirectory = ""
        FileCabinet = MasterBase.ListTable.Rows(0).Item(0).ToString + MyUser + "\" + StaffID + "\"
        Directory.CreateDirectory(FileCabinet)
        Return FileCabinet
    End Function

---

The database is ACCESS and the table consists of 15 text fields. Structurally, the table looks like below. When the query is executed, instead of picking up value from the field colFilePath, I am getting the value from colSet1.

Attachment 187397

As far as I can tell, the query is correct and I know of nothing that is wrong with the table. I will be happy to provide any additional information that would be helpful.

What is this? :-

Code:

---

MasterBase.MasterBaseQuery

---

The problem is probably there.

MasterBase.MasterBaseQuery resides in a module and is used everytime I access the database.

Code:

---

        Public Sub MasterBaseQuery(SetQuery As String)
            RecordCount = 0
            Exception = ""
            Try
#Region "Open Connection/Load Table"
                MasterBaseConnection.Open() 'Open connection
                ListCommand = New OleDbCommand(SetQuery, MasterBaseConnection) 'Database Command
                Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                Params.Clear() 'Clear params list
                ListDataSet = New DataSet
                ListTable = New DataTable
                ListAdapter = New OleDbDataAdapter(ListCommand)
                RecordCount = ListAdapter.Fill(ListTable)
                ListDataSet.Tables.Add(ListTable) 'This Dataset is used for setting comboboxes
#End Region
            Catch ex As Exception
                Exception = ex.Message
                MsgBox(ex.Message + vbLf + vbCrLf + MyError)
            End Try
            MyError = ""
            If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
        End Sub

---

I sure hope this is not the problem as it is referenced over 100 times. Basically everywhere I need to access the database.

Quote:

---

Originally Posted by gwboolean

MasterBase.MasterBaseQuery resides in a module and is used everytime I access the database.

Code:

---

        Public Sub MasterBaseQuery(SetQuery As String)
            RecordCount = 0
            Exception = ""
            Try
#Region "Open Connection/Load Table"
                MasterBaseConnection.Open() 'Open connection
                ListCommand = New OleDbCommand(SetQuery, MasterBaseConnection) 'Database Command
                Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                Params.Clear() 'Clear params list
                ListDataSet = New DataSet
                ListTable = New DataTable
                ListAdapter = New OleDbDataAdapter(ListCommand)
                RecordCount = ListAdapter.Fill(ListTable)
                ListDataSet.Tables.Add(ListTable) 'This Dataset is used for setting comboboxes
#End Region
            Catch ex As Exception
                Exception = ex.Message
                MsgBox(ex.Message + vbLf + vbCrLf + MyError)
            End Try
            MyError = ""
            If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
        End Sub

---

I sure hope this is not the problem as it is referenced over 100 times. Basically everywhere I need to access the database.

---

You need to debug that method. You ought to have already debugged that method. Step through it line by line. Just before executing the query, execute it directly in the database and see what you get. After executing it in code, examine the DataTable yourself and see what it contains.

I'd certainly make sure that the SQL being passed in is what you want, but there are other parts of that which I find a bit suspect. For example, you appear to be populating a table in a dataset, and that's all that the function seems to do (aside from passing out the count via a global, which isn't ideal, but should work so long as you remain single threaded), but you don't appear to do anything with that table in the function you showed. Instead, you appear to work with MasterBase.ListTable. Is that the same thing?

Since this routine is called from so many other routines, I had the belief that it was probably pretty robust. However, I will do as you suggest. I will additionally step through this routine from other equivalent functions that reference this.

The function that is driving this only accesses one column of the table and merely copies the data from the table to a variable. The data is not modified.

I already know what the database contains and it is not changed. Additionally, I know through tests I have already done, that the data copied to the variable comes from a different column than the one called. I have run equivalent functions that reference MasterBaseQuery() and those that I have tested go to the correct column and copy the correct data to the variable.

Either there's something wrong in your code, which debugging will show up, or there's something wrong with the OleDb ADO.NET provider or the OLE DB provider you're using, which I'd guess would be Jet or ACE. It's not impossible but it seems far less likely that one of those is at fault than your own code. Nothing jumps out when simply reading the code but that's exactly why debugging the code is the next step. You can step through your own method first but, if there's still nothing obvious, stepping into the library method is the next step after that. In both cases, examine each expression in scope at the time and confirm that it is what you expect. That would include the connection string - it wouldn't be the first time someone has connected to the wrong database - and the SQL query.

Quote:

---

Originally Posted by jmcilhinney

.... - it wouldn't be the first time someone has connected to the wrong database - and the SQL query.

---

LOL, took me once 3hrs to figure that one out.. brings back memories:blush:

I have been running a number of tests for everything that uses MasterBaseQuery(). Not all 100 plus of them, but 4 or 5 of them. The only erroneous return is from the same function discussed here. At this point I just don't see what/why it is going calling for colSet1 instead of colFilePath, as called in the function.

Here is the call line again.

Code:

---

MasterBase.MasterBaseQuery("SELECT colFilePath FROM defMasterBase WHERE colName = '" & MyApp & "'")

---

When it executes it goes directly to MasterBaseQuery(), as expected. However, here is what the ListCommand looks like when it is called.

Attachment 187404

As can be seen, instead of colFilePath being called, colSet1 is called. I cannot see any reason why this would occur.

This is just about the extent of my understanding of the debugging process. There is no other database to check, and while there is a table that is similar to the one used for this, it has neither of the columns that show up in the MasterBaseQuery(). I heard mention of, "Stepping into the Library method." . Do I even want to know what the hell that is? It sounds like an opportunity for me to dig a deeper hole than I am already in.

I am willing to learn but can this be resolved if I just replace the staffRecord.vb form (design/code) with a new staffRecord.vb with a new design/code? Of all the calls to MasterBaseQuery() that I have checked, this is the only one that calls out the wrong column.

I don't recognize that screen shot. There are several ways to do things, so that may just be an option I haven't tried before, but it isn't how I would have done it.

What I would have done is put a breakpoint on this line:

Code:

---

ListCommand = New OleDbCommand(SetQuery, MasterBaseConnection) 'Database Command

---

It wouldn't HAVE to be that line, and lines before and after that would work just as well, but that IS where I would have put the breakpoint initially.

When execution stopped on the breakpoint, I would have realized that I didn't really need to put the breakpoint there, but that it was good enough. I would then have clicked on (or highlighted) SetQuery and pressed Shift+F9 to see what was in it. If I saw what you showed in that Text Visualizer, that would have been interesting. If not, then I would have stepped forwards one more line (F11), then taken the CommandText property from the ListCommand. That probably would have been sufficient to go over to Access and run the query directly. It likely wouldn't have gotten that far, though.

I feel that you have tried to organize the code by isolating the SQL query portion, but you've done so in a fashion that relies on a bunch of indistinct global variables. Global variables can cause trouble. They live longer than they need to, they may retain some state from previous use in ways that can cause trouble, and they end up being messier overall. Something that may be relevant here.

There are issues with that MasterBaseQuery method that keeps things confusing. You create a dataset, but you don't use that. You use a single datatable, and since the dataset is created new every time MasterBaseQuery is run, the dataset is worthless. A dataset is only a collection of datatables, but you only ever add one to it in that method, then throw it out the next time the method is called.

The management of the connection is both overwrought and unnecessary. Some things, such as the connection, are kind of managed, while others, such as the dataadapter and command object, are left on their own.

The method is a Sub, even though it makes vastly more sense to turn it into a function that returns a datatable. After all, that's all you ever get out of it. The record count is pointless if you have the datatable, as you have that information whenever you have the datatable. Having the datatable be a global means never being quite sure what it holds. One of the possibilities, which the MsgBox kind of rules out, is that you were getting an exception and just never noticed, such that the datatable held not the new data that you expected, but whatever happened to last be in the table.

Parameters could also be a bit of an issue, since this is an Access DB, but in this case you aren't using parameters in the query, so that likely doesn't apply. I don't like the way Access handles parameters. I think what you are doing might be right enough, but it's not great. You appear to be setting the parameters as a different step, since the parameters appear to be in some other global variable, whereas if it were passed to the method as an argument, then there would be no chance that you forgot to do something and ended up using parameters left over from the last query. That doesn't look like it should apply, though, at least not in this case.

There's a lot wrong with your GetFilePath function, such as:

Why is your Return type Object and not String?
Why is the first passed parameter (FileCabinet) value never used? You instead use that same variable to store the return value, which makes zero sense.
What is ErrorReport?
What is MyApp?
What is StaffId?
What is the point of MyDirectory?

Setting all that aside, I'm going to take a longshot guess at what is happening here.

You have two (or more) functions named "GetFilePath". They differ by the number, type, and/or order of parameters passed (of course...). The "GetFilePath" function that you posted in post #1 is not the actual function that your code is calling, but another function with the same name and differing parameters is the one that is being called, and in that function the column name used for the query is colSet1.

Either that, or whatever "ErrorReport" is doing is also calling MasterBaseQuery and is clobbering the results of the query you made in the line above it with it's own new query that references colSet1.

Lots of messiness going on here.

Shaggy/Option

Both of you appear to be focused on the overall, messiness if you will, of the code in in both the calling query and MasterBaseQuery().

First, DataSet. I used to use datasets for all of my data but long ago quit using them, except for comboboxes. So does using the DataSet interfere with any of the elements of MasterBasseQuery() that do not use a DataSet? There are no comboboxes used on the form staffRecord.vb. I REM'd out any reference to DataSet from MasterBaseQuery() and the ListCommand line was identical to what it was before.

Global Variables. I use them because I don't know a better approach.

Parameters. I use parameters with many of the functions that use MasterBaseQuery(). Without actually looking through all of my calling functions, I would guess that all of them that are only reading the data in the record do not use parameters. I generally only use parameters for call queries that are inserting/modifying data in a table. The parameters are always passed from the calling function.
This sounds like another endorsement to eliminate the use of ACCESS and switch to SQL.


Sub vs Function. I will have to think about how I would set MasterBaseQuery() up as a function and how many calling functions would have to be changed. That really sounds like a big job. But I might be willing to take that on.

Return object instead of string. I assume you are referring to the calling function, GetFilePath(). I changed it from Object to string and noticed no difference.

The content concerning GetFilePath() I do not fully comprehend. Could you please dumb that down a little for me? That does sound like something that could cause a problem.

Perhaps either of you might give me an idea of what a clean GetFilePath() and MasterBaseQuery should look like?

At the end of the day, MasterBaseQuery() is called by a whole lot of different functions (over 100) that are doing a whole lot of different things with the data called up. This is the only one that I know of that is not yielding the correct results.

Anyway, I will put some effort into trying to check out everything you mentioned.

Rather than going down the road of trying to summarize how method overloading works, it would probably be easiest if you would post the exact code where you are calling your "GetFilePath" function. Because right now we're in the dark regarding how your code gets there.

Quote:

---

Originally Posted by gwboolean

Shaggy/Option

Both of you appear to be focused on the overall, messiness if you will, of the code in in both the calling query and MasterBaseQuery().

---

Ultimately, the problem lies there. As JMC stated, the only other option is that the query driver for Access, used millions of times in hundreds of thousands of programs, would be doing this often enough that it would be well known. The issue is almost always with your code, one way or another. Compiler bugs do happen, but they're rare, and tend to go away.

Quote:

---

First, DataSet. I used to use datasets for all of my data but long ago quit using them, except for comboboxes. So does using the DataSet interfere with any of the elements of MasterBasseQuery() that do not use a DataSet? There are no comboboxes used on the form staffRecord.vb. I REM'd out any reference to DataSet from MasterBaseQuery() and the ListCommand line was identical to what it was before.

---

Yes, the misuse of dataset is not going to cause this problem. It's still a misuse though, because a datatable was always an option.

Quote:

---

Parameters. I use parameters with many of the functions that use MasterBaseQuery(). Without actually looking through all of my calling functions, I would guess that all of them that are only reading the data in the record do not use parameters. I generally only use parameters for call queries that are inserting/modifying data in a table. The parameters are always passed from the calling function.
This sounds like another endorsement to eliminate the use of ACCESS and switch to SQL.

---

In general, I prefer the named parameters available with most every database other than Access, but one doesn't always get to choose. I doubt you do by now, so that's not really an option. Parameters don't appear to be relevant to this, as there don't appear to be any parameters in this query, and you always clear your parameter set after use.

Quote:

---

Sub vs Function. I will have to think about how I would set MasterBaseQuery() up as a function and how many calling functions would have to be changed. That really sounds like a big job. But I might be willing to take that on.

---

Yes, it would be a big job. It also won't solve this problem. There are better layouts, but it isn't going to change this.

One point about my last post: The first place I would put a breakpoint would tell me something, but it wouldn't tell me everything. If I did as I stated in that post, and found that the SQL was what I expected, then the next step I would take would be to set a breakpoint on this line:

Code:

---

MasterBase.MasterBaseQuery("SELECT colFilePath FROM defMasterBase WHERE colName = '" & MyApp & "'")

---

Is the breakpoint hit? If so, then I'd step into (F11) the MasterBaseQuery method and step all the way along, looking at the CommandText in the cmd object and if it looked good, then copying it into Notepad, or some such, to be able to store it for later.

If all of that looked good, and I was still getting the wrong results in the table, even before returning from MasterBaseQuery, then I'd be looking at the connection string to be sure that I'm looking at the right database. I could then take that stored SQL string and run that in Access to see what result I got.

In any case, there is no way to rewrite MasterBaseQuery that wouldn't also result in rewriting all the many places where it is used. Any example of how I would write it wouldn't be usable without significant time spent rewriting everywhere else it was used, so it's likely not all that useful.

EDIT: By the way, by putting a breakpoint on that one line and seeing whether or not it is hit, you'd answer one of the points OB1 made: You'd KNOW that you are calling the right function. If the breakpoint wasn't hit, then you'd know that something was wrong.

If "ListCommand" actual is "Select colSet1 .... " then I don't see anyway that MasterBaseQuery is currently being called from

Code:

---

MasterBase.MasterBaseQuery("SELECT colFilePath FROM defMasterBase WHERE colName = '" & MyApp & "'")

---

MasterBaseQuery is being called from somewhere else. I'd do a quick Find on "Select colSet1".

Don't know how are where you are checking ListCommand so that's all I can suggest. Except you should also follow SH breakpoint instructions so we can get a clearer idea of what's going on.

Quote:

---

it would probably be easiest if you would post the exact code where you are calling your "GetFilePath" function.

---

GetFilePath is called from the Load event.

Code:

---

---

Code:

---

    Private Sub staffRecord_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Select Case MyState
            Case "Add"
                lblStaffID.Text = GetNewID.GetStaffID(StaffID).ToString 'Acquires StaffID from appropriate setTable.
                rdoActive.Checked = True 'New Employee is active.
                lblStart.Text = CStr(Date.Today) 'Employee Start Date = Now
                lblFilePath.Text = GetFilePath(MyDirectory, MyPath).ToString 'Derived FilePath directory.
                SetState("Add") 'Set control and form properties
            Case Else
                GetStaffRecord(StaffID) 'queries MasterBase.FileTable for record with colStaffID value matching the StaffID value.
                If MyState = "Edit" Then SetState("Edit") Else SetState("View") 'Sets control and form properties.
                BindControls() 'Binds data acquired from sitStaffMaster query to controls.
        End Select
        GetDeptComboBox()
    End Sub

---

Quote:

---

Yes, the misuse of dataset is not going to cause this problem. It's still a misuse though, because a datatable was always an option.

---

I have used them for quite awhile for comboboxes on forms. I suppose I should update my methods to eliminate their usage.

I am going try your suggestion with the breakpoint and see what that will yield.

Quote:

---

MasterBaseQuery() is being called from somewhere else.

---

I have thought that too. I just didn't know how to look for that. Thanks for the suggestion on how to do that.

Based on the fact that Master.BaseQuery() is used so often and by so many different queries, I am still having a hard time accepting that the issue actually resides there. It is my belief that the issue lies in the code of GetFilePath(), or the code in the Load event leading up to the call for GetFilePath()

Quote:

---

Originally Posted by gwboolean

Code:

---

lblFilePath.Text = GetFilePath(MyDirectory, MyPath).ToString 'Derived FilePath directory.

---

---

Are MyDirectory and MyPath both declared as Strings?

Quote:

---

Code:

---

MasterBase.MasterBaseQuery("SELECT colFilePath FROM defMasterBase WHERE colName = '" & MyApp & "'")
If ErrorReport.NoErrors(True) = False OrElse RecordCount < 1 Then MyDirectory = ""
FileCabinet = MasterBase.ListTable.Rows(0).Item(0).ToString + MyUser + "\" + StaffID + "\"

---

---

Also, you've never answered the question, what is ErrorReport? That's the only thing that is interacted with between the time that your MasterBaseQuery stuff happens and when you attempt to retrieve the results of said query.

Quote:

---

Originally Posted by gwboolean

Perhaps either of you might give me an idea of what a clean GetFilePath() and MasterBaseQuery should look like?

---

Honestly, no. If I were writing code to do what I think you are trying to accomplish, neither of those methods would exist, and the entire project would be designed differently.

As I understand it, you are using this "MasterBase" like a global chalkboard, if you will, where each subsequent "MasterBaseQuery" erases what was on the chalkboard and puts new stuff on it. That gets messy real fast if you are making "MasterBaseQueries" from 100+ different locations, and each one is relying on the fact that the chalkboard doesn't get erased until it is able to get the information off of it that it asked to be put on it.

Good luck.

For the record, I was here to try to help but it seems like the advice I've offered has basically been ignored - if it hadn't been then you know some things that you seem still to not know. Given the apparent determination to not use the debugging tools available to you, we're just going to end up in this same situation again. For that reason, I'm out. It is my hope that letting you know that you're actually putting people off helping you will convince you to actually learn what you should already know. I'm not overly confident but it's no longer my concern.

Quote:

---

Originally Posted by gwboolean

Return object instead of string. I assume you are referring to the calling function, GetFilePath(). I changed it from Object to string and noticed no difference.

---

That sounds like you don't have "Option Strict On" which could be hiding other issues in your code.

Quote:

---

That sounds like you don't have "Option Strict On"

---

I have Option Strict on in the properties of the project. Additionally, I have Option Strict On in the code of at the beginning.

Quote:

---

As I understand it, you are using this "MasterBase" like a global chalkboard, if you will

---

That kind of sounds like what I am doing. Assuming I know what I am doing is a bit of an overestimate. I have a module called MasterSystem and it contains a number of classes filled with routines/functions that I use in many places with many of the projects contained in my solution. MasterBaseQuery() is one of those functions.

Quote:

---

That gets messy real fast if you are making "MasterBaseQueries" from 100+ different locations

---

I honestly don't understand that. I was unaware that multiple calls to the same routine could cause any issues. The whole purpose of the module was to have a place that I could maintain frequently used processes and it was my understanding that using a module like this was a good practice. Even though there are 100+ locations where MasterBaseQuery() is called from, there are no simultaneous calls to this query at any time... unless there are some that I don't know about, which is entirely possible. It was my belief that whenever the query was run it would be clean.

For Queries, my approach has been to open the database, do whatever is required with the record(s) called, then close the database. I try never to leave a database open. I don't know if that has anything to do with your statement, but it is the process I use.

What would be the best practice for dealing with frequently used queries so that this mess would not occur?

Oh, while reviewing everything I found that I don't use the Datasets for anything anymore and have removed any reference to datasets from MasterBaseQuery().

Quote:

---

Also, you've never answered the question, what is ErrorReport?

---

Sorry, I missed that. Below is Error Report. It has nearly a hundred calls throughout the solution and is used with most of my queries.

Code:

---

        Public Function NoErrors(Optional Report As Boolean = False) As Boolean
            If Not String.IsNullOrEmpty(MasterBase.Exception) Then
                If Report = True Then MsgBox(MasterBase.Exception) 'Report Errors
                Return False
            Else
                Return True
            End If
        End Function

---

Additionally, I did a search through the code for, "Select colSet1" and found it in 5 routines. In all cases it was for a different table, defFileMaster.

Quote:

---

For the record, I was here to try to help but it seems like the advice I've offered has basically been ignored

---

I am sorry. I did read your suggestion and did follow it. In fact, I had been stepping through the code for hours prior to even posting this thread. I just did not comment on it, nor did I comment on everything that was suggested to me by others. I was unaware that was a requirement.

I stepped through every single part of the code that was part of the process many, many, many times. Considering what has already been exposed from stepping through the code and some of the questions, I was indeed able to identify that when MasterBaseQuery() was run it did have the wrong column named in the SELECT.

Everyone, myself included, believes that the issue lies somewhere in my code and that is causing the a call to the wrong query. I have not yet been able find out how this occurs from stepping through the code.

One other thing you mentioned was stepping through the Library Method. Actually, I had forgotten about that, but the fact is, is that is beyond my capabilities. I do not know how to do that. Perhaps you could explain to me what the library method is and how one would review/modify it?

Quote:

---

Given the apparent determination to not use the debugging tools available to you

---

Why do you say that? You know as well as I do that I have assiduously attempted to follow your suggestions and that I use every tool available that I am capable of using. I am sorry that I am unable to meet your rigid standards.

Quote:

---

Originally Posted by gwboolean

I stepped through every single part of the code that was part of the process many, many, many times. Considering what has already been exposed from stepping through the code and some of the questions, I was indeed able to identify that when MasterBaseQuery() was run it did have the wrong column named in the SELECT.

---

So, does this mean that when this line executes:

Code:

---

ListCommand = New OleDbCommand(SetQuery, MasterBaseConnection)

---

SetQuery has the wrong column in the SQL statement? That would be pretty interesting.

Quote:

---

SetQuery has the wrong column in the SQL statement? That would be pretty interesting.

---

Exactly. That is what occurred. That is one of the places I look at when I step through the code. However, things are somewhat different now. I returned the function to the MasterSystem module, where it was located prior to doing all of this. Basically, I took the function, GetFilePath() and moved it back to the module where it previously resided. I changed the name when I did this.

What occurs is very interesting, if frustrating. I set my breakpoint at the line rdoActive.Checked = true. So, when the breakpoint is hit I then F11 to the next line. This is the call to the function GetStaffFilePath(). The line appears to be skipped and the cursor drops down to line 275, which is the beginning of a function called GetStaffRecord(). I would have thought that this would be significant, but if there is another function/routine located in this location the cursor still drops to the same line. This would seem to me to be significant, but I have no idea why.

So, the two different configurations for the call that should yield the same result are entirely different. And in neither case does the call get carried out correctly. As I had the function configured yesterday, where it resided in StaffRecord.vb, The function is executed, but the column selected is the wrong one. As the function is configured today, located in MasterSystem module, the call appears to be skipped and the cursor drops down to line 275, which right now has the function GetStaffRecord(). It never executes MasterBaseQuery().

I am so damned confused! I am sure that there is something I setup wrong or mistyped, but I just cannot find it. I have made about every mistake that can be made and am familiar with most of the outcomes, by now. But this is different than anything I have ever seen.

Although it is clear that there is a problem, I really do not believe it is in, or has anything to do with MasterBaseQuery(). But since I have been wrong about everything else, there is no reason to believe I am right on this.

Code:

---

    Private Sub staffRecord_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Select Case MyState
            Case "Add"
                lblStaffID.Text = GetNewID.GetStaffID(StaffID).ToString 'Acquires StaffID from appropriate setTable.
                rdoActive.Checked = True 'New Employee is active.
                lblStart.Text = Today.ToShortDateString 'Employee Start Date = Now
                lblFilePath.Text = FilePath.GetStaffFilePath(MyDirectory, MyUser).ToString 'Derived FilePath directory.
                SetState("Add") 'Set control and form properties
            Case Else
                GetStaffRecord(StaffID) 'queries MasterBase.FileTable for record with colStaffID value matching the StaffID value.
                If MyState = "Edit" Then SetState("Edit") Else SetState("View") 'Sets control and form properties.
                BindControls() 'Binds data acquired from sitStaffMaster query to controls.
        End Select
        GetDeptComboBox()
    End Sub

---

Code:

---

        Public Function GetStaffFilePath(ByVal FileCabinet As String, ByVal MyUser As String) As String
            'Creates FilePath directory for non revision controlled articles.
            MyError = "Failed to create the file path."
            MasterBase.MasterBaseQuery("SELECT colFilePath FROM defMasterBase WHERE colName = '" & MyApp & "'")
            If ErrorReport.NoErrors(True) = False OrElse RecordCount < 1 Then MyDirectory = ""
            FileCabinet = MasterBase.ListTable.Rows(0).Item(0).ToString + MyUser + "\" + StaffID + "\"
            Directory.CreateDirectory(FileCabinet)
            Return FileCabinet
        End Function

---

Troubleshooting this by moving code around to different modules/classes is a bit confusing to me, but it does sort of reinforce with me that your code is likely "organized chaos".

I can't imagine anyone here will be able to help further without seeing all of your code, because your snippets are contextless, and when you reference line numbers, no one here knows what code has what line number.

I can't offer any further assistance. Good luck.

Perhaps a 'clean' and 'Rebuild All' as it seems your source and complied binaries are out of sync.

Quote:

---

I can't offer any further assistance. Good luck.

---

Sorry to hear that. I understand about my code being a hodgepodge. I am a hobbyist and am not very well versed in the overall process or how things should be setup. Most of what I have are based on code that I have seen and modifed to meet my requirements. So virtually all of my bad habits are inherited from the bad habits of others. I am willing to show the whole code, but that is a lot of crap to put into a post and to ask someone to review. Is that actually OK and recommended?

Quote:

---

Troubleshooting this by moving code around to different modules/classes is a bit confusing to me, but it does sort of reinforce with me that your code is likely "organized chaos".

---

That was not troublehooting. The code was moved from it's current location, when I placed the function into StaffRecord.vb, as a function of troubleshooting a few days ago. I simply moved it back and noted that there was a different outcome when running the code.

Quote:

---

Perhaps a 'clean' and 'Rebuild All' as it seems your source and complied binaries are out of sync.

---

Good idea, I have not run a build on that for quite awhile. I just did that and got an error indicating that the MasterBase.exe is not located in the bin\debug folder. I moved it, and ran the Clean/Rebuild. Got a whole new list of errors. Going to have to work through that before I can even come close to having an idea of what I did to this.

I don't know what line 275 is, or how that is related to the line that appears to be skipped over. How far back was that? Are they even in the same method? Is this the Load method? Also, in Configuration Manager are you in Debug mode or Release? I think you have to be in release, or breakpoints wouldn't be hit, but that might not be the case depending on your answer to this question: Which version of VS are you using?

Newer versions of VS won't hit breakpoints if you are in release mode, but older versions will. There was also an issue that seems to have gone away where an exception in the Load event for a form would cause the rest of the Load event to be skipped over, so execution would skip to somewhere else. That was many years back, and I don't recall the boundaries of the issue. It might have been only on certain operating system versions (like 32 bit Win 7, or some such), and perhaps certain versions of VS. Therefore, the OS and the version of VS could both be relevant.

Beyond that, by this point, I don't even know what we are talking about anymore. You talk about moving methods around, but in such a way that I don't know where it was at what times, only where you returned it to after...something for some reason. I don't understand when things were moved, where, or why. There's a right place for any function. I can't think of a problem that can be fixed by moving a method from one place to another, aside from certain issues that would cause the program not to compile. I'm also no longer sure what the real problem is. Sure, the query returns the wrong thing, but it sounds like it returns exactly what it is told to return, it's just that it's told to return the wrong thing because...the wrong function is being called, the wrong code is being called, the wrong SQL is being constructed, or something else that isn't even a little bit clear.

When we are battling with some programming issue, it's always because we are misunderstanding something. The code is doing what it's told to do. Actual compiler errors are very rare. I've found a few, and they can be mighty frustrating, but they also tend to go away spontaneously. Still, they are so rare that one shouldn't expect that. Most likely, the problem is with your code. Usually, you THINK it is right, but it's because your thinking is essentially in a rut. We fix things when we see it from a sufficiently right way that our thinking is knocked out of that rut such that we see what the code is doing and why it is doing it.

In this case, there are so many unknowns that I'm not sure that anybody can explain what is going on other than you. It sounds like a very large amount of code (if there are a hundred calls to a method, it's not a small amount of code), so posting it isn't viable, but that talk of the execution bouncing around makes it sound like either you are not in debug mode (which can cause that), you aren't actually working with the most recent code (that can happen, though the only way I have ever seen it happen for the last two decades is when using dlls), or perhaps you are working with a sufficiently old system that you are running into that old Load event issue...which I don't remember much about.

From the sidelines: You do know, that Access only allows 4000 Characters per record (Memo/Long Text and OLEObject-Fields excluded)?
https://support.microsoft.com/en-us/...8-98c1025bb47c

Quote:

---

Number of characters in a record (excluding Long Text and OLE Object fields) when the UnicodeCompression property of the fields is set to Yes

---

I see 18 columns of "Short Text" which is 255 characters max each.
No idea about MaxSize if compression is off

Now do a bit of math.

The point im hinting at: Is there somewhere an active Error-handler masking an exception?

Quote:

---

From the sidelines: You do know, that Access only allows 4000 Characters per record (Memo/Long Text and OLEObject-Fields excluded)?

---

That has actually been worrying me. The best I can tell, is that the MAX can actually use up to 2 GB, but works the same as the other nvarcar(), in that an empty field would still take up 2 bytes of memory. Even if that is wrong, I have seen the error of my ways and eliminated the use of nvarcar(MAX). All of my text fields are nvarcar(N) (N being some whole number <= 4000). I have come to the conclusion that a MAX set datatype is probably used for some very special case that I am not aware of... wait, I am talking about SQL. I have no idea about how that works for ACCESS, but if I remember correctly, from a long time ago, I believe the specifications are similar to those I was just discussing for SQL.

Having said all of that, I have succumbed to the pressure and junked ACCESS and moved to SQL. In the process, I decided that the code is so beat up and I have built/rebuilt this project so many times that who knows what evil lurks there, and built a new project around SQL. Having said that, the code is the same and I am not having the above noted issue with the new project, even though I did not get rid of MAX until after I had checked things out.

Quote:

---

The point im hinting at: Is there somewhere an active Error-handler masking an exception?

---

That might well be the case. My guess is that the issue is something somewhere in the generated code behind the form.