Inetpubwwwroot SmartTags GetInfoasp

Dim conn Dim strSearch Dim strstrSQL

strSearch = Request.QueryString("Term") strAction = Request.QueryString("ActionId")

' establish connection to the database conn="Provider=Microsoft.Jet.0leDB.4.0; Data Source=" & _ Server.Mappath("Northwind.mdb")

' build SQL statement Select Case strAction Case "NameLookup", "CountryLookup" strSQL = strSQL & "SELECT * FROM Customers" strSQL = strSQL & " WHERE Customers.Customerld = "

Case "LastOrderDateLookup" strSQL = strSQL & "SELECT Customers.CustomerlD," strSQL = strSQL & " Max(Orders.OrderDate) AS LastDate" strSQL = strSQL & " FROM Customers INNER JOIN Orders" strSQL = strSQL & " ON Customers.CustomerlD = Orders.CustomerlD" strSQL = strSQL & " GROUP BY Customers.CustomerlD" strSQL = strSQL & " HAVING (((Customers.CustomerID)="

strSQL = strSQL & & strSearch & "'))"

End Select

' Create a Recordset

Set rst = CreateObject("ADODB.Recordset")

' Open a static (3) Recordset (and execute the SQL ' statement above) using the open connection rst.Open strSQL, conn, 3

' Display information if found If not rst.EOF then Response.Write strSearch & ": "

Select Case strAction Case "NameLookup" Response.Write "<B>" & rst("CompanyName").value & "</B>" Case "CountryLookup" Response.Write "<B>" & rst("Country").value & "</B>" Case "LastOrderDateLookup" Response.Write "<B>" & rst("LastDate").value & "</B>" End Select Else

Response.Write "No match for the specified ID: " Response.Write strSearch End if rst.close set rst = Nothing

Set conn = Nothing

The ASP script that you've just created begins by retrieving the required parameters. Notice that this is done through the QueryString collection of the Request object:

strSearch = Request.QueryString("Term")

strAction = Request.QueryString("ActionId")

Next, a connection to the Northwind database is established using the Microsoft Jet OLEDB.4.0 provider. For this connection to work properly, you need to place the Northwind.mdb file in the virtual directory that you created earlier, or change the connection string to point to a different location where this file is located. To look up a customer ID and return a different type of information from the database depending on a selected Smart Tag action, you need to build appropriate SQL statements. The first two actions will use the same SQL statement, as the information that you want to return is located in the same table. This SQL statement simply says to select all fields from the Customers table where CustomerId is the ID (term) that the user had typed:

strSQL = strSQL & "SELECT * FROM Customers" strSQL = strSQL & " WHERE Customers.CustomerId = " strSQL = strSQL & & strSearch &


In the above statement, the strSearch variable holds the value of the customer ID that was typed by the user in a worksheet cell.

The date lookup will require a different, more complex SQL statement. This statement says to select the CustomerId field and a calculated field named LastDate obtained by applying the Max function to the OrderDate field in the Orders table. The two tables (Customers, Orders) necessary to obtain this information are to be joined on the CustomerId field. All information will be grouped by CustomerId. The Having clause will limit the returned records to the one record meeting our criteria—the CustomerId (term) provided by the user in a worksheet:

strSQL = strSQL & "SELECT Customers.CustomerID,"

strSQL = strSQL &

strSQL = strSQL &

strSQL = strSQL &

strSQL = strSQL &

strSQL = strSQL &

strSQL = strSQL &

Max(Orders.OrderDate) AS LastDate" FROM Customers INNER JOIN Orders" ON Customers.CustomerID = Orders.CustomerID" GROUP BY Customers.CustomerID" HAVING (((Customers.CustomerID)=" " & strSearch & "'))"

The Select Case statement will allow you to build an SQL statement based on the action ID.

The next statements in the code create and open the recordset based on the SQL statement using the open connection to the database. The remaining code deals with the display of data. If the returned recordset is not empty, we will read the value of the CompanyName, Country, or LastDate field, depending on the type of action that the user has selected. Otherwise, we will display a message that the data for the specified ID was not found. This ASP script is pretty straightforward. For more practice with scripting, check out Chapter 16.

9. Now that you have both the XML smart tag description file (Cus-

tomer.xml) and the ASP script file (getInfo.asp) ready and placed in the appropriate directories, launch Microsoft Excel. Activate any cell in any workbook and type one of the terms defined in the Customer.xml file. When the Smart Tag options button appears, select one of the options. You should see the requested information appear in your browser (see Figure C-5).

Note: If you are looking for the ability to feed information that you have looked up back into Microsoft Excel, you will need to take the DLL approach to creating smart tags. You can find examples on how to do this in the Smart Tags SDK.

0 0

Post a comment