I am having difficulties using OleDbDataReader.

At the head of my form I have :-
Imports System
Imports System.Data
Imports System.Data.OleDb

In my code I have the statement :-
Dim reader As OleDbDataReader

When I try to use the reader, it doesn't work - Fatal error!
When I step through my program, the debugger, jumps over the OleDbDataReader statement without executing it.
When I then place my cursor over 'reader' it shows it as 'nothing'.

A simple question. Why and how do I fix this?
PS : I have no problems with OleDbDataAdapter!
CaptainBazAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JackOfPHCommented:
Did you try this?


reader = cmd.executeReader
JackOfPHCommented:
Can you show us the code that triggers the error?
CaptainBazAuthor Commented:
Yes I have the statement reader = cmd.executeReader in a later part of my code. This is where the program crashes!

The full code is as follows :-

    Public Function GetRecord(ByVal GblDataSet As String, ByVal GblIndexKey As String, ByVal GblFound As Boolean, ByVal GblRecno As Int32) As VariantType
        Dim connection As New OleDbConnection(FoxProConnection)
        connection.Open()
        Dim adapter As New OleDbDataAdapter
        Dim reader As OleDbDataReader                                         'This is the problem statement!!
        Dim command As New OleDbCommand("", connection)
With command
            .CommandText = "EXECS(" & _
                           "[SET NEAR ON]+Chr(13)+" & _
                           "[USE " & GblDataSet & " AGAIN SHARED IN 0]+Chr(13)+" & _
                           "[SEEK '" & GblIndexKey & "' IN operators]+Chr(13)+" & _
                           "[SELECT 0]+Chr(13)+" & _
                           "[CREATE CURSOR SeekResults (lfound L, nrecno I)]+Chr(13)+" & _
                           "[INSERT INTO SeekResults VALUES (FOUND('" & GblDataSet & "'), RECNO('" & GblDataSet & "'))]+Chr(13)+" & _
                           ")"
            .CommandText = .ToString
        End With
        reader = command.ExecuteReader                                             'Here is the crash!!!
        While reader.Read
            GblFound = reader.GetBoolean(0).ToString
            GblRecno = reader.GetInt32(1).ToString
        End While

(The command text is a series of FoxPro commands designed to access an indexed DataSet. While it is syntactically correct, I don't think it will work just yet. I am supposedly in the process of getting it all working, so don't take any notice of it).

The problem (crash) occurs at the reader = ...... statement.
An explanation of the fatal error says "Expression evaluator failed. Microsoft OLE DB Provider for Vsual FoxPro".
This ambiguous explanation could infer that the program crashed trying to evaluate the FoxPro commands, but I don't see that as a possibility as the debugger steps over the Dim reader statement long before it gets to the FoxPro command and if I place my cursor over the reader = statement immediately before the statement executes, it shows reader as 'nothing'. (Not so adapter, it shows :-
adapter {System.Data.OleDb.OleDbAdapter})
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Wayne Taylor (webtubbs)Commented:
Hi CaptainBaz,

The problem is most certainly due to theFoxPro command.

The 'reader' variable is always going to be 'nothing' until it is set to something, which is done at the "reader = command.ExecuteReader" line.

I don't know enough about FoxPro to help with the command. I'll add the FoxPro zone to the zones this question is posted in..

Regards,

Wayne
tusharkanvindeCommented:
Could you copy and paste the value of .CommandText here. That is the value it gets after the command
.CommandText= "EXECS(" & _ ......

Also I could not understand
            .CommandText = .ToString
But then I am a VFP guy and that statement might be OK.
Wayne Taylor (webtubbs)Commented:
tusharkanvinde makes a very good point. I see no need for the line...

    .CommandText = .ToString

Remove it and see what happens.

Wayne
CaptainBazAuthor Commented:
I agree with the .CommandText = .ToString statement being suspicious.
I put that statement in there because I was told to do so.
I have commented it out. It makes no difference!

