?
Solved

I am having difficulties using OleDbDataReader.

Posted on 2008-11-12
27
Medium Priority
?
500 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:CaptainBaz
  • 9
  • 7
  • 4
  • +3
25 Comments
 
LVL 15

Expert Comment

by:JackOfPH
ID: 22947103
Did you try this?


reader = cmd.executeReader
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 22947105
Can you show us the code that triggers the error?
0
 

Author Comment

by:CaptainBaz
ID: 22947340
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})
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22947580
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
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 22947641
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.
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22947650
tusharkanvinde makes a very good point. I see no need for the line...

    .CommandText = .ToString

Remove it and see what happens.

Wayne
0
 

Author Comment

by:CaptainBaz
ID: 22947806
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.
     
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22947826
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

0
 

Author Comment

by:CaptainBaz
ID: 22948040
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.
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22948075
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
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 22948185
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.

0
 

Author Comment

by:CaptainBaz
ID: 22948474
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
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22948546
>>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.
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22948559
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
0
 

Expert Comment

by:chiabp
ID: 22948667
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...
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22949246
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.
0
 

Author Comment

by:CaptainBaz
ID: 22957837
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!
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22958120
So, have you tried capturing the Commandtext and running it in FoxPro, to eliminate that as the problem??
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22958386
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

0
 

Author Comment

by:CaptainBaz
ID: 22965833
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!!
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22971659
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.
0
 

Author Comment

by:CaptainBaz
ID: 22974046
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
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 22989445
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

0
 

Author Comment

by:CaptainBaz
ID: 22992500
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
0
 

Accepted Solution

by:
CaptainBaz earned 0 total points
ID: 23342338
Its all too hard!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

840 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