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();
}

Answer 2

You need a reader.read before your getint.

[EDIT] and as was pointed out in a comment, you're better off with ExecuteScalar if you're only after one value. And you'd also probably want a try- catch around your reader.GetInt32(0) to trap any possible errors...

E.g. to iterate over your results..

 if (reader.HasRows)
        {
            while (reader.Read())
            {
                int test = reader.GetInt32(0);
// Do something else
            }
        }

Answer 3

if you want only one single value you can use ExecuteScalar method

 command= new OleDbCommand("SELECT FTPTARIHARALIGI FROM Table1 WHERE ID = 1", Connection);
 command.Connection.Open();
 var value=command.ExecuteScalar();
 connection.Close();

now value variable has the value that you want