Figure 3   Stores Class from SalesData.vbp


 Option Explicit
 
 Private m_strLastError As String
 Private m_strStateFilter As String
 Private m_fFilterByState As Boolean
 
 Public Property Let LastError(ByVal strNewError As String)
     m_strLastError = strNewError
 End Property
 
 ' Exposes error status to callers
 Public Property Get LastError() As String
     LastError = m_strLastError
 End Property
 
 ' Returned recordset will be filtered by State if StateFilter is not empty
 Public Property Let StateFilter(ByVal strState As String)
     m_strStateFilter = strState
     If strState = "" Then
         m_fFilterByState = False
     Else
         m_fFilterByState = True
     End If
 End Property
 
 Public Property Get StateFilter() As String
     StateFilter = m_strStateFilter
 End Property
 
 ' Connect to database
 Private Function getConnection() As ADODB.Connection
     Dim dbConn As New ADODB.Connection
     On Error GoTo HandleError
 ' **** NOTE: Enter your SQL Server and Login/Password Here
     'All entries on this statement must be on one line 
     dbConn.Open "DRIVER=SQL Server;SERVER=abaco;
     APP=SalesData;
     WSID=MYWORKSTATION;
     DATABASE=pubs;", "sa", ""
     
     Set getConnection = dbConn
     Exit Function
     
 HandleError:
     m_strLastError = "Could not obtain database connection"
     Set getConnection = Nothing
     
 End Function
 
 Private Function getRecordset(strText As String) As ADODB.Recordset
     Dim rsTable As New ADODB.Recordset
     Dim cmdTable As New ADODB.Command
     
     On Error GoTo HandleError
     Set cmdTable.ActiveConnection = getConnection()
         
     cmdTable.CommandType = adCmdText
     cmdTable.CommandText = strText
     Set rsTable.Source = cmdTable
     rsTable.CursorType = adOpenDynamic
     rsTable.CursorLocation = adUseClient
     rsTable.LockType = adLockOptimistic
     rsTable.Open
 Set getRecordset = rsTable
     Exit Function
 HandleError:
     m_strLastError = "Could not find the recordset"
 End Function
 Public Function getStores() As ADODB.Recordset
     On Error GoTo HandleError
 If Not m_fFilterByState Then
         Set getStores = getRecordset("select * from stores")
     Else
         Set getStores = getRecordset("select * from stores where state = '" &_ 	                                     m_strStateFilter & "'")
     End If
 Exit Function
 HandleError:
     m_strLastError = "Could not obtain stores from database"
     Set getStores = Nothing
 End Function
 
 Public Function newStore(strID As String, _
     strName As String, _
     strAddress As String, _
     strCity As String, _
     strState As String, _
     strZip As String) As Object
     On Error GoTo HandleError
     Dim rsTable1 As ADODB.Recordset
     Set rsTable1 = getStores()
     m_strLastError = ""
     rsTable1.AddNew
     rsTable1.Fields("stor_id").Value = CLng(strID)
     rsTable1.Fields("stor_name").Value = strName
     rsTable1.Fields("stor_address").Value = strAddress
     rsTable1.Fields("city").Value = strCity
     rsTable1.Fields("state").Value = UCase(strState)
     rsTable1.Fields("zip").Value = CLng(strZip)
     rsTable1.Update
     Set newStore = rsTable1.Fields
     Exit Function
 HandleError:
     m_strLastError = "Could not insert new store"
     Set newStore = Nothing
 End Function
 
 Public Function updStore(strID As String, _
     strName As String, _
     strAddress As String, _
     strCity As String, _
     strState As String, _
     strZip As String) As Object
     On Error GoTo HandleError
     Dim rsTable1 As ADODB.Recordset
     Dim strWhere As String
     m_strLastError = ""
     strWhere = "stor_id='" + strID + "'"
     Set rsTable1 = getStores()
     rsTable1.Find strWhere
     
     rsTable1.Fields("stor_name").Value = strName
     rsTable1.Fields("stor_address").Value = strAddress
     rsTable1.Fields("city").Value = strCity
     rsTable1.Fields("state").Value = UCase(Left$(strState, 2))
     If strZip <> "" Then
         rsTable1.Fields("zip").Value = CLng(strZip)
     End If
     
     rsTable1.Update
     Set updStore = rsTable1.Fields
     Exit Function
 
 HandleError:
     m_strLastError = "Could not update store"
     Set updStore = Nothing
     
 End Function
 
 ' Delete current store
 Public Function delStore(strID As String) As Boolean
     On Error GoTo HandleError
     
     Dim rsTable1 As ADODB.Recordset
     Dim strWhere As String
     m_strLastError = ""
     strWhere = "stor_id='" + strID + "'"
     Set rsTable1 = getStores()
     rsTable1.Find strWhere
     rsTable1.Delete
     delStore = True
     Exit Function
 HandleError:
     m_strLastError = "Could not delete store"
     delStore = False
 End Function
 
 ' Returns total title sales in dollars grouped by store
 Public Function salesbyStore() As ADODB.Recordset
     Dim rsTable1 As ADODB.Recordset
     Dim strWhere As String
     m_strLastError = ""
     Set rsTable1 = getRecordset("SELECT stores.stor_id, stores.stor_name, " + _
     SUM(sales.qty * titles.price) AS GrossSales " + _
               "FROM titles INNER JOIN sales ON titles.title_id = sales.title_id RIGHT OUTER JOIN " + _
 "stores ON sales.stor_id = stores.stor_id GROUP BY stores.stor_id, stores.stor_name")
     Set salesbyStore = rsTable1
     Exit Function
 HandleError:
     m_strLastError = "Could not compile sales data"
     Set salesbyStore = Nothing
 End Function
 
 Public Function topTitlesbyStore(strID As String) As ADODB.Recordset
     On Error GoTo HandleError    
     Dim rsTable1 As ADODB.Recordset
     Dim strWhere As String
     m_strLastError = ""
     strWhere = "stor_id='" + strID + "'"
     Set rsTable1 = getRecordset("Select titles.title_id, titles.title, sales.qty " +  _
     "from dbo.titles, dbo.sales " +  _
     where dbo.sales.title_id = dbo.titles.title_id and dbo.sales.stor_id = '" &  _
     strID & "' ORDER BY dbo.sales.qty DESC")
     Set rsTable1.ActiveConnection = Nothing
     Set topTitlesbyStore = rsTable1
     Exit Function
 HandleError:
     m_strLastError = "Could not find top title"
     Set topTitlesbyStore = Nothing
 End Function
 
 Private Sub Class_Initialize()
     m_fFilterByState = False
 End Sub


