Solved

Why is 'If reader1.HasRows = False Then Stop' the Boolean Value doesn't get updated after Reader.Read

Posted on 2008-06-12
23
1,525 Views
Last Modified: 2013-11-11
reader1.read sucessfully reads ea row created with the OLESelect Statement, in the Code Loop.

However, when the last reader1 row is returned/read, the reader1.HasRows BOOLEAN Value shows 'TRUE'.  Then instead of STOP line of being executed, in the Code,  the next line of code abends w the error msg: Invalid Operations Exception was unhandled: No data exists for row/column.

I have also tried to use the boolean reader1.NextResults = False, to detect an EOF/NO ROWS, however the circumstance occurs.  boolean value says 'TRUE', when the next reader1 process/ethod will cause a program exception w same error.

My specific questions, is how can I determine/detect when the last row in reader1 has been reached in the code logic.

A side questions, is how can I specifically see what reader.read record values and what the row/record #/postion in the dataset is, using VB.NET IDE.  Specifically, when I look at the 'locals' pane for information on reader1.  I get all sorts of Field Properties???, such as DEPTH/FIELD COUNT/IsClosed/ITEM ect ect.  But if I expand a specific Field, I can't find any specific useful detail?  Such as how many records are in the reader buffer, which record in the buffer, is  the active record?  Or how to display the record/row information in the IMMEDIATE pane of the IDE.  For instance '?reader1.read'  to display current active row/record or ?reader1.count.

Any assistance, wil be sincerely appreciated.  Thanks

Mike
ConTble.Open()
        ConTbleR1.Open()
        'Debug.Print(ConTble.State)
 
        Dim OLESelect As String = "SELECT [NISADDRBK#], ENAME, NMNTH1, TMNTH1, NMNTH2, TMNTH2 FROM TMECrdRecs"
 
        Dim OLECmndR1 As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmndR1.CommandText = OLESelect
        Dim OLECmnd As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmnd.CommandText = OLESelect
 
        Dim reader1 As OleDb.OleDbDataReader
        reader1 = OLECmndR1.ExecuteReader
        reader1.Read()
        strNISnum = reader1("NISADDRBK#").ToString
        strNISnameVal = reader1("ENAME").ToString
        strNISnumVal = strNISnum
 
        lstTMERecKeys.Add(strNISnum, strNISnameVal)
        lstTMERecVals.Add(strNISnameVal, strNISnum)
 
        While reader1.HasRows
 
            If strNISnumVal <> strNISnum Then
                lstTMERecKeys.Add(strRecKey, strRecVal)
                lstTMERecVals.Add(strRecVal, strRecKey)
                strNISnumVal = strNISnum
            Else
                Do While strNISnum = strNISnumVal And reader1.HasRows
 
                    If reader1.HasRows = False Then Stop
 
                    initRec = reader1("NISADDRBK#").ToString & " " & reader1("ENAME").ToString
                    strRecKey = reader1("NISADDRBK#").ToString
                    strRecVal = reader1("ENAME").ToString
                    strNISnum = reader1("NISADDRBK#").ToString
                    If reader1.HasRows Then reader1.Read()
                    If reader1.HasRows = False Then Stop
 
                Loop
            End If
        End While

Open in new window

0
Comment
Question by:mhotto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 4
23 Comments
 
LVL 11

Expert Comment

by:CMYScott
ID: 21775195
try

While reader1.Read

.... your code here...

End While

without trying it, I'm not sure of the exact syntax of your other questions..

? reader1.Columns.Count - should show you how many columns
? reader1("yourFieldName") - should show you the value in the field for the current row


0
 
LVL 21

Expert Comment

by:naspinski
ID: 21775509
CMYScott is correct, you need to get rid of the hasrows:
        While reader1.Read 
            If strNISnumVal <> strNISnum Then
                lstTMERecKeys.Add(strRecKey, strRecVal)
                lstTMERecVals.Add(strRecVal, strRecKey)
                strNISnumVal = strNISnum
            Else
                Do While strNISnum = strNISnumVal And reader1.HasRows
 
                    If reader1.HasRows = False Then Stop
 
                    initRec = reader1("NISADDRBK#").ToString & " " & reader1("ENAME").ToString
                    strRecKey = reader1("NISADDRBK#").ToString
                    strRecVal = reader1("ENAME").ToString
                    strNISnum = reader1("NISADDRBK#").ToString
                Loop
            End If
        End While

