By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,627 Members | 1,188 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,627 IT Pros & Developers. It's quick & easy.

Converting Recordset to DataView

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
36 Replies


P: n/a
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

"kjvt" <do********@excite.com> wrote in message
news:lb********************************@4ax.com...
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

Nov 20 '05 #2

P: n/a
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
Nov 20 '05 #3

P: n/a
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

"Cor" <no*@non.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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

Nov 20 '05 #4

P: n/a
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
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

"kjvt" <do********@excite.com> wrote in message
news:lb********************************@4ax.com...
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



Nov 20 '05 #5

P: n/a
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
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

Nov 20 '05 #6

P: n/a
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

"Microsoft" <do********@excite.com> wrote in message
news:uB**************@TK2MSFTNGP10.phx.gbl... 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

"Cor" <no*@non.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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


Nov 20 '05 #7

P: n/a
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]"
<Ja************@msn.com> wrote:
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

"Microsoft" <do********@excite.com> wrote in message
news:uB**************@TK2MSFTNGP10.phx.gbl...
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

"Cor" <no*@non.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
> 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
>
>



Nov 20 '05 #8

P: n/a
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
"kjvt" <do********@excite.com> wrote in message
news:0s********************************@4ax.com... 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]"
<Ja************@msn.com> wrote:
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 youto create the various DataViews once, without being required to recreate theDataViews, 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 recordsetwith 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 itselfsupports multiple DataViews on it.

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

Hope this helps
Jay

"Microsoft" <do********@excite.com> wrote in message
news:uB**************@TK2MSFTNGP10.phx.gbl...
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

"Cor" <no*@non.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
> 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
>
>

Nov 20 '05 #9

P: n/a
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]"
<Ja************@msn.com> wrote:
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
"kjvt" <do********@excite.com> wrote in message
news:0s********************************@4ax.com.. .
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]"
<Ja************@msn.com> wrote:
>Kees,
>> want to use .Net controls and databinding, so I need to convert to a
>> dataview, potentially multiple times as the data in the recordsetchanges. >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 allowsyou >to create the various DataViews once, without being required to recreatethe >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 therecordset >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 thedata, >the copy in the Recordset and a copy in 1 DataTable. As the DataTableitself >supports multiple DataViews on it.
>
>BTW: I would actually make sTable a member of a DataSet that isimplemented >as a Singleton someplace handy.
>
>Hope this helps
>Jay
>
>"Microsoft" <do********@excite.com> wrote in message
>news:uB**************@TK2MSFTNGP10.phx.gbl...
>> 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 themeantime, I >> want to use .Net controls and databinding, so I need to convert to a
>> dataview, potentially multiple times as the data in the recordsetchanges. >>
>> Kees
>>
>> "Cor" <no*@non.com> wrote in message
>> news:%2****************@TK2MSFTNGP12.phx.gbl...
>> > 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 donot >> > understand it and am currious?
>> >
>> > Cor
>> >
>> >
>>
>>
>


Nov 20 '05 #10

P: n/a
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" <do********@excite.com> wrote in message
news:ev********************************@4ax.com...
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]"
<Ja************@msn.com> wrote:
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
"kjvt" <do********@excite.com> wrote in message
news:0s********************************@4ax.com.. .
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]"
<Ja************@msn.com> wrote:

>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
recreatethe
>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
>
>"Microsoft" <do********@excite.com> wrote in message
>news:uB**************@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>> "Cor" <no*@non.com> wrote in message
>> news:%2****************@TK2MSFTNGP12.phx.gbl...
>> > 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
>> >
>> >
>>
>>
>

Nov 20 '05 #11