Figure 4   Sales.asp


 <SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server>
 
 Function dcStores_ongetrecordsource()
     if not IsEmpty(dcStores.getParameter(0)) then
         ' set state filter on object, if any
         dcStores.source.StateFilter = dcStores.getParameter(0)
     end if
     set dcStores_ongetrecordsource = dcStores.source.getStores()
 End Function
 
 Function dcStores_oninsert()
     dcStores.source.newStore  _
     dcStores.fields.getValue(0), _
     dcStores.fields.getValue(1), _
     dcStores.fields.getValue(2), _
     dcStores.fields.getValue(3), _
     dcStores.fields.getValue(4), _
     dcStores.fields.getValue(5)
 End Function
 
 Function dcStores_onupdate()
     dcStores.source.updStore _
     dcStores.fields.getValue(0), _
     dcStores.fields.getValue(1), _
     dcStores.fields.getValue(2), _
     dcStores.fields.getValue(3), _
     dcStores.fields.getValue(4), _
     dcStores.fields.getValue(5)
 End Function
 
 Function dcStores_ondelete()
     dcStores.source.delStore dcStores.fields.getValue(0)
 End Function
 
 Function dcSales_ongetrecordsource()
     set dcSales_ongetrecordsource = dcSales.source.topTitlesbyStore(dcSales.getParameter(0))
 End Function
     
 </SCRIPT>
 
 <SCRIPT ID=clientEventHandlersJS LANGUAGE=javascript>
 <!--
 
 // sets parameter on dcSales and requeries to refresh list of titles for current 	// store
 function updateStoreTitles() {
     dcSales.setParameter(0,dcStores.fields.getValue('stor_id'));
     dcSales.requery();
 }
 
 function lbStates_onchange() {
     dcStores.setParameter(0,lbStates.getValue(lbStates.selectedIndex));
     dcStores.requery();
 }
 
 function dcStores_onrowenter() {
     updateStoreTitles();
 }
 
 function dcStores_ondatasetcomplete() {
     updateStoreTitles();
 }
 
 function dcStores_onerror() {
     if (dcStores.errDescription!='')
         alert(dcStores.errDescription);
 }
 
 function btDelete_onclick() {
     if (confirm("Are you sure you want to delete " + _ 
                dcStores.fields.getValue("stor_name")+"?")) {
         dcStores.deleteRecord();
     }
 }
 
 function btInsert_onclick() {
     dcStores.addRecord();
 }
 
 function btUpdate_onclick() {
     dcStores.updateRecord();
 }
 
 //-->
 </SCRIPT>