Tag Archives: solution

When SqlDataReader is missing rows

I have a very basic scenario:

  1. Execute TSQL Stored procedure
  2. Return a DataReader
  3. Read data from the Reader

This is ADO.NET 101. There is one problem: DataReader loses rows. This problem has haunted me forever, extensive research and numerous suggestion didn’t help, even though the code is extremely basic:

Get the reader:

m_dbSel.CommandType = CommandType.StoredProcedure
m_dbSel.CommandText = "SP_Name"
oResult = m_dbSel.ExecuteReader()

Pass the reader to class constructor to fill Generic List (of Integer): 

Public Sub New(i_oDataReader As Data.SqlClient.SqlDataReader)

    m_aFullIDList = New Generic.List(Of Integer)

    While i_oDataReader.Read
        m_aFullIDList.Add(i_oDataReader.GetInt32(0))
    End While

    m_iTotalNumberOfRecords = m_aFullIDList.Count

End Sub

This problem occurs when number of rows returned by the reader is relatively large (over 600,000 records). If this happens – number of rows added to the list from the reader is inconsistent, but always less than real one. Most often “magic” number of 524289 rows is returned.

Well, this is no longer a mystery, thanks to the great people from Stack Overflow @RBarryYoung, @granadaCoder and especially @MartinSmith who was the first to point me in the right direction – and here it is.

Even though the problem is with SqlDataReader – it is happening because it is used in conjunction with Generic List. List, as you may know has a flexible Capacity for number of elements it can store. When count of elements exceeds capacity – capacity increases and always to a power of 2. E.g.

When the count exceeds 4 elements – capacity is set to 8 (2^3)
When the count exceeds 8 elements – capacity is set to 16 (2^4)
When the count exceeds 16 elements – capacity is set to 32 (2^5)

etc..

This is what makes Generic List such a powerful tool, used by many large scale .NET projects, e.g. bingogodz.com. And ordinary this is not a problem. Unfortunately this is not the case when it is used together with SqlDataReader. When count of items in the List exceeds 524,288 (2^19) and its capacity is set to 1,048,576 (2^20) – SqlDataReader’s Read method suddenly returns False even though not all records have been read. No exception is thrown, it simple stops.

The only possible workaround I’ve found so far (I am still looking for better ones) is to pre-set List capacity in advance. Since, when using DataReader, you do not know number of rows, you’re left either with hardcoding the number or running another DB query to retrieve number of rows via something like COUNT(*). Like I said, I don’t like this workaround, please let me know if you find a better one.

UPDATE: Finally figured it out: http://stackoverflow.com/a/18520609/961695

WebHierarchicalDataGrid: get_scrollTop() returns incorrect value

Infragistics WebHierarchicalDataGrid has a neat client-side built-in function get_scrollTop() – it is used if at any point you need to retrieve current vertical scroll position of the grid (e.g. to use it in your own calculations to display something at a specific position on the grid – tooltip, help, additional info etc.)

Unfortunately the function has a bug: its value only set if user actually manually scrolls the grid: using mouse and scrollbar on the right, keyboard etc. If no scrolling user-interaction is involved and scroll position changes due to other means (e.g. displayed data size changes) – the function retains original value, throwing all your calculation out of whack. Continue reading →

ADO.NET DataTable: Change Column DataType after table is populated with data

Sometimes there is a need to change DataType of ADO.NET DataTable column. If your table is populated as a result of some database operation – you don’t know in advance what type the columns will be. And by design you cannot change the type of the column after the table is populated. Conudrum. Catch 22. Tough luck.

But wait, there’s light at the end of the tunnel. You cannot change the type of the existing column, but you can create a new one. Continue reading →

Disable WebDataTree in client-side JavaScript

Let’s say you’re using Infrafitics WebDataTree’s server-side SelectionChanged event to perform some action when user selects a tree node. Now let’s say there’re times when you need to disable tree-selection (for example another control, e.g. WebDataGrid is being updated and clicking tree node during update will mess things up). Also you need to do it in client-side JavaScript (e.g. if that second control is inside of UpdatePanel – server-side updates will not affect controls outside the panel).

What to do? WebDataTree doesn’t have CSOM set_enabled() method and if you use client-side DOM’s:

$find('xMyTree').get_element().setAttribute('disabled','true');

tree may take appearance of being disabled in some browsers, but user can still select nodes.

Fortunately there is a solution (thanks Bhadresh from Infragisitcs techsupport for suggesting it). WebDataTree has SelectionType property (0 = None, 1 = Single, 2 = Multiple) that controls how many nodes can be selected in the tree. It also has a counterpart in client-side JavaScript set_selectionType(), so in order to disable selection simple use command

