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]

What is the structure of tblUsers? The error says there is no column named: First_Name. — Fosco
– Fosco, Commented Jul 20, 2010 at 16:53
Hi Fosco, the column names are listed in the correct order below the method code. Thanks — Frank
– Frank, Commented Jul 20, 2010 at 17:15
on a side note - be very careful concatenating user input strings or better yet parametrize the query or use an ORM tool. — Christoph
– Christoph, Commented Jul 20, 2010 at 17:23
What happens if you remove the where clause from the SQL statement? I'm wondering if something in the lecturerID is causing the SQL statement to be modified (SQL injection type stuff). Also, what happens if you use an index of 0? In your response to devio you said you tried with an index of 1. — sgriffinusa
– sgriffinusa, Commented Jul 20, 2010 at 17:34
THANK YOU EVERYONE! You are all great! If you look at the method you'll see that I foolishly declare my SqlCommand before assigning the selectQuery meaning that I was using the wrong select statements in my queries - hence the IndexOutOfRange exceptions. Thanks again. — Frank
– Frank, Commented Jul 20, 2010 at 18:10

devio · Accepted Answer · 2010-07-20 17:05:44Z

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.

answered Jul 20, 2010 at 17:05

devio

37.3k7 gold badges85 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frank Over a year ago

Hi devio, you are correct sorry for not saying it before but I have declared the string selectQuery outside of the method.

devio Over a year ago

@Frank what is the contents of selectQUery at the time you assign it to SqlCommand? You set the tblUsers query statement after creating the command

Frank Over a year ago

HAHA!! I just discovered this by stepping thru line by line for the millionth time! My foolish mistake was declaring the SqlCommand before assigning the selectQuery. I was reading data from the wrong results. Thanks for all your help!

SLaks · Accepted Answer · 2010-07-20 16:53:38Z

0

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.

answered Jul 20, 2010 at 16:53

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

1 Comment

Frank Over a year ago

Thanks Slaks, I just want to get the code functional first of all and then I intend to make it more efficient - thanks for the suggestion. It doesnt appear to be a column name though...

sgriffinusa · Accepted Answer · 2010-07-20 17:36:56Z

0

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.

edited Jul 20, 2010 at 17:36

answered Jul 20, 2010 at 16:55

sgriffinusa

4,2211 gold badge27 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Reading from a DB problem using a DataReader

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related