Solved

Error 3265 accessing MS Access Database from an ASP page: Item cannot be found in the collection corresponding to the requested name or ordinal

Posted on 2004-10-04
23
716 Views
Last Modified: 2012-06-21
I have the following error accessing from an ASP page to a MS Access Database without security passwords.

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

· I have checked security permissions and seem to be all right.

I have tried the following connection methods and none works:

set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & "C:\Inetpub\wwwroot\folder\database.mdb")

and

set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "C:\Inetpub\wwwroot\folder\database.mdb"

I have found some articles on Microsoft talking about a ressolution like:
"Set the object's ParentCatalog property to a valid Catalog object,
-or-
make sure the Parent Catalog is associated with a valid ADO Connection Object. "

but couldn't find how. Please help. Thanks.
0
Comment
Question by:GabrielCucut
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 5
  • +1
23 Comments
 
LVL 15

Expert Comment

by:Thogek
ID: 12220163
What line of code is this error raising on?  Offhand, it doesn't sound like a problem with the initial database connection, but more with the accessing of a data field after a query has been made.  (Unless I completely misread something...)
0
 
LVL 15

Expert Comment

by:Thogek
ID: 12220184
0
 
LVL 15

Expert Comment

by:Thogek
ID: 12220234
Does the error arise on the oConn.Open call, or later...?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:GabrielCucut
ID: 12220367
It looks like the error was not on the open, but when I migrated some databases to a different database some columns changed format. I am investigating it now. I will tell you in a moment.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12220375
I think that Thogek is correct--most often when I see that error, the sql select or recordset points to a field that does not exist in the table.

To make sure however, please try this page:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
</HEAD>

<BODY>
<%
dim objConnection, strDataPath
strDataPath = "C:\Inetpub\wwwroot\folder\database.mdb"
set objConnection=Server.CreateObject("ADODB.Connection")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
                  & " Data Source= " & strDataPath & ";"_
                  & " Mode=Share Deny None;User Id=admin;PASSWORD=;"

objConnection.ConnectionTimeout = 15
objConnection.CommandTimeout =  10
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
      objConnection.Open strConnectString
end if

Response.write("The Connection State is: " & objConnection.State)

%>
</BODY>
</HTML>
0
 
LVL 15

Assisted Solution

by:Thogek
Thogek earned 50 total points
ID: 12220399
> when I migrated some databases to a different
> database some columns changed format

If any of your data columns changed name, make sure that the names you use in your code match the names of the columns in the data tables (as I believe Fritz is suggesting)...
0
 

Author Comment

by:GabrielCucut
ID: 12220488
It looks like it is happenning something similar to what you are saying.

After every connection there is an error check and a collector for all errors of different connections.

Which property of the recordset can I use to obtain the name of teh adodb connection that is failing?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12220527
Why do you have so many connections? Is there more than one database?

FtB
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 12220557
>>Item cannot be found in the collection corresponding to the requested name or ordinal

This means you are refering some columns but didn't pull it from SQL or Stored PRocedure

So, check your SQL or Sp and make sure you are usign the correct name

If you have spaces or between column names use braces in your SQL statement like [Party Code]

and in recordset object, directly use it like rs("Party Code")
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12220660
@jitganguly --

Thogek has already made that observation above in his first comment.

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12220687
To review, here is where we are:

1) Thogek observes that this error usually results from pointing at a non-existant field in the table or recordset
2) I concurred that is most often the case, but since there was some concern that the connection was not being made, I offered a simple test page. If this worked, then we could be reasonably sure that Thogek had it correct.

FtB
0
 

Author Comment

by:GabrielCucut
ID: 12220724
fritz_the_blank

I have tried the code and pasted it in an html file calling it check.html and when run only a blank page is loaded (all white background).

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12220738
You have to paste it into an .asp file for it to work.

FtB
0
 

Author Comment

by:GabrielCucut
ID: 12220821
Ok, I put it as check.asp and says the connection state is 1.

How can I use it for the Microsoft SQL server being a system-DSN ?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12220857
Okay, let's stop there and make clear that you are able to make a connection, and that is a good start!!!

Now, I am a little confused. Is this question about a problem grabbing data from an Access database or about making connections in general? If the former, we should try to get your data out and isolate why you were getting that error. If the latter, then we need to work on a new connection string.

FtB

0
 

Author Comment

by:GabrielCucut
ID: 12221639
Finally: The problem is connecting to an SQL Server table.

I think I have found where the problem is:

I have migrated an Oracle database to SQL Server and changed a "to_char( end_date, "dd/mm/yyyy")" function into "convert(char(10), end_date, 103)" and I think the problem is the first time I use this CONVERT function.

So, probably the error has more to do with SQL server than ASP. Anyway, I am using ADODB.Recordset, should I try another connection mechanism to use this transact-SQL-specific functions?
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 450 total points
ID: 12221672
Sure. The SQL connection string is very similar:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
</HEAD>

<BODY>
<%
dim objConnection
set objConnection=Server.CreateObject("ADODB.Connection")
strConnectString = "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"
objConnection.ConnectionTimeout = 15
objConnection.CommandTimeout =  10
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
     objConnection.Open strConnectString
end if

Response.write("The Connection State is: " & objConnection.State)

%>
</BODY>
</HTML>
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12221675
However, I am still not sure how the above relates to your original error...

FtB
0
 

Author Comment

by:GabrielCucut
ID: 12221747
Thanks, I have found it. The problem was:

Using CONVERT( ... end_date ...) the resulting variable is a "Expr1".
Later in the ASP the program wants to access the "end_date" column in the recordset.

I am not an Oracle expert, but maybe the TO_CHAR function does not need to put "AS END_DATE" after the function, and it seems that SQL Server does need that or either it puts an Expr1, Expr2, ... names.

(Thanks a lot, I think it is fair to concede some point to Thogek and the rest for you.)
0
 
LVL 15

Expert Comment

by:Thogek
ID: 12222057
So, basically, the field names you used in your code did not match the names of the columns returned by the query?  (E.g., "end_date" vs. "Expr1".)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12222136
I am happy to have helped but am curious, why the grade of B?

FtB
0
 

Author Comment

by:GabrielCucut
ID: 12230695
I just thought that grade Excellent was when a very-very difficult question is answered really straight forward. This was not the case, maybe because I didn't provide apropriate information to target the problem, however I still think that an Excellent is when an answer has been extremely valuable.

thanks, Gabriel
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12232640
Fair enough. Just keep in mind that everyone who answers questions here does so on a voluntary basis without compensation, and for most of us, the points and the grade are a token of appreciation for time and effort spent. Moreover, in this instance, the answer had very little to do with the question posed. If you would like straightforward answers, be sure to take a lot of time to craft your question carefully. Otherwise, there is no way, other than sheer luck, for us to provide you with that straight forward response that you are looking for (providing that such an answer even exists!).

FtB
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question