Expected query name after EXECUTE
For some reason, I get the following error message when running the script below:
<?php
dim db, cmd, strjob
strjob = "programmer"
set db = server.createobject("adodb.connection")
db.mode = admodereadwrite
db.connectionstring = "provider=microsoft.jet.oledb.4.0;data source=" & _
server.mappath("data.mdb")
db.open
set cmd = server.createobject("adodb.command")
set cmd.activeconnection = db
cmd.commandtext = "{CALL query2 ('" & strjob & "')}"
cmd.commandtype = adcmdstoredproc
set rs = server.createobject("adodb.recordset")
set rs = cmd.execute
rs.movefirst
do while not rs.eof
response.write rs("name") & " " & rs("job")
rs.movenext
loop
set db = nothing
set cmd = nothing
set rs = nothing
?>I'm using an 2000 Access database with the following stored procedure:
SELECT [Table1].[ID], [Table1].[name], [Table1].[job]
FROM Table1
WHERE ((([Table1].[job])=[Class]));
I've tried other ways, but still continue to get this error message.
I was wondering if someone knows what I'm doing wrong.
Thanks...
Mark Hensler posted this at 08:37 — 2nd August 2003.
He has: 4,048 posts
Joined: Aug 2000
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.
Mark Hensler
If there is no answer on Google, then there is no question.
jlot6 posted this at 17:40 — 2nd August 2003.
He has: 9 posts
Joined: Oct 2001
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 posted this at 21:36 — 2nd August 2003.
He has: 4,048 posts
Joined: Aug 2000
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?
Mark Hensler
If there is no answer on Google, then there is no question.
jlot6 posted this at 22:01 — 2nd August 2003.
He has: 9 posts
Joined: Oct 2001
Yes. Procedures like:
SELECT [Table1].[ID], [Table1].[name], [Table1].[email], [Table2].[ID], [Table2].[job]
FROM Table1 INNER JOIN Table2 ON [Table1].[ID]=[Table2].[ID];
or
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 posted this at 22:31 — 2nd August 2003.
He has: 4,048 posts
Joined: Aug 2000
I'm sorry I don't have a better solution. But my experience with these is simply NULL.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.