Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1320
  • Last Modified:

error - 3265 Item cannot be found in the collection corresponding to the requested name or ordinal

I have inherited the maintenance and development of a VB .NET application.  I am on a learning curve with VB .NET 2010 as previously I have only done small things.  When my program attempts to perform an insert into a table I am getting a 3265 error  "Item cannot be found in the collection corresponding to the requested name or ordinal..."  I have not changed the field.  I have experimented and commented out various target fields but it always fails.  Pardon the 'old school cobol db2 speak' but it is as if the table has not been declared(no dclgen) but for the first field. "grstMembership.Fields("Sin").Value = gstrSIN"  no problem
The program blows up on: ".Fields("LastName").Value = Trim(txtLastName.Text)"


When the program executes:  DbHelper.OpenADODBConnection
it simply does a:  select 1 from table.
this connection is successful

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
         If gstrAction = "AddMember" Then

           
            grstMembership = New ADODB.Recordset
            grstMembership.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            grstMembership.Open(gstrSQL, DbHelper.OpenADODBConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
            gstrSIN = ConvertSINToDatabase(mskSIN.Text)
           
            grstMembership.AddNew()
           
            grstMembership.Fields("Sin").Value = gstrSIN
         End If


         Dim deceasedMessage As String
         With grstMembership

            'update data
            If gstrAction = "AddMember" Or gstrAction = "ChgMember" Then
                               
                .Fields("LastName").Value = Trim(txtLastName.Text)       <<<<<<<<<<< point of failure
                .Fields("FirstName").Value = Trim(txtFirstName.Text)
                .Fields("Gender").Value = cboSex.Text
                .Fields("BirthDate").Value = ConvertDateToDatabase(mskBirthDate.Text)
                .Fields("AddressEffDate").Value = ConvertDateToDatabase(mskEffDate.Text)

                ... blah blah blah

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
0
davidkjohnson
Asked:
davidkjohnson
  • 4
  • 3
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
To do what you are doing you need to alter your SQL statement to pull back all of the columns you want to write to. "SELECT 1 FROm ***" will only pull back the first column in the table.
0
 
davidkjohnsonAuthor Commented:
I changed the select 1 from table to read select * from table and continue to get the same results.  This was something I did earlier to resolve the problem unsuccessfully.  Further potential solutions are most welcome.  One thing I have noticed is that even in debug mode I cannot see the record fully defined.  I am wondering whether there is a way to see it.
0
 
Carl TawnSystems and Integration DeveloperCommented:
You need to check precisely what columns your SQL statement is returning. Your code is expecting a column called "LastName", but the error says there isn't a column with that name, so there is a discrepancy somewhere.

Also, if this is ASP.Net, why are you using ADODB rather than the .Net data objects?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
davidkjohnsonAuthor Commented:
Thank you Carl,
It is VB .NET code that I have inherited.  As a COBOL DB2 programmer I am used to defining data fields explicitly in a DCLGEN of the 'data division.'  I don't fully understand why the original programmers did things the way they did them.

I am trying to find a way that I can see the contents of the recordset to determine if the code is initializing it somewhere where I don't want it to.

0
 
Carl TawnSystems and Integration DeveloperCommented:
The only potential issue I can see with your code is that you only initialize the recordset if "gstrAction" is "AddMember". If it is "ChgMember" then it will still get to the code that tries to write to the recordset, but it doesn't get initialized anywhere in that scenario (unless it has just been omitted from your post)
0
 
davidkjohnsonAuthor Commented:
As I mentioned this was an application that I had inherited.  Unfortunately there is a lack of documentation, lack of comments in the programs, a complete absence of documented test cases\results et cetera.  The source code version and the production application were not in synch.

I inserted the line of code during a round of elimination and found that it worked.  Previously I may have inserted the code into the called module instead of the calling module.  This is all part of the learning curve.

If gstrAction = "AddMember" Then
            gstrSQL = "select * from membership"                                   <<< added this line

grstMembership.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            grstMembership.Open(gstrSQL, DbHelper.OpenADODBConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
            gstrSIN = ConvertSINToDatabase(mskSIN.Text)
           
            grstMembership.AddNew()
           
            grstMembership.Fields("Sin").Value = gstrSIN
         End If


         Dim deceasedMessage As String
         With grstMembership

            'update data
            If gstrAction = "AddMember" Or gstrAction = "ChgMember" Then
                               
                .Fields("LastName").Value = Trim(txtLastName.Text)       <<<<<<<<<<< point of failure
                .Fields("FirstName").Value = Trim(txtFirstName.Text)
                .Fields("Gender").Value = cboSex.Text
                .Fields("BirthDate").Value = ConvertDateToDatabase(mskBirthDate.Text)
                .Fields("AddressEffDate").Value = ConvertDateToDatabase(mskEffDate.Text)

                ... blah blah blah

0
 
davidkjohnsonAuthor Commented:
I knew the problem was that the fields weren't being defined but I couldn't find in the program an example of how the field definitions were created and couldn't find examples of this in any of the text books.
Through trial and error, thick headed stubborness and a little knowledge I resolved one of my own problems.
0

Featured Post

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!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now