adoRecordSet Question 
Author Message
 adoRecordSet Question

Hello,

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 am getting the following error message in the DO WHILE line:
oRS.Fields(x).Value = oField.Value
_________________________________________
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in adodb.dll
Additional information: Multiple-step operation generated errors. Check each
status value.
__________________________________________

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

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()



Wed, 21 Sep 2005 03:48:35 GMT  
 adoRecordSet Question


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 .....

Quote:
> 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()



Wed, 21 Sep 2005 14:18:03 GMT  
 adoRecordSet Question
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()



Fri, 23 Sep 2005 20:25:16 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. SQLServer backend + Access97frontend + ADOrecordset + addnew

2. ADORecordSet.RecordCount don't work

3. Problems with adoRecordset.Filter

4. Problem with ADORecordset and DataCombo

5. ADORecordSet.ASP Problem

6. ADORecordsets and crystal

7. Getting Identity back after an ADORecordset->AddNew

8. adoRecordset.Recordcount

9. Ho to Insert adoRecordset into Access table tblHIstory

10. Open ADOrecordset

11. ADORecordset.Fields("X")

12. questions,questions,questions.

 

 
Powered by phpBB® Forum Software