Converting Recordset to DataView

  • Thread starter Thread starter kjvt
  • Start date Start date
K

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
 
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
 
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,

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
 
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
 
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
 
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.
Click to expand...
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
 
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)
Click to expand...
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
 
Kees,
everything works great, but the second time the table's rows
collection is empty. As you suggested, I could use recordset events
Click to expand...
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,
Is pRS at EOF? This sounds similar to the second DataView being empty?
Click to expand...
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,
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

kjvt said:
Jay,
Is pRS at EOF? This sounds similar to the second DataView being empty?
Click to expand...
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
Click to expand...
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 said:
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
Click to expand...
allows
you
to create the various DataViews once, without being required to
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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

kjvt said:
Jay,
Is pRS at EOF? This sounds similar to the second DataView being empty?
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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

kjvt said:
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

kjvt said:
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
Click to expand...
to
help
keep sTable in sync, rather then completely refilling it each
Click to expand...
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"
Click to expand...
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
Click to expand...
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
Click to expand...
from
the
recordset, why not fill the dataset direct from the database, I do
not
understand it and am currious?

Cor
Click to expand...
Click to expand...
Click to expand...
 
Jay,

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

Thanks.

Kees

Jay B. Harlow said:
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

kjvt said:
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
Click to expand...
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,
Click to expand...
Click to expand...
I
do
Click to expand...
Click to expand...
 
Jay,

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

Thanks.

Kees

Jay B. Harlow said:
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

kjvt said:
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
Click to expand...
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,
Click to expand...
Click to expand...
I
do
Click to expand...
Click to expand...
 
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

kjvt said:
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
Click to expand...
Click to expand...
Click to expand...
 
Kees,
Its been busy here, hopefully today or tomorrow I will get to trying this...

Jay

kjvt said:
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

kjvt said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
database, I
do
not
understand it and am currious?

Cor
Click to expand...
Click to expand...
Click to expand...
 
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,
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)
Click to expand...

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



Cor said:
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
///
Click to expand...
 
Back
Top