How to change parameter order in parameter collection 
Author Message
 How to change parameter order in parameter collection

Hello Everyone

I am having a problem (so what else is new :o)). What I have developed
is a VB6 application which pass parameters to multiple SQL Server 7.0
stored procedures. One of the key thing that I wanted to accomplish is
to append one parameter to multiple command objects then execute all
commands. The problem is that even though all of the parameters are
added to each command objects parameters collection, because I am
adding them to multiply command objects (7 to be exact) I can not
append them in the order in which they need to be in. I was wondering
is the a way to manipulate the order in which the parameter objects are
stored in the parameters collection.

Here is some sample code to show what I am trying to do. This will be
too long to show everything but assume all values to be previously
declared variables.

Set paroldponbr = New Parameter
paroldponbr.Name = "popoldponbr"
paroldponbr.Type = adVarChar
paroldponbr.Direction = adParamInput
paroldponbr.Size = 15
paroldponbr.Value = oldponumber
cmdpopulatepo10.Parameters.Append paroldponbr
cmdpopulatepo11.Parameters.Append paroldponbr
cmdpopulatepo12.Parameters.Append paroldponbr
cmdpopulatepo13.Parameters.Append paroldponbr
cmdpopulatepo20.Parameters.Append paroldponbr
cmdpopulatepo30.Parameters.Append paroldponbr
cmdpopulatepo60.Parameters.Append paroldponbr

Set parporevnbr = New Parameter
 parporevnbr.Name = "poprevnbr"
 parporevnbr.Type = adVarChar
 parporevnbr.Direction = adParamInput
 parporevnbr.Size = 3
 parporevnbr.Value = porevisionnbr
 cmdpopulatepo10.Parameters.Append parporevnbr
 cmdpopulatepo11.Parameters.Append parporevnbr
 cmdpopulatepo12.Parameters.Append parporevnbr
 cmdpopulatepo13.Parameters.Append parporevnbr
 cmdpopulatepo20.Parameters.Append parporevnbr
 cmdpopulatepo30.Parameters.Append parporevnbr
 cmdpopulatepo60.Parameters.Append parporevnbr

Set parporeleasenbr = New Parameter
parporeleasenbr.Name = "poporeleasenbr"
parporeleasenbr.Type = adVarChar
parporeleasenbr.Direction = adParamInput
parporeleasenbr.Size = 5
parporeleasenbr.Value = poreleasenbr
cmdpopulatepo10.Parameters.Append parporeleasenbr
cmdpopulatepo11.Parameters.Append parporeleasenbr
cmdpopulatepo12.Parameters.Append parporeleasenbr
cmdpopulatepo13.Parameters.Append parporeleasenbr
cmdpopulatepo20.Parameters.Append parporeleasenbr
cmdpopulatepo30.Parameters.Append parporeleasenbr
cmdpopulatepo60.Parameters.Append parporeleasenbr

Set parpotype = New Parameter
parpotype.Name = "poppotype"
parpotype.Type = adVarChar
parpotype.Direction = adParamInput
parpotype.Size = 1
parpotype.Value = potype
cmdpopulatepo10.Parameters.Append parpotype

Set parpoissuedate = New Parameter
parpoissuedate.Name = "poppoissuedate"
parpoissuedate.Type = adVarChar
parpoissuedate.Direction = adParamInput
parpoissuedate.Size = 8
parpoissuedate.Value = poissuedate
cmdpopulatepo10.Parameters.Append parpoissuedate

Set parcarrier = New Parameter
parcarrier.Name = "popcarrier"
parcarrier.Type = adVarChar
parcarrier.Direction = adParamInput
parcarrier.Size = 4
parcarrier.Value = carrier
cmdpopulatepo10.Parameters.Append parcarrier
cmdpopulatepo30.Parameters.Append parcarrier

Set parpolineindex = New Parameter
parpolineindex.Name = "poppolineindex"
parpolineindex.Type = adVarChar
parpolineindex.Direction = adParamInput
parpolineindex.Size = 4
parpolineindex.Value = polineindex
cmdpopulatepo11.Parameters.Append parpolineindex
cmdpopulatepo12.Parameters.Append parpolineindex
cmdpopulatepo13.Parameters.Append parpolineindex
cmdpopulatepo20.Parameters.Append parpolineindex
cmdpopulatepo30.Parameters.Append parpolineindex
cmdpopulatepo60.Parameters.Append parpolineindex

