Liz,
Quote:
> but ... using ADODB, to get a recordset back and iterate it we're looking
at
I know how to iterate a recordset. I think you missed what I was asking.
Quote:
> Dim reader As SqlDataReader
Isn't this only for SQL Server?
Quote:
> WOW ... that's some wild and wooly code you have there !! I'm not sure
what
> you're environment is ... you seem to be half in the new world and half in
> the old ...
First what do you mean my half in new and half in old? All I am trying to
do is create an ADORecordSet with no that has no connection from the
structure of another adoRecordSet.
Sample Creating adoRecordSet with no connection:
oRS = New ADODB.Recordset()
For x = 0 To oSelect.Fields.Count - 1
oField = oSelect.Fields.Item(x)
oRS.Fields.Append(oField.Name, oField.Type, oField.Precision,
FieldAttribu{*filter*}um.adFldMayBeNull)
Next
oRS.Open()
Now once I have done that I want to be able to populate this recordset with
the values of the connected record set. My problem is when I am trying to
move the values from the connected recordset into the recordset with no
connection. Please look at line "oRS.Fields(x).Value = oField.Value" within
the DO WHILE LOOP. For some reason I am getting a runtime error on that
line.
Do While Not oSelect.EOF
oRS.AddNew()
For x = 0 To oSelect.Fields.Count - 1
oField = oSelect.Fields(x)
oRS.Fields(x).Value = oField.Value ' Error is
encountered right HERE!
Next
oSelect.MoveNext()
Loop
Thanks,
Rob
Quote:
> > I am trying to cache the results from an SQL Select into a adoRecordSet
> > (detacted from the database) with no success, see code sample below.
> > I have a hunch is has something to do with how I am building the oRS
> > Structure. Any help or advice would be greatly appreciated
> Rob,
> WOW ... that's some wild and wooly code you have there !! I'm not sure
what
> you're environment is ... you seem to be half in the new world and half in
> the old ...
> but ... using ADODB, to get a recordset back and iterate it we're looking
at
> something like this:
> dim cs as string = "dsn=YourODBC_DSN;uid=sa;pwd=" 'uses ODBC
DSN
> dim sql as string = "select top 10 * from table1"
> dim s as string = ""
> dim rs as ADODB.Recordset = new ADODB.Recordset()
> rs.Open(sql, cs, ADODB.CursorTypeEnum.adOpenKeyset,
> ADODB.LockTypeEnum.adLockOptimistic, 0)
> do while not rs.EOF
> s += rs("YourFieldName_1").Value + vbCrLf 'do something with the
data
> rs.MoveNext()
> loop
> rs.Close()
> MessageBox.Show(s)
> this works in VB.NET (and C# with some modification) .. the "preferred"
way
> is to use ADO.NET; there are many examples of how to work with ADO.NET
...
> here's a simple one:
> 'at top of file:
> Imports System.Data
> Imports System.Data.SqlClient (or System.Data.OleDb)
> '-----------------------------------------
> Sub getData()
> Dim s As String = ""
> Dim sql As String = "select top 10 * from YourTable"
> Dim reader As SqlDataReader
> Dim conn As New
> SqlConnection("server=yourServer;database=yourDB;uid=sa;pwd=")
> Dim cmd As New SqlCommand(sql, conn)
> conn.Open()
> reader = cmd.ExecuteReader()
> Do While reader.Read()
> 'do something with the data
> s += reader.GetValue(reader.GetOrdinal("someFieldName")) + vbCrLf
> Loop
> conn.close()
> MsgBox(s)
> End Sub
> There are a LOT of ADO.NET idioms (or design patterns ... take your pick)
> ... this should you give you a little idea of how it can be approached ...
> and how simple the code can be ...
> This can be very easily adapted to use OLE DB; in the main, just change
the
> "Sql" to "OleDb" and add in the Provider for Oracle .....
> > Thanks,
> > Rob Panosh
> > Here is my sample code:
> > Dim oRS As ADODB.Recordset
> > Dim oSelect As ADODB.Recordset
> > Dim x As Integer
> > Dim oConnection As New ADODB.Connection()
> > Dim oField As ADODB.Field
> > Dim oField2 As ADODB.Field
> > oConnection.Open("Provider=sqloledb;Network
> > Library=DBMSSOCN;Application Name=RobTesting;Data Source=dualasd;Initial
> > Catalog=provis40;User Id=sa;Password=asd;")
> > 'oConnection.Open("Provider=msdaora;Data Source=pvor80;User
> > Id=avonrel3x;Password=avon;")
> > oSelect = New ADODB.Recordset()
> > oSelect.ActiveConnection = oConnection
> > oSelect.Open("SELECT * FROM FMENTITYOBJECT", oConnection,
> > CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, -1)
> > oSelect.MoveFirst()
> > oRS = New ADODB.Recordset()
> > For x = 0 To oSelect.Fields.Count - 1
> > oField = oSelect.Fields.Item(x)
> > oRS.Fields.Append(oField.Name, oField.Type,
oField.Precision,
> > FieldAttribu{*filter*}um.adFldMayBeNull)
> > Next
> > oRS.Open()
> > Do While Not oSelect.EOF
> > oRS.AddNew()
> > For x = 0 To oSelect.Fields.Count - 1
> > oField = oSelect.Fields(x)
> > oRS.Fields(x).Value = oField.Value ' Error is
> > encountered right HERE!
> > Next
> > oSelect.MoveNext()
> > Loop
> > Me.AsdButton1.Text = oRS.RecordCount
> > oSelect.Close()
> > oConnection.Close()
adoRecordSet Question