AFAIK Access simply doesn't support the type of stored procedures that SQL
Server does. You only have a single SQL statement per parametised query...

Unfortunately you spend a lot of time bring information back to your ASP
pages to validate stuff (eg in the situation you describe where you need two
fields to line up with user input)

Maybe someone who knows more about parametised queries can help here...

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Martin Lee" <access@o...>
Subject: [asp_databases] Please Help - Access Parameter Queries


: Good Afternoon All:
:
: would appreciate any help or opinions anyone can provide.
:
: Have been trying to locate information on using Access Parameter Queries
: with ASP and while there is a wealth of information on SQL Stored
: procedures, have only come across:
:
: http://www.4guysfromrolla.com/webtech/021799-1.shtml
:
: It seems that, while SQL Stored Procedures can execute multiple SQL
: statements within a Stored Procedure and use Return Values, this does not
: seem possible with Access Parameter Queries.
:
: The only way we can figure out returning a value from a Parameter Query,
is
: to use the value returned by the number of records affected by the query.
eg:
:
: Say letting users change their passwords and use the following update
query:
:
: PARAMETERS [@Password] Text ( 255 ), [@UserName] Text ( 255 );
: UPDATE tblUsers SET tblUsers.Password = [@Password]
: WHERE (((tblUsers.Password)<>[@Password]) AND
: ((tblUsers.UserName)=[@UserName]));
:
:
: and execute as:
:
: With cmdUpdate
:
: .ActiveConnection = objconn
: .CommandText = "qupdPass"
: .CommandType = adCmdStoredProc
:
: .Parameters.Append = cmdUpdate.CreateParameter("@Password", adVarWChar,
: adParamInput, 50, strPassword)
: .Parameters.Append = cmdUpdate.CreateParameter("@UserName", adVarWChar,
: adParamInput, 50, strUserName)
: .Execute lngRecs, , adExecuteNoRecords
:
: End With
:
: If lngRecs = 0 we know that the record was not updated since new and old
: password are the same (or some other error?) and then can display the
: appropriate message.
:
: Now, what if we needed to update and validate two fields, instead of just
: the password field. The query would only return 1 or 0 and we would have
no
: way of knowing which of the two fields caused the query not to update the
: record.
:
: Any ideas on how to achieve this?
:
: What about multiple select statements?, such as the following Stored
: Procedure from Ken's site:
:
: http://www.adopenstatic.com/experiments/recordsetpaging.asp
:
: CREATE PROC usp_pagingtest1
:
: @SrchCriteria varChar(50),
: @TotalRecs int OUTPUT
:
: AS
:
: SELECT TestID, TestText
: FROM Test
: WHERE TestText = @SrchCriteria
:
: SELECT @TotalRecs = @@ROWCOUNT
:
: GO
:
: Is there any way one could do something similar with Access?
:
: Due to the limitations of Parameter Queries, is there any advantages to
: using them instead of coding the SQL statements?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Good Afternoon All:

would appreciate any help or opinions anyone can provide.

Have been trying to locate information on using Access Parameter Queries 
with ASP and while there is a wealth of information on SQL Stored 
procedures, have only come across:

http://www.4guysfromrolla.com/webtech/021799-1.shtml

It seems that, while SQL Stored Procedures can execute multiple SQL 
statements within a Stored Procedure and use Return Values, this does not 
seem possible with Access Parameter Queries.

The only way we can figure out returning a value from a Parameter Query, is 
to use the value returned by the number of records affected by the query. eg:

Say letting users change their passwords and use the following update query:

PARAMETERS [@Password] Text ( 255 ), [@UserName] Text ( 255 );
UPDATE tblUsers SET tblUsers.Password = [@Password]
WHERE (((tblUsers.Password)<>[@Password]) AND 
((tblUsers.UserName)=[@UserName]));


and execute as:

With cmdUpdate
		
	.ActiveConnection = objconn
	.CommandText = "qupdPass"
	.CommandType = adCmdStoredProc
	
	.Parameters.Append = cmdUpdate.CreateParameter("@Password", adVarWChar, 
adParamInput, 50, strPassword)
	.Parameters.Append = cmdUpdate.CreateParameter("@UserName", adVarWChar, 
adParamInput, 50, strUserName)
	.Execute lngRecs, , adExecuteNoRecords
			
End With

If lngRecs = 0 we know that the record was not updated since new and old 
password are the same (or some other error?) and then can display the 
appropriate message.

Now, what if we needed to update and validate two fields, instead of just 
the password field. The query would only return 1 or 0 and we would have no 
way of knowing which of the two fields caused the query not to update the 
record.

Any ideas on how to achieve this?

What about multiple select statements?, such as the following Stored 
Procedure from Ken's site:

http://www.adopenstatic.com/experiments/recordsetpaging.asp

CREATE PROC usp_pagingtest1

@SrchCriteria varChar(50),
@TotalRecs int OUTPUT

AS

SELECT TestID, TestText
FROM Test
WHERE TestText = @SrchCriteria

SELECT @TotalRecs = @@ROWCOUNT

GO

Is there any way one could do something similar with Access?

Due to the limitations of Parameter Queries, is there any advantages to 
using them instead of coding the SQL statements?

Thanks for any ideas, help or opinions.

Martin