ADO recordset inside of .Net

  • Thread starter Thread starter MFRASER
  • Start date Start date
M

MFRASER

Does anyone have an example of how to use an ADOdb recordset inside of .Net?
I am trying to interface with a VB application that uses the ADOdb
recordset.

Here is my code:


string DBConnection = "";

DBConnection = "Provider='HOleDB.1';DataSource='DEV5\\ENERPRISE';Henwood App
ID =10;InitialCatalog='NPC_RSM5_Trading';";

// sql statment

string SQL = "SELECT *, PeriodName AS TOD, 'RiskGroup_HWANG .@04-Mar-23
10:37(Index Process)' As Study FROM [Index Analysis] WHERE RunStatusID =
12777 AND ValDate BETWEEN '2004-01-01' AND '2004-02-01' AND StudyViewScope =
'UserTemp' AND PeriodName IN ('All') AND DurationID =1 AND
SystemNPVRunStatusID = 0 AND NPVDiscountDate ='2004-03-25' AND
NPVDiscountRate =0 AND AvgIters = 1 AND AggregationLevel = 'None'";


//create ADODB Connection object

ADODB.Connection Conn = new ADODB.Connection();

ADODB.Command cmd = new Command();

//create ADODB Recordset object

ADODB.Recordset rs= new ADODB.Recordset();

Conn.Open(DBConnection,"","",-1);


cmd.ActiveConnection = Conn;

cmd.CommandText = SQL;

cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;

cmd.Dialect = "{2BDA0E41-4A58-4721-BC09-6670511BC530}";

//execute the query specifying static sursor, batch optimistic locking


object rsTemp = new object();

object Temp = new object() ;




//THIS LINE FAILS

cmd.Execute(out rsTemp, ref Temp, 1);
 
I only have it in print form. If it's asking you for registration you can
get a free 30 day trial which should get you through this. Also, Bill
Vaughn is pretty active in the microsoft.dotnet.framework.adonet group, you
may want to post this over there as well b/c he may be able to send you the
text..

Bill
 
Hi Fraser,

A more complete sample I could not make I think
- It creates an access file
- It reads that where is used a ADODB parameter
- It fills that in a dataset
- It shows that in a datagrid
- And as example extra it shows it also using ADONET complete
(to show how much more easier that is)
:-))

I hope this helps?

Cor

\\\ It needs only two datagrids on a form and the references as in the code
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
'** Creating of a accessfile
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
'For the test removing it if exist
If System.IO.File.Exists("C:\dbCor.mdb") Then
System.IO.File.Delete("C:\dbCor.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\dbCor.mdb")
Dim conn2 As OleDb.OleDbConnection = New OleDb.OleDbConnection
Dim connstr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\dbCor.mdb;User Id=admin;Password=;"
conn2.ConnectionString = connstr
conn2.Open()
Dim cmd1 As New OleDb.OleDbCommand( _
"CREATE TABLE tbl1 (a int NOT NULL," & _
"b Char(20)," & _
"CONSTRAINT [pk_a] PRIMARY KEY (a))", conn2)
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
conn2.Close()
'Reading it with ADODB
Dim da1 As OleDb.OleDbDataAdapter = _
New OleDb.OleDbDataAdapter(cmd1)
Dim conn1 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strProvider As String = connstr
conn1.Open(strProvider)
cmd.CommandText = "select * from tbl1 WHERE b=?"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd.Prepared = True
cmd.ActiveConnection = conn1
Dim par(0) As ADODB.Parameter
par(0) = New ADODB.Parameter
par(0) = cmd.CreateParameter("b", ADODB.DataTypeEnum.adChar, _
ADODB.ParameterDirectionEnum.adParamInput, 1, "B")
For Each param As ADODB.Parameter In par
cmd.Parameters.Append(param)
Next
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
rs = cmd.Execute
Dim da As New OleDb.OleDbDataAdapter
Dim dt1 As New DataTable
da.Fill(dt1, rs)
'Reading it as Ado complete to show
Dim dt2 As New DataTable
da.Fill(dt1, rs)
Dim da2 As New OleDb.OleDbDataAdapter("select * from tbl1", conn2)
da2.Fill(dt2)
Me.DataGrid1.DataSource = dt1
Me.DataGrid2.DataSource = dt2
rs.Close()
conn1.Close()
conn2.Close()
rs = Nothing
End Sub
////
 
I was able to get this to work using an object nullobj = Type.Missing in the
place of optional params.
 
Back
Top