can I read from a dr, then populate a ds w/ 1 con?

[Jason Shohet]

A user provides an organization name, username & password. I've got Access
as the back end so no triggers, functions etc. This is what I do:

1. open a connection
2. use the datareader to query the orgID [user provides an org name ].
3. Then do an ExecuteNonQuery, with that orgID in the where clause.
4. close the connection.

Unfort. .NET gives me an error when I try to do my executeNonQuery because
it says the datareader connection needs to be closed first. What I'm
wondering is why I can't be more efficient and do this with 1 single
connection to the database. Why do i have to split it up.

Thanks
Jason Shohet

[Kevin Spencer]

All you probably need to do is close the DataReader before trying to use it
again.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Big things are made up of
Lots of Little Things.

" Jason Shohet" <as****@hotmail.com> wrote in message
news:Op**************@TK2MSFTNGP11.phx.gbl...

A user provides an organization name, username & password. I've got Access as the back end so no triggers, functions etc. This is what I do:

1. open a connection
2. use the datareader to query the orgID [user provides an org name ].
3. Then do an ExecuteNonQuery, with that orgID in the where clause.
4. close the connection.

Unfort. .NET gives me an error when I try to do my executeNonQuery because
it says the datareader connection needs to be closed first. What I'm
wondering is why I can't be more efficient and do this with 1 single
connection to the database. Why do i have to split it up.

Thanks
Jason Shohet

[Steve C. Orr, MCSD]

Kevin is right.
Additionally, it's more efficient than you think. ADO.NET has built in
connection pooling, so when you close a connection it usually doesn't
actually get closed. It goes back to the pool and waits for you to open
another connection. Then instead of going through all the work of opening a
connection it just grabs your previously opened connection from the pool and
uses it.

--
I hope this helps,
Steve C. Orr, MCSD
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com

" Jason Shohet" <as****@hotmail.com> wrote in message
news:Op**************@TK2MSFTNGP11.phx.gbl...

A user provides an organization name, username & password. I've got Access as the back end so no triggers, functions etc. This is what I do:

1. open a connection
2. use the datareader to query the orgID [user provides an org name ].
3. Then do an ExecuteNonQuery, with that orgID in the where clause.
4. close the connection.

Unfort. .NET gives me an error when I try to do my executeNonQuery because
it says the datareader connection needs to be closed first. What I'm
wondering is why I can't be more efficient and do this with 1 single
connection to the database. Why do i have to split it up.

Thanks
Jason Shohet

[Vaibhav]

you can use a stored procedure call to do both the things for you.
Just pass it the organisation name and let it do the rest with just one
connection, one trip.

" Jason Shohet" <as****@hotmail.com> wrote in message
news:Op**************@TK2MSFTNGP11.phx.gbl...

A user provides an organization name, username & password. I've got Access as the back end so no triggers, functions etc. This is what I do:

1. open a connection
2. use the datareader to query the orgID [user provides an org name ].
3. Then do an ExecuteNonQuery, with that orgID in the where clause.
4. close the connection.

Unfort. .NET gives me an error when I try to do my executeNonQuery because
it says the datareader connection needs to be closed first. What I'm
wondering is why I can't be more efficient and do this with 1 single
connection to the database. Why do i have to split it up.

Thanks
Jason Shohet