Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2007
    Posts
    19

    Unhappy Unanswered: Query errors - SQL or ASP problem?

    Trying to covert website from Access to SQL Server. Query that ran fine on Access throws error when I try to display value on web page using ASP. Something about a data element is not available. When I remove the alias the query displays fine. Is anyone familiar with this behavior that has figured it out? I don't even know if it is an issue with ASP, SQL Server 2005, the Express version, or what.

    I'd sure appreciate any help I can get.

    Thanks,

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You will have to provide the structure of your query or the part that contains the alias reference, for people to be able to understand your problem.

    I would at this stage however suggest that your error is completely SQL related. MS Access has some very different and often irritating SQL Implementation differences. For example, the JOIN syntax of MS Access is absolutely terrible.

    An easy way to detect your problem would be to run the SQL Statement directly in SQL Server and thus bypass any ASP involvement. This will show you where the problem is.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2007
    Posts
    19
    Thanks for helping.

    Actually the query seems to run fine. The error happens when I try to display the result, and that's why I'm not sure if it is a SQL or ASP error. Here is the code:

    Query:
    sSQL = "Select * from tblTXMup AS Tt, tblTXMupPlans AS Tp " & vbCrLf
    sSQL = sSQL & " WHERE Tt.cID = Tp.cid " & vbCrLf
    sSQL = sSQL & " AND Tt.salesID=" & salesID & vbCrlf
    sSQL = sSQL & " AND cStatus = 86 " & vbCrLf
    If sChk <> "" and sPar <> "" then
    sSQL = sSQL & " AND " & sChk & " LIKE '%" & sPar & "%'" & vbCrLf
    end if
    sSQL = sSQL & "ORDER BY " & orBy

    Display Code:
    Line 158:

    Error Message:
    Error Type:
    ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    /dev2/newweb/admin/deadUpReport.asp, line 158

    Corrected Display Code:
    Line 158:


    Next Error Message:
    Error Type:
    ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    /dev2/newweb/admin/deadUpReport.asp, line 160

    Note that Line 158 is no longer being cited. The error is on the next instance of using an alias. If I have to go through hundreds of queries and make these corrections this conversion is going to take a very long time. This query and display ran fine when run against the Access db.

    Is there any way it can be corrected without editing every query?

    Thanks for any help,

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by Loyd
    Thanks for helping.
    Error Message:
    Error Type:
    ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    /dev2/newweb/admin/deadUpReport.asp, line 158
    OK. This is just a simple problem relating to your offsets into the record set array. Before we look at this aspect of the problem, I should point out that it you should never use the Select * syntax in any query ever, outside of debugging in the Database's SQL query interface;. Select * is always, in every production database in any industry, an error.

    One reason why it's an error is because it contributes to problems such as the one you've described here. The developed is left to guess the position of the columns returned in the result set. By correctly defining the column names in the select clause you can avoid these errors and any others that arise from ambiguity in the definition of the set.

    For you specific problem, you will need to post the lines of code, for which there should be no more than two or three, which deal with the indexing of the result set, as this is the source of your error.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Nov 2007
    Posts
    19
    Robert, thanks for the input. I will specify columns in the query and see if that allows me to use the aliases in the output. If that doesn't work I'll post the code, but I have got to get some sleep now, so will do it tomorrow and report back on results. I really appreciate your help on this issue.

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The problem will not be related to the aliases. The query itself is pretty much irrelevant here. The error messages reports that the problem is to do with your method for accessing the columns of the result set, not the actual query. We therefore need to see how you are referencing the columns of the result set from your ASP code.

    Another problem with SELECT * with table aliases is that you may very well have two columns from different sets, each set having a different alias, but each column has the same name. In this case, your error message will be directly related the inability to execute your query, which is not the message you are receiving. This point does, however, highlight another reason why SELECT * should never be used in any situation other than bespoke data analysis.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to ASP topic
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Posts
    19
    I changed the query to reference specific columns, each aliased, and I still get the same errors when trying to display the returned values. When I remove the alias from the value it works fine, but when the alias is on the value I get the error shown above, that the item cannot be found in the collection.

    Now I'm back to having to edit hundreds of files to remove aliases. I could use a global search and replace but those scare me to death. Stepping through each one will still take a great deal of time.

    Is there any other answer to this problem that anyone is aware of?

    Thanks so much for all the help already provided.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What's on line 160?

    Also, is there a persuasive reason why you're not using an inner join? Also, also, is there a REALLY persuasive reason why you're using inline dynamic sql that hasn't been prepared and will thus introduce a sql injection attack vector ultimately resulting in a rather abrupt and immediate need to freshen up your resume?

    Post what you have so far. Including the bit of the script that is actually trying to use your recordset after it has been retrieved.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Nov 2007
    Posts
    19
    Teddy,

    Thanks for your input. Let me try to answer your questions. First, I recently took over this site and am working with someone else's code. They chose to use the WHERE clause instead of "INNER JOIN ON" to create the inner join. The previous developer was working with Access, which doesn't have the capability of building stored procedures and I am merely trying to convert the existing site to use SQl Server instead of Access. The site has been having multiple problems with Access lately so I need to get this done as quickly as possible. I don't need to introduce an additional complication of stored procedures until I have made the transition to a real db. The code in the area I am working with right now is in a password protected admin area. While hackers can get past the password protection, until I get code working correctly with SQL Server I don't want to waste time changing how the variables are passed.

    Lastly, the bit of code you were asking about is here:
    <a href="resurrectUpgradeProc.asp?ID=<%=rs("urpID")%> ">

    Access required the alias and SQL Server throws an error if it used.

    Hope that helps to clarify my situation, and I would be grateful for any suggestions that would save me from having to edit hundreds of files to make them SQL Server compliant.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Loyd
    While hackers can get past the password protection, until I get code working correctly with SQL Server I don't want to waste time changing how the variables are passed.
    But surely doing it right the first time is the best way forward..?
    George
    Home | Blog

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What we're saying is SQL SERVER IS NOT THROWING THE ERROR. Please post your exact sql statement with and without alias.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Nov 2007
    Posts
    19
    Correct. SQL Server is not throwing the error. The ASP/HTML code was posted earlier except that I added column names with aliases to the SELECT statement. The error comes from the web server when I try to access the recordset values.

    As far as getting it right the first time - I just don't have time to do that. I need to get this conversion done right away and doing it the right way would take quite a bit longer to accomplish.

  14. #14
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    Quote Originally Posted by Loyd
    Query:
    sSQL = "Select * from tblTXMup AS Tt, tblTXMupPlans AS Tp " & vbCrLf
    sSQL = sSQL & " WHERE Tt.cID = Tp.cid " & vbCrLf
    sSQL = sSQL & " AND Tt.salesID=" & salesID & vbCrlf
    sSQL = sSQL & " AND cStatus = 86 " & vbCrLf
    If sChk <> "" and sPar <> "" then
    sSQL = sSQL & " AND " & sChk & " LIKE '%" & sPar & "%'" & vbCrLf
    end if
    sSQL = sSQL & "ORDER BY " & orBy

    Error Type:
    ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.

    <a href="resurrectUpgradeProc.asp?ID=<%=rs("urpID")%> ">
    Seems clear that the field your asking for isn&#180;t in that table

    1. do a line break on the DT.sql execute
    2. wait for it to return
    3. do a addwatch
    4 .see the fields that it returns and how they are returned
    5 see if your field (that you are requesting )is returned as you think it is named
    Last edited by Marvels; 12-13-07 at 11:29.

  15. #15
    Join Date
    Nov 2007
    Posts
    19
    I consider this issue closed. The error is induced by ASP referencing the recordset. This code works with Access:
    <%=rs("tt.cID")%>
    and gives the error mentioned above when used with SQL Server. To fix it I have to delete the alias so the code is this:
    <%=rs("cID")%>

    I still have no idea why this is happening but I am convinced that removing the alias is the only feasible solution.

    Thanks for everyone's help.

    BTW, Marvels, I must be dense, but I really don't understand what you were telling me. It looks like something that would be very handy in debugging SQL code. What application would you use to put a line break on DT.sql execute and do an addwatch? I'd really like to understand that approach. If you could explain that a little further for a newbie I'd appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •