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>