sql server odbc: Problem accessing individual recordset fields in a Sql Server 7 view via ASP -- sql server odbc

Problem accessing individual recordset fields in a Sql Server 7 view via ASP

darren.smith NO[at]SPAM ca.trader.com
1/28/2004 10:50:26 AM

sql server odbc: I am having a great deal of difficulty accessing individual fields
generated from a Sql Server 7 view.

When I specify the actual field name, I get the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'inventory_hardware' does not match with a table name or alias name
used in the query.

The Sql Server 7 view is as follows:

CREATE VIEW [vw_inventory_hardware]
AS SELECT
[inventory_hardware].[tag_id],sw_bundle.*, inventory_software.*
FROM [inventory_hardware]
inner join sw_bundle on
inventory_hardware.sw_bundle_id=sw_bundle.sw_bundle_id
Inner Join inventory_software on
sw_bundle.sw_add1=inventory_software.inv_id or
sw_bundle.sw_add2=inventory_software.inv_id or
sw_bundle.sw_add3=inventory_software.inv_id or
sw_bundle.sw_add4=inventory_software.inv_id or
sw_bundle.sw_add5=inventory_software.inv_id or
sw_bundle.sw_add6=inventory_software.inv_id or
sw_bundle.sw_add7=inventory_software.inv_id or
sw_bundle.sw_add8=inventory_software.inv_id or
sw_bundle.sw_add9=inventory_software.inv_id or
sw_bundle.sw_add10=inventory_software.inv_id

The code from my ASP page is as follows:
<%@ Language=VBScript
Option Explicit
response.buffer=true%>
<%Dim cSql,rs,cConn,conn
cConn="dsn=TestInventory2004;UID=sa;pwd=;"
set conn = server.createobject("ADODB.connection")
conn.open cConn%>

<html>
<head></head>
<body>
<%cSql="SELECT inventory_hardware.tag_id from vw_inventory_software"
set rs=conn.execute(cSql)%>

</body>
</html>

Thanks in advance for any advise you can offer.

Re: Problem accessing individual recordset fields in a Sql Server 7 view via ASP

Bob Barrows
1/28/2004 2:33:26 PM

[quoted text, click to view]

This is the first part of your problem right here: using *. To see the
problem: can you tell me what names are being given the two fields that are
both named "sw_bundle_id"? Always provide column aliases so the two fields
in the resultset can be distinguished. This means of course, that you will
need to explicitly specify all the fields you want the view to return. This
will have the added benefit that you will be able to reduce the total number
of columns returned by the view: there is no need to return two columns both
containing the same data is there? And, of course, if you put only one of
the two sw_bundle_id columns in the SELECT list, there will be no need to
use a column alias.
[quoted text, click to view]

And here is the second part: Table aliases used in the SELECT list can only
be used if they are defined in the FROM clause. The only data source this
query knows about is vw_inventory_software. It knows nothing about the
tables used to create the view.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.