[coachdan32]

I am getting an error 800a0cc1 on a page. All my google searches say it is related to a misnamed field name or something to that effect. I have been unable to see any such problem. The code is attached and I will post a second time with a screen shot of my DB design attached. Can anyone see what may be causing the problem. The error is being returned on line 521.

[coachdan32]

Here is the screenshot.

[coachdan32]

'

[MustBNuts]

Not a very helpful error description, coach!

the error is: ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

the problem seems to exist in a part of your code you have not included. At some point you must have code that collects the data from the database, or at minimum, collects the fields available from the dbase. The error seems to indicate that your code is referencing an index that doesn't exist or a field....

I notice that you have a lot more fields in the table than you do in the code....are you retrieving all the fields from the dbase table? Make sure they are in the correct order and try to assign a value to everything.

Hope this helps,

MBN

[coachdan32]

MustBNuts,

All of the code in the file named as the one producing the error is included in the text file included on the first post. There is another page that sends values to this page (I will include an attachment). The new page that I am including is where the user inputs the data and it is sent to a confirmation page, which is the one throwing the error. Both pages worked properly until I removed 2 fields from them (I also removed them from the DB). The fields removed were called "FTPT" and "AMPM".

[MustBNuts]

Sorry coach, for some reason, I didn't see that SQL code at the bottom of the first txt file...

I have only seen this error occur when the requested field can't be retrieved from a table. You say this only began when you removed the two fields...

The first thing I would do is copy out each SQL statement and copy them into an Access query (SQL view) in the appropriate database and run them. This should give you an idea of which one might be to blame.

At least this would isolate the issue a bit...

MBN

[coachdan32]

Thanks, I will give that a try.

[coachdan32]

The was a good way to test the statements, but all of them on both the input page and the confirmation page test perfectly. The line returned on the error is actually in the middle of a very very nested portion of the connection string. The section starts like this and you can see the whole file on the tms.txt attachment.


'*********** LOAD DATA INTO TABLE **************
'
If varBusName = "Citi" Then
If varYrA = "2002" Then

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.Mappath("Training_Delivery_Data.mdb") & ";"

SQLAdd = " SELECT * FROM TMS "

Set uRS = Server.CreateObject("ADODB.Recordset")
uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic

End If

[MustBNuts]

Okay, based on your information about the nested if's, the problem must be generated as a result of this part of the code:

uRS.Fields("TName") = varTName
uRS.Fields("LocCity") = varLocCity
uRS.Fields("TypeTrn") = varTypeTrn
uRS.Fields("NumStdts") = varNumStdts
uRS.Fields("NumPCs") = varNumPCs
uRS.Fields("TrnRm") = varTrnRm
uRS.Fields("NumCls") = varNumCls
uRS.Fields("DateFrom") = varDateFrom
uRS.Fields("DateTo") = varDateTo
uRS.Fields("445Month") = var445Month
uRS.Fields("445EndMonth") = var445EndMonth
uRS.Fields("Assign") = varAssign

Either one of these variables is wrong, or the field does not exist in one of the tables/databases.

Next step would be to delete most of the nest (obviously in a test file) so that you only have one nested if statement that can only point to one of the databases. Then try to execute the form.

Looks like you have it broken down into the Citi group and the Sears group, then by years....so, for instance, just keep the Citi for 2004 and just make your form choices that you know will match those criteria...see what happens...

Or you can go in and check each of those databases to make sure they each contain the appropriate field listed in the uRS.Fields section...

Let me know how you make out...

MBN

[coachdan32]

Sorry it took me so long to respond, but they have me putting out another fire. I will try what you suggested, but it may be a day or so before I can. It is a good idea - hope it helps me figure it out.

[coachdan32]

MustBNuts,

I took the nesting away from the connection string and narrowed it down to one specific DB, it worked perfectly. When I tested the code, I put a temporary DB with the field changes in (and made sure the name matched), but the other DB's were unchanged and did not have the field changes. When selecting the options, I chose choices that would make it post to the temp DB and not one of the unchanged DB's. Is it possible that the fields not being in all the DB's could cause the error - despite only the temp was being called upon?
I don't see how the connection strings could be causing it because they are present in the code that is currently working (without the field changes). The nested code is indeed not efficient and in need of redesign, but if currently works - should work on the new as well. What is your advice?