Set parcatalogid = New Parameter
parcatalogid.Name = "popcatalogid"
parcatalogid.Type = adVarChar
parcatalogid.Direction = adParamInput
parcatalogid.Size = 10
parcatalogid.Value = catalogid
cmdpopulatepo11.Parameters.Append parcatalogid

Set parqtyorderedup = New Parameter
 parqtyorderedup.Name = "popqtyorderedup"
parqtyorderedup.Type = adVarChar
parqtyorderedup.Direction = adParamInput
parqtyorderedup.Size = 11
parqtyorderedup.Value = qtyorderedup
cmdpopulatepo11.Parameters.Append parqtyorderedup
cmdpopulatepo30.Parameters.Append parqtyorderedup

Set parspname = New Parameter
parspname.Name = "popspname"
parspname.Type = adVarChar
parspname.Direction = adParamInput
parspname.Size = 12
parspname.Value = spname
cmdpopulatepo13.Parameters.Append parspname

Set parsprevision = New Parameter
parsprevision.Name = "popsprevison"
parsprevision.Type = adVarChar
parsprevision.Direction = adParamInput
parsprevision.Size = 3
parsprevision.Value = sprevision
cmdpopulatepo13.Parameters.Append parsprevision

Set parneeddate = New Parameter
parneeddate.Name = "popneeddate"
parneeddate.Type = adVarChar
parneeddate.Direction = adParamInput
parneeddate.Size = 8
parneeddate.Value = needdate
cmdpopulatepo30.Parameters.Append parneeddate

Set parcostcentercharge = New Parameter
parcostcentercharge.Name = "popcostcentercharge"
parcostcentercharge.Type = adVarChar
parcostcentercharge.Direction = adParamInput
parcostcentercharge.Size = 1
parcostcentercharge.Value = costcentercharge
cmdpopulatepo60.Parameters.Append parcostcentercharge

Set paractivityid = New Parameter
paractivityid.Name = "popactivityid"
paractivityid.Type = adVarChar
paractivityid.Direction = adParamInput
paractivityid.Size = 8
paractivityid.Value = activityid
cmdpopulatepo60.Parameters.Append paractivityid

Set paruserdef = New Parameter
paruserdef.Name = "popuserdef"
paruserdef.Type = adVarChar
paruserdef.Direction = adParamInput
paruserdef.Size = 12
paruserdef.Value = userdef
cmdpopulatepo60.Parameters.Append paruserdef

Set pargroupidcharge = New Parameter
pargroupidcharge.Name = "popgroupidcharge"
pargroupidcharge.Type = adVarChar
pargroupidcharge.Direction = adParamInput
pargroupidcharge.Size = 2
pargroupidcharge.Value = groupidcharge
cmdpopulatepo60.Parameters.Append pargroupidcharge

cmdpopulatepo10.Execute
cmdpopulatepo12.Execute
cmdpopulatepo13.Execute
cmdpopulatepo20.Execute
cmdpopulatepo30.Execute
cmdpopulatepo60.Execute
cmdpopulatepo11.Execute

Thanks

Marc Jacobs

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



Fri, 11 Apr 2003 10:05:04 GMT  
 How to change parameter order in parameter collection
Several points
- I think that the parameter object has a parent pointer that gets assigned
when you append.
- If this is the case then only the last one will work.
- If you prepare the parameter first and set it to a adcmdStoredProcedure
then the parameters will be populated and you can just
call them by name lobjCommand.Parameters("<<ParamName>>").value = MyValue
this would be better than trying to append it to
many recordsets.
- If these don't work try making a sub in vb that handles the parameters so
that you will reduce the lines of code required.
Cheers,

Rob



Sun, 13 Apr 2003 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. passing optional parameters to the parameters collection

2. QueryDef: Parameter for ORDER BY clause ?

3. Ordering by a parameter field ???

4. using a parameter field to define sort order

5. Crystal design & order of parameters

6. QueryDef: Parameter for ORDER BY clause ?

7. Querydef: Parameter in a Order By Clause

8. Parameters order in SP when executing Commands

9. Parameter Collection Questions

10. Clearing the Command Objects Parameter Collection for re-use

11. Query search results from parameter collections!!!

12. ADO Parameters collection,Nulls, & Oracle

 

 
Powered by phpBB® Forum Software