JET error '80040e10' from Access 2000 query

[Noral Kuhlmann]

I wrote this query in Access 2000 the query has no parameters, however
when I run it on IIS 5.0 it flakes out. Here is the code:

07 Set Postings = Server.CreateObject("ADODB.Recordset")
08 Postings.ActiveConnection = MM_Jobs_STRING
09 Postings.Source = "SELECT Positions.Title, Location.Location,
Postings.ID FROM (((Postings LEFT JOIN Positions ON
Postings.ID_Position = Positions.ID) LEFT JOIN Location ON
Postings.ID_Location = Location.ID) LEFT JOIN Location AS ExamLocation
ON Postings.ID_Exam_Location = ExamLocation.ID)"
10 Postings.CursorType = 0
11 Postings.CursorLocation = 2
12 Postings.LockType = 1
13 Postings.Open()

Here is the error message:
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/Careers/Job_Listings/Index-new.asp, line 13

BTW - The query runs in access and dreamweaver.

TIA

[Aaron Bertrand - MVP]

What's with all the parens? And why do you need to LEFT JOIN to itself?
How about:

set conn = CreateObject("ADODB.Connection")
conn.open MM_Jobs_String
sql = "SELECT Positions.Title, Location.Location, Postings.ID " & _
" FROM Postings " & _
" LEFT JOIN Positions ON Postings.ID_Position = Positions.ID " & _
" LEFT JOIN Location ON Postings.ID_Location = Location.ID
AND Postings.ID_Exam_Location = Location.ExamLocationID"
set rs = conn.execute(sql)




"Noral Kuhlmann" <noral@creative-networking.com> wrote in message
news:f3284be1.0311171534.1e02fb26@posting.google.c om...

> I wrote this query in Access 2000 the query has no parameters, however
> when I run it on IIS 5.0 it flakes out. Here is the code:
>
> 07 Set Postings = Server.CreateObject("ADODB.Recordset")
> 08 Postings.ActiveConnection = MM_Jobs_STRING
> 09 Postings.Source = "SELECT Positions.Title, Location.Location,
> Postings.ID FROM (((Postings LEFT JOIN Positions ON
> Postings.ID_Position = Positions.ID) LEFT JOIN Location ON
> Postings.ID_Location = Location.ID) LEFT JOIN Location AS ExamLocation
> ON Postings.ID_Exam_Location = ExamLocation.ID)"
> 10 Postings.CursorType = 0
> 11 Postings.CursorLocation = 2
> 12 Postings.LockType = 1
> 13 Postings.Open()
>
> Here is the error message:
> Microsoft JET Database Engine error '80040e10'
>
> No value given for one or more required parameters.
>
> /Careers/Job_Listings/Index-new.asp, line 13
>
> BTW - The query runs in access and dreamweaver.
>
> TIA

[Bob Barrows]

Aaron Bertrand - MVP wrote:

> What's with all the parens?

I know - ugly.

However, Jet requires them. Unfortunately.

> "Noral Kuhlmann" <noral@creative-networking.com> wrote in message
> news:f3284be1.0311171534.1e02fb26@posting.google.c om...

>> I wrote this query in Access 2000 the query has no parameters,
>> however when I run it on IIS 5.0 it flakes out. Here is the code:
>>
>> 07 Set Postings = Server.CreateObject("ADODB.Recordset")
>> 08 Postings.ActiveConnection = MM_Jobs_STRING
>> 09 Postings.Source = "SELECT Positions.Title, Location.Location,
>> Postings.ID FROM (((Postings LEFT JOIN Positions ON
>> Postings.ID_Position = Positions.ID) LEFT JOIN Location ON
>> Postings.ID_Location = Location.ID) LEFT JOIN Location AS
>> ExamLocation ON Postings.ID_Exam_Location = ExamLocation.ID)"
>> 10 Postings.CursorType = 0
>> 11 Postings.CursorLocation = 2
>> 12 Postings.LockType = 1
>> 13 Postings.Open()
>>
>> Here is the error message:
>> Microsoft JET Database Engine error '80040e10'
>>
>> No value given for one or more required parameters.
>>
>> /Careers/Job_Listings/Index-new.asp, line 13
>>
>> BTW - The query runs in access and dreamweaver.
>>
>> TIA

I'm assuming you've tried to run this query with the Access Query Builder
.... ?

Do a

Response.Write Postings.Source

to get the actual query being sent to Access. Copy it from the browser
window and paste it into the SQL View of the Access Query Builder and verify
that you can run it as-is

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"

[Noral Kuhlmann]

Thanks for the help, but the suggestion you made did not solve the
problem. I use the parens to simplify readability. Here is a little
more background. I have one table that has ID lookup on two other
tables and I need columns from these other tables. Using Left Joins
is the only way I know how.

TIA

Noral

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message news:<e2WwAQWrDHA.2556@TK2MSFTNGP09.phx.gbl>...

> What's with all the parens? And why do you need to LEFT JOIN to itself?
> How about:
>
> set conn = CreateObject("ADODB.Connection")
> conn.open MM_Jobs_String
> sql = "SELECT Positions.Title, Location.Location, Postings.ID " & _
> " FROM Postings " & _
> " LEFT JOIN Positions ON Postings.ID_Position = Positions.ID " & _
> " LEFT JOIN Location ON Postings.ID_Location = Location.ID
> AND Postings.ID_Exam_Location = Location.ExamLocationID"
> set rs = conn.execute(sql)
>
>
>
>
> "Noral Kuhlmann" <noral@creative-networking.com> wrote in message
> news:f3284be1.0311171534.1e02fb26@posting.google.c om...

> > I wrote this query in Access 2000 the query has no parameters, however
> > when I run it on IIS 5.0 it flakes out. Here is the code:
> >
> > 07 Set Postings = Server.CreateObject("ADODB.Recordset")
> > 08 Postings.ActiveConnection = MM_Jobs_STRING
> > 09 Postings.Source = "SELECT Positions.Title, Location.Location,
> > Postings.ID FROM (((Postings LEFT JOIN Positions ON
> > Postings.ID_Position = Positions.ID) LEFT JOIN Location ON
> > Postings.ID_Location = Location.ID) LEFT JOIN Location AS ExamLocation
> > ON Postings.ID_Exam_Location = ExamLocation.ID)"
> > 10 Postings.CursorType = 0
> > 11 Postings.CursorLocation = 2
> > 12 Postings.LockType = 1
> > 13 Postings.Open()
> >
> > Here is the error message:
> > Microsoft JET Database Engine error '80040e10'
> >
> > No value given for one or more required parameters.
> >
> > /Careers/Job_Listings/Index-new.asp, line 13
> >
> > BTW - The query runs in access and dreamweaver.
> >
> > TIA

[Aaron Bertrand - MVP]

> Thanks for the help, but the suggestion you made did not solve the

> problem.

You are still getting "No value given for one or more required parameters."?
Can you show your exact code, the table structure, some sample data, and
desired results?

> tables and I need columns from these other tables. Using Left Joins
> is the only way I know how.

I think an INNER JOIN would make more sense.