?
Solved

Recordset problem

Posted on 2003-11-19
11
Medium Priority
?
326 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
Industry Leaders: 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 200 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

771 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