Passing Parameter to Access via ASP and ADO 
Author Message
 Passing Parameter to Access via ASP and ADO

Hi Folks,

Hopefully an easy and quick one.  I would like to know if it is possible to
pass a parameter to a query in Access 97 using ADO ( From and ASP page on
PWS or IIS) using some thing like the following or any other syntax

set rs =  connection.execute ( query, parameter )

or

set rs =  connection.execute ( query, array(parameters) )

The connection is fine and I can pass SQL strings with no problem. I'm sure
I'm missing something simple.
I have no problem passing parameters to SQL Server but the same syntax does
not work with access.

I would appreciate if someone out there could paste a one and or two
parameter example of a piece of code that works.

Thanks in advance,

Olan



Sun, 01 Sep 2002 03:00:00 GMT  
 Passing Parameter to Access via ASP and ADO
Olan,

Here is a simple example of executing a parameter query in Access97 using
ADO.  The query's name is "SalesStaffByID".  The query requires 1 parameter,
prmID:  a salesperson's ID, which is coming from the itemdata property of a
clicked item in a combobox.  This example assumes that an open connection
named conStaff is available:

    Dim myCommand As ADODB.Command
    Dim myParameter As ADODB.Parameter
    Dim myID As Long

    'Retrieve the salesperson id from the itemdata property
    myID = lstNames.ItemData(lstNames.ListIndex)

    'Create a new instance of a command object
    Set myCommand = New ADODB.Command

    'Set the command object's ActiveConnection
    myCommand.ActiveConnection = conStaff

    'Set the command object's CommandText property to the query name
    myCommand.CommandText = "SalesStaffByID"

    'An Access query's command type is adCmdTable
    myCommand.CommandType = adCmdTable

    'Create a parameter
    Set myParameter = myCommand.CreateParameter

    'Assign the parameter's name
    myParameter.Name = "prmID"

    'Assign the parameter's type (Access autonumber fields are adInteger
    myParameter.Type = adInteger

    'Assign the parameter's direction
    myParameter.Direction = adParamInput

    'Assign the parameter's size (4 for Access autonumber fields)
    myParameter.Size = 4

    'Assign a value to the prmID parameter
    myParameter.Value = myID

    'Append the parameter to the Parameters collection
    myCommand.Parameters.Append myParameter

    'Call the command object's Execute method to run the parameter query
    Set rsSalesStaff = myCommand.Execute

    'Display the resulting record
    ShowFields

Hope this helps,

Kerry Moorman


Quote:
> Hi Folks,

> Hopefully an easy and quick one.  I would like to know if it is possible
to
> pass a parameter to a query in Access 97 using ADO ( From and ASP page on
> PWS or IIS) using some thing like the following or any other syntax

> set rs =  connection.execute ( query, parameter )

> or

> set rs =  connection.execute ( query, array(parameters) )

> The connection is fine and I can pass SQL strings with no problem. I'm
sure
> I'm missing something simple.
> I have no problem passing parameters to SQL Server but the same syntax
does
> not work with access.

> I would appreciate if someone out there could paste a one and or two
> parameter example of a piece of code that works.

> Thanks in advance,

> Olan



Sun, 01 Sep 2002 03:00:00 GMT  
 Passing Parameter to Access via ASP and ADO

Quote:

> Hopefully an easy and quick one.  I would like to know if it is possible to
> pass a parameter to a query in Access 97 using ADO ( From and ASP page on
> PWS or IIS) using some thing like the following or any other syntax

> set rs =  connection.execute ( query, parameter )

> or

> set rs =  connection.execute ( query, array(parameters) )

No. Well, yes, but only if you involve a Command object. E.g. in pseudo-code

command.activeconnection = yourconnectionobject

command.commandtext := yourquery

set rs = command.execute(RecordsAffected, array(parameters), adCmdType)

Beware, I do it with Delphi :-)

Roger Morton



Sun, 01 Sep 2002 03:00:00 GMT  
 Passing Parameter to Access via ASP and ADO

Quote:

