Partager via

Using Stored Procedures to Access Data in FrontPage

  • Article

John Jansen, one of the FrontPage testers, submitted this tip about how to access SQL Server SPROCs (Stored Procedures) in FrontPage. He has seen questions in the newsgroups about how to do this,  such as, "How do I insert parameter values for my SPROCs".  Here's the skinny on how to do this.

REQUIREMENTS:
FrontPage 2003
Windows SharePoint Services (WSS)

FOR THIS EXAMPLE:
The SQL Server box is called "serverOne" and this example uses the Northwind Database. A user named userOne with a password of passOne has read permissions to this server and the SPROCs.

Before anyone can do this, the server administrator for Windows SharePoint Services needs to enable passthrough queries on the server. Note that this needs to be done for the server and not for an individual WSS Web site.  To do this:

  1. Browse to the Windows SharePoint Services Central Administration page.
  2. Under Component Configuration click on Configure data retrieval service settings.
  3. Check the Enable Data Retrieval Services checkbox.
  4. Check the Enable update query support checkbox.
  5. Click OK.

Now you can access all of the SPROCs in your databases from within FrontPage.  To do this, you need to add a connection to the database server.  Here's how:

  1. Open a Windows SharePoint Services Web site in FrontPage 2003.
  2. Start a new page.
  3. On the Insert Menu, click Data and then Data View.
  4. Expand Database Connections in the Data Source Catalog task pane.
  5. Click Add to catalog.
  6. Click Configure Database Connection.
  7. Fill in the information for the server to access and the user name and password.  For example, the following as described earlier:
    Server Name: serverOne
    Username: userOne
    Password: passOne
  8. Click Next.
  9. Click OK to the warning about storing the username in the connection.
    Note  This stores the username and password in clear text on the server, so in a real world situation for security purposes you would likely want to use Windows or another authentication type.
  10. Enter the name of the database.  In this case, set the Database option to "Northwind."
  11. Scroll down and see the Type: Stored Procedure.
  12. Select CustOrderHist.
  13. Click Finish. You will see the Required Parameters dialog box.
  14. Click Modify.
  15. Check the The value of this parameter can be set at runtime checkbox and give it a default value of ALFKI.
  16. Click OK to close all dialog boxes.  The Northwind data source is now listed in the Data Source Catalog.
  17. Right click and click Show data.
  18. In the Data View Details task pane select the "Product Name" and "Total" columns (hold the Ctrl key down to select multiple columns), and then click Insert Data View.

You now have a view that shows all the product names and totals for the customer with an ID of ALFKI.

To make this dynamic, do the following:

  1. Create a new database connection that returns all of the Customer data from the Northwind Customers table.
  2. Insert a Data View Web part that has the CompanyNames and Customer IDs.
  3. Right click one of the Customer Names in the Data View Web part and choose Web Part Connections.
  4. Use the Connections wizard to provide data values to modify a web part on this page using parameters and map the CustomerID to the SPROC's CustomerID parameter, finish the wizard by creating a hyperlink on the current value.  Use the following steps to do this:
    • Step One: Click Next.
    • Step Two: Click Next.
    • Step Three:   Change Target Web Part to "CustOrderHist" and, if necessary, change Target Action to Modify the view using Parameters. Click Next.
    • Step four: Change "<none>" to "CustomerID" in the row with the Input called CustomerID. Click Next.
    • Step five: Click Next.
    • Step six: Click Finish.
  5. Save the page and then preview it in the browser. Now when you click on different CompanyName links, you will see the original data view's data change to show the correct Customer's products.

The Data View Web Part is only available in Web sites based on Windows SharePoint Services, but using FrontPage and the Data View Web Part, you can create all types of custom views.  You can filter the returned data, sort it by a specific column in ascending or descending order, and even change the way the data is displayed.  Have fun playing around and creating your own custom views.

Comments

  • Anonymous
    August 11, 2004
    The comment has been removed
  • Anonymous
    August 16, 2004
    Thanks Lisa..You posted this last wee and I needed it today..right on time!
  • Anonymous
    August 17, 2004
    The comment has been removed
  • Anonymous
    August 17, 2004
    This is true. This was why I wrote the step to read "Windows SharePoint Services Central Administration." I had hoped that it would still be clear on the odd occasion where SharePoint Portal Server was included in the mix. However, thanks, Relishguy, for pointing this out.
  • Anonymous
    August 26, 2004
    Great lesson, but we can't get the data back from SQL Server when we call our own SPs in our own database (Northwind works fine, but we can't see any difference in permissions etc.)

    FP invariably returns "The Data Source does not contain valid XML data"... any idea what's going on here?

    Thanks!
  • Anonymous
    August 29, 2004
    Heh... turned out to be because the DB name included a hyphen, and wasn't automatically enclosed in square brackets in the Custom Query generated by FP. Just filing in case anyone else hits the same problem.
  • Anonymous
    September 17, 2006
    ghgh