I have been tasked to convert the methods in a data access class from
returning an open OdbcDataReader to a DataTable. I use the standard
type of code, like:

adapter = new OdbcDataAdapter();
adapter.SelectCommand = command;
dataSet = new DataSet();
adapter.Fill(dataSet, "Data");
adapter.Dispose();
dataTable = dataSet.Tables["Data"];

Where command is a command with an appropriate SQL SELECT statement.

This works fine UNLESS the connection that the command is associated
with is a connection that has an open Transaction. The adapter.Fill
method times out.

Since the row being read is the same row just inserted using the same
transaction, I tried adding IsolationLevel.ReadUncommitted to the
BeginTransaction method, but that did not help. Really didn't expect
it to since a OdbcDataReader read the row just fine with the default
isolation level.

Any help would be appreciated!!

I would run sp_lock to see if a lock contention is blocking the
operation--of course this assumes you're using SQL Server, but now that I
see you're using ODBC, it probably isn't and you're basically on your own
AFA diagnostic tools.
I would also tend to lean toward simpler code: (pseudo code follows)

Dim tb as New DataTable
Dim dr as xxDataReader
dim cmd as new xxCommand(MySelectStatement, myConnection)
cn.open
dr=cmd.ExecuteReader
tb.load (dr)
cn.close

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________



"Joe Cool" <(E-Mail Removed)> wrote in message
news:5e686a36-e0b7-47a3-9f54-(E-Mail Removed)...
> I have been tasked to convert the methods in a data access class from
> returning an open OdbcDataReader to a DataTable. I use the standard
> type of code, like:
>
> adapter = new OdbcDataAdapter();
> adapter.SelectCommand = command;
> dataSet = new DataSet();
> adapter.Fill(dataSet, "Data");
> adapter.Dispose();
> dataTable = dataSet.Tables["Data"];
>
> Where command is a command with an appropriate SQL SELECT statement.
>
> This works fine UNLESS the connection that the command is associated
> with is a connection that has an open Transaction. The adapter.Fill
> method times out.
>
> Since the row being read is the same row just inserted using the same
> transaction, I tried adding IsolationLevel.ReadUncommitted to the
> BeginTransaction method, but that did not help. Really didn't expect
> it to since a OdbcDataReader read the row just fine with the default
> isolation level.
>
> Any help would be appreciated!!

On Jul 31, 1:14*pm, "William Vaughn \(MVP\)" <bil...@Betav.com> wrote:
> I would run sp_lock to see if a lock contention is blocking the
> operation--of course this assumes you're using SQL Server, but now that I
> see you're using ODBC, it probably isn't and you're basically on your own
> AFA diagnostic tools.
> I would also tend to lean toward simpler code: (pseudo code follows)

Actually we do use a SQL Server. We use ODBC to connect for various
reasons.

But the issue was not a deadlock causing the problem. I am using a
local database set up just for testing code changes and no one else
has any access to it.

>
> Dim tb as New DataTable
> Dim dr as xxDataReader
> dim cmd as new xxCommand(MySelectStatement, myConnection)
> cn.open
> dr=cmd.ExecuteReader
> tb.load (dr)
> cn.close
>
> hth

Actually, your pseudo-code helped. The way I was loading the data
table was the way I learned it from sample code from Microsoft. I had
no idea it could be done so much simpler!!

>
>
> "Joe Cool" <joecool1...@live.com> wrote in message
>
> news:5e686a36-e0b7-47a3-9f54-(E-Mail Removed)...
>
>
>
> > I have been tasked to convert the methods in a data access class from
> > returning an open OdbcDataReader to a DataTable. I use the standard
> > type of code, like:
>
> > adapter = new OdbcDataAdapter();
> > adapter.SelectCommand = command;
> > dataSet = new DataSet();
> > adapter.Fill(dataSet, "Data");
> > adapter.Dispose();
> > dataTable = dataSet.Tables["Data"];
>
> > Where command is a command with an appropriate SQL SELECT statement.
>
> > This works fine UNLESS the connection that the command is associated
> > with is a connection that has an open Transaction. The adapter.Fill
> > method times out.
>
> > Since the row being read is the same row just inserted using the same
> > transaction, I tried adding IsolationLevel.ReadUncommitted to the
> > BeginTransaction method, but that did not help. Really didn't expect
> > it to since a OdbcDataReader read the row just fine with the default
> > isolation level.
>
> > Any help would be appreciated!!

In my experience, it's perfectly reasonable to expect that an application
can easily lock itself out from other operations.
With SQL Server you can run the profiler to see what's getting executed and
check sp_lock for contention.


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________



