Command.Parameters collection populated automatically

Hi,

We have a lot of VB6 code that uses ADO 2.7 and stored procs wih Sql 2005. I have noticed recently that if I use the follow code:

Dim con As New ADODB.Connection
con.ConnectionString = "driver={SQL Server};server=(local);database=test;uid=sa;pwd="
con.Open

Dim com As New ADODB.Command

com.ActiveConnection = con
com.CommandText = "usp_GetSetting"
com.CommandType = adCmdStoredProc

com.Parameters.Append com.CreateParameter(...)

It will fail. the reason being the after setting the CommantText and Type ADO then seems to automatically go away and populate the Parameters collection from the databases metadata according to the SP we are calling.

I have never seen this before, I thought the Refresh method had to be called before the parameters collection get populated.

Can anyone help me please?

Ok,

I think I know what is going on. Is just so happens that my latest debug code contained com.Parameters.Count before the first Parameters.Append statement. I think it is the act of counting the parameters that is causing it to go off and retrieve them all.

Sorry to be an idiot :(

Graham

comma delimited list update stored procedure

I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.

Here is how I wrote my stored procedure:

CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@.abrID int,
@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt
varchar(50),
@.incrdecr varchar(50),
@.review char(10),
@.abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @.review
WHERE ABR_ID = @.abrID

UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @.ddo, ABR_DETLS_AY = @.ay,
ABR_DETLS_STRATEGY = @.strategy, ABR_DETLS_BUDG_ACCT = @.budgacct,
ABR_DETLS_BUDG_OBJ = @.budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @.origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @.incrdecr)
WHERE
ABR_DETLS_ID = @.abrdetlsID
GO

The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.

Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?

Thanks for any help.
-D-Hi,
I think providing values in XML format rather than comma delimited will deliver more flexibility to retrieve values from that.
Still you can write User Defined Functions to pass the comma delimited string into that and get particular value.
Regards,
Leila|||Which parameter is the list, and how did you intend to use it?

-PatP|||The request form collection will return each of these parameters in a comma delimited list, which the second update in the stored procedure would handle:

@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt varchar(50),
@.incrdecr varchar(50),
@.abrdetlsID varchar(50)

So, should I use the split function and pass the information into the procedure that way? I've used the split funciton for one parameter, but not multiple parameters. So, I wasn't sure how to code for that?

Thank you for your help.
-D-|||If the number of parameters the same across the set then do parse them and execute the procedure once for each combination. Otherwise, you need to review this design and get away from doing thing this way.|||Yes, there are the same number of parameters for each variable in the set. So, if I use the split function:

ddolist = Split(Request.Form("ddo"),", "
strategylist = Split(Request.Form("strategy"),", "
aylist = Split(Request.Form("ay"),", "
budgobjlist = Split(Request.Form("budgobj"),", "
budgacctlist = Split(Request.Form("budgacct"),", "
incrdecrlist = Split(Request.Form("incrdecr"),", "
abrdetlsIDlist = Split(Request.Form("abrdetlsID"),", "

I can use any of the parameters to determine the number of loops by using Ubound?

i.e.:

Loop_Max = UBound(abrdetlsIDlist)
For x = 0 to Loop_Max
Command_Name.Parameters.Item("@.ddo").Value = ddolist(x)
Command_Name.Parameters.Item("@.strategy").Value = strategylist(x)
Command_Name.Parameters.Item("@.ay").Value = aylist(x)
Command_Name.Parameters.Item("@.budgobj").Value = budgobjlist(x)
Command_Name.Parameters.Item("@.budgacct").Value = budgacctlist(x)
Command_Name.Parameters.Item("@.incrdecr").Value = incrdecrlist(x)
Command_Name.Parameters.Item("@.abrdetlsID").Value = abrdetlsIDlist(x)
Command_Name.Execute()
Next

Would that be correct?

Thank you for your help.
Regards,
-D-