I agree that the details about the error tend to indicate that the problem is in interpreting the FoxPro commands, but I believe this is a result not a cause. I go back to the problem. Why does the program skip the Dim reader .......... statement?
I have changed (simplified) the FoxPro command sequence to :-
     command.CommandText = "SET NEAR ON"
doing away with all the complexity and using a highly innocuous FoxPro command that I have working in other parts of the program. Nothing changes, it still won't execute the Dim reader statement!!!

How am I executing the SET NEAR ON FoxPro command if I can't get this to work now - you ask?
Simple - eg :-

Dim command As New OleDbCommand("", connection)
With command
          .CommandText = "SET NEAR ON"
          .ExecuteNonQuery()
End With

Works fine!!!
My problem was (and I guess still is) that I couldn't do the complex sort of commands that I now need to do using the OleDbDataAdapter. I was shown some code that another person has working on his computer using the OleDbDataReader and thought my problems were solved. His code is essentially identical to mine and it definately works. Neither of us can see any difference. Why does my program refuse to execute the Dim reader As OleDbDataReader statement??

Of course, if there is a way of using OleDbDataAdapter then I will be very happy with that.
     
Wayne Taylor (webtubbs)Commented:
It does execute the "Dim reader As OleDbDataReader" line. It's just that it's got nothing to do at that point. Not until you assign that variable a OleDbDataReader object will it not equal nothing and finally be able to do something.

Try the below function and see if it works.

Wayne
    Public Function GetRecord(ByVal GblDataSet As String, ByVal GblIndexKey As String, ByVal GblFound As Boolean, ByVal GblRecno As Int32) As VariantType
        Dim connection As New OleDbConnection(FoxProConnection)
        connection.Open()
        Dim command As New OleDbCommand("", connection)
With command
            .CommandText = "EXECS(" & _
                           "[SET NEAR ON]+Chr(13)+" & _
                           "[USE " & GblDataSet & " AGAIN SHARED IN 0]+Chr(13)+" & _
                           "[SEEK '" & GblIndexKey & "' IN operators]+Chr(13)+" & _
                           "[SELECT 0]+Chr(13)+" & _
                           "[CREATE CURSOR SeekResults (lfound L, nrecno I)]+Chr(13)+" & _
                           "[INSERT INTO SeekResults VALUES (FOUND('" & GblDataSet & "'), RECNO('" & GblDataSet & "'))]+Chr(13)+" & _
                           ")"
        End With
        Dim reader As OleDbDataReader = command.ExecuteReader 
        While reader.Read
            GblFound = reader.GetBoolean(0).ToString
            GblRecno = reader.GetInt32(1).ToString
        End While

Open in new window

CaptainBazAuthor Commented:
Sage, I would really love to be telling you that your idea worked, but unfortunately no.

The program crashed on the line :-
Dim reader As OleDbDataReader = command.ExecuteReader
I simplified the command line down to the simplest statement I could :-
command.CommandText = "SET NEAR ON" No difference. I am sure it is nothing to do with the command text.
Wayne Taylor (webtubbs)Commented:
CaptainBaz

The problem is definitely due to FoxPro. Whether it's the command text, the connection, or something else, I don't know.

I did find this, but it's not very informative. It may mean something to you however.

"Expression evaluator failed (Error 67)"
http://msdn.microsoft.com/en-us/library/aa976603(VS.71).aspx

Have you tried capturing the command text, and executing it in FoxPro?

Wayne
tusharkanvindeCommented:
Captain Baz

One problem that can occur in your VFP code is in
SEEK ...... IN operators

Are you sure that the operators table is open. Or do you want to have
USE ....... AGAIN SHARED IN 0 ALIAS Operators

Actually, if you are sure that the problem is not in foxpro, you should remove it from the tag.

CaptainBazAuthor Commented:
Tusharkanvinde, I am sure the problem is not in FoxPro.
I have removed all reference to any FoxPro commands and the program still skips the Dim As OleDbDataReader statement.
Anyhow, it is too late now, I am going to bed. I will be back tomorrow and hopefully, in the mean time,  someone has a solution for me.
Regards,
Captain Baz
Wayne Taylor (webtubbs)Commented:
>>and the program still skips the Dim As OleDbDataReader statement.

