Data Read Returning (Invalid Attempt to Read when Reader is closed)
I am trying to read an XML returned by a query, I tested the query and it is returning the XML with the correct items.. but the code behind has a problem..It reads the first item correctly but then it throws the exception.. I am not sure what is wrong (The exception happens the second time it attempts to go through the WHILE loop. See code below
// Create Instance of Connection and Command Object开发者_如何转开发
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
SqlCommand myCommand = new SqlCommand("GetItemsXML", myConnection);
// Mark the Command as a SP
myCommand.CommandType = CommandType.StoredProcedure;
// Add Parameters to SP
SqlParameter parameterPortalID = new SqlParameter("@TheID", SqlDbType.Int, 4);
parameterPortalID.Value = portalID;
myCommand.Parameters.Add(parameterPortalID);
SqlParameter parameterKeywords = new SqlParameter("@myWords", SqlDbType.VarChar, 4000);
parameterKeywords.Value = myWords;
myCommand.Parameters.Add(parameterWords);
// Execute the command
myConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
string xmlOut = "";
while (reader.Read()) //SECOND ITERATION THROWS (Invalid attempt to read
// when reader is closed)
{
xmlOut = xmlOut + reader[0].ToString();
}
xmlOut = "<MyItemsList>" + xmlOut + "</ZMyItemsList>";
reader.Close();
myConnection.Close();
return xmlOut;
}
It may have something to do with the fact that the first ittereation would be reader[0] but surely the second itteration will be reader[1], therefore you want to use
reader.GetString(0)
instead of
reader[0].ToString();
Why you using reader[0].ToString();
this will fetch your first record and give eror on second iteration. Use below instead
reader["ColumnsName"].ToString();
精彩评论