We recently had a website running a sql 2000 server and then migrated it to a sql 2005 server and now we are having nothing but problems. The current error is this:


ADODB.Commanderror '800a0cc1'Item cannot be found in the collection corresponding to the requested name or ordinal.

any ideas as to what is going on. I have verified the code on both the old server and new server and all the .asp code is still the same. Any ideas?

That error is usually caused by a specified field name not appearing in the recordset.

Can you please be a bit more specific? I have compared the stored procedures on the 2000 server and they match the stored procedures on the 2005 sql server. I have also verified that the .asp code is exactly the same using diff and it is.

No offense, but if you want help, you need to be more specific or no one else can. How could anybody possibly diagnose your problem with only the error message? Do you have a stack trace? The code in those procs? Maybe you're using deprecated SQL?

A stack trace. Can you tell me how to get that off an external server? As for more of the code. Here is the .asp code:

<%
REM Function gets name of product entity
Function GetName(strAssocType, intAssocObjId)
Select case strAssocType
case "pf_id"
strText = "product family"
strStoredProc = "prod_family_select_by_pf_id"
strRS = "pf_name"
case "pd_id"
strText = "product department"
strStoredProc = "prod_dept_select_by_pd_id"
strRS = "pd_name"
case "prod_id"
strText = "product"
strStoredProc = "prod_select_name_by_prod_id"
strRS = "prod_name"
end select
CreateDBConnection()
ConnectionObject.BeginTrans
CommandObject.CommandType = 4 'adCommandObjectStoredProc
CommandObject.CommandText = strStoredProc
'CommandObject.Parameters.Refres
CommandObject(1) = intAssocObjId
set rsName = CommandObject.Execute()
CloseDBConnection()
If not rsName.eof then
strRS = rsName(strRS)
GetName = array(strText, strRS)
else
GetName = null
end if
End Function
%>

You need to look at the stored procedure that is being called do you know if all 3 of the stored procedures cause the error. My guess is you will find that a field in one of your tables has not migrated properly. You should check the table structure or views, depending on what the stored procedures are doing. You could also try running the stored procedure's from with in the Query Analyser manually which should give you more information on what the problem might be.

I also posted this question on:
http://www.experts-exchange.com/Web_..._22340650.html and it was finally answered with a solution. Please check it out, but basically it was a problem with the actual SQL server migration. When the server was migrated from 2000 to 2005 Microsoft suggested using the Native SQL drivers which was the mistake. Switching back to the ODBC SQL driver was the solution.

thanks for all that replied though.

This is a really interesting post.

We're going to be migrating from 2000 SQL to 2005 SQL. We're also putting our website into ASP.NET (which I'm learning).

But... we have a ton of Intranet ASP code that we were going to leave the same.

I read with ASP.NET there are four connection namespaces: one will go right into MS-SQL and another one with ODBC.

If we're using both ASP.NET and ASP, will how the 2005 SQL drivers are set in SQL Server be an issue to having the code run properly?

DonnaZ

Donna

Your old ASP code should continue to work as written as long as you allow ODBC connections. Your new ASP.NET code should use System.Data.SqlClient namespace to talk to the database, this is much faster than ODBC and also more featured.

Your ASP and ASP.NET code can both run in the same web application, but they won't be able to share session state.