DataView Timeout Becomes SQLReader Sorting Issue

  • Thread starter Thread starter Alex Davidson
  • Start date Start date
A

Alex Davidson

I am having issues with a page timing out while using DataView to go to SQL
to run a Stored Procedure to populate a DataGrid so I looked around and
found that if I use a SQLReader I can specify a Timeout value.

Problem now is how to sort the data as desired.

Right now I am using pieced together code to try to iterate through the
SQLReader data and put it into a DataView so it can be sorted but it errors:

System.NullReferenceException: Object reference not set to an instance of an
object.

on the line "Dim dr As DataRowView = myDV.AddNew" below.

Can someone suggest the correct code, or maybe there's a better way?

Code:
Dim myDV As DataView
con = New
SqlConnection(ConfigurationSettings.AppSettings("GenesisConString"))
cmd = New SqlCommand("Execute GetInactiveCustomers '" &
Request.QueryString("Div") & "', '" & varInactiveWhereField & "', '" &
varInactiveWhereValue & "'", con)
cmd.CommandTimeout = 120
con.Open()
Dim results As SqlDataReader = cmd.ExecuteReader()
Do While results.Read()
Dim dr As DataRowView = myDV.AddNew
dr("InvoiceCustomerID") = results("InvoiceCustomerID")
dr("InactiveCustName") = results("InactiveCustName")
dr("InactiveAddress1") = results("InactiveAddress1")
dr("InactiveAddress2") = results("InactiveAddress2")
dr("InactiveCity") = results("InactiveCity")
dr("InactiveState") = results("InactiveState")
dr("InactiveZip") = results("InactiveZip")
dr("InactivePhoneNo") = results("InactivePhoneNo")
dr("InactiveLastInvoiceDate") = results("InactiveLastInvoiceDate")
dr("InactiveRepName") = results("InactiveRepName")
dr("GP") = results("GP")
dr.EndEdit()
Loop
con.Close()
results.Close()
myDV.Sort = varInactiveSort
InactiveCustomersDataGrid.DataSource = myDV
InactiveCustomersDataGrid.DataBind()


Thanks,
Alex
 
I'm not sure where to begin - but here goes:
<<<so I looked around and found that if I use a SQLReader I can specify a
Timeout value>>>
The ADO.NET SQLCommand object has a timeout value... so you can specify that
even when not retrieving data via a SQLDataReader.

A SQLDataReader is meant only for forward-only read-only activity. If you
want the data coming through a SQLDataReader to be sorted, then it has to be
sorted by SQL Server (i.e., an ORDER BY clause in your query or stored
procedure).

Because a DataReader is forward-only, I doubt it's possible to create a view
on it. I've never seen anyone try it. I could be wrong.

Try retrieving your data into a DataSet or even a free-standing DataTable
and then create the view on that. I believe that's how it was designed to
hang together.

Good Luck
 
Back
Top