P: n/a
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]"
<Ja************@msn.com> wrote:
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" <do********@excite.com> wrote in message
news:ev********************************@4ax.com.. .
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]"
<Ja************@msn.com> wrote:
>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 significantfindings... >
>Hope this helps
>Jay
>
>
>"kjvt" <do********@excite.com> wrote in message
>news:0s********************************@4ax.com.. .
>> 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 = NewOleDb.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]"
>> <Ja************@msn.com> wrote:
>>
>> >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. Whichallows >you
>> >to create the various DataViews once, without being required torecreate >the
>> >DataViews, which means you do not need to rebind!
>> >
>> >Something like:
>> >
>> > Dim sAdapter As OleDb.OleDbDataAdapter = NewOleDb.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 = NewOleDb.OleDbDataAdapter >> > sAdapter.Fill(sTable, pRS)
>> >
>> >You may be able to use some of the events of the Recordset object tohelp >> >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" withthe >> >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 increasedmemory >> >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
>> >
>> >"Microsoft" <do********@excite.com> wrote in message
>> >news:uB**************@TK2MSFTNGP10.phx.gbl...
>> >> Cor,
>> >>
>> >> I'm adding new .Net functionality to an existing application thatwas >> >built
>> >> in vb6 and recently converted to .Net. The app has a data accesslayer >> >> 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
>> >>
>> >> "Cor" <no*@non.com> wrote in message
>> >> news:%2****************@TK2MSFTNGP12.phx.gbl...
>> >> > Hi Kees,
>> >> >
>> >> > What you want to archieve,
>> >> >
>> >> > The dataset stays a disconnected dataset even if you fill it fromthe >> >> > recordset, why not fill the dataset direct from the database, I do
>not
>> >> > understand it and am currious?
>> >> >
>> >> > Cor
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>


Nov 20 '05 #12

P: n/a
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" <do********@excite.com> wrote in message
news:no********************************@4ax.com...
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]"
<Ja************@msn.com> wrote:
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 isused 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 transversethe Recordset do you see records?

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:ev********************************@4ax.com.. .
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]"
<Ja************@msn.com> wrote:

>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
>
>
>"kjvt" <do********@excite.com> wrote in message
>news:0s********************************@4ax.com.. .
>> 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]"
>> <Ja************@msn.com> wrote:
>>
>> >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"
withthe
>> >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
>> >
>> >"Microsoft" <do********@excite.com> wrote in message
>> >news:uB**************@TK2MSFTNGP10.phx.gbl...
>> >> 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
>> >>
>> >> "Cor" <no*@non.com> wrote in message
>> >> news:%2****************@TK2MSFTNGP12.phx.gbl...
>> >> > Hi Kees,
>> >> >
>> >> > What you want to archieve,
>> >> >
>> >> > The dataset stays a disconnected dataset even if you fill it fromthe
>> >> > recordset, why not fill the dataset direct from the database, I

do >not
>> >> > understand it and am currious?
>> >> >
>> >> > Cor
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>

Nov 20 '05 #13

P: n/a
Jay,

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

Thanks.

Kees

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:uS**************@tk2msftngp13.phx.gbl...
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" <do********@excite.com> wrote in message
news:no********************************@4ax.com...
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]"
<Ja************@msn.com> wrote:
Kees,
Not sure what to offer. The following code works with an OleDbDataAdapteragainst 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 transversethe Recordset do you see records?

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:ev********************************@4ax.com.. .
> 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]"
> <Ja************@msn.com> wrote:
>
> >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
> >
> >
> >"kjvt" <do********@excite.com> wrote in message
> >news:0s********************************@4ax.com.. .
> >> 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]"> >> <Ja************@msn.com> wrote:
> >>
> >> >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 tohelp
> >> >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" withthe
> >> >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 increasedmemory
> >> >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
> >> >
> >> >"Microsoft" <do********@excite.com> wrote in message
> >> >news:uB**************@TK2MSFTNGP10.phx.gbl...
> >> >> Cor,
> >> >>
> >> >> I'm adding new .Net functionality to an existing application thatwas
> >> >built
> >> >> in vb6 and recently converted to .Net. The app has a data accesslayer
> >> >> 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
> >> >>
> >> >> "Cor" <no*@non.com> wrote in message
> >> >> news:%2****************@TK2MSFTNGP12.phx.gbl...
> >> >> > Hi Kees,
> >> >> >
> >> >> > What you want to archieve,
> >> >> >
> >> >> > The dataset stays a disconnected dataset even if you fill it fromthe
> >> >> > recordset, why not fill the dataset direct from the database,
I do> >not
> >> >> > understand it and am currious?
> >> >> >
> >> >> > Cor
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >
>



Nov 20 '05 #14

