Inner Join problem

Hello

I have windows 2000 professional, MS Access 2002 installed
on my system. When I execute a simple query from an asp
page it works well but when I execute a query with inner
join, it displays:
Error Type:
(0x80004005)
Unspecified error

Can some one help to resolve this problem.

Regards

[Bob Barrows]

[email]sajid622@hotmail.com[/email] wrote:

> Hello
>
> I have windows 2000 professional, MS Access 2002 installed
> on my system. When I execute a simple query from an asp
> page it works well but when I execute a query with inner
> join, it displays:
> Error Type:
> (0x80004005)
> Unspecified error
>
> Can some one help to resolve this problem.
>

Not until you give us more info.
Are you executing a saved query (recommended) or using dynamic sql to build
the query?

If you are using dynamic sql, the best troubleshooting tool you can use is
response.write. Use it like this:

dim strSQL
strSQL = " ... table1 inner join table 2 ..."
response.write strSQL
response.end

Run the page and look at the statement written to the browser window. Is it
a statement that can be run as-is in Access? To find out:

Open your database in Access.
Go to the Queries tab and create a new query in Design View.
Close the Choose Table dialog without selecting a table.
Switch to SQL View using the toolbar button, or the right-click menu, or the
View menu
Copy and paste the statement from the browser window into the SQL View
window
Try to run it

If that does not show you what your problem was, then show us the sql
statement that you pasted into SQL View and show us the code used to build
it.

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"

[Sajid]

>Are you executing a saved query (recommended) or using

dynamic sql to build

>the query?

Thank you for your help. Here are the details.
When I use dynamic sql it doesnt work. But when I save the
same query, and execute from asp it works fine.

This problem occurs with a query having more than one
inner joins. My query looks like this:

SELECT Stock.idStock, Stock.quantity, Stock.weight,
Stock.TradePrice, Stock.RetailPrice, ProductSizes.size,
ProductColors.Color, Stock.idProduct, ProductSizes.idSize,
ProductColors.idColor
FROM Stock, ProductColors, ProductSizes
WHERE (((ProductSizes.idSize)=[Stock].[idSize]) AND
((ProductColors.idColor)=[Stock].[idColor]))

Regards

Sajid

[Ken Schaefer]

Can you run that query inside Access? Does it work there?

Cheers
Ken


"Sajid" <sajid622@hotmail.com> wrote in message
news:05e601c3bfa7$b2bbed90$a501280a@phx.gbl...
:
: >Are you executing a saved query (recommended) or using
: dynamic sql to build
: >the query?
:
: Thank you for your help. Here are the details.
: When I use dynamic sql it doesnt work. But when I save the
: same query, and execute from asp it works fine.
:
: This problem occurs with a query having more than one
: inner joins. My query looks like this:
:
: SELECT Stock.idStock, Stock.quantity, Stock.weight,
: Stock.TradePrice, Stock.RetailPrice, ProductSizes.size,
: ProductColors.Color, Stock.idProduct, ProductSizes.idSize,
: ProductColors.idColor
: FROM Stock, ProductColors, ProductSizes
: WHERE (((ProductSizes.idSize)=[Stock].[idSize]) AND
: ((ProductColors.idColor)=[Stock].[idColor]))
:
: Regards
:
: Sajid

[Bob Barrows]

Sajid wrote:

>> Are you executing a saved query (recommended) or using dynamic sql
>> to build the query?

>
> Thank you for your help. Here are the details.
> When I use dynamic sql it doesnt work.

Still not enough information. What does "doesnt work" mean? error messages?
incorrect results? If you're getting an error message, show it to us.

> But when I save the
> same query, and execute from asp it works fine.

So why not continue to execute it as a saved query? That is the most
efficient way to execute a query. If you need to use dynamic search
criteria, use parameters. If you do a Google search on this newsgroup for
"saved parameter query", you will see several examples of using them.

>
> This problem occurs with a query having more than one
> inner joins. My query looks like this:
>
> SELECT Stock.idStock, Stock.quantity, Stock.weight,
> Stock.TradePrice, Stock.RetailPrice, ProductSizes.size,
> ProductColors.Color, Stock.idProduct, ProductSizes.idSize,
> ProductColors.idColor
> FROM Stock, ProductColors, ProductSizes
> WHERE (((ProductSizes.idSize)=[Stock].[idSize]) AND
> ((ProductColors.idColor)=[Stock].[idColor]))
>

