How do I create variable and accessing value from database?

Question

I have access database and oledb connection like this:

OleDbConnection Connection;
Connection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" +
               Server.MapPath("~/db.mdb"));
OleDbCommand Command;
Command = new OleDbCommand("SELECT FTPTARIHARALIGI FROM Table1 WHERE ID = 1", Connection);
Connection.Open();
OleDbDataReader reader = Command.ExecuteReader();

Select command returns just an int value, and I need to access this value to variable.

I tried:

int test = reader.GetInt32(0);

Return: No data exists for the row/column.

But data exists and select command works.

How can I do this?

Answer 1

OleDbDataReader start out before the first row. If you don't call OleDbDataReader.Read method, you never reach the first row.

From OleDbDataReader.Read Method;

The default position of the OleDbDataReader is before the first record. Therefore, you must call Read to start accessing any data.

And use using statement to dispose your OleDbConnection, OleDbCommand and OleDbDataReader.

using(OleDbConnection Connection = new OleDbConnection(conString))
using(OleDbCommand Command = Connection.CreateCommand())
{
    Command.CommandText = "SELECT FTPTARIHARALIGI FROM Table1 WHERE ID = 1";
    using(OleDbDataReader reader = Command.ExecuteReader())
    {
        if(reader.Read())
        {
            int test = reader.GetInt32(0);
        }  
    }
}

If you wanna get just one cell of your data, ExecuteScalar is a better option. It returns the first column of the first row as an object.

using(OleDbConnection Connection = new OleDbConnection(conString))
using(OleDbCommand Command = Connection.CreateCommand())
{
    Command.CommandText = "SELECT FTPTARIHARALIGI FROM Table1 WHERE ID = 1";
    Connection.Open();
    int test = (int)Command.ExecuteScalar();
}