Open in new window

0
 
LVL 21

Expert Comment

by:naspinski
ID: 21775511
that will automatically bail out when it is out of rows
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mhotto
ID: 21778393
Thanks for response.  Using your advice, I've removed all instance to 'HasRows' and ran code.

I still get same error @ same line of code, which I think happens, cause I'm trying to use the reader w no records inside the WHILE reader1.read LOOP?

I could use some 'TRY CATCH' EXCEPTION LOGIC.  But that's not real good coding practice for handling record processing(for instance empty file).

So, I'm still @ square1 here, but if you keep pitching some more solution ideas, I'd be most appreciative
 ConTble.Open()
        ConTbleR1.Open()
        'Debug.Print(ConTble.State)
 
        Dim OLESelect As String = "SELECT [NISADDRBK#], ENAME, NMNTH1, TMNTH1, NMNTH2, TMNTH2 FROM TMECrdRecs"
        Dim OLECmndR1 As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmndR1.CommandText = OLESelect
        Dim OLECmnd As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmnd.CommandText = OLESelect
 
        'Dim reader1 As OleDb.OleDbDataReader = OLECmnd.ExecuteReader(CommandBehavior.SingleRow)
        Dim reader1 As OleDb.OleDbDataReader
        reader1 = OLECmndR1.ExecuteReader
        reader1.Read()
        strNISnum = reader1("NISADDRBK#").ToString
        strNISnameVal = reader1("ENAME").ToString
        strNISnumVal = strNISnum
 
        recCntr = recCntr + 1
        Debug.Print(recCntr)
 
        lstTMERecKeys.Add(strNISnum, strNISnameVal)
        lstTMERecVals.Add(strNISnameVal, strNISnum)
 
        While reader1.Read
            Debug.Print(recCntr)
 
            If strNISnumVal <> strNISnum Then
                lstTMERecKeys.Add(strRecKey, strRecVal)
                lstTMERecVals.Add(strRecVal, strRecKey)
                strNISnumVal = strNISnum
            Else
                Do While strNISnum = strNISnumVal
 
 ABENDS on this LINE==> initRec = reader1("NISADDRBK#").ToString & " " & reader1("ENAME").ToString
                    strRecKey = reader1("NISADDRBK#").ToString
                    strRecVal = reader1("ENAME").ToString
                    strNISnum = reader1("NISADDRBK#").ToString
                    reader1.Read()
 
                    'dblLoopCntr = dblLoopCntr + 1
                    'Debug.Print(dblLoopCntr)
                    recCntr = recCntr + 1
                    Debug.Print(recCntr)
                Loop
            End If
        End While
 
STOPEXIT:
        strNISnumVal = strNISnum

Open in new window

0
 
LVL 21

Expert Comment

by:naspinski
ID: 21778411
I noticed you posted this in the LINQ section, would you be open for a LINQ solution?  And if so, do you know how to set up a DBML?
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21778448
the reader is being 'read' on the while line - so there should be no reason to read it again inside the Do While - if you must, then you need to change the condition on the Do While to determine if the reader has any remaining rows.

