Need second pair of eyes on ASP query (into Access DB)

[Jack Black]

OK, it's been a while since I've written a JOIN for Access ;) so could
someone take a look at this and tell me what's wrong with it? All columns
exist, and data types are correct...

sqlQuery = "select trip.tripname,trip.trialhead,trip.startdate from trips
trip inner join users usr on usr.username=trip.username where
usr.userticket='" & userTicket & "' and trip.enddate>#" & Date() & "# order
by trip.startdate,trip.tripname"

Getting error "Too few parameters. Expected 1"


Thanks!
Jack

[Bob Barrows]

Jack Black wrote:

> OK, it's been a while since I've written a JOIN for Access ;) so could
> someone take a look at this and tell me what's wrong with it? All
> columns exist, and data types are correct...
>
> sqlQuery = "select trip.tripname,trip.trialhead,trip.startdate from
> trips trip inner join users usr on usr.username=trip.username where
> usr.userticket='" & userTicket & "' and trip.enddate>#" & Date() & "#
> order by trip.startdate,trip.tripname"
>
> Getting error "Too few parameters. Expected 1"
>
>
> Thanks!
> Jack

Where's your "response.write sqlQuery"? How can you expect to troubleshoot a
query without looking at it? Looking at the vbscript statement that assigns
the sting of characters representing a potential query statement to a
variable is not the same thing as looking at the query itself. Do a
response.write. Then copy the resulting statement from the browser windo
into the SQL View of an Access Query Builder and attempt to run it. If you
can't see what the problem is by looking at the query, you should at least
get a better (more helpful) error message from Access than the one you get
from the Jet database engine.

One thing I can suggest is to change this:
" ... and trip.enddate>#" & Date() & "# ... "

to this:
" ... and trip.enddate> Date() ... "

The Jet engine can run some VBA functions, including the Date function, so
you don't have to concatenate the value returned from the function into the
string.

HTH,
Bob Barrows

[Jack Black]

Bob, thanks for responding...

As for outputting the string value, it looks as you would expect it to (ie,
typical Access SQL statement); that was the first thing I did when the error
first appeared. I changed the JOIN operation to a simple two-table query
and it worked perfectly, so I'm leaning toward a JOIN issue in the
supporting Access driver.

Thanks for the suggestion on the Date() method; I'll certainly try that next
time. The reason I left it the way it is was because I want to maintain a
standard style for all date-related formats in SQL queries.

Thanks,
Jack


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:%23MJZOFyeDHA.2356@TK2MSFTNGP12.phx.gbl...

>> Where's your "response.write sqlQuery"? How can you expect to

troubleshoot a

> query without looking at it? Looking at the vbscript statement that

assigns

> the sting of characters representing a potential query statement to a
> variable is not the same thing as looking at the query itself. Do a
> response.write. Then copy the resulting statement from the browser windo
> into the SQL View of an Access Query Builder and attempt to run it. If you
> can't see what the problem is by looking at the query, you should at least
> get a better (more helpful) error message from Access than the one you get
> from the Jet database engine.
>
> One thing I can suggest is to change this:
> " ... and trip.enddate>#" & Date() & "# ... "
>
> to this:
> " ... and trip.enddate> Date() ... "
>
> The Jet engine can run some VBA functions, including the Date function, so
> you don't have to concatenate the value returned from the function into

the

> string.
>
> HTH,
> Bob Barrows
>
>

[Bob Barrows]

Jack Black wrote:

> Bob, thanks for responding...
>
> As for outputting the string value, it looks as you would expect it
> to (ie, typical Access SQL statement); that was the first thing I did
> when the error first appeared. I changed the JOIN operation to a
> simple two-table query and it worked perfectly, so I'm leaning toward
> a JOIN issue in the supporting Access driver.

What? It sounds like you completely missed my point (I guess i did not
explain myself well enough). What appears in the browser window when you
change your code to this:

sqlQuery = "select trip.tripname,trip.trialhead,trip.startdate from trips
trip inner join users usr on usr.username=trip.username where
usr.userticket='" & userTicket & "' and trip.enddate>#" & Date() & "# order
by trip.startdate,trip.tripname"
response.write sqlQuery
response.end

and run the page?

If this concatenation (the creation of the sql statement) has been done
correctly, you should have a statement in the browser window that you can
copy to the clipboard and (after opening your database in Access, creating a
new query and switching to SQL View) paste into the Query Builder's SQL View
and run without modification. Tell us what happens when you try this.

We cannot be expected to troubleshoot this query without
a) knowing what the actual query is (the result of response.write)
and
b) knowing the structure of the tables involved in the query.

The "Too few Parameters" error means that your query includes a word that
the query parser cannot recognize, usually due to a misspelling of a column
name. It has nothing to do with your joins. The advantage of using Access to
troubleshoot this query will quickly become apparent to you when you try to
run it in Access and Access prompts you to provide the value for the word in
your query that the query parse does not recognize.

HTH,
Bob Barrows

[Ken Schaefer]

I second Bob's comments. We need to see what you're actually sending to the
database - not the bits your concatenating in VBScript.

That said:
[url]http://www.adopenstatic.com/faq/80040e10.asp[/url]
lists common causes of this problem, including mis-spelt column names (which
is the most common cause)

Cheers
Ken

"Jack Black" <jackblackisback@hotmail.com> wrote in message
news:uC1Erg2eDHA.556@TK2MSFTNGP11.phx.gbl...
: Bob, thanks for responding...
:
: As for outputting the string value, it looks as you would expect it to
(ie,
: typical Access SQL statement); that was the first thing I did when the
error
: first appeared. I changed the JOIN operation to a simple two-table query
: and it worked perfectly, so I'm leaning toward a JOIN issue in the
: supporting Access driver.
:
: Thanks for the suggestion on the Date() method; I'll certainly try that
next
: time. The reason I left it the way it is was because I want to maintain a
: standard style for all date-related formats in SQL queries.
:
: Thanks,
: Jack
:
:
: "Bob Barrows" <reb_01501@yahoo.com> wrote in message
: news:%23MJZOFyeDHA.2356@TK2MSFTNGP12.phx.gbl...
: >> Where's your "response.write sqlQuery"? How can you expect to
: troubleshoot a
: > query without looking at it? Looking at the vbscript statement that
: assigns
: > the sting of characters representing a potential query statement to a
: > variable is not the same thing as looking at the query itself. Do a
: > response.write. Then copy the resulting statement from the browser windo
: > into the SQL View of an Access Query Builder and attempt to run it. If
you
: > can't see what the problem is by looking at the query, you should at
least
: > get a better (more helpful) error message from Access than the one you
get
: > from the Jet database engine.
: >
: > One thing I can suggest is to change this:
: > " ... and trip.enddate>#" & Date() & "# ... "
: >
: > to this:
: > " ... and trip.enddate> Date() ... "
: >
: > The Jet engine can run some VBA functions, including the Date function,
so
: > you don't have to concatenate the value returned from the function into
: the
: > string.
: >
: > HTH,
: > Bob Barrows
: >
: >
:
: