To Add a Data Connection
| 1. | From the Project menu, choose Add Data Connection. |
| 2. | In the Data Link Properties dialog box, select Microsoft OLE DB Provider for ODBC drivers, and then click Next >>. |
| 3. | Choose Use data source name and provide the system DSN that was created to connect to the Oracle database. (For information on how to create ODBC DSNs to connect to Oracle databases, see the "References" section). |
| 4. | Type a User name and Password. Click the Test Connection button to make sure that you can connect to your Oracle database. |
Once you have established a successful data connection in the Visual InterDev Web project, you can start to create ASP pages to call nonparameterized and parameterized stored procedures from your Oracle database server. To Call a Nonparameterized Stored Procedure in Oracle
| 1. | Add an ASP page to the Web project: right-click the project in the Project Explorer, click Add, and then select Active Server Page. |
| 2. | Add a Recordset DTC to the ASP page. To do so: | a. | From the View menu, select Toolbox, and then select Design-Time Controls. | | b. | Drag a recordset DTC to a position following the <BODY> tag on your ASP page. A dialog box appears and asks if the Visual InterDev Scripting Object Model should be implemented. | | c. | Click yes. | |
| 3. | Right-click the recordset DTC, and then select Properties. Select SQL Statement. |
| 4. | In the SQL Statement text box, type the following: |
{call packperson.allperson({resultset 9, ssn, lname, fname})}
| 5. | Add a Grid DTC to the ASP page. To do so: | a. | From the Design-Time Controls Toolbox, select Grid DTC. | | b. | Drag the GRID DTC onto the ASP page to a position following the recordset DTC. | | c. | Bind it to the recordset DTC. To do so, right-click the Grid DTC, and then select Properties. | | d. | Click the Data tab, and then select your recordset DTC (for example, Recordset1) as the Recordset. | |
| 6. | Save the ASP page and view it in the browser. |
This brings up all records from the Oracle stored procedure and only the specific columns ssn, lname, and fname. Call a Parameterized Stored Procedure in Oracle
NOTE: See Microsoft Knowledge Base article Q240338 in the "References" section of this article if you want to call a parameterized stored procedure in Oracle. | 1. | Add an ASP page to the Web project. |
| 2. | Add a recordset DTC to the ASP page. A dialog box appears and asks if you want the Visual InterDev Scripting Object Model to be implemented. Click yes. |
| 3. | Right-click the recordset DTC, and then select Properties. |
| 4. | On the General tab, select SQL Statement. |
| 5. | In the SQL Statement text box, type the following: |
{call packperson.oneperson(?, {resultset 2, ssn, lname, fname})}
| 6. | Select the Parameters tab of the Recordset DTC dialog box. In the Value column of the Values for parameters frame, type the following (include the quotation marks): |
'555662222'
| 7. | Add a Grid DTC to the ASP page and bind it to the recordset DTC. |
| 8. | Save the ASP page and view it in your browser. |
Only the one record with the "SSN = '555662222'" is returned from the Oracle stored procedure (and only the columns ssn, lname, and fname appear).
No comments:
Post a Comment