Solved

Recordset problem

Posted on 2003-11-19
11
324 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
[X]
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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

733 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