It doesn't skip that line! As I've said, that line is only declaring a variable. It is NOT set to anything, therefore will not do anything. Only when the variable is assigned an object will 'reader' not equal nothing.
Wayne Taylor (webtubbs)Commented:
I suggest you read up on the DataReader object.

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.aspx

Especially the line that states....

"To create an OleDbDataReader, you must call the ExecuteReader method of the OleDbCommand object, instead of directly using a constructor."

Wayne
chiabpCommented:
You may try to recompile all source codes and run it again. This is only suggestion because I face similar problem and I solved it with it...
Olaf DoschkeSoftware DeveloperCommented:
One thing not necessarily obvious is, that you create a result cursor within the foxpro commands, but that does not make the datareader know this should be the result of your exec. You need to use SETRESULTSET("SeekResults") within the executed foxpro  script.

Bye, Olaf.
CaptainBazAuthor Commented:
Wayne,
I have read that article you referenced. I entered the example exactly as shown. Gave it a try and guess what? It crashed! Exactly the same as all the other times. That now makes 2 situations where I have used almost (or exactly) the same code as someone else only to have it crash on me though not on them (presuming the code from the example works).
2 things :-
1.   Would you like to enter the sample bit of code from that article and see what results you get? (I accept that you probably wont have access to a FoxPro database, so a different connection string can't be avoided).
2.   There is one small difference between my code and the example.  When I enter any line of code referencing OleDbDataReader, VB doesn't put the () shown on the end of the line.
For example :-
Dim reader As OleDbDataReader = command.ExecuteReader()
becomes
Dim reader As OleDbDataReader = command.ExecuteReader
and
While reader.Read()
becomes
While reader.Read
etc, etc.
I can edit the brackets in afterwards, though it makes no difference, the program still crashes.
Nonetheless, this seems like rather odd behaviour to me.

chiabp,
I will keep that option in reserve. Thankyou!

Olaf,
Your comment is duely noted and will be applied if needed when I get past this problem. Thankyou!
Wayne Taylor (webtubbs)Commented:
So, have you tried capturing the Commandtext and running it in FoxPro, to eliminate that as the problem??
Olaf DoschkeSoftware DeveloperCommented:
reviewing the foxpro code, you open a GblDataSet but then seek in a concrete alias name operators. Also you don't set any index order, so seek would fail.
I'd change it to the following code, which will seek in the opened table within the index you need to specify in "..."
.CommandText = "EXECS(" & _
             "[SET NEAR ON]+Chr(13)+" & _
             "[SELECT 0]+Chr(13)+" & _
             "[USE " & GblDataSet & " AGAIN SHARED ORDER TAG ...]+Chr(13)+" & _
             "[SEEK '" & GblIndexKey & "']+Chr(13)+" & _
             "[SELECT 0]+Chr(13)+" & _
             "[CREATE CURSOR SeekResults (lfound L, nrecno I)]+Chr(13)+" & _
                           "[INSERT INTO SeekResults VALUES (FOUND('" & GblDataSet & "'), RECNO('" & GblDataSet & "'))]+Chr(13)+" & _
                           ")"

Open in new window

CaptainBazAuthor Commented:
Olaf!
Thanks for that FoxPro code. I changed my code to it and ran the program. No difference, still crash! Nonetheless, I will use it when I get past this particular problem.

I have a specific question for you. - What does the SQL command "SELECT 0" do? I only put it in my EXECS command as I saw it in a code example given to me. I  wondered what it did.

webbtubbs! Yes I have, but with vastly simplified code. FoxPro code that I know works. ("SET NEAR ON").
It makes no difference.

I am going to start a new Project. A very simple one, with one form and one button and see if I get the same results. Wish me luck!!
Olaf DoschkeSoftware DeveloperCommented:
SELECT 0 is not the SQL-Select you know. Foxpro works with workareas, kind of handles, more like slots for opened files. SELECT 0 simply selects the next free slot, the following USE will therefore open that table in an unused slot and not close a table open in the current workarea.

Workareas otherwise can be adressed by numbers or by alias names. By openeing a tanle with "USE sometable" you can select it as the active workarea by "SELECT sometable", which also is not a shortened SQL, but simply the choice of the active workarea.

The SELECT 0 I added instead of the IN 0 you have in the USE, will select a free workarea, in which the table will be opened. If you don't do that, USE ... IN 0 would also open the table in the next free workarea, but it would not be the selected workarea, and therefore the SEEK would not work on it, but somewhere else.

In fact you originally do a SEEK ... IN operators, but the table "operators" is not adressed anywhere in your code. I assume you want to seek in the table you pass in by GblDataSet, with my changes this would be done.

Have you changed the "..." to the tag name you need? Without setting a sort order you will get an error from SEEK, it does only work, if an index is set as the active order of the active workarea, as my code does not specify IN alias with the SEEK command. Without knowledge of the TAG NAME (index name) you can't SEEK on a table.

Bye, Olaf.
CaptainBazAuthor Commented:
Olaf,
I think I understand all that. Thank you.
My use of 'operators' was a typing error. You are right. I should have used GblDataSet which is a variable containing the name of the dataset I want to work on. (That's right, it is called 'operators'!!)
Yes I have placed the Index Name in your sample code as suggested.
Still nothing works!
As previously stated, I started a new Project and attempted to run the snippet of code given in the example section of the article in MSDN called "OleDbDataReader Class".
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.aspx
No difference, it too crashed. I kept everything to the most simplistic I possibly could - the full code is shown below.
--------------------------------------------------------------------------------------
Option Explicit On

Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionstring As String = "Provider=VFPOLEDB.1;Data Source=C:\Phiteq\TCB\Data\TCB.dbc;"
        Dim querystring As String = "SET NEAR ON"
        Using connection As New OleDbConnection(connectionstring)
            Dim command As New OleDbCommand(querystring, connection)
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader
            While reader.Read
                Console.WriteLine(reader(0), ToString())
            End While
        End Using
    End Sub
End Class
-------------------------------------------------------------------------------------
What more can I do? It seems to work for everyone else but me! (Don't worry about the :-
While reader.Read
It never gets that far!
I am still intriged as to why I don't get any double brackets after command.ExecuteReader and reader.Read as other people appear to.
Regards,
Barry
Olaf DoschkeSoftware DeveloperCommented:
I'd start by trying some SQL instead of seomthing like SET NEAR ON. That's just changing a setting, it does not produce a result, which I think the OleDbDataReader would expect and need to work. Remember I mentiones SetResultSet().

Start trying useing some oldstlye methods via adodb.connection/recordset.

If that does not work you coudl try using the Sedna addons to Foxpro, which include one Visual Studion Addon: VS 2005 Extension for VFP or short DDEX (Data Designer Extension) giving you a DDEXProvider for VFP.

http://www.codeplex.com/VFPX/Release/ProjectReleases.aspx?ReleaseId=10228


OleDbConnection oleDbConnection1 = new OleDbConnection("Provider=VFPOLEDB.1;" + 
      "Data Source=C:\\myVFPDatabase.DBC;");
oleDbConnection1.Open();
OleDbRecordset oleDbRecordset1 = new OleDbRecordset
OleDbRecordset1 = oleDbConnection1.Execute(...SQL...)

Open in new window

CaptainBazAuthor Commented:
Olaf,
I will have a close look at the DDEX Provider as you suggested. I am not too hopeful and it is not the way I feel I should have to go. Also, I don't have a recent copy of Visual Studio.
The reason I used SET NEAR ON is that it was so simplistic and couldn't possibly have any syntactical or operational reason for not working, thus being a source of my problem. I wanted to eliminate FoxPro as part of the problem. Also, I have tried using simple commands that require some sort of result. No change, nothing works.
It is all getting too hard, I am going to give it all a rest for a while. I will come back to this later. (Have a talk with a few people as well). If you have any ideas and feel inclined to pass them on to me, they will be appreciated. Though it might be a week or so before I respond.
Regards,
Barry
CaptainBazAuthor Commented:
Its all too hard!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.