SqlHelper.ExecuteReader() throws an exception when no results found

Question

I have an SqlDataReader that is declared like this:

Dim myReader As SqlDataReader
myReader = SqlHelper.ExecuteReader(ConnectionString, "storedProcedure1", CInt(myTextBox.Text))

Later I use the results like this:

If myReader.HasRows Then
    While myReader.Read()
            Row = Table1.NewRow()
            Row.Item("REF") = myReader.GetString(0)
            Row.Item("CD") = myReader.GetString(1)
            Row.Item("NAME") = myReader.GetString(2)
            Row.Item("KEY") = myReader.GetDecimal(3)
            Row.Item("STRING") = myReader.GetString(0) & " - " & myReader.GetString(1) & " - " & myReader.GetString(2).ToString().Replace("'", "") & " - " & myReader.GetString(4).ToString().Replace("'", "")

            Table1.Rows.Add(Row)

            'Fill Drop Down
            drpMenu.Items.Add(New ListItem(myReader.GetString(0) & " - " & myReader.GetString(1) & " - " & myReader.GetString(2).ToString().Replace("'", "") & " - " & myReader.GetString(4).ToString().Replace("'", "")))
    End While
End If

myTextBox is a textbox that the user enters a possible location number that gets searched for using the stored procedure. If the user enters a valid location number, this works great and I have no problems. If they enter a non-existent location number, I get an exception:

System.IndexOutOfRangeException: Index was outside the bounds of the array.

I would think that the If myReader.HasRows line would keep me from trying to read and manipulate results that don't exist but there must be something I'm missing. myTextBox is already being validated elsewhere in the code to make sure the user typed in an integer without any wacky characters so bad input doesn't seem to be the problem either.

How do I find out whether the location number exists before calling SqlHelper.ExecuteReader()? Or maybe the better question is how do I gracefully handle this exception and tell the user the location wasn't found?

EDIT: Here's the stored procedure.

ALTER PROCEDURE [dbo].[storedProcedure]
    -- Add the parameters for the stored procedure here
    @MBR as  integer
AS
BEGIN 
    EXEC ('{CALL RM#IMLIB.spGETLOC( ?)}', @MBR) at AS400
END

EDIT #2: When I run the stored procedure in SMS and pass a valid location, it returns what I expect. If I pass an invalid location number, it returns nothing.

Are you doing a `Split` somewhere? This is common for getting an `IndexOutOfRangeException`. Otherwise, please post more code as alybaba requested (SQL and the code where the exception is occuring) — InbetweenWeekends, Jul 02 '15 at 15:43
If you run the stored proc in SMS, does it return any results? My guess would be `yes`. — Rick S, Jul 02 '15 at 15:44
@Sam, the code you have provided doesn't include an array. Where are you declaring or using an array? Is it inside `'do stuff` ? — InbetweenWeekends, Jul 02 '15 at 15:50
@RickS - I updated the OP. Passing an incorrect location # returns nothing. — TheIronCheek, Jul 02 '15 at 15:58
If the stored proc is returning what you expect, the next step is to step through your C# code and see what's happening. Also, it would be helpful to see some of the code where you have `do stuff`. — Rick S, Jul 02 '15 at 16:00
@onskee - I'm not using any arrays in there but I am adding rows to a DataTable based on the results. — TheIronCheek, Jul 02 '15 at 16:02
"EDIT #2" is your issue most likely.. It should return a result (all the columnnames), but no rows in the result. Try the #tempTable trick I mention in my response. — granadaCoder, Jul 02 '15 at 17:46

granadaCoder · Answer 1 · 2015-07-02T18:42:09.843

First. create a local variable...and cast the textbox value to the local variable...to make sure that isn't the error.

dim myValue as Int32
myValue = '' convert textbox value to an int.

Second...typically, my datareader code looks like this.

    If (Not ( reader Is Nothing) ) then
        If reader.HasRows Then 
            Do While reader.Read()
                Console.WriteLine(reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop 
        End If
    End If

(You'll have to adjust the GetInt32 or GetString and the ordinal number to your specific case of course).

My guess is that your child-procedure (RM#IMLIB.spGETLOC) has logic in it that does NOT return a row if there isn't a match.

You can still return a result....that has no rows in it. ** (Read that again).

For example

Select ColA, ColB from dbo.MyTable where 0=1

This will return a result, with no rows. That is different from not returning a(ny) select statement..(Read that again)

My guess is that this little nuance is where you get an issue.

APPEND:

If you cannot change the child-stored procedure....

Create a #TempTable... Populate the #TempTable with the child-stored procedure. Do a select from the #TempTable.

Here is a generic example:

    IF OBJECT_ID('tempdb..#TempOrders') IS NOT NULL
    begin
            drop table #TempOrders
    end


    CREATE TABLE #TempOrders
    ( 
        ColumnA int
      , [ColumnB] nchar(5)

    )

/* Note, your #temp table must have the exact same columns as returned by the child procedure */

INSERT INTO #TempOrders ( ColumnA, ColumnB )
exec dbo.uspChildProcedure ParameterOne

Select ColumnA, ColumnB from #TempOrders






    IF OBJECT_ID('tempdb..#TempOrderDetails') IS NOT NULL
    begin
            drop table #TempOrderDetails
    end

I don't have the ability to alter the stored procedure in any way... one of those company checks and balances things. I tried creating a local variable and casting the textbox value to an integer. I also tried adding your `If (Not ( reader Is Nothing) ) Then` line to my code, both with no change. — TheIronCheek, Jul 02 '15 at 16:16
If you're right about the stored procedure, how can I work around that? — TheIronCheek, Jul 02 '15 at 16:20

score 1 · Accepted Answer · edited Jul 02 '15 at 18:52

1

I finally figured out my issue.

Later in my code, I had a DataRow array that was empty if the location couldn't be found. I was getting the exception because it was trying to grab an Item from array(0) which makes complete sense.

I missed it initially because I thought it was a DataRow, not a DataRow array. Man, I hate fixing up code I didn't write...

edited Jul 02 '15 at 18:52

John Saunders

160,644
26
247
397

answered Jul 02 '15 at 18:34

TheIronCheek

1,077
2
20
50

SqlHelper.ExecuteReader() throws an exception when no results found

2 Answers2