Mark Hensler — Sat, 02 Aug 2003 22:31:26 +0000

I'm sorry I don't have a better solution. But my experience with these is simply NULL.

jlot6 — Sat, 02 Aug 2003 22:01:54 +0000

Yes. Procedures like:

SELECT [Table1].[ID], [Table1].[name], [Table1].[email], [Table2].[ID], [Table2].[job]
FROM Table1 INNER JOIN Table2 ON [Table1].[ID]=[Table2].[ID];

SELECT [Table1].[ID], [Table1].[name], [Table1].[email]
FROM Table1
WHERE ((([Table1].[name])="andrew schools"));

But when I try an stored procedure that tries to pass a parameter:

SELECT Table1.ID, Table1.name, Table1.email, Table2.ID, Table2.job
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE (((Table1.ID)=[table2].[id]) AND ((Table2.job)=[strjob]));

I get the following error:

Microsoft JET Database Engine (0x80040E14)
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

I don't know if this is because ACCESS doesn't support passing parameters through a stored procedure?

If I open access and open the query, it prompts me to give the paramter a value and then executes like it should. So I don't know why it wouldn't work in the ASP page?

I've looked else where and found nothing. I guess for now I will have to just enter the SQL Code directly into the ASP page.

Thanks...

Mark Hensler — Sat, 02 Aug 2003 21:36:47 +0000

I talked to a friend who's somewhat familiar with stored procs (in MS SQL), and he wasn't sure if Access 2000 supported them.

Can you get a stored proc to work without passing a parameter?

jlot6 — Sat, 02 Aug 2003 17:40:19 +0000

If I use a recordset object without using a stored procedure it works.

If I try to use a stored procedure, it gives me the following error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

While using the following stored procedure:

SELECT [Table1].[ID], [Table1].[name], [Table1].[email], [Table2].[ID], [Table2].[job]
FROM Table1 INNER JOIN Table2 ON [Table1].[ID]=[Table2].[ID]
WHERE ((([Table1].[ID])=[strjob]))
ORDER BY [Table1].[name];

However, if I use this as an SQL statment in my ASP code:

"SELECT table1.id, table1.name, table1.email, table2.id, table2.job FROM table1 INNER JOIN table2 ON table1.id = table2.id where table2.id = '" & strjob & "' ORDER BY table1.name ASC"

it works? Does it have to do with ACCESS 2000? Mabey it doesn't support passing parameter values through a stored procedure?

Mark Hensler — Sat, 02 Aug 2003 08:37:18 +0000

It's been a while since I've seen any ASP code. Even longer since I've written any. And even then, I never used the ADODB.Command object. But after consulting my limited printed library on ASP (read: one book), you're code looks fine.

So, only suggestions are these:
- Try performing the same task without using a stored proc.
- Try re-writing without using the ADODB.Command object.