since I'm not completely sure I understand the logic - below is my best attempt at a quick fix
ConTble.Open()
        ConTbleR1.Open()
        'Debug.Print(ConTble.State)
 
        Dim OLESelect As String = "SELECT [NISADDRBK#], ENAME, NMNTH1, TMNTH1, NMNTH2, TMNTH2 FROM TMECrdRecs"
        Dim OLECmndR1 As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmndR1.CommandText = OLESelect
        Dim OLECmnd As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmnd.CommandText = OLESelect
 
        'Dim reader1 As OleDb.OleDbDataReader = OLECmnd.ExecuteReader(CommandBehavior.SingleRow)
        Dim reader1 As OleDb.OleDbDataReader
        reader1 = OLECmndR1.ExecuteReader
        reader1.Read()
        strNISnum = reader1("NISADDRBK#").ToString
        strNISnameVal = reader1("ENAME").ToString
        strNISnumVal = strNISnum
 
        recCntr = recCntr + 1
        Debug.Print(recCntr)
 
        lstTMERecKeys.Add(strNISnum, strNISnameVal)
        lstTMERecVals.Add(strNISnameVal, strNISnum)
 
        While reader1.Read
            Debug.Print(recCntr)
 
            If strNISnumVal <> strNISnum Then
                lstTMERecKeys.Add(strRecKey, strRecVal)
                lstTMERecVals.Add(strRecVal, strRecKey)
                strNISnumVal = strNISnum
            Else
                Do While strNISnum = strNISnumVal And Not(reader1.Eof)
 
                    initRec = reader1("NISADDRBK#").ToString & " " & reader1("ENAME").ToString
                    strRecKey = reader1("NISADDRBK#").ToString
                    strRecVal = reader1("ENAME").ToString
                    strNISnum = reader1("NISADDRBK#").ToString
                    reader1.Read()
 
                    'dblLoopCntr = dblLoopCntr + 1
                    'Debug.Print(dblLoopCntr)
                    recCntr = recCntr + 1
                    Debug.Print(recCntr)
                Loop
            End If
        End While
 
STOPEXIT:
        strNISnumVal = strNISnum
 

Open in new window

0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21778484
btw - if I understand your logic, you're making assumptions that your data is in a certain order.  While you may be able to make that assumption now.... it could come back to bite you.  You might want to look into pulling the data into a datatable and then using the Select method to pull matching rows - or depending on the source type of the data consider using LINQ.  DataReader is FAST, but in my opinion its not intended to do what you have it doing.
0
 
LVL 21

Expert Comment

by:naspinski
ID: 21778595
Agreed^^^
0
 

Author Comment

by:mhotto
ID: 21779963
Really appreciate all your attention. I'll try to clarify exactly what the code is trying to do relative to record processing logic.  Also, my intent was to POST this in the VB.NET or .NET Developement area.  But, I didn't click the right check box.  So it ended up in the LINQ area.  And I tried to change it ~ but it didn't happen.

I have n# of records.  n# of records have 6ea records/grp-category.  That is all n# of records are Input sorted by Col1(NISnum-IntegerString).  And ea unique NISnum in Col1, will have 6 records  w duplicate NISnum in sort order.

My code objective, is to build a 'NameValue' Collection for 1ea unique NISnum value from Reader Input.  The NISnum value is Key, ENAME is value.  So, reader1.read gets row. I store the NISnum value in variable.  Then I read next row/record.  If NISnum = Variable, READ NEXT Row/Record, LOOP WHILE =(Inside Do While Loop).  When NISnum<>Variable, ADD ITEM to 'NameValue' collection.  Move new NISnum value to variable, and execute reader1.read(the automatic outside WHILE Reader1 LOOP).

Having stepped thru the code execution, for a test record set of 18records, 3unique NISnum values.  The code works correctly for the first 2 unique NISnum values.  Only ?after/when?  row#/record 18 is processed is the when the err occurs, on this line of code.
 initRec = reader1("NISADDRBK#").ToString & " " & reader1("ENAME").ToString
and the err msg basically says there nothing to process.

So, what I want to do is check to see, if there is a row/record in reader to process, before trying to do something that causes a reader ABEND.  And prevents the last record, ADD ITEM to 'NameValue' collection, not to get updated.

That's why I was asking about how to utilize the IDE Immediate Pane or Locals pane, to see exactly what row/record was being processed when stepping thru the code.  

I'm going to study your input, more closely.  But, I wanted to make sure you all have the big picture of the what my code snippet is doing.

Hopefully, this will be of value, for more assistance with this problem

Thanks

Mike
0
 

Author Comment

by:mhotto
ID: 21780170
CMYScott:

I added your code "And Not(reader1.Eof)"  but I'm getting an IDE Syntax Error, indicationg that reader1.Eof is not member of datareader.  When I use intelli window to list a valid member, eof is not listed. But I get a big list(20+) of members, such as reader1. 'GetString', 'HasRows', 'Item', 'IsClosed', 'IsDBnull', but no '.eof'.  

