0

I'm trying to run some stored queries in an Access database from an ASP page. I'd like to use an ADO Command object to run the procedure instead of simply sending a string to the database to execute. My trouble is occurring when I try to create the parameters to send to the stored procedure. I'm using the 'CreateParameter' method of the Command object. It takes 5 optional arguments, of which I only want to use two; name and value (arguments 1 and 5 respectively).

I've tried the following approaches to set up the parameters:

1) Using named arguments

command.Parameters.Append command.CreateParameter name:="name", value:="value"

2) Using named arguments inside brackets

command.Parameters.Append command.CreateParameter(name:="name", value:="value")

3) Leaving out optional parameters I don't need

command.Parameters.Append command.CreateParameter("name", , , , "value")

What is a simple way to achieve what I'm trying to do, and why does my syntax fail in these cases? I'm clearly missing something here!

David Edwards
  • 85
  • 1
  • 10

2 Answers2

2

VBScript doesn't support named arguments with or without brackets. All parameters required to pass (specific or blank) for CreateParameter method of the Command object.

For the section 3 in the question, have a look:

CreateParameter(
name`[optional],                              <- fits ("name")
type [optional, default = adEmpty],           <- NOT fits (type is not empty)
direction[Optional default = adParamInput],   <- fits (blank)
size [optional default = 0],                  <- NOT fits (at least 5 for "value")
value[optional]                               <- fits ("value")
)

So, you should specify at least type and size for a text valued parameter. Direction is adParamInput already.

Const adVarChar = 200 ' From adovbs.inc or Ado TypeLib
command.Parameters.Append command.CreateParameter("name", adVarChar, , 5, "value")
Kul-Tigin
  • 16,728
  • 1
  • 35
  • 64
  • CreateParameter is part of ADO not VBScript. see http://msdn.microsoft.com/en-us/library/ms677209%28VS.85%29.aspx – Dee Sep 22 '11 at 06:08
  • I said something like that? What's mean `CreateParameter method of the Command object`? What I said about vbscript and arguments is not related to ADO. Just for VBScript. – Kul-Tigin Sep 22 '11 at 06:36
0

here is an example that can be followed.

http://www.webconcerns.co.uk/asp/accessqueries/accessqueries.asp

see also

http://bytes.com/topic/asp-classic/answers/628368-running-access-stored-queries-parameters

Remember access uses stored queries not stored procedures so some differences apply. For additional information search for

ASP with Access stored queries using parameters - .net

Dee
  • 1,432
  • 1
  • 9
  • 8
  • Thanks, I didn't know that they were only stored queries, not procedures. Do stored procedures give you more flexibility? E.g. PL-SQL? – David Edwards Sep 27 '11 at 04:51
  • The differences are discussed pretty well here: http://social.msdn.microsoft.com/forums/en-US/architecturegeneral/thread/c6864235-5645-4632-93be-15d4d9bd286d/. You can also search on this: stored procedures vs stored queries – Dee Sep 27 '11 at 16:33