> Here is a simple example of executing a parameter query in Access97

using ADO.
[...]

Well, I didn't do this quite the same way you did, but since you seem
to know of what you speak... do you suppose you could tell me what's
wrong with the following snippet?
I'm getting the following error for the asterisked line:
ADODB.Parameters error '800a0cc1'
ADO could not find the object in the collection corresponding to the
name or ordinal reference requested by the application.

[...]
Set comm = Server.CreateObject("ADODB.Command")
Set comm.ActiveConnection = conn
comm.commandtype=4
comm.commandtext = "ACCESSQUERY"
set param = comm.createparameter("PARAMETER1",8,1)
set param = comm.createparameter("PARAMETER2",3,1)
comm.parameters.append param
comm("PARAMETER1") = VALUE1 '***********
comm("PARAMETER2") = VALUE2
rst.comm.execute()
[...]

Thanks...
Chris

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 14 Sep 2002 03:00:00 GMT  
 Passing Parameter to Access via ASP and ADO

Quote:

> [...]
> Set comm = Server.CreateObject("ADODB.Command")
> Set comm.ActiveConnection = conn
> comm.commandtype=4
> comm.commandtext = "ACCESSQUERY"
> set param = comm.createparameter("PARAMETER1",8,1)
> set param = comm.createparameter("PARAMETER2",3,1)
> comm.parameters.append param
> comm("PARAMETER1") = VALUE1 '***********
> comm("PARAMETER2") = VALUE2
> rst.comm.execute()
> [...]

Shouldn't this be

comm.parameters("Parameter1").Value = Value1

Roger Morton



Sun, 15 Sep 2002 03:00:00 GMT  
 Passing Parameter to Access via ASP and ADO

[...]

Quote:
> Shouldn't this be

> comm.parameters("Parameter1").Value = Value1

That didn't change the error... I'm guessing that I was using viable
shorthand.

Ok, as an alternative:
I know that I can run a query with 1 parameter by
         rst.Open "ACCESSQUERYNAME "& ASPVARIABLENAME, Conn, 3, 4
Is there a correct syntax for sending two params? I know what doesn't
work...
         rst.Open "QUERY "& ASPVAR1 & ASPVAR2, Conn, 3, 4

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 15 Sep 2002 03:00:00 GMT  
 Passing Parameter to Access via ASP and ADO

Quote:



> [...]
> > Shouldn't this be

> > comm.parameters("Parameter1").Value = Value1

> That didn't change the error... I'm guessing that I was using viable
> shorthand.

I'm not surprised - the penny has only just dropped, I'm afraid. To crib from your original post:

Set comm = Server.CreateObject("ADODB.Command")
Set comm.ActiveConnection = conn
comm.commandtype=4
comm.commandtext = "ACCESSQUERY"
set param = comm.createparameter("PARAMETER1",8,1)

OK so far. But the next line discards the first parameter object and assigns param to a second parameter object.

set param = comm.createparameter("PARAMETER2",3,1)

So the following line appends Parameter2 to the parameters collection, but Parameter1 has gone walkabout.

comm.parameters.append param

comm("PARAMETER1") = VALUE1 '***********
comm("PARAMETER2") = VALUE2
rst.comm.execute()

I'm sure you can figure out the fix....

Roger Morton



Sun, 15 Sep 2002 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Passing Parameters via ASP

2. Passing parameter to Sybase ASA Stored procedure via ADO

3. Can't pass a NULL date parameter via ADO to SQL stored procedure

4. Problem Passing Parameter to ACCESS Query from ASP

5. error doing a FILTER on an Access database via Asp/VBScript and ADO

6. no permission to view access database via asp(using ado)

7. error doing a FILTER on an Access database via Asp/VBScript and ADO

8. no permission to view access database via asp(using ado)

9. Passing wildcard to Access via ADO

10. Passing parameters to a via myprog.exe %1

11. Passing parameters to the URL via vbscript

12. Passing parameters to application via HTML

 

 
Powered by phpBB® Forum Software