ADODB.Command and Parameter order

[lameid]

I just learned, much to my disappointment, that the order parameters are added to a command object has to be the same as the order they are declared in the SQL Server stored procedure...

Being able to determine the parameter order easily programatically would make things much easier. Any ideas?

Access 2010, SQL Server 2008 R2, Microsoft AtiveX Data Objects 2.8 Library

Naturally the point of the procedure that is saving a record is to avoid SQL injection possibilities and prepare for future move to other Web user interface.

The purpose: I am trying to generically loop through controls on a screen and call the command with added parameters as opposed to having to know up front. Since I have a recordset for reading that has the column order which is the parameter order, I can in theory build my statement that way but would rather avoid it. -I may have fields/columns not on the screen in the recordset.

 

[vbajock]

Run that by me again? I usually just look at the SP.

 

[lameid]

Procedure declared to take say 10 params which are fields that it inserts/updates to the target table (No worries here it works).

I then loop through the controls on the form...

For Each ctl In Ctls
     'Based on naming convention of control add parameter
     'Get datatype from RS.Field that was used to read values
     'When screen loaded
     'Yields right paramater names, values and datatypes but wrong order
Next ctl

 

[vbajock]

You can do it via .net, but I don't know how you would do it via vba. The .net namespace is derivesqlparameter, google it.

 

[lameid]

In .Net that retrieves the paramater order... Is there a way to define the paramater order on the VBA side aside from sequentially?

 

[vbajock]

This might have what you are looking for:

http://www.codeguru.com/vb/gen/vb_database/ado/article.php/c19659

 

[lameid]

Read something that told me about the NamedParameters property... The behavior I am expecting is if it is set to true and it is by default set to false...

thread705-1244830

So something like the following should work...

For Each ctl In Ctls
     'Based on naming convention of control assign parameter
      strColName = Right(ctl.Name, Len(ctl.Name) - 3)
      cmd.Parameters("@" & strColName).Value = ctl.Value
Next ctl

Right now I am getting an error message indicating that a parameter is not assigned. I have additional control logic to the above so it should be a simple matter to figure out now.

 

[lameid]

It does work with the NamedParameters property set to true.