P: n/a
Jay,

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

Thanks.

Kees

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:uS**************@tk2msftngp13.phx.gbl...
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" <do********@excite.com> wrote in message
news:no********************************@4ax.com...
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]"
<Ja************@msn.com> wrote:
Kees,
Not sure what to offer. The following code works with an OleDbDataAdapteragainst 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 transversethe Recordset do you see records?

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:ev********************************@4ax.com.. .
> 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]"
> <Ja************@msn.com> wrote:
>
> >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
> >
> >
> >"kjvt" <do********@excite.com> wrote in message
> >news:0s********************************@4ax.com.. .
> >> 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]"> >> <Ja************@msn.com> wrote:
> >>
> >> >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 tohelp
> >> >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" withthe
> >> >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 increasedmemory
> >> >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
> >> >
> >> >"Microsoft" <do********@excite.com> wrote in message
> >> >news:uB**************@TK2MSFTNGP10.phx.gbl...
> >> >> Cor,
> >> >>
> >> >> I'm adding new .Net functionality to an existing application thatwas
> >> >built
> >> >> in vb6 and recently converted to .Net. The app has a data accesslayer
> >> >> 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
> >> >>
> >> >> "Cor" <no*@non.com> wrote in message
> >> >> news:%2****************@TK2MSFTNGP12.phx.gbl...
> >> >> > Hi Kees,
> >> >> >
> >> >> > What you want to archieve,
> >> >> >
> >> >> > The dataset stays a disconnected dataset even if you fill it fromthe
> >> >> > recordset, why not fill the dataset direct from the database,
I do> >not
> >> >> > understand it and am currious?
> >> >> >
> >> >> > Cor
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >
>


Nov 20 '05 #15

P: n/a
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
On Thu, 22 Jan 2004 18:26:07 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
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" <do********@excite.com> wrote in message
news:no********************************@4ax.com.. .
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]"
<Ja************@msn.com> wrote:
>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 parameteris >used in the Where clause on the Select commands, that the Adapter isusing. >
>I'm not sure why an ADODB.Recordset would be different... What are the
>parameters you are using to open the Recordset? If you manuallytransverse >the Recordset do you see records?
>
>Hope this helps
>Jay
>
>"kjvt" <do********@excite.com> wrote in message
>news:ev********************************@4ax.com.. .
>> 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]"
>> <Ja************@msn.com> wrote:
>>
>> >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
>> >
>> >
>> >"kjvt" <do********@excite.com> wrote in message
>> >news:0s********************************@4ax.com.. .
>> >> Jay,
>> >>
>> >> Thanks, I think this is good advice. However, there's still aproblem >> >> 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 expediencyI'd >> >> like to get this working.
>> >>
>> >> Any ideas?
>> >>
>> >> Kees
>> >>
>> >> On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP - Outlook]"
>> >> <Ja************@msn.com> wrote:
>> >>
>> >> >Kees,
>> >> >> want to use .Net controls and databinding, so I need to convertto 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 whenthe >> >> >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 objectto >help
>> >> >keep sTable in sync, rather then completely refilling it eachtime... >(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 DataTableobject), >the
>> >> >decreased performance from coping the Data, and simply havingmultiple >> >> >copies of the Data. What I'm suggesting is have only 2 copies ofthe >> >data,
>> >> >the copy in the Recordset and a copy in 1 DataTable. As theDataTable >> >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
>> >> >
>> >> >"Microsoft" <do********@excite.com> wrote in message
>> >> >news:uB**************@TK2MSFTNGP10.phx.gbl...
>> >> >> 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 convertto a >> >> >> dataview, potentially multiple times as the data in the recordset
>> >changes.
>> >> >>
>> >> >> Kees
>> >> >>
>> >> >> "Cor" <no*@non.com> wrote in message
>> >> >> news:%2****************@TK2MSFTNGP12.phx.gbl...
>> >> >> > Hi Kees,
>> >> >> >
>> >> >> > What you want to archieve,
>> >> >> >
>> >> >> > The dataset stays a disconnected dataset even if you fill itfrom >the
>> >> >> > recordset, why not fill the dataset direct from the database, Ido >> >not
>> >> >> > understand it and am currious?
>> >> >> >
>> >> >> > Cor
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >>
>> >
>>
>


