How to properly exit a While Reader.Read routine

Question

I am looking for a way to properly exit a while reader.read routine. In my code below I want to add back in the commented out If/End If. If I uncomment these lines the next routine in my program that tries to read from the database gives me an DataReader is already in use error. There are sometimes multiple countries are returned and I only want one of them that matches the Name variable. When that is found I want to exit out of the reader and close it out so I do not get the Reader already in use error.

SQLString = "Select * from CountryList where CountryName LIKE '%" + Name + "%'" 
        command.CommandText = SQLString
        Using rs
            rs = command.ExecuteReader()
            While rs.Read
                DXCCID = rs("DxccID")
                CountryName = rs("CountryName")
                'If CountryName = Name Then
                '    rs.Close()
                '    Exit While
                'End If
            End While
            command.Dispose()
            rs = Nothing
        End Using

Any help would be appreciated..

Take a look at these similar questions: [There is already an open DataReader associated with this Command which must be closed first](https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) and [Error "There is already an open DataReader associated with this Command which must be closed first" when using 2 distinct commands](https://stackoverflow.com/questions/18475195/error-there-is-already-an-open-datareader-associated-with-this-command-which-mu) — Étienne Laneville, Apr 23 '23 at 03:54
Your code makes no sense. If you only want one record that matches `Name` exactly then why are you using a query that does a partial match? Change your query to do an exact match and then you can't possibly get more than one row. You cane then ditch the loop and just use an `If` statement if there might be no match at all. — jmcilhinney, Apr 23 '23 at 04:37
The reason I am using a partial match in the SQL is I am reading from one Database table and attempting to match it with another Database table. For example in Table one where the Name variable comes from it might have an entry like UNITED STATES but in Table 2 where the CountryName variable comes from the entry is UNITED STATES OF AMERICA. But looking at my if statement this is not going to work. I will need to run 2 separate database calls. one using where it is not a partial match and if then it returns nothing then it does a partial match. — Rick Ellison, Apr 24 '23 at 02:50

score 0 · Answer 1 · answered Apr 23 '23 at 04:35

I haven't tested but I think this:

Using rs
    rs = command.ExecuteReader()

should be this:

Using rs = command.ExecuteReader()

Even if that's not causing the problem, there's never a reason to separate the declaration and initialisation of a variable like that. Unless the declaration and initialisation need to be in different scopes, e.g. the former outside an If block and the latter inside, always declare the variable and initialise it in one line. Do that with all your data readers and you cannot possibly wind up having two data readers open at the same time, as long as you don't try to nest those Using blocks.

In fact, I suspect that you must have Option Strict Off and that that code wouldn't even compile otherwise. ALWAYS turn Option Strict On. Turn it On in the project properties and also in the VS options, so it will be On by default for all future projects. There's every chance that doing so will flag multiple locations in your code where you are playing fast and loose with data types. Fix those error to make your code more robust and less likely to throw an exception at run time.

@RickEllison, I didn't say anything about partial matches in this answer because that has nothing to do with the problem. If you're replying to a comment I made on the question, add your comment there, not here. — jmcilhinney, Apr 24 '23 at 01:33

How to properly exit a While Reader.Read routine

1 Answers1