| Author |
Topic |
|
justa718
Starting Member
9 Posts |
 Posted - 2010-01-15 : 08:44:12
|
| Hello everybody, Iam confronted with a really weird issue the like of which ive never seen before. I recently developed a web application using ASP.net and launched it for my company, during the bug testing stages everything seemed fine, but once it was actually in production i started getting these weird errors that all stem from it not retrieving the correct rows.The issue is this, sometimes the queries sent to the back end only return a partial amount of the rows actualyl requested, so if a user has a page loaded thats suppose to return X number of rows, they can sometimes get X - Y number of rows, and upon refreshing the page they get X - Z number of rows, and refreshing the same exact page again they might get X number of rows they were initially suppose to see. I've never seen anything like this before so Iam stumped.I consulted some of the old developers in the company I used to work for, for assistance but when explaining and showing the problem they too were very confused with the issue.so if anyone has any idea on what might be causing this you help will be very much appreciated thank you. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-15 : 09:15:04
|
| Sounds like the connection is getting closed before its had a chance to pull all the rows.Connection pooling problem perhaps? |
 |
|
|
justa718
Starting Member
9 Posts |
Posted - 2010-01-15 : 09:24:13
|
| How could I go by checking this out, and fixing this possible issue? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-15 : 09:31:41
|
| SQL Profiler might give you some indication of whether it was getting to the end of the batch.It would help a lot of you had reproducible circumstances. Can you force it to happen with a query that returns a lot of data (perhaps when the system is "busy" too)?Packet-sniffer is probably going to be the only other way.You could perhaps log what you are rendering, and thus sending to the client, in your ASP.NET app, and then see if that is "short" too, or if that is everything and the client is missing some. Then its a transmission problem downstream from ASP.NET / IIS to the client and you can stop worrying about SQL Server.My doubt about that is the HTML would not be fully rendered - it would stop in the middle of a <TABLE> for example, and therefore probably wouldn't render properly. A VIEW SOURCE on the client's browser (next time it happens) would confirm-or-deny that, I suppose. |
|
|
|
justa718
Starting Member
9 Posts |
Posted - 2010-01-15 : 09:59:40
|
| Iam highly certain that it has to deal with something happening with the SQL query because for example some of my classes return default string values when no data is found, which then is rendered on the page, so we have had instance where people tell me they see a list of of employees who's name are "Not Found" which is one example that leads me to believe the results are being returned as they should, because a refresh of the same page could potentially return a list where everybodie's name is filled in properly |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-15 : 10:41:03
|
| OK, so my immediate suggestions are to see if SQL profiler will give you a clue (I have my doubts, but it might) and have your ASP.NET app log some stuff to help you discover what it is "not" getting on the occasions when you subsequently work out, in a debugging session, that it should have had more data. |
|
|
|
justa718
Starting Member
9 Posts |
Posted - 2010-01-15 : 12:46:17
|
| revolve around the issue was that, I went to someones computer and it said cannot open the reader since its already closed, reffering to the (SqlDataReader) class in c#, but again a refresh of the page populates it with the correct information. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-15 : 12:54:54
|
So the ASP.NET application is falling on its face under load, for some reason? Is that about the top-and-bottom of it?I still think you'll have to get it to log what it is doing to then work out when it fails, and what the state is when that happens. Well ... that's what I would do, but our App is chock=-full of logging code, so it would not be hard for me + my app  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 12:59:00
|
| it's probably a bug(s) in your C# code.as for the varying # of results, are you passing parameters in? make sure they contain the values that you expect.As for the name not found, I'm certain that this is a bug in your code. you need to examine the data type, the query and what is being passed. Are you using case sensitive collation? Are your trimming leading/trailing spaces? LEt's see the query that returns "Not Found" as well as the values that should be found.As for the cannot open data reader, this is strictly a mistake in your C# and has nothing to do with SQL Server |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-15 : 13:03:30
|
| Russell: why would RERESH work then? (or did I miss something?) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 13:08:33
|
| That solves the sqlreader issue...I would ask if that solves all of them. If it does, then I am off the mark and would suspect network connectivity problem.I've never seen an ADO(X) recordset get partially populated that I can recall. It's all or nothing seems to me. Could be though. |
|
|
|
justa718
Starting Member
9 Posts |
Posted - 2010-01-15 : 15:16:39
|
| I thought i posted this already but for some reason it doesnt show up, so I will reply againThe refresh fixes all the problems, and one weird thing i noticed was i created a debug version which logs all the errors, and i asked one of the people that works with use to try to replicate the error in this controlled environment, i was able to replicate one of the server errors while she was not, interestingly enough though the log shows up as she was the one that caused the error nto me, which leads me to believe maybe the Session Variable from IIS has some kind of data collision possibly? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 15:21:14
|
| highly doubt it.what are you putting in session? and what type of authentication are u using? |
|
|
|
justa718
Starting Member
9 Posts |
Posted - 2010-01-15 : 15:27:10
|
| Iam using SQL Authentication, and I get the persons userID using ASP.net Page.User.Identity being that on the IIS server i have the page set to require basic authentication by entering their userid and password, why owuld her name pop up as the cause of the error if the data on my screen shouldnt of had anything to do with her, that and it brought her name up the only other person using the debug environment i set up, if anything i would assume it would bring a random name out of the thousands of employees in the directory. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 15:31:11
|
| Perhaps she is logged on with your credentials?Other than that...You have multiple bugs in your C# code.IIS didn't randomly choose the wrong SID.You're going to need to debug your C#. Until then, you're going to be chasing your tail looking for network or database errors -- that I am inclined to believe don't exist at this point.Also, if you're logging on to IIS with domain credentials, I'd create AD groups and not use SQL Authentication, but that is a different matter altogether.Please don't think I'm being harsh. We're all happy to help, but I believe the issues are strictly application code from what I've read so far. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 15:36:51
|
| By the way, are you passing the logged on IIS user to the database to perhaps lookup information and/or determine custom permissions?If so, have a close look in that table and make sure all users are properly mapped. Also make sure you don't have some stray test code that has YOUR id hard-coded and makes it look like everyone is you (not just her). |
|
|
|
justa718
Starting Member
9 Posts |
Posted - 2010-01-15 : 15:58:05
|
| please be as harsh as you have to be as long as I get to the root of the problem.as for her logging with my credentials thats quite impossible as i saw her enter her username and password her self and the only way i get the persons identity is asking ASP.net who ASP.net believes to be on the computer.as far as my C# code is concerned this is an example of the basic generic data access i use to access a variety of tables public static dtotbl_emp[] Select(string userid) { ArrayList emps = new ArrayList(); string sQuery = "select * from tbl_emp where userid = @userid and status = 'a'"; SQLConn.openTimesheet(); try { SqlCommand thisCommand = SQLConn.timesheetConnection.CreateCommand(); thisCommand.CommandText = sQuery; thisCommand.Parameters.AddWithValue("@userid", userid); SqlDataReader thisReader = thisCommand.ExecuteReader(); while (thisReader.Read()) { dtotbl_emp emp = new dtotbl_emp(thisReader["person"].ToString(), thisReader["location"].ToString(), thisReader["department"].ToString(), thisReader["title"].ToString(), thisReader["phone"].ToString(), thisReader["supervisor"].ToString(), thisReader["user_password"].ToString(), thisReader["userid"].ToString(), thisReader["compa"].ToString(), thisReader["emp_type"].ToString(), thisReader["file_no"].ToString(), thisReader["status"].ToString().ToCharArray()[0], DateTime.Parse(thisReader["date_entered"].ToString()), thisReader["recid"].ToString(), thisReader["ext"].ToString()); emps.Add(emp); } thisReader.Close(); thisCommand.Dispose(); } catch (SqlException e) { daotbl_error_log.Insert(e.Message, "userid = " + userid); onError(e.Message); } catch (Exception e) { daotbl_error_log.Insert(e.Message, "userid = " + userid); onError(e.Message); } SQLConn.closeTimesheet(); return (dtotbl_emp[])emps.ToArray(typeof(dtotbl_emp)); }if there are any flaws in this code that could cause the errors, or the reader being closed while its trying to read the data please let me know, all help is greatly apprieciated |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 16:26:43
|
at this line:thisCommand.Parameters.AddWithValue("@userid", userid);what is the value of userid? Make sure it's what you expect. Put some code right there to display the value so you can be certain |
|
|
|
justa718
Starting Member
9 Posts |
Posted - 2010-01-15 : 16:30:51
|
| The userid isn't the problem as iam getting it straight from ASP.net's page, this was just an example of some of the access code, one thing ive been figuring out during my detective work into the logs and stuff, is that it looks like the SqlDataReader is being closed before it finished doing what its suppose to do... why would this happen? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 16:38:18
|
| you don't know if it is or not until you display the value. but while i'm not saying it is THE problem, it demonstrates that you have application code errors that need to be rooted out before suspecting network or database errors.i can assure you with near certainty that IIS isn't mixing up sessions.but we have no idea what you are doing with it. could be that you hard-coded your userid somehwere in test phase and forgot to remove it. could be that you have mis-mapped users in your db.as for the data reader, what is your sql timeout and session timeout values? and how long are your queries taking to execute? what errors are you seeing in the SQL Server and IIS hosts event logs and application error logs? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-15 : 16:41:09
|
are you getting any errors here?while (thisReader.Read()){ dtotbl_emp emp = new dtotbl_emp(thisReader["person"].ToString(), thisReader["location"].ToString(), thisReader["department"].ToString(), thisReader["title"].ToString(), thisReader["phone"].ToString(), thisReader["supervisor"].ToString(), thisReader["user_password"].ToString(), thisReader["userid"].ToString(), thisReader["compa"].ToString(), thisReader["emp_type"].ToString(), thisReader["file_no"].ToString(), thisReader["status"].ToString().ToCharArray()[0], DateTime.Parse(thisReader["date_entered"].ToString()), thisReader["recid"].ToString(), thisReader["ext"].ToString()); emps.Add(emp);}That would cause the reader to close prematurely...perhaps a datatype issue... |
|
|
|
Next Page
|
Sql Seems not to return correct rows