| Groups | Blog | Home |
dotnet ado.net : Recordset to DataTable
Hi All,
I am having the strangest and most frustrating time trying to load a recordset into a data table more than once. For some reason, when I execute the following code, the first messagebox displays a record count of 2 and a second call to it displays a count of 0. Why the inconsistent result. The recordset seems to be open and I even perform a MoveFirst, etc. Nothing works. Should it not consistently return a record count of 2 since I am executing the same code twice. The code is extremly simple, but for some reason, it seems as if the recordset object itself becomes invalid after the ..Fill operation is complete. The only way I can find this is to perform my BuildDataSource (real world scenario involves connecting to a database, etc.) function which simply creates and returns an ADODB recordset. Any idea or help. Note: I reference the ADODB 2.7 InterOp library installed with VS 2005. Are there any specifications as to what the cursorlocation, cursor type, or connection open parameters. I've tried many combinations. Dim DataSource As ADODB.Recordset Dim DataTable As Data.DataTable DataSource = BuildDataSource() 'Builds test recordset with 2 records. DataTable = RecordsetToDataTable(DataSource) MessageBox.Show(DataTable.Rows.Count) ' Displays 2. Correct. DataTable = RecordsetToDataTable(DataSource) MessageBox.Show(DataTable.Rows.Count) ' Displays 0. Incorrect Public Function RecordsetToDataTable(ByVal recordset As ADODB.Recordset) As Data.DataTable Dim OleDbDataAdapter As Data.OleDb.OleDbDataAdapter Dim DataTable As Data.DataTable OleDbDataAdapter = New OleDb.OleDbDataAdapter DataTable = New Data.DataTable("Employees") OleDbDataAdapter.Fill(DataTable, recordset) Return DataTable End Function Public Function BuildDataSource() As ADODB.Recordset Dim NewDataSource As ADODB.Recordset NewDataSource = New ADODB.Recordset NewDataSource.Fields.Append("FirstName", ADODB.DataTypeEnum.adVariant, 100, ADODB.FieldAttributeEnum.adFldIsNullable NewDataSource.Fields.Append("LastName", ADODB.DataTypeEnum.adVariant, 100, ADODB.FieldAttributeEnum.adFldIsNullable NewDataSource.CursorLocation = ADODB.CursorLocationEnum.adUseClient NewDataSource.CursorType = ADODB.CursorTypeEnum.adOpenDynamic NewDataSource.Open(CursorType:=ADODB.CursorTypeEnum.adOpenStatic,LockType:=ADODB.LockTypeEnum.adLockOptimistic) NewDataSource.AddNew() NewDataSource.Fields("FirstName").Value = "Uncle" NewDataSource.Fields("LastName").Value = "Bob" NewDataSource.Update() NewDataSource.AddNew() NewDataSource.Fields("FirstName").Value = "Aunty" NewDataSource.Fields("LastName").Value = "Jane" NewDataSource.Update() Return NewDataSource End Function
Why are you using ADODB?
Robin S. --------------------------------- [quoted text, click to view] "Giovanni" <Giovanni@discussions.microsoft.com> wrote in message news:8C81B3D7-2823-4D41-84BC-74907B26FF54@microsoft.com... > Hi All, > > I am having the strangest and most frustrating time trying to load > a > recordset into a data table more than once. For some reason, when I > execute > the following code, the first messagebox displays a record count of 2 > and a > second call to it displays a count of 0. Why the inconsistent result. > The > recordset seems to be open and I even perform a MoveFirst, etc. > Nothing > works. Should it not consistently return a record count of 2 since I > am > executing the same code twice. The code is extremly simple, but for > some > reason, it seems as if the recordset object itself becomes invalid > after the > .Fill operation is complete. The only way I can find this is to > perform my > BuildDataSource (real world scenario involves connecting to a > database, etc.) > function which simply creates and returns an ADODB recordset. Any > idea or > help. Note: I reference the ADODB 2.7 InterOp library installed with > VS > 2005. Are there any specifications as to what the cursorlocation, > cursor > type, or connection open parameters. I've tried many combinations. > > Dim DataSource As ADODB.Recordset > Dim DataTable As Data.DataTable > > DataSource = BuildDataSource() 'Builds test recordset with 2 records. > DataTable = RecordsetToDataTable(DataSource) > > MessageBox.Show(DataTable.Rows.Count) ' Displays 2. Correct. > DataTable = RecordsetToDataTable(DataSource) > MessageBox.Show(DataTable.Rows.Count) ' Displays 0. Incorrect > > > Public Function RecordsetToDataTable(ByVal recordset As > ADODB.Recordset) As > Data.DataTable > Dim OleDbDataAdapter As Data.OleDb.OleDbDataAdapter > Dim DataTable As Data.DataTable > > OleDbDataAdapter = New OleDb.OleDbDataAdapter > DataTable = New Data.DataTable("Employees") > OleDbDataAdapter.Fill(DataTable, recordset) > Return DataTable > End Function > > Public Function BuildDataSource() As ADODB.Recordset > Dim NewDataSource As ADODB.Recordset > > NewDataSource = New ADODB.Recordset > NewDataSource.Fields.Append("FirstName", > ADODB.DataTypeEnum.adVariant, > 100, ADODB.FieldAttributeEnum.adFldIsNullable > NewDataSource.Fields.Append("LastName", > ADODB.DataTypeEnum.adVariant, > 100, ADODB.FieldAttributeEnum.adFldIsNullable > NewDataSource.CursorLocation = > ADODB.CursorLocationEnum.adUseClient > NewDataSource.CursorType = ADODB.CursorTypeEnum.adOpenDynamic > > NewDataSource.Open(CursorType:=ADODB.CursorTypeEnum.adOpenStatic,LockType:=ADODB.LockTypeEnum.adLockOptimistic) > > NewDataSource.AddNew() > NewDataSource.Fields("FirstName").Value = "Uncle" > NewDataSource.Fields("LastName").Value = "Bob" > NewDataSource.Update() > > NewDataSource.AddNew() > NewDataSource.Fields("FirstName").Value = "Aunty" > NewDataSource.Fields("LastName").Value = "Jane" > NewDataSource.Update() > > Return NewDataSource > End Function >
Hi Robin,
I need to as I am interacting with a legacy VB6 application. I have built an InterOp Class Library and ave referenced the ADODB Primary InterOp Assembly library to help me accomplish this. As stated, the problem occurs after I execute the .Fill method. For some reason, my original recordset is left in limbo. I cannot execute the same procedure on it again. [quoted text, click to view] "RobinS" wrote:
> Why are you using ADODB? > > Robin S. > --------------------------------- > "Giovanni" <Giovanni@discussions.microsoft.com> wrote in message > news:8C81B3D7-2823-4D41-84BC-74907B26FF54@microsoft.com... > > Hi All, > > > > I am having the strangest and most frustrating time trying to load > > a > > recordset into a data table more than once. For some reason, when I > > execute > > the following code, the first messagebox displays a record count of 2 > > and a > > second call to it displays a count of 0. Why the inconsistent result. > > The > > recordset seems to be open and I even perform a MoveFirst, etc. > > Nothing > > works. Should it not consistently return a record count of 2 since I > > am > > executing the same code twice. The code is extremly simple, but for > > some > > reason, it seems as if the recordset object itself becomes invalid > > after the > > .Fill operation is complete. The only way I can find this is to > > perform my > > BuildDataSource (real world scenario involves connecting to a > > database, etc.) > > function which simply creates and returns an ADODB recordset. Any > > idea or > > help. Note: I reference the ADODB 2.7 InterOp library installed with > > VS > > 2005. Are there any specifications as to what the cursorlocation, > > cursor > > type, or connection open parameters. I've tried many combinations. > > > > Dim DataSource As ADODB.Recordset > > Dim DataTable As Data.DataTable > > > > DataSource = BuildDataSource() 'Builds test recordset with 2 records. > > DataTable = RecordsetToDataTable(DataSource) > > > > MessageBox.Show(DataTable.Rows.Count) ' Displays 2. Correct. > > DataTable = RecordsetToDataTable(DataSource) > > MessageBox.Show(DataTable.Rows.Count) ' Displays 0. Incorrect > > > > > > Public Function RecordsetToDataTable(ByVal recordset As > > ADODB.Recordset) As > > Data.DataTable > > Dim OleDbDataAdapter As Data.OleDb.OleDbDataAdapter > > Dim DataTable As Data.DataTable > > > > OleDbDataAdapter = New OleDb.OleDbDataAdapter > > DataTable = New Data.DataTable("Employees") > > OleDbDataAdapter.Fill(DataTable, recordset) > > Return DataTable > > End Function > > > > Public Function BuildDataSource() As ADODB.Recordset > > Dim NewDataSource As ADODB.Recordset > > > > NewDataSource = New ADODB.Recordset > > NewDataSource.Fields.Append("FirstName", > > ADODB.DataTypeEnum.adVariant, > > 100, ADODB.FieldAttributeEnum.adFldIsNullable > > NewDataSource.Fields.Append("LastName", > > ADODB.DataTypeEnum.adVariant, > > 100, ADODB.FieldAttributeEnum.adFldIsNullable > > NewDataSource.CursorLocation = > > ADODB.CursorLocationEnum.adUseClient > > NewDataSource.CursorType = ADODB.CursorTypeEnum.adOpenDynamic > > > > NewDataSource.Open(CursorType:=ADODB.CursorTypeEnum.adOpenStatic,LockType:=ADODB.LockTypeEnum.adLockOptimistic) > > > > NewDataSource.AddNew() > > NewDataSource.Fields("FirstName").Value = "Uncle" > > NewDataSource.Fields("LastName").Value = "Bob" > > NewDataSource.Update() > > > > NewDataSource.AddNew() > > NewDataSource.Fields("FirstName").Value = "Aunty" > > NewDataSource.Fields("LastName").Value = "Jane" > > NewDataSource.Update() > > > > Return NewDataSource > > End Function > > > >
I find it difficult to believe that you can do a Fill on a table adapter, and it would fill an ADODB recordset correctly. Do you have Option Strict On at the top of your program, and/or for your project? What I would try if I were you is when moving data from an ADODB recordset to a .Net DataSet or DataTable, read it from ADODB the VB6/ADODB way, then read through it and stick it in a .Net DataTable. And vice versa. And see if it works right. Just out of curiousity. Robin S. ------------------------------------------------------------ [quoted text, click to view] "Giovanni" <Giovanni@discussions.microsoft.com> wrote in message news:67DF7B83-D340-4F2B-9468-3FF50C887DA0@microsoft.com... > Hi Robin, > > I need to as I am interacting with a legacy VB6 application. I > have > built an InterOp Class Library and ave referenced the ADODB Primary > InterOp > Assembly library to help me accomplish this. As stated, the problem > occurs > after I execute the .Fill method. For some reason, my original > recordset is > left in limbo. I cannot execute the same procedure on it again. > > > "RobinS" wrote: > >> Why are you using ADODB? >> >> Robin S. >> --------------------------------- >> "Giovanni" <Giovanni@discussions.microsoft.com> wrote in message >> news:8C81B3D7-2823-4D41-84BC-74907B26FF54@microsoft.com... >> > Hi All, >> > >> > I am having the strangest and most frustrating time trying to >> > load >> > a >> > recordset into a data table more than once. For some reason, when >> > I >> > execute >> > the following code, the first messagebox displays a record count of >> > 2 >> > and a >> > second call to it displays a count of 0. Why the inconsistent >> > result. >> > The >> > recordset seems to be open and I even perform a MoveFirst, etc. >> > Nothing >> > works. Should it not consistently return a record count of 2 since >> > I >> > am >> > executing the same code twice. The code is extremly simple, but >> > for >> > some >> > reason, it seems as if the recordset object itself becomes invalid >> > after the >> > .Fill operation is complete. The only way I can find this is to >> > perform my >> > BuildDataSource (real world scenario involves connecting to a >> > database, etc.) >> > function which simply creates and returns an ADODB recordset. Any >> > idea or >> > help. Note: I reference the ADODB 2.7 InterOp library installed >> > with >> > VS >> > 2005. Are there any specifications as to what the cursorlocation, >> > cursor >> > type, or connection open parameters. I've tried many combinations. >> > >> > Dim DataSource As ADODB.Recordset >> > Dim DataTable As Data.DataTable >> > >> > DataSource = BuildDataSource() 'Builds test recordset with 2 >> > records. >> > DataTable = RecordsetToDataTable(DataSource) >> > >> > MessageBox.Show(DataTable.Rows.Count) ' Displays 2. Correct. >> > DataTable = RecordsetToDataTable(DataSource) >> > MessageBox.Show(DataTable.Rows.Count) ' Displays 0. Incorrect >> > >> > >> > Public Function RecordsetToDataTable(ByVal recordset As >> > ADODB.Recordset) As >> > Data.DataTable >> > Dim OleDbDataAdapter As Data.OleDb.OleDbDataAdapter >> > Dim DataTable As Data.DataTable >> > >> > OleDbDataAdapter = New OleDb.OleDbDataAdapter >> > DataTable = New Data.DataTable("Employees") >> > OleDbDataAdapter.Fill(DataTable, recordset) >> > Return DataTable >> > End Function >> > >> > Public Function BuildDataSource() As ADODB.Recordset >> > Dim NewDataSource As ADODB.Recordset >> > >> > NewDataSource = New ADODB.Recordset >> > NewDataSource.Fields.Append("FirstName", >> > ADODB.DataTypeEnum.adVariant, >> > 100, ADODB.FieldAttributeEnum.adFldIsNullable >> > NewDataSource.Fields.Append("LastName", >> > ADODB.DataTypeEnum.adVariant, >> > 100, ADODB.FieldAttributeEnum.adFldIsNullable >> > NewDataSource.CursorLocation = >> > ADODB.CursorLocationEnum.adUseClient >> > NewDataSource.CursorType = ADODB.CursorTypeEnum.adOpenDynamic >> > >> > NewDataSource.Open(CursorType:=ADODB.CursorTypeEnum.adOpenStatic,LockType:=ADODB.LockTypeEnum.adLockOptimistic) >> > >> > NewDataSource.AddNew() >> > NewDataSource.Fields("FirstName").Value = "Uncle" >> > NewDataSource.Fields("LastName").Value = "Bob" >> > NewDataSource.Update() >> > >> > NewDataSource.AddNew() >> > NewDataSource.Fields("FirstName").Value = "Aunty" >> > NewDataSource.Fields("LastName").Value = "Jane" >> > NewDataSource.Update() >> > >> > Return NewDataSource >> > End Function >> > >> >> >>
Giovanni,
Are you sure that there is a method that returns a datatable, I thought it was a dataset. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingadofromadonet.asp Cor "Giovanni" <Giovanni@discussions.microsoft.com> schreef in bericht news:8C81B3D7-2823-4D41-84BC-74907B26FF54@microsoft.com... [quoted text, click to view] > Hi All, > > I am having the strangest and most frustrating time trying to load a > recordset into a data table more than once. For some reason, when I > execute > the following code, the first messagebox displays a record count of 2 and > a > second call to it displays a count of 0. Why the inconsistent result. > The > recordset seems to be open and I even perform a MoveFirst, etc. Nothing > works. Should it not consistently return a record count of 2 since I am > executing the same code twice. The code is extremly simple, but for some > reason, it seems as if the recordset object itself becomes invalid after > the > .Fill operation is complete. The only way I can find this is to perform > my > BuildDataSource (real world scenario involves connecting to a database, > etc.) > function which simply creates and returns an ADODB recordset. Any idea or > help. Note: I reference the ADODB 2.7 InterOp library installed with VS > 2005. Are there any specifications as to what the cursorlocation, cursor > type, or connection open parameters. I've tried many combinations. > > Dim DataSource As ADODB.Recordset > Dim DataTable As Data.DataTable > > DataSource = BuildDataSource() 'Builds test recordset with 2 records. > DataTable = RecordsetToDataTable(DataSource) > > MessageBox.Show(DataTable.Rows.Count) ' Displays 2. Correct. > DataTable = RecordsetToDataTable(DataSource) > MessageBox.Show(DataTable.Rows.Count) ' Displays 0. Incorrect > > > Public Function RecordsetToDataTable(ByVal recordset As ADODB.Recordset) > As > Data.DataTable > Dim OleDbDataAdapter As Data.OleDb.OleDbDataAdapter > Dim DataTable As Data.DataTable > > OleDbDataAdapter = New OleDb.OleDbDataAdapter > DataTable = New Data.DataTable("Employees") > OleDbDataAdapter.Fill(DataTable, recordset) > Return DataTable > End Function > > Public Function BuildDataSource() As ADODB.Recordset > Dim NewDataSource As ADODB.Recordset > > NewDataSource = New ADODB.Recordset > NewDataSource.Fields.Append("FirstName", ADODB.DataTypeEnum.adVariant, > 100, ADODB.FieldAttributeEnum.adFldIsNullable > NewDataSource.Fields.Append("LastName", ADODB.DataTypeEnum.adVariant, > 100, ADODB.FieldAttributeEnum.adFldIsNullable > NewDataSource.CursorLocation = ADODB.CursorLocationEnum.adUseClient > NewDataSource.CursorType = ADODB.CursorTypeEnum.adOpenDynamic > > NewDataSource.Open(CursorType:=ADODB.CursorTypeEnum.adOpenStatic,LockType:=ADODB.LockTypeEnum.adLockOptimistic) > > NewDataSource.AddNew() > NewDataSource.Fields("FirstName").Value = "Uncle" > NewDataSource.Fields("LastName").Value = "Bob" > NewDataSource.Update() > > NewDataSource.AddNew() > NewDataSource.Fields("FirstName").Value = "Aunty" > NewDataSource.Fields("LastName").Value = "Jane" > NewDataSource.Update() > > Return NewDataSource > End Function >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |