We help IT Professionals succeed at work.

Using named parameters with stored procedures (ADO 2.7, Access ADP, SQL SVR 2000  and VBA)

SEANWYATT
SEANWYATT asked
on
Medium Priority
1,157 Views
Last Modified: 2013-12-25
I was under the impression that utilizing named parameters I could only specify the parameters needed and execute with those leaving others blank.

I have a large Access form that I need to be able to save as incomplete and come back to it later to finish. I have update and insert stored procedures defined to do this and I am using code to create and append parameters to the appropriate stored procedure as necessary. However, I am getting an error that the stored procedure expects a parameter that was not supplied.

I am thinking that I need default values but am not sure how to use the value already in the table or Null as default.
Comment
Watch Question

Commented:
You will still need to create all of the parameters, but if the stored procedure is set up to use defaults then you only have to set the ones that you are using.

Author

Commented:
So If I set the defaults to Null in the stored procedure, I need to loop through all the controls on the form before executing the update to add the parameters and not just the fields that were changed on the current visit. That is going to be an interesting project... Is there a better way to handle this?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I guess the best way would be to build a parameter array by going through the controls and adding the control value or "" to the array.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.