Usually, if a query can be run successfully by your database's query tool,
but not via ADO, this indicates the presence of at least one reserved
keyword in the query. I checked here:
[url]http://www.aspfaq.com/show.asp?id=2080[/url], and sure enough, one of your fields
is named "Size", which appears on the list of reserved keywords which should
be avoided when naming fields and tables in your database.

My recommendation is to change the name of that field to a non-reserved
word. If you can't do that, then you will need to remember to wrap it with
brackets whenever using it in a query executed via ADO: [size]. The query
parser will ignore strings placed inside brackets.

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"

[McKirahan]

"Sajid" <sajid622@hotmail.com> wrote in message
news:05e601c3bfa7$b2bbed90$a501280a@phx.gbl...

>

> >Are you executing a saved query (recommended) or using

> dynamic sql to build

> >the query?

>
> Thank you for your help. Here are the details.
> When I use dynamic sql it doesnt work. But when I save the
> same query, and execute from asp it works fine.
>
> This problem occurs with a query having more than one
> inner joins. My query looks like this:
>
> SELECT Stock.idStock, Stock.quantity, Stock.weight,
> Stock.TradePrice, Stock.RetailPrice, ProductSizes.size,
> ProductColors.Color, Stock.idProduct, ProductSizes.idSize,
> ProductColors.idColor
> FROM Stock, ProductColors, ProductSizes
> WHERE (((ProductSizes.idSize)=[Stock].[idSize]) AND
> ((ProductColors.idColor)=[Stock].[idColor]))
>
> Regards
>
> Sajid

I may be wrong (it happens often) but wouldn't an INNER JOIN look like:

SELECT
Stock.idStock,
Stock.quantity,
Stock.weight,
Stock.TradePrice,
Stock.RetailPrice,
ProductSizes.size,
ProductColors.Color,
Stock.idProduct,
ProductSizes.idSize,
ProductColors.idColor
FROM (Stock
INNER JOIN ProductColors ON Stock.idSize = ProductSizes.idSize)
INNER JOIN ProductSizes ON Stock.idColor = ProductColors.idColor
)

Watch for word-wrap. Also, enclose reserved words in brackets.

[McKirahan]

Please ignore the final ")" in my previous post.

"McKirahan" <News@McKirahan.com> wrote in message
news:Cu1Cb.21699$8y1.102215@attbi_s52...

> "Sajid" <sajid622@hotmail.com> wrote in message
> news:05e601c3bfa7$b2bbed90$a501280a@phx.gbl...

> >

> > >Are you executing a saved query (recommended) or using

> > dynamic sql to build

> > >the query?

> >
> > Thank you for your help. Here are the details.
> > When I use dynamic sql it doesnt work. But when I save the
> > same query, and execute from asp it works fine.
> >
> > This problem occurs with a query having more than one
> > inner joins. My query looks like this:
> >
> > SELECT Stock.idStock, Stock.quantity, Stock.weight,
> > Stock.TradePrice, Stock.RetailPrice, ProductSizes.size,
> > ProductColors.Color, Stock.idProduct, ProductSizes.idSize,
> > ProductColors.idColor
> > FROM Stock, ProductColors, ProductSizes
> > WHERE (((ProductSizes.idSize)=[Stock].[idSize]) AND
> > ((ProductColors.idColor)=[Stock].[idColor]))
> >
> > Regards
> >
> > Sajid

>
>
> I may be wrong (it happens often) but wouldn't an INNER JOIN look like:
>
> SELECT
> Stock.idStock,
> Stock.quantity,
> Stock.weight,
> Stock.TradePrice,
> Stock.RetailPrice,
> ProductSizes.size,
> ProductColors.Color,
> Stock.idProduct,
> ProductSizes.idSize,
> ProductColors.idColor
> FROM (Stock
> INNER JOIN ProductColors ON Stock.idSize = ProductSizes.idSize)
> INNER JOIN ProductSizes ON Stock.idColor = ProductColors.idColor
> )
>
> Watch for word-wrap. Also, enclose reserved words in brackets.
>
>

[Bob Barrows]

McKirahan wrote:

> "Sajid" <sajid622@hotmail.com> wrote in message
> news:05e601c3bfa7$b2bbed90$a501280a@phx.gbl...

>>

> I may be wrong (it happens often) but wouldn't an INNER JOIN look
> like:

>> FROM (Stock

> INNER JOIN ProductColors ON Stock.idSize = ProductSizes.idSize)
> INNER JOIN ProductSizes ON Stock.idColor = ProductColors.idColor
> )
>

This is the (newer) ANSI-style join syntax. The older style syntax (putting
the join conditions in the WHERE clause) still works but is not recommended.
And yes, it was called an "inner join" in the older syntax.

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.