Nov 20 '05 #16

P: n/a
Kees,
Its been busy here, hopefully today or tomorrow I will get to trying this...

Jay

"kjvt" <do********@excite.com> wrote in message
news:bg********************************@4ax.com...
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
On Thu, 22 Jan 2004 18:26:07 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
Kees,
It sounds like something unique with the Recordset, hence my question on howyou are opening it.

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

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:no********************************@4ax.com.. .
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]"
<Ja************@msn.com> wrote:

>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" <do********@excite.com> wrote in message
>news:ev********************************@4ax.com.. .
>> 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]"
>> <Ja************@msn.com> wrote:
>>
>> >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
>> >
>> >
>> >"kjvt" <do********@excite.com> wrote in message
>> >news:0s********************************@4ax.com.. .
>> >> 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 expediencyI'd
>> >> like to get this working.
>> >>
>> >> Any ideas?
>> >>
>> >> Kees
>> >>
>> >> On Sat, 17 Jan 2004 09:54:11 -0600, "Jay B. Harlow [MVP -
Outlook]" >> >> <Ja************@msn.com> wrote:
>> >>
>> >> >Kees,
>> >> >> want to use .Net controls and databinding, so I need to convertto 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 objectto
>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
>> >> >
>> >> >"Microsoft" <do********@excite.com> wrote in message
>> >> >news:uB**************@TK2MSFTNGP10.phx.gbl...
>> >> >> 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 convertto a
>> >> >> dataview, potentially multiple times as the data in the
recordset >> >changes.
>> >> >>
>> >> >> Kees
>> >> >>
>> >> >> "Cor" <no*@non.com> wrote in message
>> >> >> news:%2****************@TK2MSFTNGP12.phx.gbl...
>> >> >> > 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, Ido
>> >not
>> >> >> > understand it and am currious?
>> >> >> >
>> >> >> > Cor
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >>
>> >
>>
>

Nov 20 '05 #17

P: n/a
Cor
Hi Jay B,

I did some work for you.

Has to be in the thread already

:-))

Cor
Nov 20 '05 #18

P: n/a
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
///
Nov 20 '05 #19

P: n/a
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

"Cor" <no*@non.com> wrote in message
news:uS**************@tk2msftngp13.phx.gbl... 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
///

Nov 20 '05 #20

P: n/a
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

"Cor" <no*@non.com> wrote in message
news:uS**************@tk2msftngp13.phx.gbl... 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
///

Nov 20 '05 #21

P: n/a
Jay,

This code works!

And, it helps isolate what is going wrong with my code, but I don't
understand why my code fails. Try the following modification to your
code:

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=MyWorkstation;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)

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)

End Sub
Private 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

When I run this, the second datagrid has no rows. Do you have any
idea what makes this different from your example?

Many thanks to you and Cor for helping me through this.

Kees

On Sun, 1 Feb 2004 14:37:19 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
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


Nov 20 '05 #22

P: n/a
Kees,
This code works! Works? It doesn't work as you expect, as it demonstrates the problem,
correct? ;-)

Interesting, same thing happens here, I should have had a clear between the
two Data.Fills in my example:
da.Fill(dt, rs)
dt.Clear()
da.Fill(dt, rs)


I'll ask some others to see if they have any ideas.

Jay

"kjvt" <do********@excite.com> wrote in message
news:ar********************************@4ax.com... Jay,

This code works!

And, it helps isolate what is going wrong with my code, but I don't
understand why my code fails. Try the following modification to your
code:

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=MyWorkstation;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)

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)

End Sub
Private 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

When I run this, the second datagrid has no rows. Do you have any
idea what makes this different from your example?

Many thanks to you and Cor for helping me through this.

Kees

<<snip>>
Nov 20 '05 #23

P: n/a
Cor
Hallo Kees (and Jay B),

The only thing I really see is that you create two datatables from one
recordset with both the same name in the function, and that datatable has in
my opinion to disapear. But it stays the underlayer from the datagrid. I
think it should not even display one datagrid.

This should be the newest procedure to create an unvisable array of
datatables. (The dataview is no table, but a view on the table). I tried
what happens if I supply the new datatable to the function and than all goes
well.

