IndexOutOfRangeException (C#) on OleDBDataReader with SQL Count Function

Question

Good afternoon,

I'm having a problem with a section of code I'm working on that checks to see if a dataitem of the same name already exists in a database.

The following code throws up an IndexOutOfRangeException

static int checkExists(String checkIf)
    {
        String connectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + @"ffmpegDB.mdb";
        OleDbConnection connection = new OleDbConnection(connectionString);
        connection.Open();
        OleDbCommand command = connection.CreateCommand();           

        try
        {
            command.CommandText = checkIf;

            OleDbDataReader reader = command.ExecuteReader();

            reader.Read();
            try
            {
                result = (int)reader["Result"];
            }
            catch (InvalidOperationException ioe) { }

            reader.Close();
            connection.Close();
        }
        catch (FormatException Fe)
        {}

        return result;            
    }

..on this line

result = (int)reader["Result"];

The function is executed as follows:

if (checkExists("SELECT COUNT(*) AS 'Result' FROM Files WHERE Filename ='"+fileNames[i]+"' AND File_Directory ='"+directories[i]+"'")==0)

{

}

etc..

Could this be a problem with OleDB and how it handles SQL statements? Perhaps the reader is looking for the column 'Result' literally?

I hope I've provided enough information.

Regards

Answer 1

Use,

result=(int)reader[0];

Answer 2

Alternatively, have you tried using ExecuteScalar? Skips the whole data reader thing.

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

object results = cmd.ExecuteScalar();

or

int results = (int)cmd.ExecuteScalar();

Answer 3

I think you can just try doing following..

    if (checkExists("SELECT COUNT(*) AS Result FROM Files WHERE Filename ='"+fileNames[i]+"' AND File_Directory ='"+directories[i]+"'")==0);//
reader.GetInt32(reader.GetOrdinal("Result"));

OR

    if (checkExists("SELECT COUNT(*) FROM Files WHERE Filename ='"+fileNames[i]+"' AND File_Directory ='"+directories[i]+"'")==0); //
reader.GetInt32(0);// use this

Answer 4

have you tried the SQL statement in a query analyzer?