Unspecified error when adding a link

[Mark]

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

set Products = Server.CreateOb ject("ADODB.Rec ordset")
Products.Active Connection = myConString
Products.Source = "SELECT Products.produc tid, Products.produc t,
Products.price, Products.partno FROM Products"
Products.Cursor Type = 0
Products.Cursor Location = 2
Products.LockTy pe = 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.produc tid, Products.produc t,
Products.price, Products.partno , Sizes.ProductSi ze 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]]

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" <an*******@devd ex.com> wrote in message
news:#U******** ******@tk2msftn gp13.phx.gbl...

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

set Products = Server.CreateOb ject("ADODB.Rec ordset")
Products.Active Connection = myConString
Products.Source = "SELECT Products.produc tid, Products.produc t,
Products.price, Products.partno FROM Products"
Products.Cursor Type = 0
Products.Cursor Location = 2
Products.LockTy pe = 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.produc tid, Products.produc t,
Products.price, Products.partno , Sizes.ProductSi ze 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]

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!

[Bob Barrows [MVP]]

Mark wrote:

Products.Source = "SELECT Products.produc tid, Products.produc t,
Products.price, Products.partno , Sizes.ProductSi ze 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"

[David C. Holley]

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.Sour ce = "SELECT Products.produc tid, Products.produc t,
Products.pric e, Products.partno , Sizes.ProductSi ze 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

[Bob Barrows [MVP]]

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"

[Mark]

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!

[Roland Hall]

"Mark" wrote in message news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..
: 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

[Bob Barrows [MVP]]

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"

[Mark]

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!