Converting Recordset to DataView

[kjvt]

Based on a prior posting, I've written a function to convert a
recordset to a dataview. The first call to the function for a given
recordset works perfectly, but the second call always returns a
dataview with a count = 0. Can someone explain why and how I might
work around this problem?


Here is the code for my function:
Public Shared Function GetViewFromRS(ByVal pRS As ADODB.Recordset) _
As DataView

Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)

End Function

I call the function passing a recordset created from a SQL Server
stored procedure with the following properties:
CursorLocation = adUseClient
CursorType = adOpenStatic
LockType = adLockBatchOptimistic

The second call to the function always returns a dataview with a count
= 0. However, I can call the function twice passing different
recordsets and it succeeds, but the second call for each recordset
fails.

Here's a function that I use to test this problem:
Public Shared Function RunTwice(ByVal pRS As ADODB.Recordset)
Dim sView1 As DataView = GetViewFromRS(pRS)
MessageBox.Show("First count: " & sView1.Count.ToString)
Dim sView2 As DataView = GetViewFromRS(pRS)
MessageBox.Show("Second count: " & sView2.Count.ToString)
End Function

Interestingly, the problem still occurs when I pass a clone of the
recordset:
Public Shared Function RunTwiceWithClone(ByVal pRS As ADODB.Recordset)
Dim sView1 As DataView = GetViewFromRS(pRS.Clone)
MessageBox.Show("First count: " & sView1.Count.ToString)
Dim sView2 As DataView = GetViewFromRS(pRS.Clone)
MessageBox.Show("Second count: " & sView2.Count.ToString)
End Function

Kees VanTilburg
VanTilburg Enterprises

 

[Jay B. Harlow [MVP - Outlook]]

Kees,
I'm not sure why you want to copy a single recordset into two DataTables, I
would recommend you copy a single recordset to a single DataTable, then
create two DataViews over the single table.

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable )
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable )
MessageBox.Show("Second count: " & sView2.Count.ToString)

If you really do need duplicate Copies of the data, then you may want to
check the position of pRS when the first OleDbDataAdapter.Fill method is
done with it, I suspect it is EOF. Hence the second OleDbDataAdapter.Fill
goes, EOF = no data, I'm done. Otherwise I'm not sure as I do not use
Recordsets in .NET.

If you don't have it you may want to consider getting David Sceppa's book
"Microsoft ADO.NET - Core Reference" from MS Press it is a very good
tutorial on learning ADO.NET as well as a good desk reference once your
using ADO.NET.

Hope this helps
Jay

 

[Cor]

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it from the
recordset, why not fill the dataset direct from the database, I do not
understand it and am currious?

Cor

 

[Microsoft]

Cor,

I'm adding new .Net functionality to an existing application that was built
in vb6 and recently converted to .Net. The app has a data access layer
built in vb6 that is not being converted at this time. In the meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset changes.

Kees

 

[Microsoft]

Jay,

Thanks for your response. The record pointer is a definite possibility,
though I don't think the pointer would be on EOF in my actual code scenario.
I'm away from my code today, but will definitely look at that when I get
back.

BTW, I don't want to copy a single recordset into two datatables, that code
was a simplification of the problem, in case anyone wanted to test it for
themselves. I'm actually converting a recordset to a dataview for display
in a databound list box. Then as the recordset is updated, I convert it
into a dataview again to update the display. I'm using recordsets for the
database updating since I'm working with a legacy ADO data access layer.

Kees

 

[Cor]

Hi Kees,

Thanks for your reply

Reading your answer to Jay B and this I think that I understand it now.

However it is a problem I never saw in this newsgroup and therefore if I was
you, I definitly would ask it also the newsgroup

microsoft.public.dotnet.framework.adonet

But give the reason why with it, because there are a lot of people who want
to keep the recordset because they are afraid to use the dataset.

If I see something about an answer for you, I will answer you also.

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Kees,

want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset changes.

