Does aliased table cause a problem?

Question

Does aliased table cause a problem?

loyd

Valued Member

Points: 62
More actions
November 15, 2007 at 3:29 pm

#176843

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,

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 1

I'm thinking that if you want us to fix your car, ya gotta let us look under the hood... post both versions of the code and the precise error message.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Giri Duddu SSCommitted Points: 1638 More actions · Answer 2

just put the actual querry to have better idea...........

loyd Valued Member Points: 62 More actions · Answer 3

Thank you very much for looking at this.

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,

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

At first glance i dont think giving a alias name will casue any issues ( i may be wrong).

Is the value u r passing in "sChk " a valid column name

existing in either of the 2 tables.

"Keep Trying"

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 5

Maybe I'm going blind... but I don't see anything obviously wrong in your query code... heh, well except for the fact that it's embedded SQL 😉

With that in mind, you say removing the aliases "fix" the problem... have you considered the size of sSQL? Is it possible that with the aliases, it's too big for the query text and when you remove an alias or two, that it suddenly fits?

Dunno how big sSQL is, but try making it larger and running the test with the aliases in the code, again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

loyd Valued Member Points: 62 More actions · Answer 6

First to Chirag, yes, sChk is a radio button selection and all selection values are valid column names.

Next to Jeff, I posted the entire sSQL statement, so you can see that it is not very large. I have seen much larger SQL statements than this one. Nevertheless I did as you suggested and copied two lines and pasted them back in. Same error with the aliased line of code.

Any other ideas other than editing hundreds of queries?