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=Micro soft.Jet.OLEDB. 4.0;Data Source=Settings
\Users.mdb;"
ConnectionStrin g = "SELECT * FROM Users WHERE UserName='Fred' ;"
Dim rs As ADODB.Recordset
Dim cnTemp As New ADODB.Connectio n
cnTemp.Open(MyC onnection)
rs = cnTemp.Execute( ConnectionStrin g)
MsgBox(rs.Field s(2).Value)
MsgBox(rs.Field s(3).Name)
cnTemp.Close()
End Sub
I get the following error at the line
MsgBox(rs.Field s(2).Value)
An unhandled exception of type
'System.Runtime .InteropService s.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. 3 3878
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.publi c.dotnet.framew ork.adonet
You can try as well
microsoft.publi c.datatabase.ad o
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********@yah oo.com> schreef in bericht
news:Xn******** *************** ********@130.13 3.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=Micro soft.Jet.OLEDB. 4.0;Data Source=Settings \Users.mdb;" ConnectionStrin g = "SELECT * FROM Users WHERE UserName='Fred' ;" Dim rs As ADODB.Recordset Dim cnTemp As New ADODB.Connectio n cnTemp.Open(MyC onnection) rs = cnTemp.Execute( ConnectionStrin g) MsgBox(rs.Field s(2).Value) MsgBox(rs.Field s(3).Name) cnTemp.Close() End Sub I get the following error at the line MsgBox(rs.Field s(2).Value) An unhandled exception of type 'System.Runtime .InteropService s.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.
Hi Yuk,
the following code works for me:
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim i As Int16
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Try
If cn.State <> 1 Then
With cn
.CursorLocation =
ADODB.CursorLoc ationEnum.adUse Client
.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.CursorTyp eEnum.adOpenDyn amic,
ADODB.LockTypeE num.adLockOptim istic)
End If
With rs
For i = 0 To .Fields.Count - 1
Console.WriteLi ne(.Fields(i).N ame & " - " &
..Fields(i).Val ue)
Next
End With
Catch ex As Exception
MsgBox("ERROR: " & ex.Source & " - " & ex.Message & vbCrLf
& ex.StackTrace, MsgBoxStyle.Cri tical)
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
Thanks for the replies. I guess I'll have to switch over to ADO.NET or
something sooner or later.
--
Cheers, ymt. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: middletree |
last post by:
Yesterday, I posted a problem which, by the way, I haven't been able to
solve yet. But in Aaron's reply, he questioned why I did several things the
way I did. My short answer is that I have a lot to learn, but now I'd like
to ask anyone who reads this, including Aaron, for some clarification. I
imagine others might benefit, too.
"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote
> A few suggestions.
> (3) why do you constantly set...
|
by: InvisibleMan |
last post by:
Hi, I feel a little dumb for asking this (considering im writing
TSQL) but there doesn't seem to be any definitive answers on the
search engines...
Okay I understand that if you open the ADO connection that you
close it with:
adoCon.Close
Set adoCon = Nothing
|
by: jason |
last post by:
hello,
i have a C# class library that i recently had to add ADODB
functionality to, in order to support consumer applications that can
only work with recordsets.
i added the ADODB library reference, and implemented recordsets without
a problem, however now my class library dll won't register. when i
attempt to register it with COM+, the MMC reports "could not find the
file specified". since this occured right AFTER i added the ADODB
|
by: |
last post by:
Hello ppl,
I have snippet that works fine udner ADODB with VB6, but something wrong
with it in Vb.NET. Anyone can help?
Recordset1 (ADODB.Recordset) Error: Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.
Error Source:
SELECT * FROM Accounts
|
by: Wonder |
last post by:
We have a VB.NET project with a reference to ADO 2.5
When I open it on my machine, I get the following build error: "The
referenced component 'ADODB' has an updated custom wrapper available."
When I double click on it, I get the following dialog appear:
If I click 'No', for every object declared from the ADODB library, I get a
build error as follows: "Reference required to assembly 'ADODB' containing
the type 'ADODB.Connection'. Add...
| |
by: Peter Newman |
last post by:
Im still trying to get my head around this, but its the only way i have seen
to be able to create a 'recordset' and bind text boxex to it and navigate
throw each record by the click of a button ... ok it may not be the prettist
of code but i have that bit working . i have however stumbled across another
problem ...
I make a connection to a SQL2005 database when my app loads using the
following
CON_BOSSCONNECTION.ConnectionString =
|
by: Drew |
last post by:
I posted this to the asp.db group, but it doesn't look like there is much
activity on there, also I noticed that there are a bunch of posts on here
pertaining to database and asp. Sorry for cross-posting.
I am trying to build a "checklist", where a user can navigate to an ASP page
on the intranet which shows a list of "questions" that the user can check
off. I am trying to figure out how to do this so that it is scalable, but I
am...
|
by: boyleyc |
last post by:
Hi all
I have written a database in access and used ADODB recordsets all the
way through. The only recordsets that are not ADODB are the listbox
navigation code automatically generated by access 2003 as follows :
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
|
by: patjones |
last post by:
Hi -
I am just starting my study of ADO, and I have a purpose in my current project which seems to lend itself to using an ADODB recordset. The set up is this.
I have a table tblScreenShot that has three fields: fldTicketNum, fldScreenShotDescription, and fldScreenShot. Basically, what this table will do is store screen shots (fldScreenShot is typed "OLE Object"). Perhaps 1 in 20 records in my project will necessitate a screen shot...so...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |