multiple database connections?

[Mortar]

i am binding a datagrid do a sqlcommand, which uses a sql connection.

fine.

if i want to run another query and bind another datagrid, do i have to
create new connections and commands? because i can't seem to get it to
work using just one connection

[W.G. Ryan eMVP]

You aren't binding to a Command or a SqlConnection per se. I'm guessing you
are using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
remember is close the connection as soon as you're done - because of pooling
you can create another connection at very little cost

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Mortar" <[e-mail]> wrote in message
news:41***************@nntp.wlfdle.phub.net.cable. rogers.com...

i am binding a datagrid do a sqlcommand, which uses a sql connection.

fine.

if i want to run another query and bind another datagrid, do i have to
create new connections and commands? because i can't seem to get it to
work using just one connection

[Mortar]

yes i am creating a connection and a command, then binding the
datagrid to the command's executeReader.

you suggest that creating another connection is little cost. Is this
because I can't use the existing connection? If I can use it, please
explain how. Thanks.
On Wed, 13 Oct 2004 19:36:44 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

You aren't binding to a Command or a SqlConnection per se. I'm guessing you
are using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
remember is close the connection as soon as you're done - because of pooling
you can create another connection at very little cost

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Mortar" <[e-mail]> wrote in message
news:41***************@nntp.wlfdle.phub.net.cable .rogers.com...

i am binding a datagrid do a sqlcommand, which uses a sql connection.

fine.

if i want to run another query and bind another datagrid, do i have to
create new connections and commands? because i can't seem to get it to
work using just one connection

[W.G. Ryan eMVP]

What I'm actually advocating is not reusing the same one. You have the
postback to deal with so go ahead and just create the connection and call
executereader. Justmake sure you close that connection ie using
(SqlConnection cn = new SqlConnection()) so you know it gets disposed of.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Mortar" <[e-mail]> wrote in message
news:41***************@nntp.wlfdle.phub.net.cable. rogers.com...

yes i am creating a connection and a command, then binding the
datagrid to the command's executeReader.

you suggest that creating another connection is little cost. Is this
because I can't use the existing connection? If I can use it, please
explain how. Thanks.
On Wed, 13 Oct 2004 19:36:44 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

You aren't binding to a Command or a SqlConnection per se. I'm guessing youare using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
remember is close the connection as soon as you're done - because of poolingyou can create another connection at very little cost

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Mortar" <[e-mail]> wrote in message
news:41***************@nntp.wlfdle.phub.net.cable .rogers.com...

i am binding a datagrid do a sqlcommand, which uses a sql connection.

fine.

if i want to run another query and bind another datagrid, do i have to
create new connections and commands? because i can't seem to get it to
work using just one connection

[Mortar]

i know you are advocating creating a new one. What I am wondering is
why? Personal preference or because the same connection cannot
actually be used.

also, there is no postback, i am trying to do this all on the same
page load. I have a connection object, a command object, and 2
datagrids. I simply want to open a connection, run 2 queries to fill 2
grids, then close the connection. The error I get is that when it
tries to fill the second grid, it says there is a dataReader already
open associated with the connection, and it has to be closed first.
Thing is, i'm not using a datareader anywhere.

here is what i would like to do :

SqlConnection con = new SqlConnection([mydatabasedetails]);
SqlCommand cmd = new SqlCommand(SQL, con);
con.Open();

dgResults.DataSource = cmd.ExecuteReader();
dgResults.DataBind();

'now fill 2nd grid (dgResultsTwo):
'not sure how to do this on same connection and command object

con.Close();


On Thu, 14 Oct 2004 00:44:01 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

What I'm actually advocating is not reusing the same one. You have the
postback to deal with so go ahead and just create the connection and call
executereader. Justmake sure you close that connection ie using
(SqlConnection cn = new SqlConnection()) so you know it gets disposed of.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Mortar" <[e-mail]> wrote in message
news:41***************@nntp.wlfdle.phub.net.cable .rogers.com...

yes i am creating a connection and a command, then binding the
datagrid to the command's executeReader.

