Unanswered: ADODB.Fields error '800a0cc1' - Long ongoing issue
New to the forum and I thought you may be able to help with the issue we are having.
Here is some background on this issue:
We have a website based on ASP that users log into to check mission data. It uses an SQL 2000 backend to house the data.
Users have access to various regions/client data that will show in a list box on the left hand side.
When we now add a new region/client the website presents the following error for everyone accessing Mission Reports:
ADODB.Fields error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/_ScriptLibrary/Recordset.ASP, line 925
The way a new region/client is added is very simple, there is a table called client where we add an ID field, Client name and the mission abbreviation( 3 characters long). After adding the client to the table we then add a new column in the Profiles(users) table. If a user is granted access it shows a 1 for that client/region.
So typically all we had to do grant access to a client/region was to check a box for that client in the administration GUI. Now we have to hard code the profile_id for each for any new clients. It's a PITA.
I have researched this error and it is not a spelling error and the column exists.
I have looked at the recordset.ASP page and this is the following function where it has the issue:
var r = '';
if (!this._newRecord && this._isValid && (typeof(field) == 'number' || typeof(field) == 'string'))
-->line 925 if (this._rsFields(field).value != null)
var fieldType = this._rsFields(field).type;
//avoid JScript formating for the following field types:
//adDate, adDBDate, adDBTime, adDBTimeStamp, adSingle
if ((fieldType == 7) || (fieldType == 133) || (fieldType == 134) || (fieldType == 135) || (fieldType == 4))
r = Server.HTMLEncode(this._rsFields(field));
r = this._rsFields(field).value;
The error occurs when I add this line of code to the missions.asp page(This is the page that displays the clients list):
To avoid the error I had to add this code instead for each user:
if (Session("svUID") == 1123) <-- Users profile_id
Any ideas would be greatly appreciated. If this is in the wrong section, mods feel free to move it.
If you have any questions or need any more clarity on the setup feel free to ask.
If you're making schema changes like this then your design is flawed.
What you have here is a many-to-many relationship between users and clients.
To resolve your issues you need to create a 3rd table that has but 2 columns: one for the user id and one for the client id. These two columns make up your primary key for this table.
This design will make everything a lot easier to deal with!
I might guess that the query that generates the recordset used by the object "this" runs something along the lines of "select * from table(s)". The asterisk being the critical bit. If the application is up and running, the definition of the recordset may be cached, and limited to the original number of columns (i.e. actual minus one). Alternatively, there may be a view defined by "select *" in the mix, which is a very definite bad choice in design. SQL Server itself will cache the number of columns returned, and leave out the new column even at that level. If you have a test system, add a new client the old fashioned way, confirm you get the error, then restart the application, and see if you get the error again. If you do, then there is probably a view defined with "select *" in the mix.