Populating disconnected ADODB.Recordset with System.Data.DataTable data.

E

elcc1958

I need to support a VB6 application that will be receiving
disconnected ADODB.Recordset from out DotNet solution. Our dotnet
solution deals with System.Data.DataTable. I need to populate a
disconnected ADODB.Recordset from System.Data.DataTable data. Below
is the source code I am implementing to test the process. I do not
get any error, that I can see. The problem I have is that at the end,
the recordset seems to be empty. Any suggestions are very much
appreciated. At this moment I do not know what to do.
Thank you so much for the time and effort you may spend on this
matter.
Elcc.


private bool testConvertDT_RS()
{
System.Data.DataTable dt;
ADODB.Recordset rs;

// create dummy data table.
dt = PopulateDataTable( );

// attempt to populate disconnected ADODB.Recordset
with System.Data.DataTable data
rs = ConvertToRS ( dt );

if( null == rs )
{
throw new Exception(" Recordset is null.");
}

if( 0 == rs.Fields.Count )
{
throw new Exception(" Recordset Field count is zero.");
}

return true;


}


private System.Data.DataTable PopulateDataTable()
{

System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataRow dr;
string[,] ColValues = {{"R1-C1", "R2-C1", "R3-C1", "R4-C1", "R5-C1"}
, {"R1-C2", "R2-C2", "R3-C2", "R4-C2", "R5-C2"} ,
{"R1-C3", "R2-C3", "R3-C3", "R4-C3", "R5-C3"}};

dt.Columns.Add(new System.Data.DataColumn("Column01",
System.Type.GetType("System.String")));
dt.Columns.Add(new System.Data.DataColumn("Column02",
System.Type.GetType("System.String")));
dt.Columns.Add(new System.Data.DataColumn("Column03",
System.Type.GetType("System.String")));
for( int i = 0 ; i < 5 ; i++ )
{
dr = dt.NewRow();
for( int n = 0 ; n < 3 ; n++ )
{
dr[n] = ColValues[ n, i];
} dt.Rows.Add(dr);
} return dt;
}


private ADODB.Recordset ConvertToRS(System.Data.DataTable dt)
{
ADODB.Recordset rs = new ADODB.Recordset();

try
{

rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
rs.ActiveConnection = null;

for( int i = 0; i < dt.Columns.Count; i++)
{
rs.Fields.Append( dt.Columns.ColumnName,
ADODB.DataTypeEnum.adBSTR,
dt.Columns.MaxLength,
ADODB.FieldAttributeEnum.adFldUnspecified,
null );
}
rs.Open( Type.Missing,
Type.Missing,
ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic,
-1);


foreach(System.Data.DataRow dr in dt.Rows)
{
rs.AddNew(Type.Missing, Type.Missing);

for( int n = 0; n < dt.Columns.Count; n++)
{
rs.Fields[n].Value = dr[n];
}

rs.Update(Type.Missing, Type.Missing);

}

rs.Close();
return rs;

}
catch(Exception e)
{
throw e;
}
}
 
V

Val Mazur

Hi,

Check next example how to do this. Actually it is pretty *dirty* job

http://support.microsoft.com/default.aspx?scid=kb;en-us;316337&Product=adonet

--
Val Mazur
Microsoft MVP


[email protected] said:
I need to support a VB6 application that will be receiving
disconnected ADODB.Recordset from out DotNet solution. Our dotnet
solution deals with System.Data.DataTable. I need to populate a
disconnected ADODB.Recordset from System.Data.DataTable data. Below
is the source code I am implementing to test the process. I do not
get any error, that I can see. The problem I have is that at the end,
the recordset seems to be empty. Any suggestions are very much
appreciated. At this moment I do not know what to do.
Thank you so much for the time and effort you may spend on this
matter.
Elcc.


private bool testConvertDT_RS()
{
System.Data.DataTable dt;
ADODB.Recordset rs;

// create dummy data table.
dt = PopulateDataTable( );

// attempt to populate disconnected ADODB.Recordset
with System.Data.DataTable data
rs = ConvertToRS ( dt );

if( null == rs )
{
throw new Exception(" Recordset is null.");
}

if( 0 == rs.Fields.Count )
{
throw new Exception(" Recordset Field count is zero.");
}

return true;


}


private System.Data.DataTable PopulateDataTable()
{

System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataRow dr;
string[,] ColValues = {{"R1-C1", "R2-C1", "R3-C1", "R4-C1", "R5-C1"}
, {"R1-C2", "R2-C2", "R3-C2", "R4-C2", "R5-C2"} ,
{"R1-C3", "R2-C3", "R3-C3", "R4-C3", "R5-C3"}};

dt.Columns.Add(new System.Data.DataColumn("Column01",
System.Type.GetType("System.String")));
dt.Columns.Add(new System.Data.DataColumn("Column02",
System.Type.GetType("System.String")));
dt.Columns.Add(new System.Data.DataColumn("Column03",
System.Type.GetType("System.String")));
for( int i = 0 ; i < 5 ; i++ )
{
dr = dt.NewRow();
for( int n = 0 ; n < 3 ; n++ )
{
dr[n] = ColValues[ n, i];
} dt.Rows.Add(dr);
} return dt;
}


