Dumb questions about ADODB recordsets

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

[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

"Yuk Tang" <ji********@yahoo.com> schreef in bericht
news:Xn*******************************@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.

[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

[Yuk Tang]

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