MOD2000: Data Environment Designer Can't Refresh Parameters of Command Object

• Microsoft Office 2000 Developer

SYMPTOMS

If you have a command object defined in a Data Environment that runs a stored procedure with parameters, any changes that you make directly to the parameter specifications in the SQL statement are not reflected in the command object.

CAUSE

Parameter definition changes are not updated automatically. Also, they are not refreshed when the command is refreshed.

RESOLUTION

To refresh the parameters in your connection object, follow these steps:

1. Right-click the command, and then click Properties.



2. In the Object Name list, reselect the stored procedure that has not been refreshed.



3. Click Apply or OK.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and perform these steps on a copy of the project.

Steps to Reproduce Behavior

1. On a computer with the following software installed
   
   
   • Microsoft Office 2000 Developer
   
   
   
   • Microsoft Data Engine (MSDE)
   
   
   
   open the sample MSDE database NorthwindCS.adp.



2. Under Objects, click Stored Procedures.



3. Click New, type the following in the new stored procedure box, and then save the stored procedure:




Create Procedure orders_list

@input_customerid VarChar(20)

As

Select * From Orders

Where CustomerID = @input_customerid

4. On the Tools menu, point to Macro, and then click Visual Basic Editor.



5. In the Visual Basic Editor, click New Project on the File menu.



6. In the New Project box, click Empty Project, and then click OK.



7. On the Insert menu, click Components.



8. In the Components dialog box, click the Designers tab and make sure that the Data Environment check box is selected. Then, click OK.



9. Click Project1, and then on the Insert menu, click Data Environment.



10. Right-click Connection1, and then click Properties.



11. In the Data Link Properties box, click the Provider tab, click Microsoft OLE DB Provider for SQL Server, and then click Next.



12. On the Connection tab, fill in all the boxes. If you have the NorthwindCS.adp file installed with default security, use following entries:




Server name = (local)
User name = sa
Password = <leave blank>
Blank Password = checked
Database = NorthwindCS

13. Click Test Connection, and then click OK to close the Data Link Properties dialog box.



14. Right-click Connection1, and then click Add Command.



15. Right-click Command1, and then click Properties.



16. In the Command1 Properties dialog box, click Stored Procedure in the Database Object list.



17. In the Object Name list, click the stored procedure that you created earlier. It should appear in the list as dbo.orders_list.



18. Click OK to close the Command Properties dialog box.



19. Right-click Command1, and then click Properties.



20. In the Command1 Properties dialog box, click the Parameters tab, and in the Parameter list, click input_customerid. Note that the Size box setting is 20. Click OK.



21. On the View menu, click Microsoft Access to return to Microsoft Access.



22. In Access, click to select the orders_list stored procedure, and then click Design.



23. Make the following changes to the stored procedure. Note that you are changing the size of the original parameter to 40 and that you are also adding an extra parameter called input_shipname.




Alter Procedure orders_list

@input_customerid VarChar(40),
@input_shipname VarChar(40)

As

Select * From Orders

Where CustomerID = @input_customerid

and ShipName = @input_shipname

24. Save and close the procedure.



25. On the Tools menu, point to Macro, and then click Visual Basic Editor.



26. Right-click Command1, and then click Refresh.



27. Right-click Command1, and then click Properties.



28. Click the Parameters tab.

Additional query words: pra

Keywords : kbdta modDesigners
Version : :
Platform : WINDOWS
Issue type : kbbug