ODBCDataReader dBase IV

[Guest]

Hey

I am attempting to read data from a dBase IV database. After hours of struggling, I finally got my connection string working -- so I felt I was pretty well off. I was wrong

The problem I'm having is when I perform an ExecuteReader, I get back a ODBCDataReader, which is fine. I can call the Read() method on the DataReader and it will return true. But, when I attempt to pull items from the Reader (i.e. reader["LastName"]) I get an exception: System.IndexOutOfRangeException - LastName . If I walk through the DataReader using the position Index I get values returned to me, but when I try to access using the field name, I get the OutOfRangeException.

I haven't tried using a DataAdapter -- I would prefer not to have the extra overhead..

Any thoughts or answers are greatly appreciated

Thanks
-Kevin

 

[Rob Richardson]

Kevin,

I thought a DataReader was only for sequential, forward-only access to your
data. You read the first record, then the next, then the next, and so on.
I don't think it supports random access, which is what you're trying to do.
From what little I know, I'm afraid you're going to have to use an Adapter.

Of course, I could be wrong, and I look forward to corrections, since I'm
pretty new at this also.

Good luck!

Rob

 

[Rob Richardson]

Kevin,

I'm sorry. I think I understand a bit better now. You are asking about
retrieving a particular field from the current record, rather than selecting
which record you are looking at in your data set.

I'm afraid I don't have any suggestions for you.

Rob

 

[Paul Clement]

¤ Hey,
¤
¤ I am attempting to read data from a dBase IV database. After hours of struggling, I finally got my connection string working -- so I felt I was pretty well off. I was wrong.
¤
¤ The problem I'm having is when I perform an ExecuteReader, I get back a ODBCDataReader, which is fine. I can call the Read() method on the DataReader and it will return true. But, when I attempt to pull items from the Reader (i.e. reader["LastName"]) I get an exception: System.IndexOutOfRangeException - LastName . If I walk through the DataReader using the position Index I get values returned to me, but when I try to access using the field name, I get the OutOfRangeException.
¤
¤ I haven't tried using a DataAdapter -- I would prefer not to have the extra overhead...
¤
¤ Any thoughts or answers are greatly appreciated.

Unless there is no such column name, I can't think of why this would happen. Do you have a snippet
of code?

Here is an example that uses the Jet OLEDB and dBase ISAM driver:

String ConnectionString;

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=e:\\My Documents\\dBase;Extended Properties=dBase IV";
System.Data.OleDb.OleDbConnection dBaseConnection = new System.Data.OleDb.OleDbConnection();
dBaseConnection.ConnectionString = ConnectionString;
dBaseConnection.Open();

System.Data.OleDb.OleDbCommand dBaseCommand = new OleDbCommand();
dBaseCommand.CommandText = "SELECT * FROM MyDBase";
dBaseCommand.Connection = dBaseConnection;
System.Data.OleDb.OleDbDataReader dBaseDataReader =
dBaseCommand.ExecuteReader(CommandBehavior.SequentialAccess);

while (dBaseDataReader.Read())
{
Console.WriteLine(dBaseDataReader["Column1"]);
Console.WriteLine(dBaseDataReader["Column2"]);
Console.WriteLine(dBaseDataReader["Column3"]);
}

dBaseConnection.Close();


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Guest]

Paul

Below you will find a code snippit. Now, one thing I was thinking about as I was gathering this snippit, I am storing the reader in an IDataReader type instead of the ODBCDataReader type. I am going to go ahead and post this code, but I will try to change the type to an ODBCDataReader. I will let you know what happens. -- My code is relatively the same, I simply have a different ConnectionString..

[Code
ConnectionString: DRIVER={Microsoft dBase Driver (*.dbf)};DBQ=C:\Documents and Settings\ghantousk\Desktop\Ghantous\DATAFILE;DefaultDir=C:\Documents and Settings\ghantousk\Desktop\Ghantous;DriverId=277;MaxBufferSize=2048;PageTimeout=5

Odbc.OdbcConnection dbConn = new Odbc.OdbcConnection(ConnectionString)
Odbc.OdbcCommand dbCmd = new Odbc.OdbcCommand("SELECT * FROM User WHERE LTrim(ID) = @ID",dbConn)
IDataReader dbReader

dbCmd.Parameters.Add("@ID", "1234")

_connection.Open(); //Open Connectio
dbCmd.Prepare(); //Pre-Compile Quer
dbReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)

