Define application wide lookup tables from a variaty of data sources.

dbQP Abbr: ct
Version: All

Namespace:  Tornado
Assembly:  Tornado (in Tornado.dll) Version: 12.6.0.9 (1.0.0.0)

Syntax

C#
public string dbCommonTables { get; set; }
Visual Basic (Declaration)
Public Property dbCommonTables As String

Field Value

Obj.dbCommonTables = "(;,~)New| Index| SQL=Select..~DSN~Provider| Value| File; ....repeat "

* Note that first group delimiter [;] is used to delimit the indexes.

KeywordDescription
NewNew=True|False(Def). 'True' will cause the common tables to be re-built upon every re-entrance. A popular reason is that the SQL used to generate the 'Lists' changes in every entry.
IndexIndex=Name1,Name2,.... Index is a comma (2nd delimiter) delimited Lists names. A list can be used as Master/Keyword or Slave/Lookup. Example - Index="Name,SS" - You can use Name to lookup SS or use SS to lookup Name.
SQLSQL=Statement~DSN~Provider. This is the SQL statement that generates the same amount of fields as the Index. If index=Name,SS then SQL should be "SELECT Name, SS from Table order by Name". If index=SS,Name then SQL="SELECT SS, Name from Table Order by SS. SQL fill the list according to Index. You can specify a different datasource and provider for the query using delimiter #2 (default=~). Watch out for uneven lists for a draw of list values from different sources. For Lookup, it returns blanks and for edit, the value is length is truncated to the textlist length.

DSN = dbDSN style

Provider = ODBC, OLEDB or SQLClient. Def=dbProvider

Value Value=Data Lists. Instead of using SQL to generate the lists, you can use hard coded data. Values of list will be fill the index. Example Index=A | Value=1,2,3; Ind=B|SQL = Select...(results in 4,5,6); Ind=C|File=....(results in 7,8,9)
abc
147
258
369
File File=Name,Block. Name = name of file. Can be virtual or physical. Block is the <block Id>...</block Id> to read from. The CR will be replaced by delimiter (,) and it will fill the index the same way as the "Value". So you can imagine this option enables you to switch lookup tables by just specifying a different <Block id>. When File is specified, other parameters will be processed in that group. The format of this file is -

<Selection1>

[Index_Name1],v1,v2,v3....

[Index_Name2],d1,d2,d3,d4,d5,d6

</Selection1>

ONE list per line without any <CR> in between.

Note: File generates the list in a stand alone mode and does not depends on index. So, they can be mixed in the same group.

Remarks

  • The common 'Tables/Lists/Columns' used by lookups and dropdowns. Data from sources like Hard Code, DB (SQL), Global.asax, Application and CommonFile is designed to build these lists.
  • Do not mix Value with SQL and File. Use mltiple values as valuelise1~valuelist2. Do not use Valuelist1~SQL=...

    Obj.dbCommonTables = "(;,~)ind=DUMMY,EID|val=1-5~1-75; ind=EName| SQL= Select lastname from employees"

Examples

CopyVB.NET
Dim GD As New Tornado.GetData
Dim Cf As New Tornado.z
With Cf
   .dbQP = "U=5| nh=t| M=ty=SBox!SboxMacro=(<<statesvalue>>~<<statestext>>)"
   .dbTextHolder = "Title=SelectBox via user data"
   .ASPdbNET()
   Response.Write(GD.Get_Sbox(5))
   Response.Write(GD.Get_SelectBoxSelection(5))
End With
 File = c:\inetpub\wwwroot\bin\ASPdbCommon.txt
CopyVB.NET
<StatesText>
Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,
District of Columbia,Florida,Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,
Kentucky,Louisiana, Maine,Maryland,Massachusetts,Michigan,Minnesota,Mississippi,
Missouri,Montana,Nebraska,Nevada,New Hampshire,New Jersey,New Mexico,New York,
North Carolina,North Dakota,Ohio,Oklahoma,Oregon,Pennsylvania,Rhode Island,
South Carolina,South Dakota,Tennessee,Texas,Utah,Vermont,Virginia,Washington,
West Virginia,Wisconsin,Wyoming
</StatesText>

<StatesValue>
AL,AK,AZ,AR,CA,CO,CT,DE,DC,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MN,MS,MO,
MT,NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY
</StatesValue>
 Manual Edit Dropdown
CopyVB.NET
Dim X As New Tornado.z
X.dbQP = "U=1|S=1|D=NWIND|ni=b5,update|Q=EMPLOYEES| bm=EMPLOYEES;0| gdf=0,1,2,4"
X.dbTextHolder = "Title=Manual DropDown"
X.dbCommonTables = "(;,~)Index=TOC|Value=Mr.,Mrs.,Ms.,Dr."
X.dbEditUpdateFlds = "fi=0|Ty=RONOUP,fi=TitleOfCourtesy|Ty=Selectbox|Text=TOC|Val=TOC"
X.ASPdbNET()

See Also