SQL Reading from a DB problem using a DataReader

Question

I have a table of Users (tblUsers) which contains details of University staff. I am trying to populate a text box with the names of lecturers associated with a selected module.

I am getting all UserIDs associated with a particular module, testing if the User is a lecturer, if so then I add the ID to an ArrayList.

I then iterate through this array and call the method below during each iteration passing through the current ID.

However, if you look at the method below I am using a SqlDataReader and am getting an error while reading from it on this line:

txtLecturerName.Text += myReader["First_Name"].ToString();

The error message is: 'myReader["First_Name"]' threw an exception of type 'System.IndexOutOfRangeException'

The table layout I am using is below the method code. Any help with this would be greatly appreciated, I am one cup of coffee away from putting my head through the screen.

public void outputLecturerNames(string lecturerID)
{
    // Create a new Connection object using the connection string
    SqlConnection myConnection = new SqlConnection(conStr);

    // If the connection is already open - close it
    if (myConnection.State == ConnectionState.Open)
    {
        myConnection.Close();
    }

    // 'using' block allows the database connection to be closed
    // first and then the exception handling code is triggered.
    // This is a better approach than using a 'finally' block which
    // would close the connection after the exception has been handled.
    using (myConnection)
    {
        try
        {
            // Open connection to DB
            myConnection.Open();

            SqlCommand selectCommand = new SqlCommand(selectQuery, myConnection);

            // Declare a new DataReader
            SqlDataReader myReader;

            selectQuery = "SELECT * FROM tblUsers WHERE User_ID='";
            selectQuery += lecturerID + "'";

            myReader = selectCommand.ExecuteReader();

            while (myReader.Read())
            {
                txtLecturerName.Text += myReader["First_Name"].ToString();
                txtLecturerName.Text += " ";
                txtLecturerName.Text += myReader["Last_Name"].ToString();
                txtLecturerName.Text += " , ";
            }
            myReader.Close();
        }
        catch (Exception err)
        {
            Console.WriteLine("Error: " + err);
        }
    }
}

tblUsers:

[User_ID][First_Name][Last_Name][Email_Address]

Answer 1

You've probably misspelled a column name.

In general, you should never write SELECT * FROM ....
Instead, you should select only the columns you need.

This will make your program run faster by only querying the information that you need, and can produce better error messages.

Answer 2

This error is created when the column name given is not found. If you are anything like me, you've probably checked it several times, but is the table name correct (correct database, correct schema) and is the column name correct?

http://msdn.microsoft.com/en-us/library/f01t4cfy.aspx

You might try fully qualifying the name of the table (database.dbo.tblUsers). This would ensure that you are hitting the table you think you are. Also, try and put the names of the columns into the SQL statement. If they are not correct, your SQL statement will not execute properly.

Answer 3

In your method, the variable selectQuery is not declared, and it is used as parameter to SqlCommand before it is assigned the query string on tblUsers.