if(dbReader.Read()
Response.Write(dbReader["LastName"].ToString())

dbConn.Close(

Thanks
-Kevin

 

[Paul Clement]

¤ Paul,
¤
¤ Below you will find a code snippit. Now, one thing I was thinking about as I was gathering this snippit, I am storing the reader in an IDataReader type instead of the ODBCDataReader type. I am going to go ahead and post this code, but I will try to change the type to an ODBCDataReader. I will let you know what happens. -- My code is relatively the same, I simply have a different ConnectionString...
¤
¤

¤ ConnectionString: DRIVER={Microsoft dBase Driver (*.dbf)};DBQ=C:\Documents and Settings\ghantousk\Desktop\Ghantous\DATAFILE;DefaultDir=C:\Documents and Settings\ghantousk\Desktop\Ghantous;DriverId=277;MaxBufferSize=2048;PageTimeout=5;
¤
¤ Odbc.OdbcConnection dbConn = new Odbc.OdbcConnection(ConnectionString);
¤ Odbc.OdbcCommand dbCmd = new Odbc.OdbcCommand("SELECT * FROM User WHERE LTrim(ID) = @ID",dbConn);
¤ IDataReader dbReader;
¤
¤ dbCmd.Parameters.Add("@ID", "1234");
¤
¤ _connection.Open();		//Open Connection
¤ dbCmd.Prepare();		//Pre-Compile Query
¤ dbReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection);
¤
¤ if(dbReader.Read())
¤     Response.Write(dbReader["LastName"].ToString());
¤
¤
¤ dbConn.Close()

I don't see anything out of the ordinary in your code. Changing to the IReader interface definition
probably won't make any difference.

You may want to set a breakpoint in your code and then take a look at the metadata under your
DataReader (dbReader) in the Locals windows. There should be a node under metadata for each column
that contains a number of properties, one of which is columnName.


Paul ~~~ [email protected]
Microsoft MVP (Visual Basic)

 

[Guest]

Paul,

I looked at the metadata property and there is nothing defined in that property. But here is some intersting things.

I have a function performing the DataReader Query -- so, when I go within the function that is making the call, it is using a variable of type ODBCDataReader. I can do the <reader>[<fieldName>] no problem! - But when the variable is of type IDataReader I loose that functionality. Strange huh?! -- I don't understand why.

I haven't gone to the trouble of using reflection to see if that fixes the problem, but I would think it would still work.

Your thoughts,
-Kevin

 

[Paul Clement]

¤ Paul,
¤
¤ I looked at the metadata property and there is nothing defined in that property. But here is some intersting things.
¤
¤ I have a function performing the DataReader Query -- so, when I go within the function that is making the call, it is using a variable of type ODBCDataReader. I can do the <reader>[<fieldName>] no problem! - But when the variable is of type IDataReader I loose that functionality. Strange huh?! -- I don't understand why.
¤
¤ I haven't gone to the trouble of using reflection to see if that fixes the problem, but I would think it would still work.

IDataReader represents only a small part of the interface for the ODBCDataReader. I think you're
losing some of the functionality by not working with the full implementation.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Guest]

Paul

Sorry I didn't get back to you until today. I ment to post this on Thursday but I got side tracked.

After looking at my code again, I resolved the problem. The issue was this

I declare the Command Object and Reader Object.
1) I call ExecuteReader from the Command Objec
2) Verify that a reader was returne
3)**Dispose of the Command Object [.Dispose()]**
4) Return the Reader Objec
Note: I maintain the connection even after disposing of the Command Object

I have used the same procedure with SQL and OleDb with no problem, but when I called Dispose on the ODBC Command Object it must (some how) break the Reader and cause it to not to be able to find the Db fields

After removing the Dispose call everything worked great! Thanks for all of your help

-Kevin