item cannot be found in the collection corresponding to the requested name or ordinal

al4629740
al4629740 used Ask the Experts™
on
I get the error on this line:

frmRegistrationTitleXX.Text22.text = rec.Fields(4)

Can anyone see what is wrong?  I am using VB6 and my db is SQL 2005
Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",1433;Network Library=DBMSSOCN;Initial Catalog= Informational; User ID=sa;Password=xxxxxxxx"
 
 
ravi = Chr(39)
 
 
        esql = "select * from Organizations where Password = " & ravi & txtPassword.text & ravi
        rec.Open (esql), conn, adOpenDynamic, adLockOptimistic
 
    
    If rec.EOF And rec.BOF Then
    
    MsgBox "Note: " & txtPassword & " not found.  Please try another code or contact your Administrator.", vbOKOnly, "Password Not Found"
    
    rec.Close
    Exit Sub
    
    End If
 
 
 
 
 
 
 
 
 
 
DataGridOrganization = rec.Fields(1)
 
 
 
 
frmRegistrationTitleXX.Combo11.text = DataGridOrganization
frmRegistrationTitleXX.Combo11.Locked = True
frmRegistrationTitleXX.Text22.text = rec.Fields(4)
 
 
 
 
conn.Close
Set conn = Nothing

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dirk HaestProject manager

Commented:
How many fields does your table contain ?
   select * from Organizations


GOOD AFTERNOON!

it means ur pointing to a field on the table which is not there...

make sure there is really rec.Fields(4) exist on ur table...


game-master


instead of using rec.Fields(4)

why not use rec!fldName        'im just using fldName as the name of my field.. change it by the name of ur
field on the table..

i hope i can give u idea...


game-master
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I had a similar error in Access. It may be relevant..
The problem then was that Access was preserving the ordinal positions of original fields even after new fields were inserted between them.. I.e. The fourth field was no longer ordinal position 4.
 The problem was resolved by copying the whole table to another name then deleting the original table and renaming the new back to the old name.. The copy process forced a re-alignment of ordinal positions..
Allong the lines of "do you really have a Fields(4), do you really have 4 feilds, but they are Fields(0) -> Fields(3) rather than Fields(1) -> Fields(4).

Author

Commented:
Yes there are five fields and I double checked it.  I also added the data to a new table and still have the same problems.  It must be the data itself,eh?
In the statement...
frmRegistrationTitleXX.Text22.text = rec.Fields(4)
... 'Fields' is the only collection (unless you have a control array).

Now the one thing you can TRY is to specify what you want from that last field.  After all, the command "rec.Fields(4)" is actually just a reference to a field object.  What you want is the field's VALUE, so you could try changing the command to ...

frmRegistrationTitleXX.Text22.text = rec.Fields(4).Value

...just to be more specific.

Have you tried running this in the IDE?  If so, you should be able to put a watch on things like 'rec' and ''rec.Fields" so that you can look and see exactly how many field objects are in the Fields collection.

If it wasn't for the fact that you don't claim any problems with...
DataGridOrganization = rec.Fields(1)
... I would have suspected that there was a problem at the 'rec' level (such as an empty record set).

The only thing else that I can GUESS might be the problem is if the value of Fields(4) is Null.  I guess I would suggest seeing if something like the following works instead...

...
 
Dim V as Variant
 
rec.Open (esql), conn, adOpenDynamic, adLockOptimistic
If rec.EOF And rec.BOF Then
  V = rec.Fields(4).Value
Endif
 
...

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial