There is no row at position 0, C#

Question

Hello Fellow Developers, I am trying to return a Datatable from a function to be used for my code. Its a login table, where i have my employeeroles, and based upon the employeerole i am doing my further navigation. But the problem is whenever i try to fire the query, the query fires and returns some value in the dataTable but when i try to access it, the Error: "There is no row at position 0" pops up.

I am checking the DataTable if its null or not.

The Initialization of DatabaseConnection class and its object creation can be found here and My Function (class declaration, constructor and the function )

public class DatabaseConnection
{
    const string strConnectionString = "DATA SOURCE =dilbert.humber.ca:1521/grok; USER ID =n00993435; PASSWORD= oracle;";
    OracleConnection oracleConnection;
    OracleCommand oracleCommand;
    string query;
    OracleDataReader oracleReader;
    OracleDataAdapter oracleDataAdapter;
    OracleCommandBuilder oracleCommandBuilder;
    DataTable dataTable;

    public DatabaseConnection()
    {
        try
        {
            oracleConnection = new OracleConnection(strConnectionString);
            oracleCommand = new OracleCommand();
            oracleCommand.Connection = oracleConnection;
            oracleCommand.Connection.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.StackTrace);
        }
    }
public DataTable Login(string u, string p)
    {
        try
        {
            string query = "select employeeid, username, password, firstname, lastname, employeerole from login where username='" + u + "' and password='" + p + "'";
            oracleCommand.CommandText = query;
            oracleDataAdapter = new OracleDataAdapter(oracleCommand);
            dataTable = new DataTable();
            oracleDataAdapter.Fill(dataTable);
            return dataTable;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return null;
        }
        finally
        {
            oracleConnection.Close();
        }
    }
}

My Implementation:

private void pictureBox1_Click(object sender, EventArgs e)
    {
        if (txtUName.Text == null || txtUName.Text == "")
        {
            MessageBox.Show("Please Enter UserName");
        }
        else if (txtPassword.Text == null || txtPassword.Text == "")
        {
            MessageBox.Show("Please Enter Password");
        }
        else
        {
            string username = txtUName.Text;
            string password = txtPassword.Text;
            DatabaseConnection obj = new DatabaseConnection();
            DataTable dt = new DataTable();
            dt = obj.Login(username, password);
            if (dt == null)
            {
                MessageBox.Show("User with these credentials not found in our Database. Please contact the Admin");
            }
            else
            {
                DataRow row = dt.Rows[0];
                string role = row[5].ToString();
                if (role == "ADMIN")
                {
                    Admin madmin = new Admin();
                    this.Hide();
                    madmin.Show();
                }
            else if (role == "MANAGER")
            {
                BranchManager badmin = new BranchManager();
                this.Hide();
                badmin.Show();
            }
            else if (role == "EMPLOYEE")
            {
                MainBank obj2 = new MainBank();
                obj2.Show();
                this.Hide();

            }
            else
            {
                lblWarning.Visible = true;
                lblWarning.Text = "Invalid Login Credentials.";
                txtUName.BackColor = Color.Red;
                txtPassword.BackColor = Color.Red;
                txtUName.Focus();
            }
            }
        }
    }

The Data is properly retrieved in Oracle :

I am Clueless as to why this error is popping up.

This is the Query that is created : "select employeeid, username, password, firstname, lastname, employeerole from login where username='ADMIN' and password='ADMIN'" This one seems fine to me as i tried to run the same on the editor and it works! Is there something i am missing??

Thanks in advance!

Answer 1

Thank You Everyone! I found the answer and it was pretty silly of me.. :( after adding the insert query i didn't commit it.. hence the C# code count find it.. after the commit the query is properly retrieved! :) thanks a ton for the quick response!

All hail Developers!

Answer 2

It looks like no records from the database results in an empty DataTable, not a null one.

Check to make sure there is at least one row before you try to access it.

if (dt.Rows.Count == 0)
{
    MessageBox.Show("User with these credentials not found in our Database. Please contact the Admin");
}

You have some stuff that's defined outside of your Login method, which should probably all be defined inside it, so you can be more assured that nothing else is negatively affecting your connection to the database.

This is untested, but should work. I parameterized your query, and removed the try/catch. (If there are any exceptions, handle them in whatever event or method you're calling calling Login from.)

public DataTable Login(string u, string p)
{
    var dataTable = new DataTable();

    var connectionString = "whatever";  // change this obviously :)
    var query = "select employeeid, username, password, firstname, lastname, employeerole from login where username = :username and password = :password";

    using (var oracleConnection = new OracleConnection(connectionString))
    {
        oracleConnection.Open();

        using (var oracleCommand = new OracleCommand(query, oracleConnection))
        {
            oracleCommand.Parameters.AddWithValue("username", u);
            oracleCommand.Parameters.AddWithValue("password", p);

            using (var oracleDataAdapter = new OracleDataAdapter(oracleCommand))
            {
                oracleDataAdapter.Fill(dataTable);
            }
        }

        oracleConnection.Close();
    }

    return dataTable;
}

Answer 3

dt == null only checks if the DataTable is not null. You should check if dt.Rows.Count == 0.

if (dt == null || dt.Rows.Count == 0)

If it's zero that means your query did not return any results.

Answer 4

It is as the message says, you have no rows in your data table, so when you try and index 0 it is failing.

You need to make sure you are correctly populating your data table from your login function.

To avoid the error, you can simply check that a row has been returned.

if (dt != null && dt.Rows.Any()) {
   // it has results.
}