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

Discussion in 'ASP General' started by Darren Smith, Jan 28, 2004.

  1. Darren Smith

    Darren Smith Guest

    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
    Darren Smith, Jan 28, 2004
    #1
    1. Advertising

  2. Darren Smith

    Bob Barrows Guest

    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.
    Bob Barrows, Jan 28, 2004
    #2
    1. Advertising

  3. Darren Smith

    Darren Smith Guest

    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!
    Darren Smith, Jan 28, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Parthiv Joshi
    Replies:
    1
    Views:
    659
    Samuel L Matzen
    Jul 6, 2004
  2. Hung Huynh
    Replies:
    8
    Views:
    300
    Bob Barrows
    Sep 24, 2003
  3. Aaron Bertrand - MVP

    ASP and SQL Server Recordset

    Aaron Bertrand - MVP, Jan 21, 2004, in forum: ASP General
    Replies:
    2
    Views:
    127
    Tom Kaminski [MVP]
    Jan 21, 2004
  4. AlanMF
    Replies:
    6
    Views:
    246
    AlanMF
    Nov 15, 2005
  5. CrazyAtlantaGuy
    Replies:
    2
    Views:
    134
    CrazyAtlantaGuy
    Nov 30, 2005
Loading...

Share This Page