Databinding a SQLDataReader to a DataGrid control

[Jeff]

Hi -

I'm having trouble Databinding a SQLDataReader to a DataGrid control.

I have an ASP.NET web page accessing a SQL database. I've used VS to build
the app and stored it in a directory of my localhost on my development
machine. The database is on the web.

When I run the app on the local machine, IE opens, loads my aspx page from
localhost, and hangs. (Eventually I get a page cannot be displayed error.)
No exceptions are raised.

In the aspx page, I dragged a datagrid from the toolbox to the page and
named it "dgCustomers".

In the aspx.vb page, I define the reader as follows:

Dim conMain As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConn As String
Dim strSQL As String

strConn = [my connection string]
conMain = New SqlConnection(strConn)
sqlCmd = New SqlCommand("SELECT FirstName, LastName FROM
eNPCustomers", conMain)
conMain.Open()
Dim rdrCustomers As SqlDataReader = sqlCmd.ExecuteReader

I bind it to the datagrid as follows:

dgCustomers.DataSource = rdrCustomers
dgCustomers.DataBind()

The page hangs, again when I run it on my development machine. (It runs
fine if I change the page directive from codebehind to src and upload it to
my 3rd-party web server.)

If, instead of the last 2 databinding lines, I simply print out the reader
rows, the page runs fine (all data is displayed as expected):

While rdrCustomers.Read
Response.Write("<br>" & rdrCustomers.GetString(0) & " " &
rdrCustomers.GetString(1))
End While

So the reader seems to successfully get the data from the sql database.
Just to see whether there is something amiss with databinding, I tried the
following (instead of the datareader) and it works fine:

Dim arrCustomers() As String = {"tom", "dick", "harry"}
dgCustomers.DataSource = arrCustomers
dgCustomers.DataBind()

What am I doing wrong??

Thanks for your help.

- Jeff

 

[Elton Wang]

DataGrid's data source has to be bound to an object that
implements IEnumerable interface, such as DataView (or
DataTable / DataSet), ArrayList, HashTable, and so on. But
not DataReader.

HTH

Elton Wang

 

[Jeff]

Thanks Elton -

OK I tried the same thing with a data table; same result (page hangs running
on local machine):

Dim conMain As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConn As String

strConn = [my connection string]
conMain = New SqlConnection(strConn)
sqlCmd = New SqlCommand("SELECT FirstName, LastName FROM
eNPCustomers", conMain)
conMain.Open()

Dim daCustomers As SqlDataAdapter = New SqlDataAdapter(strSQL,
conMain)
Dim dsCustomers As DataSet = New DataSet

daCustomers.Fill(dsCustomers, "Customers")

dgCustomers.DataSource = dsCustomers.Tables("Customers").DefaultView
dgCustomers.DataBind()

conMain.Close()

[Also, if I can't bind a data reader to a data grid, then why does the same
code - with the data reader - work fine when I upload to the server?]

Any other ideas? Can you spot something I'm doing wrong?

- Jeff

DataGrid's data source has to be bound to an object that
implements IEnumerable interface, such as DataView (or
DataTable / DataSet), ArrayList, HashTable, and so on. But
not DataReader.

HTH

Elton Wang

-----Original Message-----
Hi -

I'm having trouble Databinding a SQLDataReader to a DataGrid control.

I have an ASP.NET web page accessing a SQL database. I've used VS to build
the app and stored it in a directory of my localhost on my development
machine. The database is on the web.

When I run the app on the local machine, IE opens, loads my aspx page from
localhost, and hangs. (Eventually I get a page cannot be displayed error.)
No exceptions are raised.

In the aspx page, I dragged a datagrid from the toolbox to the page and
named it "dgCustomers".

In the aspx.vb page, I define the reader as follows:

Dim conMain As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConn As String
Dim strSQL As String

strConn = [my connection string]
conMain = New SqlConnection(strConn)
sqlCmd = New SqlCommand("SELECT FirstName, LastName FROM
eNPCustomers", conMain)
conMain.Open()
Dim rdrCustomers As SqlDataReader = sqlCmd.ExecuteReader

I bind it to the datagrid as follows:

dgCustomers.DataSource = rdrCustomers
dgCustomers.DataBind()

The page hangs, again when I run it on my development machine. (It runs
fine if I change the page directive from codebehind to src and upload it to
my 3rd-party web server.)

If, instead of the last 2 databinding lines, I simply print out the reader
rows, the page runs fine (all data is displayed as expected):

While rdrCustomers.Read
Response.Write("<br>" & rdrCustomers.GetString(0) & " " &
rdrCustomers.GetString(1))
End While

So the reader seems to successfully get the data from the sql database.
Just to see whether there is something amiss with databinding, I tried the
following (instead of the datareader) and it works fine:

Dim arrCustomers() As String = {"tom", "dick", "harry"}
dgCustomers.DataSource = arrCustomers
dgCustomers.DataBind()

What am I doing wrong??

Thanks for your help.

- Jeff


.

 

[Scott Allen]

DataGrid's data source has to be bound to an object that
implements IEnumerable interface, such as DataView (or
DataTable / DataSet), ArrayList, HashTable, and so on. But
not DataReader.

HTH

Elton Wang

Elton:

A data reader does implement IEnumerable and can be used as a
DataSource (but not if the grid is set for automatic paging - then it
needs an object with an IList interface).

 

[Jeff]

Thanks Scott and Elton -

I think I've gotten this fixed now, Scott's paging clue got me trying a few
different things --

If I set the datagrid's EnableViewState = False, everything works fine (with
either the dataset or the datareader approach).

- Jeff

 

[Scott Allen]

HI Jeff:

Thanks Elton -

OK I tried the same thing with a data table; same result (page hangs running
on local machine):

<snip>

There is nothing obviously wrong with the code. Perhaps there is
something awry with the environment. Is there a firewall between your
machine and the database? Could it be the browser?

[Also, if I can't bind a data reader to a data grid, then why does the same
code - with the data reader - work fine when I upload to the server?]

You can bind a data reader to a grid - it does work - really.

Any other ideas? Can you spot something I'm doing wrong?

Like I say - nothing looks wrong with the code - so check other pieces
of the system: browser settings, network settings, machine settings..

HTH,

 

[Scott Allen]

Glad you figured out a workaround, Jeff. The strange thing is the page
should work with ViewState enabled - is it a really large Viewstate
perhaps?

That being said - no need to enable view state if you don't need the
overhead.

--
Scott
http://www.OdeToCode.com/blogs/scott/

Thanks Scott and Elton -

I think I've gotten this fixed now, Scott's paging clue got me trying a few
different things --

If I set the datagrid's EnableViewState = False, everything works fine (with
either the dataset or the datareader approach).

- Jeff

 

[Jeff]

Thanks Scott -

I wouldn't think the viewstate was large - the file has about 400 records,
and I'm just selecting 2 columns. But, when I opened the source of the
'hung' page, the viewstate field looked pretty large. I obviously couldn't
make sense of its contents, so I tried disabling it. Could be something
about size. Could be some kind of a timeout issue. I'm not aware of any
limits or other issues that would apply.

- Jeff

Glad you figured out a workaround, Jeff. The strange thing is the page
should work with ViewState enabled - is it a really large Viewstate
perhaps?

That being said - no need to enable view state if you don't need the
overhead.

 

[Elton Wang]

Hi Scott,

Thanks

-----Original Message-----


Elton:

A data reader does implement IEnumerable and can be used as a
DataSource (but not if the grid is set for automatic paging - then it
needs an object with an IList interface).