ADO Parameter Refresh using Stored Procs

ADO Parameter Refresh using Stored Procs

Post by JGP » Sat, 15 Sep 2001 01:34:19



Hi,
I'm using MDAC 2.6 with SQL2000 and VB6.  I'm trying to run a storec proc
from VB using ADO Command object.
I was wondering if there is a better way to fill the Parameters collection
than sing the Refresh method.
I came across an interesting article in MSDN
(http://www.microsoft.com/Mind/1199/storedproc/storedproc.htm) which uses a
stored proc
to replace the Refresh method, but I'm not sure if this is the right way to
go about it since we have to hard-code all the data type values in the
stored proc and I feel uncomfortable about that.
Any ideas about the best way to fill the Parameters collection would be
appreciated. Does anyone think that manually appending each Parameter to the
Command object is the best way to go?
Thanks for ur inputs.
JGP
 
 
 

ADO Parameter Refresh using Stored Procs

Post by Griff » Sat, 15 Sep 2001 02:31:24


We wrote a little utility that called the Refresh method and generated the
code necessary to fill the parameters collection, basically, we called
Refresh and reverse engineered the parameters collection into code, in fact,
calling a stored proc took no programming, we generated all of the code.
That's the best way to go.


Quote:> Hi,
> I'm using MDAC 2.6 with SQL2000 and VB6.  I'm trying to run a storec proc
> from VB using ADO Command object.
> I was wondering if there is a better way to fill the Parameters collection
> than sing the Refresh method.
> I came across an interesting article in MSDN
> (http://www.microsoft.com/Mind/1199/storedproc/storedproc.htm) which uses
a
> stored proc
> to replace the Refresh method, but I'm not sure if this is the right way
to
> go about it since we have to hard-code all the data type values in the
> stored proc and I feel uncomfortable about that.
> Any ideas about the best way to fill the Parameters collection would be
> appreciated. Does anyone think that manually appending each Parameter to
the
> Command object is the best way to go?
> Thanks for ur inputs.
> JGP


 
 
 

ADO Parameter Refresh using Stored Procs

Post by Zachary Well » Sat, 15 Sep 2001 02:43:04


From what I've read, manually filling the parameters is the fastest way to
go, refresh is the slowest. Personally I hard code the parameters for that
very reason. Remember, doing a refresh requires an additional call to the
DB. If you're running your app over a 128K that can be a significant delay.

Zach


Quote:> Hi,
> I'm using MDAC 2.6 with SQL2000 and VB6.  I'm trying to run a storec proc
> from VB using ADO Command object.
> I was wondering if there is a better way to fill the Parameters collection
> than sing the Refresh method.
> I came across an interesting article in MSDN
> (http://www.microsoft.com/Mind/1199/storedproc/storedproc.htm) which uses
a
> stored proc
> to replace the Refresh method, but I'm not sure if this is the right way
to
> go about it since we have to hard-code all the data type values in the
> stored proc and I feel uncomfortable about that.
> Any ideas about the best way to fill the Parameters collection would be
> appreciated. Does anyone think that manually appending each Parameter to
the
> Command object is the best way to go?
> Thanks for ur inputs.
> JGP

 
 
 

ADO Parameter Refresh using Stored Procs

Post by Tibor Karasz » Sat, 15 Sep 2001 18:59:55


Below might help, generating the code. I've never used it myself, though...

http://msdn.microsoft.com/code/default.asp?URL=/code/sample.asp?url=/...
0/152/MsdnCompositeDoc.xml

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


> From what I've read, manually filling the parameters is the fastest way to
> go, refresh is the slowest. Personally I hard code the parameters for that
> very reason. Remember, doing a refresh requires an additional call to the
> DB. If you're running your app over a 128K that can be a significant delay.

> Zach



> > Hi,
> > I'm using MDAC 2.6 with SQL2000 and VB6.  I'm trying to run a storec proc
> > from VB using ADO Command object.
> > I was wondering if there is a better way to fill the Parameters collection
> > than sing the Refresh method.
> > I came across an interesting article in MSDN
> > (http://www.microsoft.com/Mind/1199/storedproc/storedproc.htm) which uses
> a
> > stored proc
> > to replace the Refresh method, but I'm not sure if this is the right way
> to
> > go about it since we have to hard-code all the data type values in the
> > stored proc and I feel uncomfortable about that.
> > Any ideas about the best way to fill the Parameters collection would be
> > appreciated. Does anyone think that manually appending each Parameter to
> the
> > Command object is the best way to go?
> > Thanks for ur inputs.
> > JGP

 
 
 

1. ADO Parameter Refresh using Stored Procs

Hi,
I'm using MDAC 2.6 with SQL2000 and VB6.  I'm trying to run a storec proc
from VB using ADO Command object.
I was wondering if there is a better way to fill the Parameters collection
than sing the Refresh method.
I came across an interesting article in MSDN
(http://www.microsoft.com/Mind/1199/storedproc/storedproc.htm) which uses a
stored proc
to replace the Refresh method, but I'm not sure if this is the right way to
go about it since we have to hard-code all the data type values in the
stored proc and I feel uncomfortable about that.
Any ideas about the best way to fill the Parameters collection would be
appreciated. Does anyone think that manually appending each Parameter to the
Command object is the best way to go?
Thanks for ur inputs.
JGP

ps. The solution needs to work on SQL 7 as well..

2. entering dates via web interface

3. How can I query a stored procs Input/Output parameters using ADO

4. SQL 6.5 -- Programming Logins with VB 4.0

5. ce FL-Miami-->Contractor - Programmer/Analyst Oracle v8 Data Warehouse

6. REFERENCING VALUES ON OTHER FORM

7. Using ADO Refresh against an ORACLE Database to get Parameters info for a Stored procedure(s)

8. ADO, Parameters and SQL Strings (not stored procs) in SQL Server

9. How to bind BLOB parameter of a stored procedure using Parameter Object of ADO

10. stored procedure and ADO .Parameters.Refresh