Re: Parameterized Queries - Variables, and Access

Peter Nimmo wrote:
"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in
news:ONxjNkHFHHA.3288@xxxxxxxxxxxxxxxxxxxx:


Otherwise in what way do you solve this problem.

My only other solution at the moment is to use two ? placeholders
and declare two identical Parameters in the Parameters collection.

Do you mean you have this working? Please show us an example.

Nope, sorry Bob. That was my next plan of attack based on my
understanding from SQL Server, but as you say Access does not support
batched queries.

I would be interested to know if there is a better way when using SQL
Server, than my method using DECLARE. I once tried to use the
variable name that I setup with the parameters collection but of
course this kind of named parameters does not work with parameterized
queries only stored procedures.
What is your objection to stored procedures?
The best solution would be a stored procedure. At its simplest it would
look like:

create procedure DelRows (@id as int) as
set nocount on
delete table1 where id = @id
delete table2 where id = @id

Of course you would add error-handling and perhaps explicit transaction
rollbacks.

This could be executed via ADO as simply as:

'conn is an open connection object
conn.DelRows id

Or you could go to the trouble of creating an explicit command object
and explicitly appending a parameter object to its parameters
collection - overkill for this procedure IMO.

I would do something similar in Access, subject to the limitation
against batched queries of course. Two saved parameter queries would be
my path:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • RE: Stored procedure executes twice
    ... How are you constructing the Command object's Parameters collection? ... "Derrick Pope" wrote: ... > The ADO recordset executes stored procedures. ... > Is there something in ADO that would cause it to call my SP twice? ...
    (microsoft.public.sqlserver.programming)
  • sql injection query
    ... According to Microsoft's "improving web application security document" to prevent sql injection you should use the parameters collection with stored procedures and they give the following code ...
    (microsoft.public.dotnet.framework.adonet)