ExecuteScalar not working in reader?

Question

When I try to get the value of 1 column out of my Database(using ExecuteScalar(), to string or integer nothing works) It crashes at the execution.

Is it because I am using ExecuteScalar() in my open SqlDataReader?

Here is my code, it crashes at iAantal:

SqlCommand get_order = new SqlCommand("SELECT * FROM Factuur WHERE ID = @ID1 OR order_id = @ID2", con);
get_order.Parameters.AddWithValue("@ID1", Session["LastOrderID"].ToString());
get_order.Parameters.AddWithValue("@ID2", Session["LastOrderID"].ToString());

SqlDataReader rdrOrder = get_order.ExecuteReader();
iAantal = 2;
while(rdrOrder.Read())
{
    from_db_producten += "<tr>";

    sHuidigeDatum = rdrOrder["besteldatum"].ToString();
    sLeverdatum = rdrOrder["leverdatum"].ToString();
    sToestelID = rdrOrder["item_id"].ToString();

    iPrijsPerStuk = Convert.ToInt32(rdrOrder["prijs"]);


    SqlCommand check_aantal_toestel = new SqlCommand("SELECT COUNT(item_id) FROM Factuur WHERE ID = @orderID1 OR order_id = @orderID2 AND item_id = @itemID", con);
    check_aantal_toestel.Parameters.AddWithValue("@orderID1", Session["LastOrderID"].ToString());
    check_aantal_toestel.Parameters.AddWithValue("@orderID2", Session["LastOrderID"].ToString());
    check_aantal_toestel.Parameters.AddWithValue("@itemID", sToestelID);


    iAantal = Convert.ToInt32(check_aantal_toestel.ExecuteScalar());

    SqlCommand get_toestel_merk = new SqlCommand("SELECT item_value FROM ItemSpecificatie WHERE item_key = @merk AND item_id = @ID", con);
    get_toestel_merk.Parameters.AddWithValue("@merk", "Merk");
    get_toestel_merk.Parameters.AddWithValue("@ID", sToestelID);

    SqlCommand get_toestel_naam = new SqlCommand("SELECT item_value FROM ItemSpecificatie WHERE item_key = @naam AND item_id = @ID", con);
    get_toestel_merk.Parameters.AddWithValue("@naam", "Naam");
    get_toestel_merk.Parameters.AddWithValue("@ID", sToestelID);

    sToestelmerk = Convert.ToString(get_toestel_merk.ExecuteScalar());
    sToestelnaam = Convert.ToString(get_toestel_naam.ExecuteScalar());

    iPrijsTotaal = iAantal * iPrijsPerStuk;
}

Is ID a numeric field? If so, don't do a `ToString()` on your parameter value. Use a Convert.ToInt function. — LarsTech, Jul 16 '14 at 16:52
@JeffKoch System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. My question answers itself.. I can't do an ExecuteScalar while in a SqlDataReader, any solutions for this? — Orion, Jul 16 '14 at 16:57
@LarsTech My queries work in SQL Management studio, but I can't get the results in a variable. — Orion, Jul 16 '14 at 16:59
See [There is already an open DataReader associated with this Command which must be closed first](http://stackoverflow.com/q/6062192/719186) — LarsTech, Jul 16 '14 at 16:59
@LarsTech I already found the MARS technique but it isn't working for me, this is my connectionstring: connectionString="Data Source=ORION;Initial Catalog=webshop;Integrated Security=True;MultipleActiveResultSets=True" — Orion, Jul 16 '14 at 17:01
Couldn't you make a second connection for your inner command? Otherwise extract data from your datareader into a datatable and iterate over the datatable instead of your reader. (Also remember to wrap your readers in a using clause, to avoid leaks) — Allan S. Hansen, Jul 16 '14 at 18:10
You might want to look into `JOIN`s in SQL - it should be possible to write a *single* query that returns all of the results in one go, rather than you querying one table, then, for each lookup value, you running other queries to obtain additional data from other tables. — Damien_The_Unbeliever, Jul 21 '14 at 13:34

score 0 · Answer 1 · answered Jul 21 '14 at 13:30

As mentioned in your question itself, you cannot perform ExecuteScalar() when you have ExecuteReader() open.

However, you can add MultipleActiveResultSets = true in the connection string provider part to achieve multiple connections to the data source.

However, this is do-able this is not recommended way for database connectivity. Because, opening multiple connections with the data source will cause additional load on the database.

ExecuteScalar not working in reader?

1 Answers1