But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which allows you
to create the various DataViews once, without being required to recreate the
DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset object to help
keep sTable in sync, rather then completely refilling it each time... (It
really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync" with the
recordset, you may be able to leverage that to slowly replace the recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased memory
pressure from the various copies of the Data (each DataTable object), the
decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is implemented
as a Singleton someplace handy.

Hope this helps
Jay

 

[kjvt]

Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for expediency I'd
like to get this working.

Any ideas?

Kees

 

[Jay B. Harlow [MVP - Outlook]]

Kees,

everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events

Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant findings...

Hope this helps
Jay

 

[kjvt]

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?

Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

 

[Jay B. Harlow [MVP - Outlook]]

Kees,
Not sure what to offer. The following code works with an OleDbDataAdapter
against an Access database.

Dim adapter As OleDbDataAdapter = ...
Dim select As OleDbCommand = ...

Dim table As New DataTable

select.Parameters("P1").Value = "value1"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

select.Parameters("P1").Value = "value2"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

Where the select is the select command for the adapter. The P1 parameter is
used in the Where clause on the Select commands, that the Adapter is using.

I'm not sure why an ADODB.Recordset would be different... What are the
parameters you are using to open the Recordset? If you manually transverse
the Recordset do you see records?

Hope this helps
Jay

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?

Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

Kees,

sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events

Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant findings...

Hope this helps
Jay

Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:
sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for expediency I'd
like to get this working.

Any ideas?

Kees

On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset changes.
But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which

allows
you

to create the various DataViews once, without being required to

recreate
the

DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset object to help
keep sTable in sync, rather then completely refilling it each time... (It
really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync" with the
recordset, you may be able to leverage that to slowly replace the recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased memory
pressure from the various copies of the Data (each DataTable object), the
decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is implemented
as a Singleton someplace handy.

Hope this helps
Jay

Cor,

I'm adding new .Net functionality to an existing application that was
built
in vb6 and recently converted to .Net. The app has a data access layer
built in vb6 that is not being converted at this time. In the meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset changes.

Kees

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it from the
recordset, why not fill the dataset direct from the database, I do not
understand it and am currious?

Cor

 

[kjvt]

Jay,

Thanks again for your help.

I think the difference is that I'm not reloading the recordset from
the database between Fill calls. In my application, the recordset is
first generated by a stored procedure call, then Fill is called to
create a DataTable copy, then the recordset is manipulated via code
(column values are changed, rows are added or deleted), then
UpdateBatch is called on the recordset, then Fill is called again to
create a new DataTable copy. It is this second call that is failing.

As a simplification, I've tested an found that the stuff between the
two fill calls doesn't have any effect: if I just call Fill twice in
sequence, the first call succeeds and the second fails, even though
the recordset appears unaffected by the first call. Strangely, if I
clone the recordset each time before calling Fill, the second call
still fails. I've also tried resetting the record pointer, removing
filters, etc. The only success I've had is if I recreate the
recordset between Fill calls by calling the stored procedure, then the
second call succeeds.

It's very, very strange to me, but probably makes sense at some level.

Kees

Kees,
Not sure what to offer. The following code works with an OleDbDataAdapter
against an Access database.

Dim adapter As OleDbDataAdapter = ...
Dim select As OleDbCommand = ...

Dim table As New DataTable

select.Parameters("P1").Value = "value1"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

select.Parameters("P1").Value = "value2"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

Where the select is the select command for the adapter. The P1 parameter is
used in the Where clause on the Select commands, that the Adapter is using.

I'm not sure why an ADODB.Recordset would be different... What are the
parameters you are using to open the Recordset? If you manually transverse
the Recordset do you see records?

Hope this helps
Jay

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?

Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

Kees,
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant findings...

Hope this helps
Jay


Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:
sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for expediency I'd
like to get this working.

Any ideas?

Kees

On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.
But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which allows
you
to create the various DataViews once, without being required to recreate
the
DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset object to help
keep sTable in sync, rather then completely refilling it each time... (It
really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync" with the
recordset, you may be able to leverage that to slowly replace the
recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased memory
pressure from the various copies of the Data (each DataTable object), the
decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the
data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable
itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is
implemented
as a Singleton someplace handy.

Hope this helps
Jay

Cor,

I'm adding new .Net functionality to an existing application that was
built
in vb6 and recently converted to .Net. The app has a data access layer
built in vb6 that is not being converted at this time. In the
meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.

Kees

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it from the
recordset, why not fill the dataset direct from the database, I do
not
understand it and am currious?

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Kees,
It sounds like something unique with the Recordset, hence my question on how
you are opening it.

What are the parameters to the Recordset.Open method itself?

Hope this helps
Jay

Jay,

Thanks again for your help.

I think the difference is that I'm not reloading the recordset from
the database between Fill calls. In my application, the recordset is
first generated by a stored procedure call, then Fill is called to
create a DataTable copy, then the recordset is manipulated via code
(column values are changed, rows are added or deleted), then
UpdateBatch is called on the recordset, then Fill is called again to
create a new DataTable copy. It is this second call that is failing.

As a simplification, I've tested an found that the stuff between the
two fill calls doesn't have any effect: if I just call Fill twice in
sequence, the first call succeeds and the second fails, even though
the recordset appears unaffected by the first call. Strangely, if I
clone the recordset each time before calling Fill, the second call
still fails. I've also tried resetting the record pointer, removing
filters, etc. The only success I've had is if I recreate the
recordset between Fill calls by calling the stored procedure, then the
second call succeeds.

It's very, very strange to me, but probably makes sense at some level.

Kees

Kees,
Not sure what to offer. The following code works with an OleDbDataAdapter
against an Access database.

Dim adapter As OleDbDataAdapter = ...
Dim select As OleDbCommand = ...

Dim table As New DataTable

select.Parameters("P1").Value = "value1"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

select.Parameters("P1").Value = "value2"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

Where the select is the select command for the adapter. The P1 parameter is
used in the Where clause on the Select commands, that the Adapter is using.

I'm not sure why an ADODB.Recordset would be different... What are the
parameters you are using to open the Recordset? If you manually transverse
the Recordset do you see records?

Hope this helps
Jay

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?
Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

On Tue, 20 Jan 2004 17:16:53 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant findings...

Hope this helps
Jay


Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:
sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for expediency I'd
like to get this working.

Any ideas?

Kees

On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.
But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which allows
you
to create the various DataViews once, without being required to recreate
the
DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset object

to
help

keep sTable in sync, rather then completely refilling it each

time...
(It

really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync"

with
the

recordset, you may be able to leverage that to slowly replace the
recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased memory
pressure from the various copies of the Data (each DataTable

object),
the

decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the
data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable
itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is
implemented
as a Singleton someplace handy.

Hope this helps
Jay

Cor,

I'm adding new .Net functionality to an existing application that was
built
in vb6 and recently converted to .Net. The app has a data access layer
built in vb6 that is not being converted at this time. In the
meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.

Kees

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it

from
the

recordset, why not fill the dataset direct from the database, I do
not
understand it and am currious?

Cor

 

[kjvt]

Jay,

Here are the parameters I'm setting, all others are default:
CursorLocation = adUseClient
CursorType = adOpenStatic
LockType = adLockBatchOptimistic

Thanks.

Kees

Kees,
It sounds like something unique with the Recordset, hence my question on how
you are opening it.

What are the parameters to the Recordset.Open method itself?

Hope this helps
Jay

Jay,

Thanks again for your help.

I think the difference is that I'm not reloading the recordset from
the database between Fill calls. In my application, the recordset is
first generated by a stored procedure call, then Fill is called to
create a DataTable copy, then the recordset is manipulated via code
(column values are changed, rows are added or deleted), then
UpdateBatch is called on the recordset, then Fill is called again to
create a new DataTable copy. It is this second call that is failing.

As a simplification, I've tested an found that the stuff between the
two fill calls doesn't have any effect: if I just call Fill twice in
sequence, the first call succeeds and the second fails, even though
the recordset appears unaffected by the first call. Strangely, if I
clone the recordset each time before calling Fill, the second call
still fails. I've also tried resetting the record pointer, removing
filters, etc. The only success I've had is if I recreate the
recordset between Fill calls by calling the stored procedure, then the
second call succeeds.

It's very, very strange to me, but probably makes sense at some level.

Kees

parameter

is

used in the Where clause on the Select commands, that the Adapter is using.

I'm not sure why an ADODB.Recordset would be different... What are the
parameters you are using to open the Recordset? If you manually transverse
the Recordset do you see records?

Hope this helps
Jay

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?
Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

On Tue, 20 Jan 2004 17:16:53 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant
findings...

Hope this helps
Jay


Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:
sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for expediency I'd
like to get this working.

Any ideas?

Kees

On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.
But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which
allows
you
to create the various DataViews once, without being required to
recreate
the
DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset object to
help
keep sTable in sync, rather then completely refilling it each time...
(It
really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync" with
the
recordset, you may be able to leverage that to slowly replace the
recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased
memory
pressure from the various copies of the Data (each DataTable object),
the
decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the
data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable
itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is
implemented
as a Singleton someplace handy.

Hope this helps
Jay

Cor,

I'm adding new .Net functionality to an existing application that
was
built
in vb6 and recently converted to .Net. The app has a data access
layer
built in vb6 that is not being converted at this time. In the
meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.

Kees

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it from
the
recordset, why not fill the dataset direct from the database,

I

do

 

[kjvt]

Jay,

Here are the parameters I'm setting, all others are default:
CursorLocation = adUseClient
CursorType = adOpenStatic
LockType = adLockBatchOptimistic

Thanks.

Kees

Kees,
It sounds like something unique with the Recordset, hence my question on how
you are opening it.

What are the parameters to the Recordset.Open method itself?

Hope this helps
Jay

Jay,

Thanks again for your help.

I think the difference is that I'm not reloading the recordset from
the database between Fill calls. In my application, the recordset is
first generated by a stored procedure call, then Fill is called to
create a DataTable copy, then the recordset is manipulated via code
(column values are changed, rows are added or deleted), then
UpdateBatch is called on the recordset, then Fill is called again to
create a new DataTable copy. It is this second call that is failing.

As a simplification, I've tested an found that the stuff between the
two fill calls doesn't have any effect: if I just call Fill twice in
sequence, the first call succeeds and the second fails, even though
the recordset appears unaffected by the first call. Strangely, if I
clone the recordset each time before calling Fill, the second call
still fails. I've also tried resetting the record pointer, removing
filters, etc. The only success I've had is if I recreate the
recordset between Fill calls by calling the stored procedure, then the
second call succeeds.

It's very, very strange to me, but probably makes sense at some level.

Kees

parameter

is

used in the Where clause on the Select commands, that the Adapter is using.

I'm not sure why an ADODB.Recordset would be different... What are the
parameters you are using to open the Recordset? If you manually transverse
the Recordset do you see records?

Hope this helps
Jay

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?
Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

On Tue, 20 Jan 2004 17:16:53 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant
findings...

Hope this helps
Jay


Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:
sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for expediency I'd
like to get this working.

Any ideas?

Kees

On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.
But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which
allows
you
to create the various DataViews once, without being required to
recreate
the
DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset object to
help
keep sTable in sync, rather then completely refilling it each time...
(It
really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync" with
the
recordset, you may be able to leverage that to slowly replace the
recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased
memory
pressure from the various copies of the Data (each DataTable object),
the
decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the
data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable
itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is
implemented
as a Singleton someplace handy.

Hope this helps
Jay

Cor,

I'm adding new .Net functionality to an existing application that
was
built
in vb6 and recently converted to .Net. The app has a data access
layer
built in vb6 that is not being converted at this time. In the
meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.

Kees

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it from
the
recordset, why not fill the dataset direct from the database,

I

do

 

[kjvt]

Jay,

Here are the parameters of the Open method:
sRS.Open cmd

Prior to calling Open, the recordset object parameters are set to:
sRS.CursorLocation = adUseClient
sRS.CursorType = adOpenStatic
sRS.LockType = adLockBatchOptimistic

The command object paramters are set to:
Set cmd.ActiveConnection = mConn 'an open database connection
cmd.CommandText = pSP 'stored proc name
cmd.CommandType = adCmdStoredProc

Kees

Kees,
It sounds like something unique with the Recordset, hence my question on how
you are opening it.

What are the parameters to the Recordset.Open method itself?

Hope this helps
Jay

Jay,

Thanks again for your help.

I think the difference is that I'm not reloading the recordset from
the database between Fill calls. In my application, the recordset is
first generated by a stored procedure call, then Fill is called to
create a DataTable copy, then the recordset is manipulated via code
(column values are changed, rows are added or deleted), then
UpdateBatch is called on the recordset, then Fill is called again to
create a new DataTable copy. It is this second call that is failing.

As a simplification, I've tested an found that the stuff between the
two fill calls doesn't have any effect: if I just call Fill twice in
sequence, the first call succeeds and the second fails, even though
the recordset appears unaffected by the first call. Strangely, if I
clone the recordset each time before calling Fill, the second call
still fails. I've also tried resetting the record pointer, removing
filters, etc. The only success I've had is if I recreate the
recordset between Fill calls by calling the stored procedure, then the
second call succeeds.

It's very, very strange to me, but probably makes sense at some level.

Kees

Kees,
Not sure what to offer. The following code works with an OleDbDataAdapter
against an Access database.

Dim adapter As OleDbDataAdapter = ...
Dim select As OleDbCommand = ...

Dim table As New DataTable

select.Parameters("P1").Value = "value1"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

select.Parameters("P1").Value = "value2"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

Where the select is the select command for the adapter. The P1 parameter is
used in the Where clause on the Select commands, that the Adapter is using.

I'm not sure why an ADODB.Recordset would be different... What are the
parameters you are using to open the Recordset? If you manually transverse
the Recordset do you see records?

Hope this helps
Jay

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?
Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

On Tue, 20 Jan 2004 17:16:53 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant
findings...

Hope this helps
Jay


Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:
sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for expediency I'd
like to get this working.

Any ideas?

Kees

On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.
But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which
allows
you
to create the various DataViews once, without being required to
recreate
the
DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset object to
help
keep sTable in sync, rather then completely refilling it each time...
(It
really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync" with
the
recordset, you may be able to leverage that to slowly replace the
recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased
memory
pressure from the various copies of the Data (each DataTable object),
the
decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the
data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable
itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is
implemented
as a Singleton someplace handy.

Hope this helps
Jay

Cor,

I'm adding new .Net functionality to an existing application that
was
built
in vb6 and recently converted to .Net. The app has a data access
layer
built in vb6 that is not being converted at this time. In the
meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset
changes.

Kees

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it from
the
recordset, why not fill the dataset direct from the database, I do
not
understand it and am currious?

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Kees,
Its been busy here, hopefully today or tomorrow I will get to trying this...

Jay

Jay,

Here are the parameters of the Open method:
sRS.Open cmd

Prior to calling Open, the recordset object parameters are set to:
sRS.CursorLocation = adUseClient
sRS.CursorType = adOpenStatic
sRS.LockType = adLockBatchOptimistic

The command object paramters are set to:
Set cmd.ActiveConnection = mConn 'an open database connection
cmd.CommandText = pSP 'stored proc name
cmd.CommandType = adCmdStoredProc

Kees

Kees,
It sounds like something unique with the Recordset, hence my question on how
you are opening it.

What are the parameters to the Recordset.Open method itself?

Hope this helps
Jay

Jay,

Thanks again for your help.

I think the difference is that I'm not reloading the recordset from
the database between Fill calls. In my application, the recordset is
first generated by a stored procedure call, then Fill is called to
create a DataTable copy, then the recordset is manipulated via code
(column values are changed, rows are added or deleted), then
UpdateBatch is called on the recordset, then Fill is called again to
create a new DataTable copy. It is this second call that is failing.

As a simplification, I've tested an found that the stuff between the
two fill calls doesn't have any effect: if I just call Fill twice in
sequence, the first call succeeds and the second fails, even though
the recordset appears unaffected by the first call. Strangely, if I
clone the recordset each time before calling Fill, the second call
still fails. I've also tried resetting the record pointer, removing
filters, etc. The only success I've had is if I recreate the
recordset between Fill calls by calling the stored procedure, then the
second call succeeds.

It's very, very strange to me, but probably makes sense at some level.

Kees

On Thu, 22 Jan 2004 01:13:25 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
Not sure what to offer. The following code works with an OleDbDataAdapter
against an Access database.

Dim adapter As OleDbDataAdapter = ...
Dim select As OleDbCommand = ...

Dim table As New DataTable

select.Parameters("P1").Value = "value1"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

select.Parameters("P1").Value = "value2"
table.Clear()
adapter.Fill(table)
For Each row As DataRow in Table.Rows
Debug.WriteLine(row!Field1)
Next

Where the select is the select command for the adapter. The P1

parameter
is

used in the Where clause on the Select commands, that the Adapter is using.

I'm not sure why an ADODB.Recordset would be different... What are the
parameters you are using to open the Recordset? If you manually transverse
the Recordset do you see records?

Hope this helps
Jay

Jay,

Is pRS at EOF? This sounds similar to the second DataView being empty?
Cor suggested this as well, but I inserted a pRS.MoveFirst prior to
the Fill method and received the same result.

BTW, yesterday I posted this problem on the adonet newsgroup and have
not yet received any guidance.

Kees

On Tue, 20 Jan 2004 17:16:53 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
Is pRS at EOF? This sounds similar to the second DataView being empty?

I'll try to play with this later and report back any significant
findings...

Hope this helps
Jay


Jay,

Thanks, I think this is good advice. However, there's still a problem
with the sAdapter.Fill method. The first time I execute this code:
sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
to trigger like modifications to the datatable, but for

expediency
I'd

like to get this working.

Any ideas?

Kees

On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"

Kees,
want to use .Net controls and databinding, so I need to

convert
to a

dataview, potentially multiple times as the data in the recordset
changes.
But do you need to convert it to a NEW DataTable each time?

I would think if you convert it to a SINGLE DataTable, then when the
recordset changes convert it to that same SINGLE DataTable. Which
allows
you
to create the various DataViews once, without being required to
recreate
the
DataViews, which means you do not need to rebind!

Something like:

Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
Dim sTable As System.Data.DataTable = New DataTable

sAdapter.Fill(sTable, pRS)

Dim sView1 As DataView = New DataView(sTable)
MessageBox.Show("First count: " & sView1.Count.ToString)

Dim sView2 As DataView = New DataView(sTable)
MessageBox.Show("Second count: " & sView2.Count.ToString)


Later elsewhere when "pRS" changes:

sTable.Clear()
Dim sAdapter As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter
sAdapter.Fill(sTable, pRS)

You may be able to use some of the events of the Recordset

object
to

help
keep sTable in sync, rather then completely refilling it each time...
(It
really depends on what happens when the recordset changes).

If you can get a single DataTable with a copy that is "in sync" with
the
recordset, you may be able to leverage that to slowly replace the
recordset
with a Dataset itself...

My concern with a new DataTable for each DataView is the increased
memory
pressure from the various copies of the Data (each DataTable object),
the
decreased performance from coping the Data, and simply having multiple
copies of the Data. What I'm suggesting is have only 2 copies of the
data,
the copy in the Recordset and a copy in 1 DataTable. As the DataTable
itself
supports multiple DataViews on it.

BTW: I would actually make sTable a member of a DataSet that is
implemented
as a Singleton someplace handy.

Hope this helps
Jay

Cor,

I'm adding new .Net functionality to an existing application that
was
built
in vb6 and recently converted to .Net. The app has a data access
layer
built in vb6 that is not being converted at this time. In the
meantime, I
want to use .Net controls and databinding, so I need to

convert
to a

dataview, potentially multiple times as the data in the recordset
changes.

Kees

Hi Kees,

What you want to archieve,

The dataset stays a disconnected dataset even if you fill it from
the
recordset, why not fill the dataset direct from the

database, I
do

not
understand it and am currious?

Cor

 

[Cor]

Hallo Kees,

Because I'v seen Jay B has it to busy.

Can you try this code I have pasted in bellow?

It is a complete test you only have to open a new project and paste this in
and make a directory on your C: drive test1 or whatever if you change the
code.

You have to add a datagrid on your form and set the references to com
microsoft.Adodb and adox ext 2.7 for dll and security.

(And I did not do anything more than was necessary for the sample and I also
do not know how you could do updates in this way. This was for me also the
first time that I did this. It does nothing more than the subject from this
message)

And I am of course curious to the result.

I hope this helps?

Cor

\\\
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Add a datagrid to the form
'Set a reference to
'microsoft ADODB
'microsoft adox ext 2.7 for dll and security
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\test1\dbKees.mdb") Then
System.IO.File.Delete("C:\test1\dbKees.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test1\dbKees.mdb")
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim connectionstring As String = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\test1\dbKees.mdb;User Id=admin;Password=;"
conn1.ConnectionString = connectionstring
conn1.Open()
Dim cmd1 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
cmd1.ExecuteNonQuery()
For i As Integer = 1 To 9
cmd1.Parameters.Clear()
cmd1.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd1.Parameters.Add(New OleDb.OleDbParameter _
("@a", OleDb.OleDbType.Integer)).Value = i
cmd1.Parameters.Add(New OleDb.OleDbParameter _
("@b", OleDb.OleDbType.Char, 20)).Value = Chr(64 + i)
cmd1.ExecuteNonQuery()
Next
cmd1.CommandText = "Select * from tbl1"
Dim da1 As OleDb.OleDbDataAdapter = _
New OleDb.OleDbDataAdapter(cmd1)
conn1.Close()
Dim conn2 As New ADODB.Connection
Dim strProvider As String = connectionstring
conn2.Open(strProvider)
Dim rs As New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs.Open("select * from tbl1", conn2)
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
da.Fill(dt, rs)
Dim dv As New DataView(dt)
Me.DataGrid1.DataSource = dv
End Sub
///

 

[Cor]

Hi Jay B,

I did some work for you.

Has to be in the thread already

)

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,
Thanks for the sample, the problem Kees was having (correct me if I am wrong
Kees) is he could not call Fill twice with the same record set.

Adding to your sample Cor:

Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
da.Fill(dt, rs)

Dim da2 As New OleDb.OleDbDataAdapter
da2.Fill(dt, rs)

I can fill the data table any number of Times I want, with the same
ADODB.RecordSet!

So now I'm not sure if its something to do with how Kees is doing it, or
something to do with SQL Server as opposed to Access...

As using code similar to the following I can fill that DataTable any number
of times, from the same ADODB.Recordset.

Private Sub SqlServerDataBase()
Const PubsDataBase As String = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Data Source=.;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=My
Workstation;Use Encryption for Data=False;Tag with column collation when
possible=False;Initial Catalog=pubs;"
Dim conn2 As New ADODB.Connection
Dim strProvider As String = PubsDataBase
conn2.Open(strProvider)
Dim rs As New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs.Open("select * from titles", conn2)
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
da.Fill(dt, rs)

Dim da2 As New OleDb.OleDbDataAdapter
da2.Fill(dt, rs)

da.Fill(dt, rs)
da2.Fill(dt, rs)

Dim dv As New DataView(dt)
Me.DataGrid1.DataSource = dv

End Sub

We'll have to see what Kees says.

Thanks
Jay

Hallo Kees,

Because I'v seen Jay B has it to busy.

Can you try this code I have pasted in bellow?

It is a complete test you only have to open a new project and paste this in
and make a directory on your C: drive test1 or whatever if you change the
code.

You have to add a datagrid on your form and set the references to com
microsoft.Adodb and adox ext 2.7 for dll and security.

(And I did not do anything more than was necessary for the sample and I also
do not know how you could do updates in this way. This was for me also the
first time that I did this. It does nothing more than the subject from this
message)

And I am of course curious to the result.

I hope this helps?

Cor

\\\
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Add a datagrid to the form
'Set a reference to
'microsoft ADODB
'microsoft adox ext 2.7 for dll and security
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\test1\dbKees.mdb") Then
System.IO.File.Delete("C:\test1\dbKees.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test1\dbKees.mdb")
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim connectionstring As String = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\test1\dbKees.mdb;User Id=admin;Password=;"
conn1.ConnectionString = connectionstring
conn1.Open()
Dim cmd1 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
cmd1.ExecuteNonQuery()
For i As Integer = 1 To 9
cmd1.Parameters.Clear()
cmd1.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd1.Parameters.Add(New OleDb.OleDbParameter _
("@a", OleDb.OleDbType.Integer)).Value = i
cmd1.Parameters.Add(New OleDb.OleDbParameter _
("@b", OleDb.OleDbType.Char, 20)).Value = Chr(64 + i)
cmd1.ExecuteNonQuery()
Next
cmd1.CommandText = "Select * from tbl1"
Dim da1 As OleDb.OleDbDataAdapter = _
New OleDb.OleDbDataAdapter(cmd1)
conn1.Close()
Dim conn2 As New ADODB.Connection
Dim strProvider As String = connectionstring
conn2.Open(strProvider)
Dim rs As New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs.Open("select * from tbl1", conn2)
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
da.Fill(dt, rs)
Dim dv As New DataView(dt)
Me.DataGrid1.DataSource = dv
End Sub
///