I tried it while I changed the dataview for the datatable as datasource for
the datagrid (what is a normal datasource), and then both datagrids where
empty. (As I expected it should be).

It took me longer so Jay B did already answer, a pity.

Cor

Private 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


Nov 20 '05 #24

P: n/a
Jay,

On Mon, 2 Feb 2004 16:03:24 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
Kees,
This code works!Works? It doesn't work as you expect, as it demonstrates the problem,
correct? ;-)


Oops. You're right, it really doesn't work. I was fooled by the
missing clear statement.

Interesting, same thing happens here, I should have had a clear between the
two Data.Fills in my example:
> da.Fill(dt, rs)
> dt.Clear() >
> da.Fill(dt, rs)

I'll ask some others to see if they have any ideas.


I'll keep my fingers crossed. I don't really want to write the code
to do this manually.

Jay

"kjvt" <do********@excite.com> wrote in message
news:ar********************************@4ax.com.. .

Kees
Nov 20 '05 #25

P: n/a
Cor
Hi Jay B,

It becomes very intresting, I was curious if it was maybe a table staying on
the heap (special for you I use that word that I always avoid) with a
reference to it from the dataview.

But it is stranger, try this code.

Or do I understand it not totaly anymore and is there an easy explenation.
(there are things totaly unneeded which I normaly never would do, but I do
that to avoid that you will test it again, by example there is in the
documentation that you have to close the recordset for dispossing the
dataset (I did try it with a dataset also and got the same result).

So I am curious what you think about this.

Cor

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)
rs.Close()
conn2.Close()
rs = Nothing
DirectCast(Me.DataGrid1.DataSource, DataView).Table.Dispose()
DirectCast(Me.DataGrid2.DataSource, DataView).Table.Dispose()
GC.Collect()
Dim sta As String = DirectCast(Me.DataGrid1.DataSource,
DataView).Table.TableName
Dim stb As String = DirectCast(Me.DataGrid2.DataSource,
DataView).Table.TableName
'with debugging sta = "0" and stb is "1"

End Sub
Private Function GetViewFromRS(ByVal pRS As ADODB.Recordset) As DataView
Static jay As Integer = 0
Dim sAdapter As New OleDb.OleDbDataAdapter
Dim sTable As New DataTable(jay.ToString)
sAdapter.Fill(sTable, pRS)
GetViewFromRS = New DataView(sTable)
sTable = Nothing
jay += 1
End Function

Nov 20 '05 #26

P: n/a
Cor,
As far as I can tell, something happens to the ADODB.Recordset when you use
OleDbDataAdapter.Fill method on the recordset, such that another call to
OleDbDataAdapter.Fill on that same ADODB.Recordset does not work a second
time.

It doesn't really matter how many DataTables you have (one or two) its
subsequent calls to OleDbDataAdapter.Fill that do not do anything...

I'm in the process of submitting something to MS about this, so we shall see
what they have to say. I tried searching the KB but did not find anything, I
don't remember any in Sceppa's book about this either...

Hope this helps
Jay

"Cor" <no*@non.com> wrote in message
news:u$**************@TK2MSFTNGP12.phx.gbl...
Hi Jay B,

It becomes very intresting, I was curious if it was maybe a table staying on the heap (special for you I use that word that I always avoid) with a
reference to it from the dataview.

But it is stranger, try this code.

Or do I understand it not totaly anymore and is there an easy explenation.
(there are things totaly unneeded which I normaly never would do, but I do
that to avoid that you will test it again, by example there is in the
documentation that you have to close the recordset for dispossing the
dataset (I did try it with a dataset also and got the same result).

So I am curious what you think about this.

Cor

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)
rs.Close()
conn2.Close()
rs = Nothing
DirectCast(Me.DataGrid1.DataSource, DataView).Table.Dispose()
DirectCast(Me.DataGrid2.DataSource, DataView).Table.Dispose()
GC.Collect()
Dim sta As String = DirectCast(Me.DataGrid1.DataSource,
DataView).Table.TableName
Dim stb As String = DirectCast(Me.DataGrid2.DataSource,
DataView).Table.TableName
'with debugging sta = "0" and stb is "1"

