• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

The LIKE Operator

Started by Fredrick Ughimi, June 03, 2014, 10:51:18 AM

Previous topic - Next topic

Fredrick Ughimi

Hello,

I wrote this a while ago but it isn't giving me what I want exactly. I want a LIKE query based on  first, second third, etc letters entered.

Can some one point me in the right direction.

Thank you.


Method SearcEaterySaleshListView(ByVal nCbHndl As Long) As Long
        Local iItem&
        Local sDescription As String
        Local sProductNo As String 
        Local sDate As String
       
        VD_ListView_Item_Delete(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem)
           
        sDate = VD_GetText (nCbHndl, %ID_FRMUPDATEEATERYSALES_DPKDATE)
       
        Control Get Text nCbHndl, %ID_FRMUPDATEEATERYSALES_TXTSEARCH To sDescription
       
        slSEL "SELECT * from tblMainInventory WHERE Description LIKE '" + sDescription + "%'"
       
        'Do While slGetRow   
       
        If IsTrue slGetRow() Then
                       
            iItem = VD_ListView_Item_Add(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, slFN("ProductNo"), 0)
            VD_ListView_Item_Text(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem, 1, slFN("Description"))
            VD_ListView_Item_Text(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem, 2, slFN("CurrentQuantity"))
            VD_ListView_Item_Text(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem, 3, slFN("SellingPrice"))
       
        End If
        slCloseSet
             
        'Loop 
       
   End Method

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Mike Doty

#1
Michael Mattias read better on PowerBASIC BBS than me.
http://www.powerbasic.com/support/pbforums/showthread.php?t=54981
Want to match on first 3 characters using SUBSTR.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL sArray() AS STRING
  LOCAL sDescription AS STRING
  LOCAL SearchFor    AS STRING
  slopen "sample.db3"
  slexe  "create table if not exists tblMainInventory(description)"
  sDescription = "HOW now brown cow"
  slexe  "insert into tblMainInventory values('how now brown cow')
  SearchFor = LEFT$(sDescription,3)
  slSelAry "Select * from tblMainInventory Where SUBSTR(Description,1,3) LIKE '" + SearchFor + "%'",sArray(),"Q9"
  ? JOIN$(sArray(),$CRLF),,"SQLitening is great"
END FUNCTION

Fredrick Ughimi

Hello,

Thank you Mike. It works perfect now. The problem was, I used:


VD_ListView_Items_Delete(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem&)


Instead of:


VD_ListView_Items_Delete(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, %TRUE)


to clear the Listview when neccesary. I honestly don't know how/why I used the first option.

Even my initial code worked when placed in a loop.


Method SearcEaterySaleshListView(ByVal nCbHndl As Long) As Long
        Local iItem&
        Local sDescription As String
        Local sProductNo As String 
         
        VD_ListView_Items_Delete(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, %TRUE)
         
        Control Get Text nCbHndl, %ID_FRMUPDATEEATERYSALES_TXTSEARCH To sDescription
           
        slSEL "SELECT * from tblMainInventory WHERE Description LIKE '" + sDescription + "%' ORDER BY Description"
       
        Do While slGetRow   
                       
            iItem = VD_ListView_Item_Add(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, slFN("ProductNo"), 0)
            VD_ListView_Item_Text(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem, 1, slFN("Description"))
            VD_ListView_Item_Text(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem, 2, slFN("CurrentQuantity"))
            VD_ListView_Item_Text(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, iItem, 3, slFN("SellingPrice"))
       
        Loop 
       
    End Method 


But I had to place this codes in the CHANGE EVENT of my Search TEXTBOX:


Local sSearch As String

Sales.SearcEaterySaleshListView(nCbHndl)

sSearch = VD_GetText (nCbHndl, %ID_FRMUPDATEEATERYSALES_TXTSEARCH)
If Trim$(sSearch) = "" Then
     VD_ListView_Items_Delete(nCbHndl, %ID_FRMUPDATEEATERYSALES_LISTVIEW1, %TRUE)
End If
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet