Solved

Recordset problem

Posted on 2003-11-19
11
322 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2003, my txt file export is running backwards 4 52
Excel object stays open 19 76
Recommendation vb6 to vb.net or others 14 149
VB6 ListBox Question 4 44
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
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…

778 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