End Sub
Private Function GetViewFromRS(ByVal pRS As ADODB.Recordset) As DataView Static jay As Integer = 0
Dim sAdapter As New OleDb.OleDbDataAdapter
Dim sTable As New DataTable(jay.ToString)
sAdapter.Fill(sTable, pRS)
GetViewFromRS = New DataView(sTable)
sTable = Nothing
jay += 1
End Function

Nov 20 '05 #27

P: n/a
Cor
Hi Jay,

I think that conclusion is not right, the main problem for me is that a
dataset that is declared in a function holds scope, there is no way to get
rid of that thing (except setting it to nothing outside the function (you do
not see that but that I did try also by using that casting of the dataview).

When I do the same function normal providing the datatable to the function
everything goes ok.
\\\
private function(byval dt as datatable, byval rs as recordset) as dataview
etc
///

It is of course a strange instruction because you cannot do anyting with
that dataview

Cor
Nov 20 '05 #28

P: n/a
Kees,
If you add a Recordset.Requery just before the OleDbDataAdapter.Fill it will
function as you expect, however I not sure you want to have to Requery the
database each time...
Private 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

pRS.Requery()
sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)
End Function
Of course my suggestion of keeping a single DataTable remains, having
GetViewFromRS create a new DataTable each time is resource wasteful.

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:ar********************************@4ax.com... Jay,

This code works!

And, it helps isolate what is going wrong with my code, but I don't
understand why my code fails. Try the following modification to your
code:

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=MyWorkstation;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)

Me.DataGrid1.DataSource = GetViewFromRS(rs)
Me.DataGrid2.DataSource = GetViewFromRS(rs)

End Sub
Private 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

When I run this, the second datagrid has no rows. Do you have any
idea what makes this different from your example?

Many thanks to you and Cor for helping me through this.

Kees

On Sun, 1 Feb 2004 14:37:19 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
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

Nov 20 '05 #29

P: n/a
Cor,
I think that conclusion is not right, the main problem for me is that a
dataset that is declared in a function holds scope, there is no way to get Don't look at Kees's code then, look at my code!

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 da1 As New OleDb.OleDbDataAdapter
Dim dt1 As New DataTable
da1.Fill(dt1, rs)

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

Me.DataGrid1.DataSource = dt1
Me.DataGrid1.DataSource = dt2

End Sub

dt2 is empty while dt1 has records in it, Kees & I would not expect dt2 to
be empty, as the Recordset itself has records in it. If you use Requery on
rs before filling dt2, then dt2 has records.

It seems that the GetViewFromRS is largely just confusing the issue, hence
in my sample that shows the underlying problem does not include that
function.

Hope this helps
Jay

"Cor" <no*@non.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... Hi Jay,

I think that conclusion is not right, the main problem for me is that a
dataset that is declared in a function holds scope, there is no way to get
rid of that thing (except setting it to nothing outside the function (you do not see that but that I did try also by using that casting of the dataview).
When I do the same function normal providing the datatable to the function
everything goes ok.
\\\
private function(byval dt as datatable, byval rs as recordset) as dataview
etc
///

It is of course a strange instruction because you cannot do anyting with
that dataview

Cor

Nov 20 '05 #30

P: n/a
Cor
Hi Jay,

I am not looking at Kees code anymore, I know how to get it working on 100
different ways, but please have a look at the part that I did send.

It confuses me that that datatable is still alive while it is created in a
sub.

Therefore because you are in that part theoretical good, I ask if I see
something real wrong.

I am not even able to kill that datatable.
not with dispose and not with = nothing
while it is created in that sub.

Cor
Nov 20 '05 #31

P: n/a
Jay,

On Tue, 3 Feb 2004 10:42:36 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
Kees,
If you add a Recordset.Requery just before the OleDbDataAdapter.Fill it will
function as you expect, however I not sure you want to have to Requery the
database each time...
Unfortunately, I cannot requery. There may be unsaved changes in the
recordset that I want to display, but not commit to the database. I
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table.
Private 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

pRS.Requery()
sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)
End Function


Of course my suggestion of keeping a single DataTable remains, having
GetViewFromRS create a new DataTable each time is resource wasteful.


