SQLCommand and SqlDataReader error

Question

Invalid attempt to read when no data is present.

This error occurs on this line

string ingredientName = reader2.GetString(0);

My code:

var ingredientList = new List<Ingredient>();

SqlCommand staffCommand = new SqlCommand();

string conString = EventsUnlimited.Properties.Settings.Default.DatabaseEventsUnlimitedConnectionString;

using (SqlConnection connection = new SqlConnection(conString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("SELECT IngredientID, Quantity FROM CourseIngredients WHERE CourseID =" + courseID, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Obtains the columns from customer
                int ingredientID = reader.GetInt32(0);
                decimal quantity = reader.GetDecimal(1);

                string ingredientNameConstruct;

                SqlCommand ingredientCommand = new SqlCommand();
                string conString2 = EventsUnlimited.Properties.Settings.Default.DatabaseEventsUnlimitedConnectionString;

                using (SqlConnection connection2 = new SqlConnection(conString2))
                {
                    connection2.Open();

                    using (SqlCommand command2 = new SqlCommand ("SELECT IngredientName FROM Stock WHERE IngredientID =" + ingredientID, connection2))
                    {
                        using (SqlDataReader reader2 = command2.ExecuteReader())
                        {
                            string ingredientName = reader2.GetString(0);
                            ingredientNameConstruct = ingredientName;
                        }  
                    }
                }

                ingredientList.Add(new Ingredient(courseID, ingredientNameConstruct, ingredientID, quantity));
            }
        }
    }

    return ingredientList;
}

I am not sure as to what is causing this issue. There is data in the table and the row I am trying to read from.

This can be done with a single query if you JOIN the two tables. — Alex K.
– Alex K., Commented Mar 2, 2018 at 13:31
Your second reader isn't reading, and you also need to parameterize your queries to prevent SQL Injection. — Zack
– Zack, Commented Mar 2, 2018 at 13:33
As stated, you should use parameters. This would also be less code and easier to read if you applied a proper join in sql and used a sql command to retrieve all the information. — Igor
– Igor, Commented Mar 2, 2018 at 13:44

Ehsan Sajjad · Accepted Answer · 2018-03-02 13:45:58Z

1

You are not calling Read() method for your reader2 object which actually reads a row against the result returned, add that before actually reading column values:

using (SqlDataReader reader2 = command2.ExecuteReader())
{
      if(reader2.Read()) // this is needed
      {
          string ingredientName = reader2.GetString(0);
          ingredientNameConstruct = ingredientName;
      }
}

if you are expecting multiple rows then use while loop, and if it is always a single row to be expecting as result them you can use reader2.ExecuteScalar too as @bradbury9 mentioned in the comment:

string ingredientName = command2.ExecuteScalar()?.ToString();
ingredientNameConstruct = ingredientName;

edited Mar 2, 2018 at 13:45

answered Mar 2, 2018 at 13:32

Ehsan Sajjad

62.6k16 gold badges112 silver badges171 bronze badges

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

Comments

Cleptus · Accepted Answer · 2018-03-02 13:47:39Z

0

Call DataReader.Read Method in your reader2 DataReader to get the results.

edited Mar 2, 2018 at 13:47

Cleptus

3,5494 gold badges31 silver badges38 bronze badges

answered Mar 2, 2018 at 13:30

user1773603

Collectives™ on Stack Overflow

SQLCommand and SqlDataReader error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related