"Joe Cool" <(E-Mail Removed)> wrote in message
news:c9a3004d-b922-440c-b64c-(E-Mail Removed)...
> On Jul 31, 1:14 pm, "William Vaughn \(MVP\)" <bil...@Betav.com> wrote:
>> I would run sp_lock to see if a lock contention is blocking the
>> operation--of course this assumes you're using SQL Server, but now that I
>> see you're using ODBC, it probably isn't and you're basically on your own
>> AFA diagnostic tools.
>> I would also tend to lean toward simpler code: (pseudo code follows)
>
> Actually we do use a SQL Server. We use ODBC to connect for various
> reasons.
>
> But the issue was not a deadlock causing the problem. I am using a
> local database set up just for testing code changes and no one else
> has any access to it.
>
>>
>> Dim tb as New DataTable
>> Dim dr as xxDataReader
>> dim cmd as new xxCommand(MySelectStatement, myConnection)
>> cn.open
>> dr=cmd.ExecuteReader
>> tb.load (dr)
>> cn.close
>>
>> hth
>
> Actually, your pseudo-code helped. The way I was loading the data
> table was the way I learned it from sample code from Microsoft. I had
> no idea it could be done so much simpler!!
>
>>
>>
>> "Joe Cool" <joecool1...@live.com> wrote in message
>>
>> news:5e686a36-e0b7-47a3-9f54-(E-Mail Removed)...
>>
>>
>>
>> > I have been tasked to convert the methods in a data access class from
>> > returning an open OdbcDataReader to a DataTable. I use the standard
>> > type of code, like:
>>
>> > adapter = new OdbcDataAdapter();
>> > adapter.SelectCommand = command;
>> > dataSet = new DataSet();
>> > adapter.Fill(dataSet, "Data");
>> > adapter.Dispose();
>> > dataTable = dataSet.Tables["Data"];
>>
>> > Where command is a command with an appropriate SQL SELECT statement.
>>
>> > This works fine UNLESS the connection that the command is associated
>> > with is a connection that has an open Transaction. The adapter.Fill
>> > method times out.
>>
>> > Since the row being read is the same row just inserted using the same
>> > transaction, I tried adding IsolationLevel.ReadUncommitted to the
>> > BeginTransaction method, but that did not help. Really didn't expect
>> > it to since a OdbcDataReader read the row just fine with the default
>> > isolation level.
>>
>> > Any help would be appreciated!!

On Jul 31, 3:22*pm, "William Vaughn \(MVP\)" <bil...@Betav.com> wrote:
> In my experience, it's perfectly reasonable to expect that an application
> can easily lock itself out from other operations.
> With SQL Server you can run the profiler to see what's getting executed and
> check sp_lock for contention.

I had run the profiler. An Insert had started and completed before the
select started and later timed ot.

>
> --
>
> "Joe Cool" <joecool1...@live.com> wrote in message
>
> news:c9a3004d-b922-440c-b64c-(E-Mail Removed)...
>
>
>
> > On Jul 31, 1:14 pm, "William Vaughn \(MVP\)" <bil...@Betav.com> wrote:
> >> I would run sp_lock to see if a lock contention is blocking the
> >> operation--of course this assumes you're using SQL Server, but now that I
> >> see you're using ODBC, it probably isn't and you're basically on your own
> >> AFA diagnostic tools.
> >> I would also tend to lean toward simpler code: (pseudo code follows)
>
> > Actually we do use a SQL Server. We use ODBC to connect for various
> > reasons.
>
> > But the issue was not a deadlock causing the problem. I am using a
> > local database set up just for testing code changes and no one else
> > has any access to it.
>
> >> Dim tb as New DataTable
> >> Dim dr as xxDataReader
> >> dim cmd as new xxCommand(MySelectStatement, myConnection)
> >> cn.open
> >> dr=cmd.ExecuteReader
> >> tb.load (dr)
> >> cn.close
>
> >> hth
>
> > Actually, your pseudo-code helped. The way I was loading the data
> > table was the way I learned it from sample code from Microsoft. I had
> > no idea it could be done so much simpler!!
>
> >> "Joe Cool" <joecool1...@live.com> wrote in message
>
> >>news:5e686a36-e0b7-47a3-9f54-(E-Mail Removed)....
>
> >> > I have been tasked to convert the methods in a data access class from
> >> > returning an open OdbcDataReader to a DataTable. I use the standard
> >> > type of code, like:
>
> >> > adapter = new OdbcDataAdapter();
> >> > adapter.SelectCommand = command;
> >> > dataSet = new DataSet();
> >> > adapter.Fill(dataSet, "Data");
> >> > adapter.Dispose();
> >> > dataTable = dataSet.Tables["Data"];
>
> >> > Where command is a command with an appropriate SQL SELECT statement.
>
> >> > This works fine UNLESS the connection that the command is associated
> >> > with is a connection that has an open Transaction. The adapter.Fill
> >> > method times out.
>
> >> > Since the row being read is the same row just inserted using the same
> >> > transaction, I tried adding IsolationLevel.ReadUncommitted to the
> >> > BeginTransaction method, but that did not help. Really didn't expect
> >> > it to since a OdbcDataReader read the row just fine with the default
> >> > isolation level.
>
> >> > Any help would be appreciated!!