Okay, so I have done this successfully before but now when I try to replicate the FTS5 full text search I get the following error: “no such table: virtTblProdList”
Here is the setup:
Calling Window has a property value set
Name: srchDBase
Type: SQLiteDatabase
Default:
Scope: Public
On Window.Open
’ Primary Variable Declaration(s)
srchDbase = New SQLiteDatabase
Var rowSet As RowSet
' Function(s) / Logic...
txtItemSearch.SetFocus
mthSetupSrchDB
mthAddValues
Method.Name: mthSetupSrchDB
Parameters:
Return Type:
Scope:
' Function(s) / Logic...
Try
srchDbase.Connect
Catch err as DatabaseException
MessageDialog.Show("Error: " + err.Message)
Return
End Try
// Create FTS5 table
Try
srchDbase.ExecuteSQL( "CREATE VIRTUAL TABLE virtTblProdList USING fts5( barCode, prodDescrip, vendorPartNum, sellPrice, taxable );" )
Catch err As DatabaseException
MessageDialog.Show("Error: " + err.Message)
Return
End Try
Method.Name: mthAddValues
Parameters:
Return Type:
Scope:
' Primary Variable Declaration(s)
Var rowSel As RowSet = modSel.mthSelProdList( 0, "" )
Var strBarCode, strProdDesc, strSellPrice, strTaxable, strVendPartNum As String
' Function(s) / Logic...
Try
For Each row As DatabaseRow In rowSel
// Add each product item to the virtual table for searching
strBarCode = rowSel.Column( "barCode" ).StringValue
strProdDesc = rowSel.Column( "productName" ).StringValue
strVendPartNum = rowSel.Column( "vendorPartNum" ).StringValue
strSellPrice = rowSel.Column( "sellPrice" ).StringValue
strTaxable = rowSel.Column( "taxable" ).StringValue
srchDbase.ExecuteSQL( "INSERT INTO virtTblProdList( barCode, prodDescrip, vendorPartNum, sellPrice, taxable ) VALUES( '" + strBarCode + "','" + strProdDesc + "','" + strVendPartNum + "','" + strSellPrice + "','" + strTaxable + "')" )
Next
Catch err As DatabaseException
MessageDialog.Show( "Error: " + err.Message )
End Try
And the “Trigger” to search:
TextBox.KeyDown
blnTest = IsNumeric( Me.Value ) ' Test for barcode value
If( blnTest ) = True Then
rowSel = mthSrchByBarcode( Me.Value ) ' Search by barcode...
iFuncSelect = 2 // Call
Else
rowSel = mthSrchByVpartNum( Me.Value ) ' Search by vPartNum
If( rowSel = Nil ) Then
mthSrchByFullText( Me.Value ) // Search by characters...
Else
iFuncSelect = 2
End If
End If
And finally one of the search methods
Method.Name: mthSearchByBarcode
Parameters: strSearchParam As String
Return Type: RowSet
Scope: Public
' Primary Variable Declaration(s)
Var rowSel As RowSet
' Function(s) / Logic...
// This queries by barcode only...
Try
rowSel = dbJHGCGC.SelectSQL( "SELECT * FROM virtTblProdList WHERE barCode=?", strSearchParam )
If( rowSel <> Nil ) Then
Return rowSel
rowSel.Close
End If
Catch error As DatabaseException
MessageBox( "Error: " + error.Message )
End Try