DataReader throws exception

[jamie21cor]

Hi, I was wondering if anyone could help me with a DataTable problem. I am working in ASP.NET C#, The code that I have written is listed below. The moSqlDataReader can be viewed when in debug mode and the record that is asked for is seen, but when the reader is called into the DataTable using the .Load() function the DataReader shows a thrown exception of System.InvalidOperationException in the Depth and FieldCount properties when viewed in Debug mode Code: msUserName = Request.QueryString.Get("username"); LabelUserName.Text = msUserName; UserName.Value = msUserName; moMembershipUser = Membership.GetUser(msUserName); msEmailAddress = moMembershipUser.Email; moSqlConnection = new SqlConnection(); moSqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AppSqlServer"].ConnectionString; moSqlCommand = new SqlCommand(); moSqlCommand.CommandText = "SELECT * From tblUserInfo WHERE UserID = @UserID "; moSqlCommand.CommandType = CommandType.Text; moSqlCommand.Connection = moSqlConnection; // UserID Parameter moSqlParameterUser = new SqlParameter(); moSqlParameterUser.ParameterName = "@UserID"; moSqlParameterUser.SqlDbType = SqlDbType.UniqueIdentifier; moSqlParameterUser.Direction = ParameterDirection.Input; moSqlParameterUser.Value = new Guid(moMembershipUser.ProviderUserKey.ToString()); moSqlCommand.Parameters.Add(moSqlParameterUser); moSqlCommand.Connection.Open(); moSqlDataReader = moSqlCommand.ExecuteReader(CommandBehavior.CloseConnection); moDataTable = new DataTable(); moDataTable.Load(moSqlDataReader); if (moDataTable.Rows.Count == 0){ msFirstName = ""; msLastName = ""; }else{ moDataTable.PrimaryKey = new DataColumn[] { moDataTable.Columns["UserID"] }; msFirstName = moDataTable.Rows.Find("FirstName"); msLastName = moDataTable.Rows.Find("LastName"); } FirstName.Text = msFirstName; LastName.Text = msLastName; EmailAddress.Text = msEmailAddress; moDataTable.Dispose(); moSqlCommand.Dispose(); moSqlConnection.Dispose(); I dont understand why the application is returning this, I have checked the internet and several books but have found no reason for this error. I would appreciate if anyone could help fix this. Thanks

modified on Monday, April 14, 2008 9:44 AM

[Herman T Instance]

Hi, did you run a DEBUG over this part of code? Can you place this code in a try...catch? it will tell you where something goes wrong.

[jamie21cor]

Hi, Thanks for replying. Yes I have Debugged the code in order to find out what exactly is wrong but everything seems fine up until the DataTable uses the Load function again it displays the exception of System.InvalidOperationException in the Depth and FieldCount properties when in Debug mode. I have not looked at trying the Try Catch methods yet. I will try these and see what l can find. Thanks

[Herman T Instance]

Got the Error: You us e a DataReader object instead of a DataTable object. The reader will give up data per row until there is no more to give. After loading a datareader you ask 2 things if (moSqlDataReader.HasRows) { WHILE (moSqlDataReader.Read()) { // your code here } } in your case it is better to do a DataSet dSet = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter("Query", con); adapter.SelectCommand.CommandType = CommandType.Text; etc .. adapter.Fill(dSet);

[eyeseetee]

Double check your query is correct and maybe try another simple query and load that this will eliminate the possibility that it is your query which is the problem or it will highlight what the problem is

[Soumini Ramakrishnan]

Have you declared moSqlDataReader ?