I'm thinking there is something in that member list, I can use to check for EOF, but I don't know which one?

Mike
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21780409
its failing on the last row - because there's nothing to tell your inner loop that it has reached the last row

HasRows simply indicates there are rows available to the reader - it does NOT indicate there are STILL rows available based on the row position of the reader.

I could be wrong on the Eof - I though it was there.

is the source data in Access?  SQL?  Something else?

Could you post the schema for the table you are reading?
0
 
LVL 11

Accepted Solution

by:
CMYScott earned 375 total points
ID: 21780495
try this instead
ConTble.Open()
        ConTbleR1.Open()
        'Debug.Print(ConTble.State)
 
        Dim OLESelect As String = "SELECT [NISADDRBK#], ENAME, NMNTH1, TMNTH1, NMNTH2, TMNTH2 FROM TMECrdRecs"
        Dim OLECmndR1 As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmndR1.CommandText = OLESelect
        Dim OLECmnd As OleDb.OleDbCommand = ConTble.CreateCommand()
        OLECmnd.CommandText = OLESelect
 
        'Dim reader1 As OleDb.OleDbDataReader = OLECmnd.ExecuteReader(CommandBehavior.SingleRow)
        Dim reader1 As OleDb.OleDbDataReader
        reader1 = OLECmndR1.ExecuteReader
        reader1.Read()
        strNISnum = reader1("NISADDRBK#").ToString
        strNISnameVal = reader1("ENAME").ToString
        strNISnumVal = strNISnum
 
        recCntr = recCntr + 1
        Debug.Print(recCntr)
 
        lstTMERecKeys.Add(strNISnum, strNISnameVal)
        lstTMERecVals.Add(strNISnameVal, strNISnum)
 
        While reader1.Read
            Debug.Print(recCntr)
 
            If strNISnumVal <> strNISnum Then
                lstTMERecKeys.Add(strRecKey, strRecVal)
                lstTMERecVals.Add(strRecVal, strRecKey)
                strNISnumVal = strNISnum
            Else
                Do 
                    initRec = reader1("NISADDRBK#").ToString & " " & reader1("ENAME").ToString
                    strRecKey = reader1("NISADDRBK#").ToString
                    strRecVal = reader1("ENAME").ToString
                    strNISnum = reader1("NISADDRBK#").ToString
 
                    'dblLoopCntr = dblLoopCntr + 1
                    'Debug.Print(dblLoopCntr)
                    recCntr = recCntr + 1
                    Debug.Print(recCntr)
                Loop While strNISnum = strNISnumVal And reader1.Read()
            End If
        End While
 
STOPEXIT:
        strNISnumVal = strNISnum

Open in new window

0
 

Author Comment

by:mhotto
ID: 21782349
CMYScott, you ascertained correctly.  It's an ACCESS.mdb Table.  It's a FLAT SQL TABLE, that is the  SQL Table is just a list of report records ~ no KEYS, NO JOINS, nothing - just produced w VBA Code in ACCESS database. The SELECT Statement FIELD_NAMES  in the CONTbleR1 Select Statement is the Table Schema in my code snippet attached.

I was just now thinking on doing what you proposed, changing read logic.  Thanks for saving me some time.  I'll give it a try and get back to you.  

Thanks

Mike
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21782561
I can't be sure - but it looks like you are doing all of this simply to populate two listboxes.. is that correct?

