SQLite EASY example

[BackupUser]

Restored from previous forum. Originally posted by ricardo.

Hi,

Thanks to Manolo & El_Choni now i get how to work easily with SQLite.

This a a fully working little example that let the user store, retreive and search in a database.
I will post it at resources site once its checked and fixed (if its necessary), so please test it. Thanks.

Code: Select all

;NOTE: THIS EXAMPLE WILL ONLY WORK If YOU USE EL_CHONIS SQLITE LIBRARY
;DOWNLOADIT FROM www.reelmediaproductiosn.com/pb search in ASM Libs.

; PureBasic Visual Designer v3.62


;- Window Constants
;
#Window_0 = 0

;- Gadget Constants
;
#Id = 99
#Name = 0
#Web = 1
#Email = 2
#NameLabel = 3
#WebLabel = 4
#EmailLabel = 5
#ClearButton = 6
#SaveButton = 7
#DeleteButton = 8
#QuerryLabel = 9
#Querry = 10
#QuerryButton = 11
#NextButton = 12
#PrevButton = 13

;-SQLite

InitSQLite()
Global DBHandle.l
Global Cont.l ;> buttons to see it",0)
  EndIf
EndIf
EndProcedure



Procedure NextResult()
If Num  1
Num = Num - 1
SetGadgetText(#Id,SQLiteData(Num,0))
SetGadgetText(#Name,SQLiteData(Num,1))
SetGadgetText(#Web,SQLiteData(Num,2))
SetGadgetText(#Email,SQLiteData(Num,3))
Beep_(1000,30)
EndIf
EndProcedure




Procedure Open_Window_0()
  If OpenWindow(#Window_0, 217, 6, 402, 259,  #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_TitleBar , "SQLite EASY example")
    If CreateGadgetList(WindowID())
      TextGadget(#Id,60, 0, 305, 20, Str(Cont))
      StringGadget(#Name, 60, 20, 305, 20, "")
      StringGadget(#Web, 60, 50, 305, 20, "")
      StringGadget(#Email, 60, 80, 305, 20, "")
      TextGadget(#NameLabel, 10, 20, 40, 20, "Name")
      TextGadget(#WebLabel, 10, 50, 40, 20, "Web")
      TextGadget(#EmailLabel, 10, 80, 40, 20, "e mail")
      ButtonGadget(#ClearButton, 50, 125, 65, 20, "Clear")
      ButtonGadget(#SaveButton, 310, 125, 65, 20, "Save", #PB_Button_Default)
      ButtonGadget(#DeleteButton, 240, 125, 65, 20, "Delete");>")
      ButtonGadget(#PrevButton, 160, 230, 55, 20, "<<")
      DisableGadget(#NextButton,1)
      DisableGadget(#PrevButton,1)
    EndIf
  EndIf
EndProcedure


Open_Window_0()

  Repeat
    EventID=WaitWindowEvent()
    
    Select EventID
    
      Case #PB_EventGadget
        Select EventGadgetID()
          Case #SaveButton
            DisableGadget(#NextButton,1)
            DisableGadget(#PrevButton,1)
            Save2SQLite()
          Case #QuerryButton
            QuerrySQLite(GetGadgetText(#Querry))
          Case #NextButton
            NextResult()
          Case #PrevButton
            PrevResult()
          Case #ClearButton
            SQLiteGetTable("SELECT Num FROM Test WHERE Num LIKE '%'");Ops a little trciky!!
            SetGadgetText(#Id,Str(SQLiteRows()+1))
            SetGadgetText(#Name,"")
            SetGadgetText(#Web,"")
            SetGadgetText(#Email,"")
          Case #DeleteButton
            ;DELETING are not working... how to delete one file from records?
            MessageRequester("Ops!","Cant delete at this time  ",0)
        EndSelect
    
    EndSelect
    
  Until EventID=#PB_EventCloseWindow
End

Best Regards

Ricardo

Dont cry for me Argentina...

[BackupUser]

Restored from previous forum. Originally posted by Manolo.

>;DELETING are not working... how to delete one file from records?


Is very easy Ricardo,

SQL.S="DELETE FROM Test WHERE Id= '"+Id$+'"

or if you want DELETE all table:

SQL.s="DROP TABLE Test"
and SQLiteExecute(SQL) and "pataplafff" "Te has pegado una hostia". Hehehe. ByBy table...

In the last case, is very dangerous.

Please, change you "lema", I don't cry mooooreeeeeeee.

Manolo

[BackupUser]

Restored from previous forum. Originally posted by Num3.

A little bit more here:

sql.s = "Select * FROM test ORDER by id"

to get the output from the table sorted by the 'id' field....

--
Kind Regards
Rui Carvalho

Old programmers never die... They branch into a subroutine...

[BackupUser]

Restored from previous forum. Originally posted by Manolo.

And other question,
You save null gadgets???. Nooooo. This is one error.
Check this before save.

And other:

"CREATE TABLE Test (Id, Num, Name, Web, Email)"
This is the consecuence...

If you change this for...

"CREATE TABLE Test (Id primary key unique, Num, Name, Web, Email)"

You can check the error 19 (Dup key)

Result=SQLiteExec("INSERT ........

If Result=19
MesageRequester("Error","The Id "+str(key)+" Esist"+chr(10)+"Do you want change???, ????Yes/not (I dont remember in this moment))

If yes
UPDATE ...... etc, etc
Else
Dont cry for me Ricardo. I love the peace.

[BackupUser]

Restored from previous forum. Originally posted by Manolo.

And other question,
You save null gadgets???. Nooooo. This is one error.
Check this before save.

And other:

"CREATE TABLE Test (Id, Num, Name, Web, Email)"
This is the consecuence...

If you change this for...

"CREATE TABLE Test (Id primary key unique, Num, Name, Web, Email)"

You can check the error 19 (Dup key)

Result=SQLiteExec("INSERT ........

If Result=19
MesageRequester("Error","The Id "+str(key)+" Esist"+chr(10)+"Do you want change???, ????Yes/not (I dont remember in this moment))

If yes
UPDATE ...... etc, etc
Else
Dont cry for me Ricardo. I love the peace.
Endif

Manolo

[BackupUser]

Restored from previous forum. Originally posted by ricardo.

Hi Manolo Thanks for the SQL lesson

Best Regards

Ricardo

Dont cry anymore...

[BackupUser]

Restored from previous forum. Originally posted by ricardo.

Hi,

The example is finished an flying from Argentina to Canada ot the resources site.

Example finished available at:

http://www.reelmediaproductions.com/pb/

Best Regards

Ricardo

Dont cry anymore...