Hi all,

I'm having a strange problem! It's strange cause it doen't happend 
everytime.

When I try to execute a parameter query in my access databese i got the  
message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; 
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

It happends in this code:
Set objConn = Server.CreateObject("ADODB.Connection")
ObjConn.Open Application("ConnectString")
		
strSQL = "qryVerificaAcesso " & CStr(Request.Form("Username")) & ", " & 
CStr(Request.Form("Password"))
Set objRS = objConn.Execute (strSQL)

The query qryVerificaAcesso is:
PARAMETERS NomeAcesso Text ( 255 ), CodigoAcesso Text ( 255 );
SELECT tblUtilizador.IDUtilizador, tblUtilizador.Nome, 
tblUtilizador.Administrador, tblUtilizador.UltimoAcesso, 
tblUtilizador.Verificado, tblUtilizador.EMail
FROM tblUtilizador
WHERE (((tblUtilizador.Verificado)=True) AND ((tblUtilizador.NomeAcesso)
[NomeAcesso]) AND ((tblUtilizador.CodigoAcesso)=[CodigoAcesso]));

But it works about 19 times in 20 page opens! I'm not understanding why 
it happends, if it works once it should work every time, and if it works 
19 times and fails one...

Maybe someone can help me!

My system is:
Windows XP (.Net framework instaled)
The page is regular ASP for a site that has about 50 visitors a day 
hosted in my computer.

Thanks in advance.
Vítor

a) It may be possible that sometimes the variables you are using to send
values for the parameters are "empty" (ie they have no value).

b) Why don't you do it this way:

<%
objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly,
adCmdStoredProc
%>

so that you are explicity telling ADO that you are using a parametised
query. If a parameter has no value, you will get an appropriate error
message. At the moment, ADO has to *guess* what you are trying to do, and (I
think) because a parameter has no value, it is thinking you are sending an
SQL statement to the database.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Vítor Marinho" <vitor.marinho@n...>
To: "Access ASP" <access_asp@p...>
Sent: Wednesday, July 10, 2002 7:17 PM
Subject: [access_asp] Strange error PARAMETER QUERY


: Hi all,
:
: I'm having a strange problem! It's strange cause it doen't happend
: everytime.
:
: When I try to execute a parameter query in my access databese i got the
: message:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
: expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
:
: It happends in this code:
: Set objConn = Server.CreateObject("ADODB.Connection")
: ObjConn.Open Application("ConnectString")
:
: strSQL = "qryVerificaAcesso " & CStr(Request.Form("Username")) & ", " &
: CStr(Request.Form("Password"))
: Set objRS = objConn.Execute (strSQL)
:
: The query qryVerificaAcesso is:
: PARAMETERS NomeAcesso Text ( 255 ), CodigoAcesso Text ( 255 );
: SELECT tblUtilizador.IDUtilizador, tblUtilizador.Nome,
: tblUtilizador.Administrador, tblUtilizador.UltimoAcesso,
: tblUtilizador.Verificado, tblUtilizador.EMail
: FROM tblUtilizador
: WHERE (((tblUtilizador.Verificado)=True) AND ((tblUtilizador.NomeAcesso)
: [NomeAcesso]) AND ((tblUtilizador.CodigoAcesso)=[CodigoAcesso]));
:
: But it works about 19 times in 20 page opens! I'm not understanding why
: it happends, if it works once it should work every time, and if it works
: 19 times and fails one...
:
: Maybe someone can help me!
:
: My system is:
: Windows XP (.Net framework instaled)
: The page is regular ASP for a site that has about 50 visitors a day
: hosted in my computer.
:
: Thanks in advance.
: Vítor

Hi

I guess you're right ADO was really guessing what query type was it, beut 
the values are always the same, and the error doesn't happend every time!

I've changed the code so that i create the recordset object and open it 
with the adstoredProc option, but the error continues now with a diferent 
error, but it hapens ocasionly!

Now i got:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Unknown name

In this line:
objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly,
adCmdStoredProc

The strSQL are always the same string!

Maybe my computer is not able to respond to several requests very fast. I 
really don't understand it!

Thanks for any more info!
(I'm thinking of geting windows 2000 Server back, it worked well, but XP, 
well it's brand new, and the user of the computer want it!)