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

-----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 buildthe app and stored it in a directory of my localhost on my developmentmachine. The database is on the web.

When I run the app on the local machine, IE opens, loads my aspx page fromlocalhost, 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 andnamed 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 FROMeNPCustomers", 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 runsfine if I change the page directive from codebehind to src and upload it tomy 3rd-party web server.)

If, instead of the last 2 databinding lines, I simply print out the readerrows, 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 thefollowing (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
.

[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
"Elton Wang" <an*******@discussions.microsoft.com> wrote in message
news:45****************************@phx.gbl...

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]

On Fri, 4 Mar 2005 19:31:28 -0800, "Elton Wang"
<an*******@discussions.microsoft.com> wrote:

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).

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

[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" <sc***@nospam.odetocode.com> wrote in message
news:qu********************************@4ax.com...

On Fri, 4 Mar 2005 19:31:28 -0800, "Elton Wang"
<an*******@discussions.microsoft.com> wrote:

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).

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

[Scott Allen]

HI Jeff:

On Sat, 05 Mar 2005 03:50:03 GMT, "Jeff" <je*********@eNetPortals.com>
wrote:

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
http://www.OdeToCode.com/blogs/scott/

[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/

On Sat, 05 Mar 2005 04:34:44 GMT, "Jeff" <je*********@eNetPortals.com>
wrote:

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" <sc***@nospam.odetocode.com> wrote in message
news:qu********************************@4ax.com.. .

On Fri, 4 Mar 2005 19:31:28 -0800, "Elton Wang"
<an*******@discussions.microsoft.com> wrote:

>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).

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

[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
"Scott Allen" <sc***@nospam.odetocode.com> wrote in message
news:bc********************************@4ax.com...

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/

On Sat, 05 Mar 2005 04:34:44 GMT, "Jeff" <je*********@eNetPortals.com>
wrote:

Thanks Scott and Elton -

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

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

- Jeff
"Scott Allen" <sc***@nospam.odetocode.com> wrote in message
news:qu********************************@4ax.com.. .

On Fri, 4 Mar 2005 19:31:28 -0800, "Elton Wang"
<an*******@discussions.microsoft.com> wrote:

>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).

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

[Elton Wang]

Hi Scott,

Thanks

-----Original Message-----
On Fri, 4 Mar 2005 19:31:28 -0800, "Elton Wang"
<an*******@discussions.microsoft.com> wrote:

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. Butnot DataReader.

HTH

Elton Wang

Elton:

A data reader does implement IEnumerable and can be used

as aDataSource (but not if the grid is set for automatic paging - then itneeds an object with an IList interface).

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