you suggest that creating another connection is little cost. Is this
because I can't use the existing connection? If I can use it, please
explain how. Thanks.
On Wed, 13 Oct 2004 19:36:44 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

>You aren't binding to a Command or a SqlConnection per se. I'm guessingyou >are using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
>remember is close the connection as soon as you're done - because ofpooling >you can create another connection at very little cost
>
>--
>W.G. Ryan MVP (Windows Embedded)
>
>TiBA Solutions
>www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
>"Mortar" <[e-mail]> wrote in message
>news:41***************@nntp.wlfdle.phub.net.cable .rogers.com...
>> i am binding a datagrid do a sqlcommand, which uses a sql connection.
>>
>> fine.
>>
>> if i want to run another query and bind another datagrid, do i have to
>> create new connections and commands? because i can't seem to get it to
>> work using just one connection
>
>

[Scott Allen]

Hi Mortar,

The line of code:
dgResults.DataSource = cmd.ExecuteReader();

*is* opening a SqlDataReader. You need to close the SqlDataReader
before you can do anything else with the SqlCommand.

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

On Thu, 14 Oct 2004 15:58:29 GMT, [e-mail] (Mortar) wrote:

i know you are advocating creating a new one. What I am wondering is
why? Personal preference or because the same connection cannot
actually be used.

also, there is no postback, i am trying to do this all on the same
page load. I have a connection object, a command object, and 2
datagrids. I simply want to open a connection, run 2 queries to fill 2
grids, then close the connection. The error I get is that when it
tries to fill the second grid, it says there is a dataReader already
open associated with the connection, and it has to be closed first.
Thing is, i'm not using a datareader anywhere.

here is what i would like to do :

SqlConnection con = new SqlConnection([mydatabasedetails]);
SqlCommand cmd = new SqlCommand(SQL, con);
con.Open();

dgResults.DataSource = cmd.ExecuteReader();
dgResults.DataBind();

'now fill 2nd grid (dgResultsTwo):
'not sure how to do this on same connection and command object

con.Close();


On Thu, 14 Oct 2004 00:44:01 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

What I'm actually advocating is not reusing the same one. You have the
postback to deal with so go ahead and just create the connection and call
executereader. Justmake sure you close that connection ie using
(SqlConnection cn = new SqlConnection()) so you know it gets disposed of.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Mortar" <[e-mail]> wrote in message
news:41***************@nntp.wlfdle.phub.net.cabl e.rogers.com...

yes i am creating a connection and a command, then binding the
datagrid to the command's executeReader.

you suggest that creating another connection is little cost. Is this
because I can't use the existing connection? If I can use it, please
explain how. Thanks.
On Wed, 13 Oct 2004 19:36:44 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

>You aren't binding to a Command or a SqlConnection per se. I'm guessing

you

>are using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
>remember is close the connection as soon as you're done - because of

pooling

>you can create another connection at very little cost
>
>--
>W.G. Ryan MVP (Windows Embedded)
>
>TiBA Solutions
>www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
>"Mortar" <[e-mail]> wrote in message
>news:41***************@nntp.wlfdle.phub.net.cable .rogers.com...
>> i am binding a datagrid do a sqlcommand, which uses a sql connection.
>>
>> fine.
>>
>> if i want to run another query and bind another datagrid, do i have to
>> create new connections and commands? because i can't seem to get it to
>> work using just one connection
>
>

[Mortar]

how do I close it when it is created in the way I did (no object
variable)? The only way I could get it to work was using the cmd
object to close the connection (cmd.Connection.Close), then setting
the cmd to the new query, then open the connection again. If there is
a better way of doing it, please let me know.

On Thu, 14 Oct 2004 14:08:27 -0400, Scott Allen
<bitmask@[nospam].fred.net> wrote:

Hi Mortar,

The line of code:
dgResults.DataSource = cmd.ExecuteReader();

*is* opening a SqlDataReader. You need to close the SqlDataReader
before you can do anything else with the SqlCommand.

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

On Thu, 14 Oct 2004 15:58:29 GMT, [e-mail] (Mortar) wrote:

i know you are advocating creating a new one. What I am wondering is
why? Personal preference or because the same connection cannot
actually be used.

