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
36  4333 
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
Hi Kees,
What you want to archieve,
The dataset stays a disconnected dataset even if you fill it from the
recordset, why not fill the dataset direct from the database, I do not
understand it and am currious?
Cor
Cor,
I'm adding new .Net functionality to an existing application that was built
in vb6 and recently converted to .Net. The app has a data access layer
built in vb6 that is not being converted at this time. In the meantime, I
want to use .Net controls and databinding, so I need to convert to a
dataview, potentially multiple times as the data in the recordset changes.
Kees
"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
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
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
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
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
>
>
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
>
>
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
>> >
>> >
>>
>>
>
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
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 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
>> >
>> >
>>
>>
>
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
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>
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
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 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>
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 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 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >
>
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 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 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >
>
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
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >>
>> >
>>
>
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
parameteris >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
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >>
>> >
>>
>
Hi Jay B,
I did some work for you.
Has to be in the thread already
:-))
Cor
Hallo Kees,
Because I'v seen Jay B has it to busy.
Can you try this code I have pasted in bellow?
It is a complete test you only have to open a new project and paste this in
and make a directory on your C: drive test1 or whatever if you change the
code.
You have to add a datagrid on your form and set the references to com
microsoft.Adodb and adox ext 2.7 for dll and security.
(And I did not do anything more than was necessary for the sample and I also
do not know how you could do updates in this way. This was for me also the
first time that I did this. It does nothing more than the subject from this
message)
And I am of course curious to the result.
I hope this helps?
Cor
\\\
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Add a datagrid to the form
'Set a reference to
'microsoft ADODB
'microsoft adox ext 2.7 for dll and security
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\test1\dbKees.mdb") Then
System.IO.File.Delete("C:\test1\dbKees.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\test1\dbKees.mdb")
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim connectionstring As String = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\test1\dbKees.mdb;User Id=admin;Password=;"
conn1.ConnectionString = connectionstring
conn1.Open()
Dim cmd1 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn1)
cmd1.ExecuteNonQuery()
For i As Integer = 1 To 9
cmd1.Parameters.Clear()
cmd1.CommandText = "INSERT INTO tbl1 (a,b) VALUES (@a,@b)"
cmd1.Parameters.Add(New OleDb.OleDbParameter _
("@a", OleDb.OleDbType.Integer)).Value = i
cmd1.Parameters.Add(New OleDb.OleDbParameter _
("@b", OleDb.OleDbType.Char, 20)).Value = Chr(64 + i)
cmd1.ExecuteNonQuery()
Next
cmd1.CommandText = "Select * from tbl1"
Dim da1 As OleDb.OleDbDataAdapter = _
New OleDb.OleDbDataAdapter(cmd1)
conn1.Close()
Dim conn2 As New ADODB.Connection
Dim strProvider As String = connectionstring
conn2.Open(strProvider)
Dim rs As New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs.Open("select * from tbl1", conn2)
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
da.Fill(dt, rs)
Dim dv As New DataView(dt)
Me.DataGrid1.DataSource = dv
End Sub
///
Cor,
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
///
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
///
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
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>>
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
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
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
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
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
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
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
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
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.. .
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
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.. .
Hi JayB,
Make sence
Thanks,
Cor
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.. .
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.. . This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ed Staffin |
last post by:
Hi, I have a need to conver the xml produced by a .net
dataset (with a single table) into an adodb.recordset. The
adodb.recordset doesn't understand the xml that the
dataset.GetXml method...
|
by: Eric Peterson |
last post by:
I use the shape object to make recordsets that have no db
connection for use in grids and such. For example
(mPhoneNumbersRS as ADODB.Recordset)
****************************************...
|
by: Ramakrishnan Nagarajan |
last post by:
Hi can any one help me in converting a DataView into a DataSet or a DataTable
into a DataSet,
I tried the following ways
dSet= ((DataSet)_gridRegStudDetails.DataSource);
dSet =...
|
by: Brett Romero |
last post by:
I have a dataset with one table, which has four columns. All are of
type INT. I need to convert this dataset into a dataview so I can sort
on the last three columns. I may sort one of the three...
|
by: RP |
last post by:
I have to convert this VB code to C#.
Dim dvProduct As DataView =
CType(sqlDS_Halloween.Select(DataSourceSelectArguments.Empty),DataView)
DataView dvProduct =...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
| |
