Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Recordset problem

Posted on 2003-11-19
11
Medium Priority
?
327 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 D
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 D
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

610 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