Unspecified error when adding a link

Discussion in 'ASP General' started by Mark, Jun 2, 2004.

  1. Mark

    Mark Guest

    Hi - using asp and ms Access - this works ok:

    set Products = Server.CreateObject("ADODB.Recordset")
    Products.ActiveConnection = myConString
    Products.Source = "SELECT Products.productid, Products.product,
    Products.price, Products.partno FROM Products"
    Products.CursorType = 0
    Products.CursorLocation = 2
    Products.LockType = 3
    Products.Open()

    ...but when I try to add a connection to another table I get the dreaded
    unspecified error:

    (same code)
    Products.Source = "SELECT Products.productid, Products.product,
    Products.price, Products.partno, Sizes.ProductSize AS Size FROM Products
    INNER JOIN (Sizes ON Products.SizeID = Sizes.SizeID) "
    (same code)

    Any ideas why this may be happening? I have latest MDAC, XP Pro (IIS
    running locally), and no memo fields!

    Thanks,

    Mark


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark, Jun 2, 2004
    #1
    1. Advertising

  2. Access is very fussy about the placement of parentheses. Try designing the
    query inside of Access' GUI, and see if it looks any different. Or, you
    could try the non-ANSI way, e.g.

    SELECT p.productid, ..., s.ProductSize
    FROM products AS p, Sizes AS s
    WHERE p.SizeID = s.SizeID

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Mark" <> wrote in message
    news:#...
    >
    >
    > Hi - using asp and ms Access - this works ok:
    >
    > set Products = Server.CreateObject("ADODB.Recordset")
    > Products.ActiveConnection = myConString
    > Products.Source = "SELECT Products.productid, Products.product,
    > Products.price, Products.partno FROM Products"
    > Products.CursorType = 0
    > Products.CursorLocation = 2
    > Products.LockType = 3
    > Products.Open()
    >
    > ..but when I try to add a connection to another table I get the dreaded
    > unspecified error:
    >
    > (same code)
    > Products.Source = "SELECT Products.productid, Products.product,
    > Products.price, Products.partno, Sizes.ProductSize AS Size FROM Products
    > INNER JOIN (Sizes ON Products.SizeID = Sizes.SizeID) "
    > (same code)
    >
    > Any ideas why this may be happening? I have latest MDAC, XP Pro (IIS
    > running locally), and no memo fields!
    >
    > Thanks,
    >
    > Mark
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    Aaron [SQL Server MVP], Jun 2, 2004
    #2
    1. Advertising

  3. Mark

    Mark Guest

    Hi Aaron - thank you (I checked your site before posting - but did not
    think to rewrite the query in the way you have shown) - I do not have
    Access, just an Access database file - which is why I could not test
    this in it's gui.

    Thank you for your help,

    Mark


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark, Jun 2, 2004
    #3
  4. Mark wrote:
    > Products.Source = "SELECT Products.productid, Products.product,
    > Products.price, Products.partno, Sizes.ProductSize AS Size FROM
    > Products INNER JOIN (Sizes ON Products.SizeID = Sizes.SizeID) "


    Why the parentheses? You only have two tables.

    Products INNER JOIN Sizes ON Products.SizeID = Sizes.SizeID "


    You should always build and test your queries in your database's native
    query tool before trying to run them in asp. With Access, that means using
    the Access Query Builder to create and test your queries.

    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jun 2, 2004
    #4
  5. I agree. I'm to the point where I build any complex query in Access and
    then copy & past it over. Keep in mind that purchasing MSAccess is
    actually to your advantage as it will allow you direct access to the
    objects if you need to make a change, look up a field name or field data
    type. Then, of course, there are the benefits of having Access in the
    event that the database ends up corrupted.

    David H

    I also use the VBEditor in Access to test & debug any VBScript function
    that I'm working on.

    Bob Barrows [MVP] wrote:

    > Mark wrote:
    >
    >>Products.Source = "SELECT Products.productid, Products.product,
    >>Products.price, Products.partno, Sizes.ProductSize AS Size FROM
    >>Products INNER JOIN (Sizes ON Products.SizeID = Sizes.SizeID) "

    >
    >
    > Why the parentheses? You only have two tables.
    >
    > Products INNER JOIN Sizes ON Products.SizeID = Sizes.SizeID "
    >
    >
    > You should always build and test your queries in your database's native
    > query tool before trying to run them in asp. With Access, that means using
    > the Access Query Builder to create and test your queries.
    >
    > HTH,
    > Bob Barrows
    >
    David C. Holley, Jun 3, 2004
    #5
  6. David C. Holley wrote:
    > I agree. I'm to the point where I build any complex query in Access
    > and then copy & past it over.


    Now you're ready for the next step: make a clean break from using dynamic
    sql and execute your saved queries instead. :)

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jun 3, 2004
    #6
  7. Mark

    Mark Guest

    Hi - thanks for all of your comments, all of which I take on board
    (setting up queries in a designer - means getting Access, and running
    queries as sort of SPs from within Access).

    If you're interested, it turns out that Access didn't like the "AS
    Sizes" part of the query. Don't know if it's a reserved word - but by
    changing to "AS [Sizes]" it now works without any errors.

    Again, thanks, Mark



    *** Sent via Devdex http://www.devdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark, Jun 3, 2004
    #7
  8. Mark

    Roland Hall Guest

    "Mark" wrote in message news:%...
    : Hi - thanks for all of your comments, all of which I take on board
    : (setting up queries in a designer - means getting Access, and running
    : queries as sort of SPs from within Access).
    :
    : If you're interested, it turns out that Access didn't like the "AS
    : Sizes" part of the query. Don't know if it's a reserved word - but by
    : changing to "AS [Sizes]" it now works without any errors.

    Rather that purchasing Access, if you can, consider using SQL. You can get
    MSDE for nothing.
    http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1

    --
    Roland Hall
    /* This information is distributed in the hope that it will be useful, but
    without any warranty; without even the implied warranty of merchantability
    or fitness for a particular purpose. */
    Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
    WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
    MSDN Library - http://msdn.microsoft.com/library/default.asp
    Roland Hall, Jun 3, 2004
    #8
  9. Mark wrote:
    > Hi - thanks for all of your comments, all of which I take on board
    > (setting up queries in a designer - means getting Access, and running
    > queries as sort of SPs from within Access).
    >
    > If you're interested, it turns out that Access didn't like the "AS
    > Sizes" part of the query. Don't know if it's a reserved word - but by
    > changing to "AS [Sizes]" it now works without any errors.
    >

    Ah! I missed that! "Size" is a reserved ODBC keyword
    (http://www.aspfaq.com/show.asp?id=2080).

    It would have been easier to spot that if you had been able to tell us that
    the query ran fine in the Access Query Builder but failed when run by ADO
    .... ;-)

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jun 3, 2004
    #9
  10. Mark

    Mark Guest

    Hi Roland - yes I know, thank you. I already have the full SQL Server
    through MSDN (which didn't include Access) - trouble is with this
    project, the client is insisting it uses Access. I tried to push them
    down the sql route, but to no avail.

    And unfortunately the way that Sql Server builds its queries isn't
    always backwards compatile with what Access wants (parenthesis was
    mentioned above - I know there are changes there) - so using that gui
    isn't always an option either - even for testing.

    Thanks for the input either way,

    Mark



    *** Sent via Devdex http://www.devdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mark, Jun 3, 2004
    #10
    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. RM
    Replies:
    3
    Views:
    599
    Yan-Hong Huang[MSFT]
    Aug 19, 2003
  2. Kevin Spencer

    Re: Link Link Link DANGER WILL ROBINSON!!!

    Kevin Spencer, May 17, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    795
    Kevin Spencer
    May 17, 2005
  3. Gatsu
    Replies:
    2
    Views:
    314
    Gatsu
    Sep 5, 2008
  4. abcd
    Replies:
    0
    Views:
    154
  5. abcd
    Replies:
    0
    Views:
    119
Loading...

Share This Page