Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Problem accessing individual recordset fields in a Sql Server 7 view via ASP

Reply
Thread Tools

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

 
 
Darren Smith
Guest
Posts: n/a
 
      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
 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      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.


 
Reply With Quote
 
 
 
 
Darren Smith
Guest
Posts: n/a
 
      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!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


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 Aaron Bertrand - MVP 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



Advertisments