Do you mean wasteful in terms of the time it takes to create the
table? I thought that the prior table would be released when I bind
the datagrid to the new table/view, so the memory would be freed (once
garbage collected). Anyway, I agree that there's no need to create a
table each time. This will be especially true when I fill the table
manually.

Thank you again.

Kees

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:ar********************************@4ax.com.. .

Nov 20 '05 #32

P: n/a
Cor,
The DataTable will continue to exist as long as you have a reference to it.
Seeing as you were returning a DataView, which you set the
DataGrid.DataSource property to, the DataTable still has a reference to it.

Remember that DataView has a reference to the DataTable it is built over.

Hope this helps
Jay

"Cor" <no*@non.com> wrote in message
news:Oo*************@TK2MSFTNGP11.phx.gbl...
Hi Jay,

I am not looking at Kees code anymore, I know how to get it working on 100
different ways, but please have a look at the part that I did send.

It confuses me that that datatable is still alive while it is created in a
sub.

Therefore because you are in that part theoretical good, I ask if I see
something real wrong.

I am not even able to kill that datatable.
not with dispose and not with = nothing
while it is created in that sub.

Cor

Nov 20 '05 #33

P: n/a
Kees,
Do you mean wasteful in terms of the time it takes to create the
table? I thought that the prior table would be released when I bind We discussed this and I thought you understood.

Its wasteful in both the time it takes to build the DataTable, and its
wasteful in that you have N copies of the data, you really only need 2
copies of the data. One in the Recordset and one in the DataTable!

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:up********************************@4ax.com... Jay,

On Tue, 3 Feb 2004 10:42:36 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
Kees,
If you add a Recordset.Requery just before the OleDbDataAdapter.Fill it willfunction as you expect, however I not sure you want to have to Requery thedatabase each time...


Unfortunately, I cannot requery. There may be unsaved changes in the
recordset that I want to display, but not commit to the database. I
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table.
Private 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


pRS.Requery()
sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)
End Function


Of course my suggestion of keeping a single DataTable remains, having
GetViewFromRS create a new DataTable each time is resource wasteful.


Do you mean wasteful in terms of the time it takes to create the
table? I thought that the prior table would be released when I bind
the datagrid to the new table/view, so the memory would be freed (once
garbage collected). Anyway, I agree that there's no need to create a
table each time. This will be especially true when I fill the table
manually.

Thank you again.

Kees

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:ar********************************@4ax.com.. .

Nov 20 '05 #34

P: n/a
Cor
Hi JayB,

Make sence

Thanks,

Cor
Nov 20 '05 #35

P: n/a
Kees,
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table. Rather then refilling the DataTable from scratch each time, I would consider
encapsulating the Recordset & DataTable in a class that uses the events of
the Recordset & DataTable to keep the two in sync.

I would start with something like:

Public Class MyDataTable

Private WithEvents m_rs As ADODB.Recordset
Private WithEvents m_table As DataTable

Public Sub New(ByVal rs As ADODB.Recordset, ByVal table As
DataTable)
m_rs = rs
m_table = table
End Sub

Public Readonly Property Recordset() As ADODB.Recordset
Get
Return m_rs
End Get
End Property

Public Readonly Property Table() As DataTable
Get
Return m_table
End Get
End Property

Private Sub m_rs_RecordChangeComplete(ByVal adReason As
ADODB.EventReasonEnum, ByVal cRecords As Integer, ByVal pError As
ADODB.Error, ByRef adStatus As ADODB.EventStatusEnum, ByVal pRecordset As
ADODB.Recordset) Handles m_rs.RecordChangeComplete
' update m_table with new values from m_rs
End Sub

Private Sub m_table_RowChanged(ByVal sender As Object, ByVal e As
System.Data.DataRowChangeEventArgs) Handles m_table.RowChanged
' update m_rs with new values from m_table
End Sub

End Class

There may be other events you need to monitor, also watch out for recursive
updating.

I have not heard back on why the OleDbDataAdapter.Fill method behaves the
way it does.

Hope this help
Jay
"kjvt" <do********@excite.com> wrote in message
news:up********************************@4ax.com... Jay,

On Tue, 3 Feb 2004 10:42:36 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
Kees,
If you add a Recordset.Requery just before the OleDbDataAdapter.Fill it willfunction as you expect, however I not sure you want to have to Requery thedatabase each time...


Unfortunately, I cannot requery. There may be unsaved changes in the
recordset that I want to display, but not commit to the database. I
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table.
Private 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


pRS.Requery()
sAdapter.Fill(sTable, pRS)

GetViewFromRS = New DataView(sTable)
End Function


Of course my suggestion of keeping a single DataTable remains, having
GetViewFromRS create a new DataTable each time is resource wasteful.


Do you mean wasteful in terms of the time it takes to create the
table? I thought that the prior table would be released when I bind
the datagrid to the new table/view, so the memory would be freed (once
garbage collected). Anyway, I agree that there's no need to create a
table each time. This will be especially true when I fill the table
manually.

Thank you again.

Kees

Hope this helps
Jay

"kjvt" <do********@excite.com> wrote in message
news:ar********************************@4ax.com.. .

Nov 20 '05 #36

P: n/a
Thanks Jay, I like this approach and I think it will fit my scenario.

Kees
On Wed, 4 Feb 2004 09:21:58 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
Kees,
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table.

Rather then refilling the DataTable from scratch each time, I would consider
encapsulating the Recordset & DataTable in a class that uses the events of
the Recordset & DataTable to keep the two in sync.

I would start with something like:

Public Class MyDataTable

Private WithEvents m_rs As ADODB.Recordset
Private WithEvents m_table As DataTable

Public Sub New(ByVal rs As ADODB.Recordset, ByVal table As
DataTable)
m_rs = rs
m_table = table
End Sub

Public Readonly Property Recordset() As ADODB.Recordset
Get
Return m_rs
End Get
End Property

Public Readonly Property Table() As DataTable
Get
Return m_table
End Get
End Property

Private Sub m_rs_RecordChangeComplete(ByVal adReason As
ADODB.EventReasonEnum, ByVal cRecords As Integer, ByVal pError As
ADODB.Error, ByRef adStatus As ADODB.EventStatusEnum, ByVal pRecordset As
ADODB.Recordset) Handles m_rs.RecordChangeComplete
' update m_table with new values from m_rs
End Sub

Private Sub m_table_RowChanged(ByVal sender As Object, ByVal e As
System.Data.DataRowChangeEventArgs) Handles m_table.RowChanged
' update m_rs with new values from m_table
End Sub

End Class

There may be other events you need to monitor, also watch out for recursive
updating.

I have not heard back on why the OleDbDataAdapter.Fill method behaves the
way it does.

Hope this help
Jay
"kjvt" <do********@excite.com> wrote in message
news:up********************************@4ax.com.. .
Jay,

On Tue, 3 Feb 2004 10:42:36 -0600, "Jay B. Harlow [MVP - Outlook]"
<Ja************@msn.com> wrote:
>Kees,
>If you add a Recordset.Requery just before the OleDbDataAdapter.Fill itwill >function as you expect, however I not sure you want to have to Requerythe >database each time...


Unfortunately, I cannot requery. There may be unsaved changes in the
recordset that I want to display, but not commit to the database. I
guess I'll write a routine that spins through the recordset rows and
updates the datatable "manually" after I have used Fill to create the
table.
>
>> Private 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
>>
>
> pRS.Requery()
>
>> sAdapter.Fill(sTable, pRS)
>>
>> GetViewFromRS = New DataView(sTable)
>> End Function
>
>Of course my suggestion of keeping a single DataTable remains, having
>GetViewFromRS create a new DataTable each time is resource wasteful.


Do you mean wasteful in terms of the time it takes to create the
table? I thought that the prior table would be released when I bind
the datagrid to the new table/view, so the memory would be freed (once
garbage collected). Anyway, I agree that there's no need to create a
table each time. This will be especially true when I fill the table
manually.

Thank you again.

Kees
>
>Hope this helps
>Jay
>
>"kjvt" <do********@excite.com> wrote in message
>news:ar********************************@4ax.com.. .


Nov 20 '05 #37

This discussion thread is closed

Replies have been disabled for this discussion.