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

Darren Smith · Jan 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_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.

Darren

Bob Barrows · Jan 28, 2004

Darren said:
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

Darren Smith · Jan 28, 2004

Mr Barrows,

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

Best Regards,

Darren

Problem in Returning Recordset in ASP	1	Apr 14, 2007
ASP recordset retrieval problem (SQL Server)	6	Nov 14, 2005
Problems accessing data from recordset (SQL Server 2000)	2	Nov 30, 2005
Using IIS ASP to display a blob field in SQL Server 2005	4	Aug 30, 2010
Problem in Returning Recordset in ASP	1	Apr 12, 2007
ASP linking to SQL Server using ODBC - problem	3	Oct 23, 2003
ASP / SQL Problem	8	Feb 2, 2006
Asp script connected to sql server gives error	6	Dec 20, 2005

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

Darren Smith

Bob Barrows

Darren Smith

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads