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

01-28-2004

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_bundl e_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.

Darren

01-28-2004

Darren Smith wrote:
> 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.*

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.
>
> <%cSql="SELECT inventory_hardware.tag_id from vw_inventory_software"

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.

01-28-2004

Mr Barrows,

Thank you kindly for your informative and prompt response. All issues
have now been resolved.

Best Regards,

Darren

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Problems accessing data from recordset (SQL Server 2000)	CrazyAtlantaGuy	ASP General	2	11-30-2005 03:54 PM
ASP recordset retrieval problem (SQL Server)	AlanMF	ASP General	6	11-15-2005 08:12 PM
Asp to Aspx, Response.Write Recordset.fields(1).value question	Gian Paolo Clarici	ASP .Net	0	07-21-2004 12:14 PM
ASP and SQL Server Recordset	Erik	ASP General	2	01-21-2004 08:20 PM
RecordSet.Move or RecordSet.AbsolutePosition??	Hung Huynh	ASP General	8	09-24-2003 11:07 AM