Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Recordset problem

Posted on 2003-11-19
11
323 Views
Last Modified: 2010-05-03
Hi there,

I have a ADODB Recordset from a query with all the fields from the table at the SQL database. I only want to write some of the fields to a listview and I have an array with the names of the fields I want to show.

Code is as follows:

        RsLlista.MoveFirst
        For i = 1 To RsLlista.RecordCount
            With FLlista.ListView1
                Set mitem = .ListItems.Add
                    mitem.Text = RsLlista(arraycols(0)).Name
                    mitem.Tag = RsLlista!Id
                    For j = 1 To FLlista.ListView1.ColumnHeaders.Count - 1
                        mitem.SubItems(j) = RsLlista(array(j)).Name
                    Next
            End With
            RsLlista.MoveNext
        Next

Problem is that I get an error saying more or less (translation from Spanish) "Cannot find element from collection...".

If I write the name of a field i.e. RsLlista("NAME").Name, it works, but when VB has to get the name from a variable I have problems.

Any idea is welcome.

Thanks in advance,

Alex.
0
Comment
Question by:AlexEspinal
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 9782582
RsLlista.Fields(I).Value
0
 

Author Comment

by:AlexEspinal
ID: 9782666
Hi Mikal

the "I" in your solution is a number, and I only have the name of the field I want to get.

for example, from 10 fields I have in the table, I only want to get field "NAME" and field "TELEPHONE". Recordset has all the fields from the query "select * from friends"

I have 2 variables. var1="NAME" and var2="TELEPHONE"

I want to put the two fields in a listview.

RsLlista("NAME").name works in the code above, but RsLlista(var1).name does not work.
Why?

Thanks in advance,
Alex.
0
 
LVL 8

Expert Comment

by:Rog
ID: 9782759
What you could do is create the recordset with only the fields you want.  I . E. list from List box.

This would be dynamic SQL and could solve your problem.

Rog
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:AlexEspinal
ID: 9782880
Hi Rog,

That's what I don't want to do. I would like to know if there is any way to put a variable instead of a fixed string between the brackets in the RsLlista(variable).Name

Thanks for your time and efforts,
Alex.
0
 
LVL 8

Expert Comment

by:Rog
ID: 9782942
What you could do is store the list in a comma delimited string.

Then use the Instr function to see if the field exists in the string.  If it does then you can add it to the listview.


Might work.

Rog
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9783006
RsLlista(""" &  var1 & """ ).name

0
 

Author Comment

by:AlexEspinal
ID: 9783088
Hi Mikal,

It doesn't work.

var1="NAME"
rsllista(""" & var1 & """).name   'returns the error of my first message. It seems that it is unable to find the field name that is in var1.

I am sure we are close. Keep on trying.

Alex.
0
 

Accepted Solution

by:
blazher earned 50 total points
ID: 9785210
it's kinda confusing here

by using   .name   what u want is to find the name of the record...

if you type this way,   RsLlista("NAME").Name  
                                               ^--> this is already the name of the record column!!!???

different if u type it this way  RsLlista.fields(0).Name
this way, u'll get the name of the first column in the recordset.

if you're trying to get the value...
try this  RsLlista(var1).Value   (as the one you like)
0
 

Author Comment

by:AlexEspinal
ID: 9785885
I'll try to explain it with other words. Sorry if I have not been quite clear, but English is not my native language.

I have RsLlista defined as ADODB.Recordset
RsLlista is the result of a stored procedure in SQL Server which has the following code:

CREATE PROCEDURE ae_findclient
AS
select * from clients order by name
GO

So I have all the information from clients table, with the fields ID, NAME, ADDRESS, TEL, E-MAIL, FAX, WEBSITE, BIRTHDAY

In the VB project, I have a listview where I want to show all the clients as a report, but the user has a small app in the main program where he decides which fields to show. i.e: just NAME, ADDRESS AND PHONE (but this can vary from day to day as the user can change it)

NAME                  | ADDRESS               | TELEPHONE
-------------------------------------------------------------
Alex                    | 12 Main St              | 555-4321
John                    | 1300 Ocean Rd       | 555-1234
...

Then doubleclick or Enter on a row will show the complete information of the selected line, in a new form.

I have put the names of the fields I want to show in an array, so
array(0)="NAME"
array(1)="ADDRESS"
array(2)="TELEPHONE"
(but this could be also array(3)="E-MAIL", and so on till complete all the fields in the table)

I have created the following procedure to fill the listview:

        RsLlista.MoveFirst
        For i = 1 To RsLlista.RecordCount
            With FLlista.ListView1
                Set mitem = .ListItems.Add
                    mitem.Text = RsLlista(arraycols(0)).Name
                    mitem.Tag = RsLlista!Id
                    For j = 1 To FLlista.ListView1.ColumnHeaders.Count - 1
                        mitem.SubItems(j) = RsLlista(array(j)).Name
                    Next
            End With
            RsLlista.MoveNext
        Next

I have all the info in RsLlista, and I don't have to requery to find all the info to show in the clients form.

My doubt is: if rsllista("NAME").name works fine (first record shows "Alex"), why rllista(array(0)).name does not work, if array(0)="NAME"?

I don't want to build the query string like "select name,address,telephone from clients" and then execute the query, because when I have to show the complete info in the clients form I have to requery "select * from clients where Id=" & FLlista.ListView1.SelectedItem.Tag

The database is quite big and having to requery wastes a lot of time.

If you need more info, just ask.

Thanks to all for your time.

Alex.

0
 

Author Comment

by:AlexEspinal
ID: 9786670
Thanks blazher. The .Value was what I needed.

I was confused with the datareport way of filling text fields, where I put

text1.Datafield= recordset("fieldname").Name

And I was stuck with the .Name

Cheers!
Alex.
0
 

Expert Comment

by:blazher
ID: 9792300
i'm so glad that i can be such an assistance on my first comment

tehehe

thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA error replacing data 6 40
MS Access Search and Replace Using VBA 6 80
SLMGR Switches Are Not Working On KMS Host 3 107
Advice in Xamarin 21 96
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question