How do I get around the following error I get when I fill a dataset?

[Rani]

How do I get around the following error I get when I fill a dataset?

Cannot sort a row of size 8430, which is greater than the allowable maximum of 8094

Line 184: Dim da2 As SqlDataAdapter = New SqlDataAdapter(command2)
Line 185: Dim ds2 As New DataSet
Line 186: da2.Fill(ds2)

I had written the following code to check for the rowsize and if it was too big to flag it so that I could skip over displaying the record so that I wouldn’t get the error, but come to find out the error happens right when the dataset is filled.

For RowIndex = 0 To ds.Tables(0).Rows.Count - 1
 
For ColumnIndex = 0 To ds.Tables(0).Columns.Count - 1
 
RowSize += Len(ds.Tables(0).Rows(RowIndex).Item(ColumnIndex))
 
Next
 
If RowSize > 8000 Then
Session("dsAllocatedUserRecords").Tables(0).Rows (Session("currentRowIndex")).Item("SkipGrouping") = 1
End If
RowSize = 0
 
Next

Thanks

 

[zekeman]

Sorry about this..Im new to this.

Is there a select that loads this table and if so can you show it with
the call to read the table?

 

[Rani]

It’s within a stored procedure and there are many select statements that are UNIONed together.

The problem isn’t the Select statement but the fact that the dataset object can’t sort a row that contains more than 8092 characters, either by default or by design. I just need a workaround or to increase this limit.
Thanks

 

[TechGnome]

The "workaround" is to look at the source data and find out what record is causing the problem... then making at correction at that point.

-tg

 

[Rani]

Please read my questions properly and then answer. Thank you

 

[TechGnome]

*reads*.. Okay... yeah... right.... the problem is that you have one or more rows of data that is TOO BIG! It cannot be read into the DataTable. At all. Period. You wanted a solution... the solution is to reduce the size of the data that is being returned. I'm sorry that this doesn't work for you, but unless you plan to write your own data adaptor to extract the data from the database, there's not much else you can do. You're right in that the problem isn't the SELECT, but it IS in the DATA returned by the SELECT.

Somewhere in there, there is at least one row of data that is just too danged long.

Now, I was going to leave it at that, but against my better judgement, I'll just mention in a passing that I don't know if a reader has the same issues or not.

-tg