Query does not trigger Throw

[gwboolean]

I am having a little (lot) of trouble understanding what is going on here. I read the code below as this. If no record is yielded by the query call below, then the Trow CaseError should be executed. It is not.

                    If String.IsNullOrEmpty(LnkChangeRequestTableAdapter.FillByChangeManager(_MasterBase4_0ItemMasterDataSet.lnkChangeRequest, glbstrName).ToString) Then
                        Throw CaseError
                    End If

I have almost identical code in another form class that carries out this action exactly as desired.

            If String.IsNullOrEmpty(TblEmployeeTableAdapter.FillByLogin(_MasterBase4_0ItemMasterDataSet.tblEmployee, _strLoginName, _strPassword).ToString) Then
                Throw LoginError
            Else

So what is going south on me or what am I doing wroing?

[jmcilhinney]

A Fill method is supposed to populate a DataTable with matching records and return the number of records retrieved. If there are no matching records then that number will be zero, otherwise it will be greater than zero. Calling ToString on an Integer can NEVER produce Nothing or an empty String so neither of those exceptions should ever be thrown. If that second one is then there's something wrong with that, not the first.

[gwboolean]

I am not clear on what you are saying. So here is the query return when executed and there are no records meeting the criteria:



So I do not see where the number of records are returned.

As for why the wrong record is right and the right record wrong (sorry, couldn't help it) the second example executes the throw I have even less idea than you do.

[jmcilhinney]

A Fill* method is not a query. It is a method that executes a query. The Fill method on a table adapter executes the default query, which is basically a 'SELECT *'. If you add a query to a table adapter then the designer will add a corresponding Fill* method to execute it. The method will internally execute the query and populate a DataTable with the results, then return the number of rows it populated the DataTable with. The records are not the return value; the number of records is. If your FillByLogin method is not returning the number of records retrieved then I can only assume that it is a bad method, i.e. it is not a method that does what I described above but it has been named as though it is.

You need to look at your table adapter in the designer and make sure that it is configured appropriately and you also need to debug your code. If the first code snippet is not throwing an exception then obviously the result of the expression you're evaluating is not null or empty but did you bother to check what it actually is or did you just conclude that it was a mystery? That also leads onto to what the expression being tested in the second code snippet actually does return when the exception is not thrown.

[gwboolean]

OK, I understand I am using the wrong terminology. As for the rest, I will look at the Fill method again and I will look at the code again and see if by some miracle I can obtain that which I have not been able to see. I have checked everything I have the knowledge and skill to cover. If that is what you call just concluding that it is a mystery then yes that is what I did. Look man, if all you have to offer is, what appears to me, to be condescending jargonistic comments about why I don't get it but should, and you want to help then why do you bother?

[jmcilhinney]

I have checked everything I have the knowledge and skill to cover.

Do you know what the values of the highlighted expressions are when the exceptions are not thrown?

                    If String.IsNullOrEmpty(LnkChangeRequestTableAdapter.FillByChangeManager(_MasterBase4_0ItemMasterDataSet.lnkChangeRequest, glbstrName).ToString) Then
                        Throw CaseError
                    End If

            If String.IsNullOrEmpty(TblEmployeeTableAdapter.FillByLogin(_MasterBase4_0ItemMasterDataSet.tblEmployee, _strLoginName, _strPassword).ToString) Then
                Throw LoginError
            Else

If not then I would dispute that you have done everything you can. You're testing the value of something and it is not what you expect it to be. It seems fairly obvious to me that what it actually is is important information.

If you would care to point out where you don't understand my "jargon", maybe I can clarify for you.

[gwboolean]

I know what the values of both of the variables. As far as the highlighted methods, the only values I could observe in my efforts were the ones I obtained from executing the method in the query builder. Beyond that I do not know.

I never claimed that I had done everything I can. What I stated was that I had done everything that I knew to do. They are not equivalent.

[jmcilhinney]

I'm not sure that you're really thinking this through. They're just methods - functions - like any other. Surely you're not saying that you don't know how to get the value returned by a function. You do it all the time. As I have already said, the data retrieved by the query and the value returned by the function are not the same thing. I am, and have been, referring to the value returned by the method. I think that you know how to do this:

Dim x = LnkChangeRequestTableAdapter.FillByChangeManager(_MasterBase4_0ItemMasterDataSet.lnkChangeRequest, glbstrName)
Dim y = TblEmployeeTableAdapter.FillByLogin(_MasterBase4_0ItemMasterDataSet.tblEmployee, _strLoginName, _strPassword)

[gwboolean]

It seems apparent that I did not think it completely through. I would have been able to discern the answer if that were the case. Thank you for helping to think it through.

[Sitten Spynne]

Here's a thing that's frustrating about error handling: there are conventions, but everyone's on the honor system for following them and some big portions of .NET were designed before conventions were established. (Many of them are the REASONS we settled on certain conventions.)

You learned recently the .NET convention is, "A method should do what it says it does or throw". Sometimes people follow a different convention, like, "This method returns a special value if some error condition is met." TECHNICALLY that is still following the main convention: the method SAYS what it will do when that error condition happens, so it's still doing "what it says". Programmers really like these kinds of "technically right".

Really, though, this isn't a break from convention. The Fill method executes a query and tells you what the result of the query was. If the query syntax is invalid, it can't actually execute the query and THEN it will throw an exception. But if the query syntax is valid but wrong, the SQL server won't report an error even though you don't think "0 results" is the right response.

So, in short. If you want to "get all customers with a negative balance", this is the kind of query that will throw an exception:

SELECT START B A LOL

It's not a valid SQL query so the database will say "What the heck?" and that will get propagated back to you, probably as an exception. This query won't get you what you want, but is NOT the kind of thing that will throw an exception:

SELECT * FROM customers WHERE balance > 0

That's a positive balance, which you know is an error. But it's a valid query. The SQL server doesn't know it's the wrong query. This kind of error isn't appropriate for throwing an exception, mostly because none of the code involved has any clue it's "wrong".

Exceptions are really for "things are so wrong I can definitely tell this isn't what you wanted and I can't even figure out how to get back into a good state." But that's harder to describe than "I can't do what I say I do" and has a lot more subtlety. This is a case of, "Everything looks good to me, I can satisfy this request" even though you asked it to do the wrong thing.

[gwboolean]

That actually sounds pretty good Sitten. However, I have already adopted another approach, which is:

                Try
                    If Me.TblEmployeeTableAdapter.FillByDepartment(Me._MasterBase4_0ItemMasterDataSet.tblEmployee, glbstrDept) = Nothing Then
                        Throw ExMessageString
                    End If
                Catch ex As Exception
                    Dim exQueryFailure As New ErrorCode1003
                    Dim ExMessage As String = "No record returned from query call to tblEmployee." + vbNewLine + vbNewLine
                    exQueryFailure.strMessage = String.Format("{0} StackTrace: {1}", ExMessage, ex.StackTrace)
                    Me.Close()
                    MsgBox(exQueryFailure.strMessage)
                    'frmSignRecord.Show()
                End Try

This Query call appears to provide what I was after.

[jmcilhinney]

Is that code just for test purposes or are you really throwing and catching your own exception in the same block?

[Shaggy Hiker]

I don't think that WILL do what you want. One thing isn't clear from this discussion? JMC highlighted a few parts of a few lines, and asked about the values of them. In your reply, you said you knew what the values were, but you said it in such a way that it left me unclear as to whether or not the two of you were talking about the same thing, so I'll ask it more explicitly:

For the values that JMC highlighted, did you determine the values by putting a breakpoint on those lines, and when execution stopped at the breakpoint, did you highlight what JMC had highlighted and look at the value by pressing Shift+F9? That breakpoint and examining the value are the only way I would be certain that you and JMC are actually talking about the same thing.

If you did that, then I would say that in your current code, you should do the same thing for this part of the line:

 Me.TblEmployeeTableAdapter.FillByDepartment(Me._MasterBase4_0ItemMasterDataSet.tblEmployee, glbstrDept)

When the breakpoint is reached, and you highlight that and press Shift+F9, what do you see? That is the key point, in my mind. There is the issue of throwing and catching your own exception, but I don't think it should ever happen, in this case.

[jmcilhinney]

I don't think that WILL do what you want. One thing isn't clear from this discussion? JMC highlighted a few parts of a few lines, and asked about the values of them. In your reply, you said you knew what the values were, but you said it in such a way that it left me unclear as to whether or not the two of you were talking about the same thing, so I'll ask it more explicitly:

For the values that JMC highlighted, did you determine the values by putting a breakpoint on those lines, and when execution stopped at the breakpoint, did you highlight what JMC had highlighted and look at the value by pressing Shift+F9? That breakpoint and examining the value are the only way I would be certain that you and JMC are actually talking about the same thing.

If you did that, then I would say that in your current code, you should do the same thing for this part of the line:

 Me.TblEmployeeTableAdapter.FillByDepartment(Me._MasterBase4_0ItemMasterDataSet.tblEmployee, glbstrDept)

You can't get a value for that expression without actually calling the method, and you don't want to call the method twice (i.e. once in the code proper and once in the debugger) so you need to separate the code into multiple lines. That way, you can execute the expression once, see what value it returns and then use that value.

There seems to be some sort of implication post #9 that some sort of resolution was reached without any indication of what that resolution was.

[Shaggy Hiker]

You can do it that way, or you can do it with the debugger. It will execute it twice if you let it run the line AND check in the debugger, but I don't think it will come to that. I would say that checking it in the debugger will provide so much illumination into the issue that gw will immediately stop the code and make a change. The real point to my post is to make sure that gw is looking explicitly at what that section returns using a means such as the debugger. Setting the return to a value would work, I just expect that it wouldn't come to that.

[gwboolean]

I use code breaks just prior to the point of failure and step through the code. Generally, I do this a number of times until some sort of illumination occurs or I am unable to figure it out and try something else. In doing that I was able to discern that the variable had the correct value, but method did not yield anything I was able to read and make sense of. What I finally ended up doing was using a method that does what I want done.

The problem always was with the method. I knew that and that was what I was trying to work through. Why the original method did not trigger the throw when it should. I never figured that out, it was never explained to me in a way that I was able to comprehend and I moved on.

The code above is for test purposes and is there for the duration of working in that area. Once the work is done, there is no need for it, unless the code right there somehow becomes contaminated (unlike many claim that does happen). Yes, I was really throwing and catching in the same block.

[Shaggy Hiker]

The problem from the start was confusion about what the .Fill method returned. It returns an integer. Originally, you converted that to a string, then tried to use IsNullOrEmpty. It will NEVER be null or empty. If .Fill returned no rows, then the integer would be 0. Convert that to a string and you have "0", which is neither null nor empty. The same logic applies to any other possible return from .Fill.

You seem to still have that confusion, because you are now comparing the integer to Nothing. That will work, because, if .Fill returns 0, then 0 = Nothing will return True, but it's still confusion. You are getting an integer back. You should be comparing that integer to an integer. If you want to see if it is 0, then compare to 0, not Nothing. That only adds confusion.

[jmcilhinney]

If .Fill returned no rows, then the integer would be 0.

I think that this sort of language can be at least part of the cause for the confusion. Fill doesn't return rows. Fill retrieves rows and puts them into a DataTable. It returns an Integer; specifically the number of rows that it retrieved. I'm not quite sure why the difference between the data retrieved and the number of records in the data retrieved is not clear to the OP. It was never conveyed to us what the actual return values of those two Fill* methods was so we can't really explain any further with respect to this specific case.

[jmcilhinney]

A Fill* method works something (certainly not exactly) like this:

Public Function Fill(table As DataTable) As Integer
    Dim recordCount = 0
 
    Me.Connection.Open()
 
    Using reader = Me.SelectCommand.ExecuteReader()
        Do While reader.Read()
            recordCount += 1
 
            'Add a row to table here.
        Loop
    End Using
 
    Me.Connection.Close()
 
    Return recordCount
End Function

Hopefully it's clear from that that the value returned by the function will ALWAYS be an Integer: zero if no data was retrieved or greater than zero if there was data. That could be Nothing if compared directly because Nothing converted to an Integer is zero but if you convert the result to a String then it can NEVER be Nothing or an empty String because it will always contain at least one character. Going back to post #1, if that second snippet was ever throwing an exception then it must not have been returning an Integer because, as has been said numerous times, an Integer converted to a String can NEVER be Nothing or an empty String. That means that that FillByLogin method must not have been returning an Integer. You chose not to tell us what it was returning though, so we can only speculate.

[gwboolean]

Apparently that is true, Shaggy. I also found out, much to my chagrin, that IsNullOrEmpty will not get the job done. Now at the time I was having this problem I would run the fill method and what I would see as the result was a row of nulls and this led me to believe that I could use IsNullOrEmpty.

I no longer have any confusion at all. Using the If/Then = Nothing provided me with exactly what I was after.

What I was after was, based on the parameter provided, for either rows of data yielded or no rows of data yielded. If rows were in the yield I wanted to process them. If no rows were yielded then I wanted to be aware of it and process that. I believe that we have all lost sight of the objective while getting into the details of the problem causing the failure to achieve the objective.

No, I do not yet fully understand what all is going on with this whole process, but I am getting there.

JM,

I can understand your claim about confusion and precise usage of terminology. However, you fail to understand that precise usage of terminology is directly proportional to ones understanding and level of expertise with the subject matter at hand. Things were never conveyed to you because I had no idea what was actually occurring and did not know what I should even look for to gain the required understanding and to be able to properly convey the issue. Had I had a full understanding of the process with the fill method and been able to explain exactly what was going on I would not have needed assistance.

At any rate, I do understand the process now, much more than I did before, and am able to see why the fill method did not yield what I was after. As for conveyance of what the problem is that occurred, I am only able to convey that information as well as I can understand the problem and the causative agents.

So, back to the original issue. I was wanting to use a fill method to respond to a parameter and yield (retrieve) rows of data, which I could then process. Additionally, I wanted a fill method that when no rows were retrieved would allow me to process that. I did not know how to do that and chose a method that would not meet those requirements. I now have a fill method that meets both of those requirements.

[Sitten Spynne]

It seems to me a big contributing factor here is you must have Option Strict Off. This allowed you to pass an Integer argument to String.IsNullOrEmpty() without complaint. Passing an Integer where a String is expected is one of the most common sources of programming errors, so if Option Strict is on that particular situation and dozens of others are not allowed.

So spend some time in both your project options and in the Visual Studio options and make sure this project and all future VB projects are set to use Option Strict On.

If we were in a universe where you did that before this thread, the compiler would've complained that it can't convert the Integer return result to a String for String.IsNullOrEmpty(). That would be the hint to go look at the String.IsNullOrEmpty() method ot make sure it does what you think, and to try to figure out what the Integer return value from Fill*() methods means.

In some languages, the integer 0 can be equivalent to "null". VB is not one of those languages. I can probably contrive some scenarios where it's allowed, but with Option Strict On it can't really be true, or more truthfully the "Well, actually..." is so contrived there's a compiler warning that tells you "DON'T DO THIS".

In .NET, if you are working with String methods like String.IsNullOrEmpty(), you need to give it Strings. 0 is not a String, it's a number, just like 1 or 100. You can convert those to Strings. If you do, it becomes obvious that "0" is certainly not an empty string. So I like to think if you'd had Option Strict on to begin with, the whole thing would've been self-evident. That's why we like it on.

Why is it off by default? VB behaved like it does with Option Strict Off for every version BEFORE .NET. It's part of what led to VB having an undeserved bad reputation as a garbage dump of unmaintainable code. (The biggest part is "what happens when you hire the cheapest developers you can find".) .NET in general is strongly-typed, so VB needed the Option Strict On behavior to be compliant. But MS left it Off by default under the delusion that developers would quickly port all of their VB6 code to VB .NET. That was... an idealistic assumption. A lot of that code can't and won't be ported. So we're stuck with a stupid burden for backwards compatibility that no one ever really used.

Does it make sense? No. Nothing in programming that starts with, "For historical reasons..." ever does.

[gwboolean]

Sitten,

No, Option Strict is on, as you suspected. I just checked. I always turn it on anyway. While in the past I am sure that I have used the String.IsNullOrEmpty() as I attempted to use it here with success I am pretty sure that Option Strict was always on. However, I am no longer sure that I ever successfully used a method that way so I have thrown all of my assumptions out the window. Anyway, I can see what you mean and will make sure that I frequently check on things like that in the future.

That was illuminating information Sitten. It does make sense and it truly fills in a number of gaps and misinformation I have long held. And absolutely, any sentences that begins with "For historical reasons" never does. That holds true for many things.