private ADODB.Recordset ConvertToRS(System.Data.DataTable dt)
{
ADODB.Recordset rs = new ADODB.Recordset();

try
{

rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
rs.ActiveConnection = null;

for( int i = 0; i < dt.Columns.Count; i++)
{
rs.Fields.Append( dt.Columns.ColumnName,
ADODB.DataTypeEnum.adBSTR,
dt.Columns.MaxLength,
ADODB.FieldAttributeEnum.adFldUnspecified,
null );
}
rs.Open( Type.Missing,
Type.Missing,
ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic,
-1);


foreach(System.Data.DataRow dr in dt.Rows)
{
rs.AddNew(Type.Missing, Type.Missing);

for( int n = 0; n < dt.Columns.Count; n++)
{
rs.Fields[n].Value = dr[n];
}

rs.Update(Type.Missing, Type.Missing);

}

rs.Close();
return rs;

}
catch(Exception e)
{
throw e;
}
}
Click to expand...
 
E

elcc1958

Thank you for your response. I appreciate your willingness to help
me.
The article you reference is a very good one. But before I take that
route, I am encouraged to explore more options. Basically, I am told
to make the solution work in a very similar way I am trying to do it
right now. I will be exploring with the parameters passed to those
functions or try similar approaches. I being told that there is a C++
solution that does something similar in a very simple way. I am still
very open to suggestions. Again, thanks to all. If I find a solution
that is not posted in this message, I will post it for other to see. (
I need to find a solution first… :) ).
 
E

elcc1958

The answer was really simple, I missed it completely. This code works
fine if you add a rs.MoveFirst() instead of rs.Close() at the end of
the ConvertToRS. That will do the trick.
Thank you to all that spent sometime trying to solve this problem.
 
S

Sandeep Limaye

Hi,

I tried the same thing, but my code was throwing an exception while
accessing the columns of the DataTable populated by calling GetSchemaTable()
on the DataReader returned by a query execution.

I have the following code:



public Recordset GetRecordset (string strQuery)

{

DataTable oDataTable;

Recordset oRecSet;

int i = 0;

OracleCommand oOracleCmd = oOracleConn.CreateCommand ();

oOracleCmd.CommandText = strQuery;


// Execute the command

OracleDataReader oOraReader = oOracleCmd.ExecuteReader
(CommandBehavior.SingleResult);

// Get the result into a DataTable

oDataTable = oOraReader.GetSchemaTable ();


oRecSet = new Recordset ();

DataColumn oDataColumn;

for (i = 0; i < oDataTable.Columns.Count; i++) // Exception being thrown
here, for i = 11. oDataTable.Columns.Count = 20 for my query.

{

oDataColumn = oDataTable.Columns;


oRecSet.Fields.Append (oDataColumn.ColumnName,

GetADOType (oDataColumn.DataType.ToString ()),

GetADOTypeSize (oDataColumn.DataType.ToString ()),

FieldAttributeEnum.adFldIsNullable,

System.Reflection.Missing.Value);

}

oRecSet.Open (System.Reflection.Missing.Value,

System.Reflection.Missing.Value,

CursorTypeEnum.adOpenKeyset,

LockTypeEnum.adLockOptimistic,

1);

for (int iRowIndex = 0; iRowIndex < oDataTable.Rows.Count; iRowIndex++)

{

oRecSet.AddNew (System.Reflection.Missing.Value,

System.Reflection.Missing.Value);

for (int iColIndex = 0; iColIndex < oDataTable.Columns.Count; iColIndex++)

{

oRecSet.Fields[iColIndex].Value = oDataTable.Rows[iRowIndex][iColIndex];

}

oRecSet.Update (System.Reflection.Missing.Value,

System.Reflection.Missing.Value);

}

oRecSet.MoveFirst ();

oOraReader.Close();

return oRecSet;

}

It is throwing an exception in the for loop for accessing the columns. My
query is something like

SELECT id, revision_number FROM Table1 WHERE ...

It should return me only 2 columns. But the oDataTable.Columns.Count is
giving me 20.

For a hardcoded DataTable object (as in your example code), this works
absolutely fine. It's only for the results of an actual query is this
failing.

I'll be really grateful for a helping hand in this...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rearrange data from columns to rows 2
need help with query 3
Compare and Update elements from Sheet1 with Sheet2 3
AutoFiltering with Grouped Data 3
Calculating formula ..circular references 1
Multiple rows to one column 4
Manual Transpose 2
Copy paste visible on same sheet 1

Top