also, there is no postback, i am trying to do this all on the same
page load. I have a connection object, a command object, and 2
datagrids. I simply want to open a connection, run 2 queries to fill 2
grids, then close the connection. The error I get is that when it
tries to fill the second grid, it says there is a dataReader already
open associated with the connection, and it has to be closed first.
Thing is, i'm not using a datareader anywhere.

here is what i would like to do :

SqlConnection con = new SqlConnection([mydatabasedetails]);
SqlCommand cmd = new SqlCommand(SQL, con);
con.Open();

dgResults.DataSource = cmd.ExecuteReader();
dgResults.DataBind();

'now fill 2nd grid (dgResultsTwo):
'not sure how to do this on same connection and command object

con.Close();


On Thu, 14 Oct 2004 00:44:01 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

What I'm actually advocating is not reusing the same one. You have the
postback to deal with so go ahead and just create the connection and call
executereader. Justmake sure you close that connection ie using
(SqlConnection cn = new SqlConnection()) so you know it gets disposed of.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Mortar" <[e-mail]> wrote in message
news:41***************@nntp.wlfdle.phub.net.cab le.rogers.com...
yes i am creating a connection and a command, then binding the
datagrid to the command's executeReader.

you suggest that creating another connection is little cost. Is this
because I can't use the existing connection? If I can use it, please
explain how. Thanks.
On Wed, 13 Oct 2004 19:36:44 -0400, "W.G. Ryan eMVP"
<Wi*********@NoSpam.gmail.com> wrote:

>You aren't binding to a Command or a SqlConnection per se. I'm guessing
you
>are using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
>remember is close the connection as soon as you're done - because of
pooling
>you can create another connection at very little cost
>
>--
>W.G. Ryan MVP (Windows Embedded)
>
>TiBA Solutions
>www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
>"Mortar" <[e-mail]> wrote in message
>news:41***************@nntp.wlfdle.phub.net.cable .rogers.com...
>> i am binding a datagrid do a sqlcommand, which uses a sql connection.
>>
>> fine.
>>
>> if i want to run another query and bind another datagrid, do i have to
>> create new connections and commands? because i can't seem to get it to
>> work using just one connection
>
>

[Scott Allen]

Hi Mortar:

Here is one of many possible ways you could do it, I think Bill was
alluding to this earlier:

using(SqlConnection connection = new SqlConnection(<connstring>))
{
connection.Open();

SqlCommand command = new SqlCommand();
command.Connection = connection;

command.CommandText = <query1>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid1.DataSource = reader;
DataGrid1.DataBind();
}

command.CommandText = <query2>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid2.DataSource = reader;
DataGrid2.DataBind();
}
}

The using clause ensures the SqlDataReader is properly closed before
it leaves scope.

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

On Thu, 14 Oct 2004 18:41:06 GMT, [e-mail] (Mortar) wrote:

how do I close it when it is created in the way I did (no object
variable)? The only way I could get it to work was using the cmd
object to close the connection (cmd.Connection.Close), then setting
the cmd to the new query, then open the connection again. If there is
a better way of doing it, please let me know.

[Mortar]

excellent. Thanks.
On Thu, 14 Oct 2004 15:28:27 -0400, Scott Allen
<bitmask@[nospam].fred.net> wrote:

Hi Mortar:

Here is one of many possible ways you could do it, I think Bill was
alluding to this earlier:

using(SqlConnection connection = new SqlConnection(<connstring>))
{
connection.Open();

SqlCommand command = new SqlCommand();
command.Connection = connection;

command.CommandText = <query1>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid1.DataSource = reader;
DataGrid1.DataBind();
}

command.CommandText = <query2>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid2.DataSource = reader;
DataGrid2.DataBind();
}
}

The using clause ensures the SqlDataReader is properly closed before
it leaves scope.

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

On Thu, 14 Oct 2004 18:41:06 GMT, [e-mail] (Mortar) wrote:

how do I close it when it is created in the way I did (no object
variable)? The only way I could get it to work was using the cmd
object to close the connection (cmd.Connection.Close), then setting
the cmd to the new query, then open the connection again. If there is
a better way of doing it, please let me know.