Test for 'blank table' and 'no return records' before read/write actions.
Obj.dbTestBlankTable = "TableName| Action| TestSQL| DSN | BlankDBURL | ErrorURL| EveryTime
| Keyword | Description |
|---|---|
| TableName | Name of table to be tested. (Mandatory parameter) |
| Action | Add, Exit, BlankURL, or ErrorURL. Action when table is blank - goto Add mode or Exit to different URLs. (Def = Exit) |
| TestSQL | SQL used to test for blank table. Do not use Count type SQL. Use normal type like Select ID FROM MyTable. Do not include any unnecessary SQL clauses in the statement. The purpose is to open the table and see whether there is any records in it. If this parameter is missing. System will create an SQL as "SELECT * from Tablename". |
| DSN | Connection string for the table. If missing, the dbDSN property will be used. |
| BlankDBURL | Redirected to this URL when Action=BlankURL and DB is blank. The variable passed with the URL is ?Code=BlankDB. If http:// is not present then it'll be added |
| ErrorURL | Redirected to this URL when Action=URL and DB is blank. The variable passed with the URL is ?Code=ConnectionError. If http:// is not present then it'll be added |
| EveryTime | True | False (def). Test for blank everytime. Default is testBlank will only run once at first entry unless set to true. You need this test if you are conducting a serial multi-module operations to detect the query built with a master index result in blank or not. |
Return codes after test -
This example test for blank and action is BlankURL.
<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim Q As New Tornado.Z()
With Q
.dbQP = "U=3|DSN=Nwind|TextHolder=Title=TestBlankTable|skin=5"
.dbSQL = "SELECT * From BlankTable"
.dbNavigationItem = "Basic5"
.dbBookMark = "blanktable; 0"
.dbStartUp = "editadd"
.dbEditAddFlds = "0,1,2"
'...This prop will cause a branch off to www.aspdb.com if DB is blank.
.dbTestBlankTable = "Table=blanktable| Action=BlankDBURL|" & _
"TestSQL=SELECT ID FROM blanktable| BlankDBURL=www.aspdb.com"
.ASPdbNET()
End With
End Sub
</script>
Delete all the records of a particular 'Custom' in the Northwind 'Orders' table and execute the follwoing example. When you select the master table that points to the 'missing' details, the dbTestBlankTable will kick in and perform the 'Add' function. <script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim GD As New Tornado.Getdata()
Dim SysIndex As New Tornado.Z()
With SysIndex
.dbQP = "U=5|s=ASPdbBB|xf=0,1|gdf=0,1,2,3,4|d=nwind.mdb|" & _
"th=title=TestBlank Demo!sub=Delete all records of a " & _
"customer ID in Orders Table to test"
.dbMode = "type=Grid| sysindex=true"
.dbSQL = "Select * From customers"
.ASPdbNET()
End With
Response.Write("<Center><P><table class=ts border='0' cellpadding=2
cellspacing=1><tr><td class='GH' COLSPAN=2>
Export CustomerID Field</td><tr>")
Dim c As Integer
Dim r As Long
Dim arr() As String = GD.Get_Fexport
For c = 0 To UBound(arr)
Response.Write("<td class=nr align=center>" & arr(c) & "</td>")
Next c
Response.Write("</tr></table></center>")
Response.Write("<center><P><B>Now use the exported
Master data to produce corresponding Details Query</B><P>")
Dim z2 As New Tornado.Z()
With z2
.dbQP = "u=28| m=type=g| D=nwind| ps=-1| ni=none| de=Activesql| nh=t"
Dim sql As String = "Select * From orders WHERE customerid ='" & arr(0) & "'"
.dbCommonTables = "ind=EID,ENAM| SQL=SELECT Employeeid," & _
"FirstName & ' ' & Lastname FROM Employees; ind=CID,CNAM|" & _
"SQL=SELECT DISTINCTROW CustomerID,CompanyName FROM Customers;" & _
"ind=SHIPID,SHIPPER| SQL=SELECT ShipperID, CompanyName FROM Shippers"
.dbEditAddFlds = "fi=1| ty=ROADD| Def=" & arr(0) & ", fi=2| ty=SB|" & _
"val=EID| Tex=ENAM, fi=3|Ty=TEXT|Def=now(MMM/dd/yy),fi=6| Ty=RA|" & _
"Val=SHIPID|Tex=SHIPPER|def=1,4,5,6"
.dbSQL = sql
.dbBookMark = "Orders;0"
.dbTestBlankTable = "Table=orders| everytime=t| Action=Add| TestSQL=" & sql
.ASPdbNET()
End With
Response.Write("Test Blank Return Code =" & GD.Get_ReturnCode("28"))
End Sub
</script>
The following code detects a zero row return of a valid query - <script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim Q As New Tornado.Z()
With Q
.dbQP = "U=3|DSN=Nwind|TextHolder=Title=No Match Test|skin=5"
.dbSQL = "SELECT * From employees where employeeid = 100"
'...This prop will cause a branch off to www.aspdb.com if query returns no data.
.dbTestBlankTable = "Table=blanktable| Action=BlankDBURL|" & _
"TestSQL=SELECT * From employees where employeeid = 100 | BlankdbURL=www.yahoo.com"
.ASPdbNET()
End With
End Sub
</script>
z Class | Tornado Namespace | Get_ReturnCode