[MustBNuts]

Okay, I'm a bit confused, but it sounds like you've made changes to something just before you tested....that defeats the purpose.

The idea is to force the data onto the dbase and, hopefully, duplicate the error (in an attempt to isolate the issue). If you've changed some fields and it is now not throwing an error, then it sounds like you've isolated the problem.

Yes, you could have problems if you are setting variables and querying a dbase that doesn't contain that/those fields...in fact, I'm betting that is why you kept getting the error.

With regard to the nested ifs, if it is working now, don't change it - first isolate your error.

MBN

[coachdan32]

The time lapse between posts has confused the issue I think. The only change I made to the file since the prior post was to take the optional DB's out of the picture. The DB was being selected via a nested If statement that checked for the value of the year and the Business name (Citi or Sears) - I simply took the IF statement out and hard-coded it to one DB. It posted the data fine. All the talk about changing the fields was only to refresh your memory of what change to the original code started the problem.

[MustBNuts]

Okay, I was playing around with your nested if stmts and came up with a more streamlined Select Case stmt. HOWEVER, in the process, I may have found your problem:

If varBusName = "Citi" Then
If varYrA = "2002" Then
Select Case varYrA

Case "2002"
txtDBase = "Training_Delivery_Data.mdb"

Case "2003"
txtDBase = "Training_Delivery_Data03.mdb"

Case "2004"
txtDBase = "Training_Delivery_Data04.mdb"

Case "2005"
txtDBase = "Training_Delivery_Data05.mdb"

Case "2006"
txtDBase = "Training_Delivery_Data06.mdb"

Case "2007"
txtDBase = "Training_Delivery_Data07.mdb"

Case "2008"
txtDBase = "Training_Delivery_Data08.mdb"

Case "2009"
txtDBase = "Training_Delivery_Data09.mdb"

Case "2010"
txtDBase = "Training_Delivery_Data10.mdb"

end select

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.Mappath("Training_Delivery_Data.mdb") & ";"

SQLAdd = " SELECT * FROM TMS "

Set uRS = Server.CreateObject("ADODB.Recordset")
uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic


Else
...
end if

Look at the bolded line above. I was going to input the newly created variable txtDBase in there to replace your quoted dbase, when I noticed that it appears you have some quotation marks, etc., in the wrong place.

I have absolutely no way of testing this, but try this type of phrasing:

Connect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.Mappath( txtDBase) & ";"

The problem is that with your nested if way, I'm not sure how to replace the required quotation marks...

Your Connect breaks down when it comes to a quotation mark (which ends the connection statement) but then continues without the & it needs....

You will need to play with single/double quotes around the txtDBase variable if the mappath demands the quotation marks...

I realize this sounds confusing...but reallly dissect the connect statement.

MBN

[coachdan32]

I had to redesign the connection string on another page because of errors within it. The final code went like this:


'*********** LOAD DATA INTO TABLE **************
'

lsYear = Right(varYrA, 2)

If varBusName = "Citi" Then
varDB = "Training_Delivery_Data" & lsYear & ".mdb"
Else
varDB = "Training_Delivery_Data" & lsYear & "Sears.mdb"
End If

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath(varDB) & ";"

SQLAdd = " SELECT * FROM TMS "

Set uCN = Server.CreateObject("ADODB.Connection")
Set uRS = Server.CreateObject("ADODB.Recordset")

On Error Resume Next
uCN.Open Connect
if ERR.Number <> 0 then
Response.Write("Error Number -> " & Err.Number)
Response.Write("<BR>Error Source -> " & Err.Source)
Response.Write("<BR>Error Desc -> " & Err.Description)
Response.Write("<BR>Connect -> " & Connect)
Err.Clear
end if
uRS.Open SQLAdd, Connect, adOpenKeyset,adLockOptimistic


Does this also take care of the problem you saw? I am not sure exactly what in the file path you saw an error in.