Dumb questions about ADODB recordsets

20 Mar 2006 3:08 AM

Yuk Tang

I'm trying to grab the fieldnames and values from a recordset, but
I'm getting errors. I have an idea what the error might come from,
but I'm not sure how to correct it.

I'm connecting to an Access db which has the table 'Users', and I
want to grab the fieldnames and values without necessarily knowing
their numbers and formats. The msgboxes are there to illustrate the
error.

To run this, you need an Access db called Users.mdb in the executable
directory, a table called Users in that db, at least 4 fields in the
table, including a field called UserName. One of the records must
have the UserName Fred. For older versions of Access, OLEDB.4.0 needs
to be changed (3.5 instead of 4.0 for Access 97 IIRC).

Public Sub GetValues
MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Settings
\Users.mdb;"
ConnectionString = "SELECT * FROM Users WHERE UserName='Fred';"
Dim rs As ADODB.Recordset
Dim cnTemp As New ADODB.Connection
cnTemp.Open(MyConnection)
rs = cnTemp.Execute(ConnectionString)
MsgBox(rs.Fields(2).Value)
MsgBox(rs.Fields(3).Name)
cnTemp.Close()
End Sub

I get the following error at the line
MsgBox(rs.Fields(2).Value)

An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in adodb.dll

Additional information: Either BOF or EOF is True, or the current
record has been deleted. Requested operation requires a current
record.

It reads the recordset fine, since I get no errors if I change Value
to Name. My guess is that the recordset can potentially return more
than one record, and hence more than one potential value for that
field. So how do I get around this by pointing instead to the first
record in the recordset? I've tried rs.movefirst before accessing
the Value, but I get the same error.

Also, I would like to disconnect the recordset before doing anything
with it, but ending the connection appears to wipe out the recordset
as well, as counting the fields before (6) and after (0) closing the
connection shows. Should I create another recordset to move rs into,
or should I just not bother and close cnTemp at the end?

--
Cheers, ymt.

20 Mar 2006 7:57 AM

Cor Ligthert [MVP]

Yuk,

In dotNet is ADODB almost not used except in conversion projects from VB6.

Therefore you can better try another group, which is as well not the best
one for this question, however in which are some regulars who answers these
questions as well. (There is a better one, however I have looked where the
last week active in that and that is not so high active.)

I would try it in your case in:
microsoft.public.dotnet.framework.adonet

You can try as well
microsoft.public.datatabase.ado
Which has the problem at the moment that it is not so active.

It does not mean that you are not welcome here or that there are no persons
active in this newsgroup who cannot answer this question. However as with
me, you have the change that it is very old long time not used knowledge.

Cor

Show quoteHide quote

"Yuk Tang" <jim.lak***@yahoo.com> schreef in bericht
news:Xns978C203B087BEjimlaker2yahoocom@130.133.1.4...
> I'm trying to grab the fieldnames and values from a recordset, but
> I'm getting errors. I have an idea what the error might come from,
> but I'm not sure how to correct it.
>
> I'm connecting to an Access db which has the table 'Users', and I
> want to grab the fieldnames and values without necessarily knowing
> their numbers and formats. The msgboxes are there to illustrate the
> error.
>
> To run this, you need an Access db called Users.mdb in the executable
> directory, a table called Users in that db, at least 4 fields in the
> table, including a field called UserName. One of the records must
> have the UserName Fred. For older versions of Access, OLEDB.4.0 needs
> to be changed (3.5 instead of 4.0 for Access 97 IIRC).
>
>
>
> Public Sub GetValues
> MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Settings
> \Users.mdb;"
> ConnectionString = "SELECT * FROM Users WHERE UserName='Fred';"
> Dim rs As ADODB.Recordset
> Dim cnTemp As New ADODB.Connection
> cnTemp.Open(MyConnection)
> rs = cnTemp.Execute(ConnectionString)
> MsgBox(rs.Fields(2).Value)
> MsgBox(rs.Fields(3).Name)
> cnTemp.Close()
> End Sub
>
>
>
> I get the following error at the line
> MsgBox(rs.Fields(2).Value)
>
>
>
> An unhandled exception of type
> 'System.Runtime.InteropServices.COMException' occurred in adodb.dll
>
> Additional information: Either BOF or EOF is True, or the current
> record has been deleted. Requested operation requires a current
> record.
>
>
>
> It reads the recordset fine, since I get no errors if I change Value
> to Name. My guess is that the recordset can potentially return more
> than one record, and hence more than one potential value for that
> field. So how do I get around this by pointing instead to the first
> record in the recordset? I've tried rs.movefirst before accessing
> the Value, but I get the same error.
>
> Also, I would like to disconnect the recordset before doing anything
> with it, but ending the connection appears to wipe out the recordset
> as well, as counting the fields before (6) and after (0) closing the
> connection shows. Should I create another recordset to move rs into,
> or should I just not bother and close cnTemp at the end?
>
>
> --
> Cheers, ymt.

20 Mar 2006 9:16 AM

diego

Hi Yuk,

the following code works for me:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        Dim i As Int16
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset

        Try
            If cn.State <> 1 Then
                With cn
                    .CursorLocation =
ADODB.CursorLocationEnum.adUseClient
                    .Open("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=<YOUR DATABASE FILENAME GOES HERE>")
                End With
            End If

            If rs.State <> 1 Then
                rs.Open("SELECT * FROM HOSTS", cn,
ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockOptimistic)
            End If

            With rs
                For i = 0 To .Fields.Count - 1
                    Console.WriteLine(.Fields(i).Name & " - " &
..Fields(i).Value)
                Next
            End With

        Catch ex As Exception
            MsgBox("ERROR: " & ex.Source & " - " & ex.Message & vbCrLf
& ex.StackTrace, MsgBoxStyle.Critical)
        Finally
            If rs.State = 1 Then
                rs.Close()
            End If

            If cn.State = 1 Then
                cn.Close()
            End If
        End Try
    End Sub

hth,

diego

20 Mar 2006 10:59 PM

Yuk Tang

Thanks for the replies. I guess I'll have to switch over to ADO.NET or
something sooner or later.

--
Cheers, ymt.