$find('xMyTree').set_selectionType(0);

Later on selection can be re-enabled either by passing 1 to above function or even server-side by calling

xMyTree.SelectionType = NavigationControls.NodeSelectionTypes.Single

Solution for IE10 error: SCRIPT5022: Sys.ArgumentOutOfRangeException: Value must be an integer

If you’re testing your ASP.NET project in Internet Explorer 10, you may encounter following error:

SCRIPT5022: Sys.ArgumentOutOfRangeException: Value must be an integer.
Parameter name: (x or y)
Actual value was (some floating point value)
ScriptResource.axd, line … character …

Often it come up when you use some 3rd party libraries, Telerik or Infragistics (in my case it happened in WebDataMenu control).

Here why it happens. Continue reading →

Solution for WebHierarchicalDataGrid’s “Async request failed” error

This is a solution for specific (and maybe somewhat obscure, but it helped me, so perhaps it will be helpful to someone else) scenario for Infragistics’ WebHierarchicalDataGrid error “Async request failed“.

It could be accompanied by inner errors

Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request

or more generic

Object Reference not set

In this particular scenario WHDG uses manual LoadOnDemand for to populate children (i.e. RowIslandPopulating event is used) and parent grid is sorted by one or more columns. Error is happening when attempting to expand second child or a grandchild. Continue reading →

FusionCharts: Invalid Data when using javascript renderer (solved)

If you’re using FusionCharts you may encounter a strange issue – chart renders correctly when Flash renderer is in use and displays “Invalid Data” error message when falling back (or forced) to JavaScript renderer.

In most cases culprit is invalid XML data passed to the engine. And while Flash is more forgiving, JavaScript requires strict valid XML. Most often the cause for the issue are characters invalid in XML. Check your data and if they contain following characters – replace them with their encoded values:

" (quote) --> "
' (apostrophe) --> '
< (less sign) --> &lt;
> (greater sign) --> &gt;
& (ampersand)  --> &amp;

And the error will disappear.

Happy charting!

Infragistics WebTab: Simulating SelectedIndexChanging event when programmaticaly changing tabs via set_selectedIndex

Infragistics WebTab control is very versatile and offers rich server- and client-side model. Among client-side events are

  • SelectedIndexChanged – fires after user has changed the tab to provide ways to interact with newly selected tab
  • SelectedIndexChanging – fires before user has changed the tab to provide ways to interact with currently selected tab and to give user a chance to cancel change.

Both work fine if user manually changes tabs by clicking on tab headers, but call to client-side set_selectedIndex() function (which changes tabs programmaticaly) only fires SelectedIndexChanged, skipping SelectedIndexChanging altogether. But there’s a way to make it work.
Continue reading →

FusionCharts: Use non-numeric Xaxis in Bubble and Scatter Charts

FusionCharts states in their documentation that in Bubble and Scatter Charts both X-Axis and Y-Axis must be numeric. But what if you want X-Axis to display some names or dates or other non-numeric values? That is still possible via label attribute of chart’s categories element.

The method below utilizes ADO.NET/VB.NET to build XML for chart data, but similar approach can be easily used in other languages/technologies.

Consider the following ADO.NET DataTable, called dtChartData:

               Login Failure  Login Success
-------------- -------------- -------------
2013-03-27     1              69
2013-03-26     0              32
2013-03-25     1              86
2013-03-22     0              11

It holds data for number of successful/unsucessful logins for a given date. We want to display this data as a Bubble chart with dates displayed on X-Axis. Continue reading →

Solution: eventArgs.get_type() incorrectly detects header of WebDataGrid in FireFox as cell

If you use CSOM of Infragistics WebDataGrid you may encounter a bug in client-side event handlers. Let’s say some action is needed on double clicking of a grid cell. The handler looks something like this:

function xMyGrid_DoubleClick(sender, eventArgs) {
   if (eventArgs.get_type() == "cell") {
      //perform action
   }
}

IF condition on line 2 makes sure that action is executed only if actual grid cell is double clicked. If you double click column header, eventArgs.get_type() would return "header" and the action would be skipped.

But not in FireFox. In that browser eventArgs.get_type() returns "cell" even when header is clicked. So an additional condition is needed. Grid column header is rendered as a TH HTML element and this is what we can check:

function xMyGrid_DoubleClick(sender, eventArgs) {
   if (eventArgs.get_type() == "cell" &&  eventArgs.get_item().get_element().tagName != 'TH') {
      //perform action
   }
}

This solution works universally in all browsers.