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,511 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
  • 11
  • 8
  • 4
23 Comments
 
LVL 11

Expert Comment

by:CMYScott
Comment Utility
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
Comment Utility
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
Comment Utility
that will automatically bail out when it is out of rows
0
 

Author Comment

by:mhotto
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Agreed^^^
0
 

Author Comment

by:mhotto
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Accepted Solution

by:
CMYScott earned 375 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks, I'm glad it all helped.
0
 
LVL 11

Expert Comment

by:CMYScott
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
DataGridView 2 29
.NET Error 7 42
Convert string to date 3 40
Is setting this variable making a duplicate in memory 4 11
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

743 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now