0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21782805
if the answer to the above question is yes - you might want to try the code below  (I wrote it out in Notepad, so there could be some typing errors - nothing you shouldn't be able to debug pretty quickly)
ConTble.Open()
 
Dim newSelect As String = "SELECT DISTINCT [NISADDRBK#],ENAME FROM TMECrdRecs"
Dim newCommand As OleDbCommand = New OleDbCommand(newSelect,ConTble)
Dim newReader As OleDbDataReader = newCommand.ExecuteReader
Dim strRecKey As String
Dim strRecVal As String
 
While newReader.Read()
	strRecKey = newReader(0).ToString
	strRecVal = newReader(1).ToString
	
	lstTMERecKeys.Add(strRecKey,strRecVal)
	lstTMERecVals.Add(strRecVal,strRecKey)
End While
 
STOPEXIT:
	strNISnumVal = strNISnum

Open in new window

0
 

Author Comment

by:mhotto
ID: 21782834
Correct, that's the immediate goal.  However, the bigger purpose is to use this ReadRoutine, for other purposes in the future.

Your comment sounds to me like you think, I'm going to too much trouble, for such a trivial task.  However, I'd be interested in hearing your thoughts, on a better way to do that.

However, I'm only displaying 1 listbox, and the user_selected value from the NameValue Collection, will be looked up from a textbox(FIND) and highlighed in LISTBOX(if entry exists), or the LISTBOX ITEM Selected will return/display value in textbox(FIND)  And Either/Or Display for User on the Form.

Sincerely Appreciate your input.  And I'm outa here for the wkend.

Mike
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21782905
well - I like LINQ alot, but since your data is in Access, LINQ isn't going to work well for you; at least not to access the data from the database.

yes, I think you've written alot of code to do a fairly simple task.

I'd have to know a bit more about your app and the database before I could give you a strong opinion about how to do what you want to do.

Although datatables/dataset are slow compared to the datareader - it may be a much better option - especially if the number of rows is relatively small and especially if the database is not multi-user.

Another option would be to create objects to support each row and its associated fields.  This new object would need to be able to Load (SELECT), Add (CREATE), Edit (UPDATE) and Delete (DELETE).  The advantage here is that once you had each row from the database into a List (... array or some other collection) - you CAN use LINQ to query the collections for specific records or groups of records.  The problem with this approach is that you have to be extra careful if the database is multi-user as the data in the database could be changed while you are working with the objects representing the datarows.
 
0
 

Author Comment

by:mhotto
ID: 21803133
CMYScott:

You get the points!

I'd also be glad to give you some more details, on my application.  Since, I'd be interested in your detailed tech opinions, as mentioned in your last comment.  However, I don't think this venue, is where this discussion, should be developed, if you were interested in doing so.  

And since your code worked.  The Question parameters, have been met and you should get the points.

Really appreciate the time, you invested in helping me with this question.

Thanks Again

Mike
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21803203
thanks, I'm glad it all helped.
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21818833
I'm not sure if its okay to post an email address on here - if someone says its okay, I'll post my email and you can send me direct if you like

not to be 'greedy' - but are you going to award the points?

thanks
0
 

Author Closing Comment

by:mhotto
ID: 31466714
CMYScott,  Nah, you aren't being greedy.  I was just waiting for response from my last Post, concerning a follow on discussion.  I agree w you.  Posting an E_MAIL address here, not good idea.  Since EE, has our E_MAIL respective E_MAIL address.  Maybe a EE moderater has an answer. For how to initiate an discussion in different venue.  As I said, I'd be very interested in getting your technical opinion, on a better way, 'to skin this cat' in my case.

However, your code specifically solved my problem.  Specficically the 'And reader' code @ end of inside loop.  Thank you, for taking the time to help me resolve it.  As they say in 'EAST PINEY WOODS TX ~ U's Done Good'

Mike
0
 

Author Comment

by:mhotto
ID: 21825836
CMYScott,

I contacted EE Community Moderator.

Here is his response:

Hi mhotto,

Welcome to the Community Advisor Zone.

We won't facilitate this.  Your email is in your profile, so let CMYScott know that and you guys work it out from there.

The official Experts Exchange position on email is that we prefer it not be used to resolve questions because seeing the discussion between you and the Expert(s) is what gives the question value for future users.  However, if you have extended the question as far as it can go without swapping files or if the code is too sensitive to post, then email might work.  Just make sure the final "solution" is posted here and you close the question and award points.

Thanks for using Experts Exchange!

Jason1178
Community Advisor
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21825909
ok - I'll grab your email later tonight - I did read the rules later this morning myself - I think since this question is finished we're not violating the terms or even the spirit of the terms - as its just about an offline discussion you would like to have - not about any specific question.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question