'SQLite -- Show some simple SQLite operations
'This program runs on Windows XP, 2000 and CE
'This program will compile in NS Basic/Desktop and NS Basic/CE.
'See http://www.nsbasic.com for more information

'This program show how to create a simple SQLite database and do additions and deletions..
'The code may be copied freely.

AddObject "newObjects.sqlite.dbutf8","db"
Dim records	'The current record set
Dim rec		'The current record number

openDatabase
	
Sub Form1_Load
	form1.caption="SQLite Demo"
	showStatus "Welcome to SQLite Version " & db.SQLiteVersion
	showStatus "NameDB database opened with " & records.count & " records."
	
	'Display the first record in the database
	rec=1
	displayRecord(rec)	
	
End Sub

Sub cbSave_Click
	Dim cmd 
	Dim r
	If tbLastName.text="" Then Exit Sub
		
	cmd="INSERT OR REPLACE INTO  ""NameDB""  VALUES( """ & tbFirstName.text & """,""" & tbLastName.text &""",""" & tbAge.text & """)"
	showstatus cmd
	
	On Error resume next
	Set r=db.Execute(cmd)
	On Error Goto 0
	If db.LastError<>"" Then MsgBox "INSERT error: " & db.lasterror
		
	'Get new selection, now that a record has been added
	Set records=db.Execute("Select * from NameDB")
	showStatus  "Database now has " & records.count & " records."

End Sub
	
Sub cbDelete_Click
	Dim cmd
	cmd="DELETE FROM NameDB WHERE lastname = """ & tbLastName.Text & """"
	showStatus cmd
	
	On Error resume next
	Set r=db.Execute(cmd)
	On Error Goto 0
	If db.LastError<>"" Then MsgBox "DELETE error: " & db.lasterror
		
	'Get new selection, now that a record has been added
	Set records=db.Execute("Select * from NameDB")
	showStatus "Database now has " & records.count & " records." 
	
	'display new record since current one was deleted
	If rec>1 Then
		rec=rec-1
	Else
		rec=records.count
	End If
	displayRecord(rec)
	
End Sub
	
Sub openDatabase
	'Open the database
	If db.Open("Name.db")  Then 'create the database if it doesn't exist yet
		db.AutoType = True
		db.TypeInfoLevel = 4
	Else
		MsgBox "Error on db.Open: " & db.lastError
	End If
		
	'Get the initial selection of all records
	On Error resume next
	Set records=db.Execute("Select * from NameDB")
	On Error Goto 0
	If  Left(db.lasterror, 13)="no such table" Then Call createDatabase

End Sub

Sub createDatabase
	Dim cmd
	Dim res
	cmd="CREATE TABLE NameDB(""FirstName"", ""LastName"","" Age"", PRIMARY KEY(""FirstName""))"

	On Error resume next
	Set res=db.Execute(cmd)
	On Error Goto 0
	If db.lasterror<>"" Then MsgBox "Create Table error " & db.lasterror
		
	'Get the initial selection of records - it will be empty at first
	Set records=db.Execute("Select * from NameDB")
	
End Sub

Sub cbForward_Click
	If rec<records.count Then rec=rec+1
	displayRecord(rec)	
End Sub

Sub cbBack_Click
	If rec<1 Then rec=rec-1
	displayRecord(rec)
End Sub

Sub displayRecord(n)
	If n<1 Or n>records.count Then Exit Sub
	tbFirstName.Text = 	records(n)(1)
	tbLastName.Text = records(n)(2)
	tbAge.Text = records(n)(3)
	showStatus "Record " & n & " of " & records.count 
End Sub

Sub showStatus(txt)
	tbStatus.text=txt & vbcrlf & tbStatus.Text
End Sub

Sub cbList_DropDown
	cbList.clear
	For row=1 To records.count
		cbList.addItem records(row)(1) & " " & records(row)(2)
	Next
End Sub

Sub cbList_Click
	rec=cbList.ListIndex+1
	displayRecord(rec)
End Sub




'*** Begin Generated Code ***

Dim AppEXEName: AppEXEName = "SQLite"
Dim AppPath: AppPath = "E:\Customer Programs\SQLite.txt"

Form1_Show 'Default Form

Dim Form1_Temp
Sub Form1_Show
   On Error Resume Next

   UpdateScreen

   If Not IsObject(Form1_Temp) Then
      AddObject "Form", "Form1", 0, 0, 240, 320
      Form1.Visible = False
      Form1.Caption = "Form1"
      Set Form1_Temp = Form1

      AddObject "Label", "lbFirstName", 12, 16, 56, 21, Form1
      lbFirstName.BackColor = 12632256
      lbFirstName.Caption = "First Name"
      lbFirstName.FontSize =  8.25
      '--------
      AddObject "Label", "lbLastName", 12, 44, 56, 21, Form1
      lbLastName.BackColor = 12632256
      lbLastName.Caption = "Last Name"
      lbLastName.FontSize =  8.25
      '--------
      AddObject "Label", "lbAge", 12, 72, 56, 21, Form1
      lbAge.BackColor = 12632256
      lbAge.Caption = "Age"
      lbAge.FontSize =  8.25
      '--------
      AddObject "TextBox", "tbFirstName", 112, 16, 120, 20, Form1
      tbFirstName.BackColor = 16777215
      tbFirstName.FontSize =  8.25
      '--------
      AddObject "TextBox", "tbLastName", 112, 44, 120, 20, Form1
      tbLastName.BackColor = 16777215
      tbLastName.FontSize =  8.25
      '--------
      AddObject "TextBox", "tbAge", 112, 72, 120, 20, Form1
      tbAge.BackColor = 16777215
      tbAge.FontSize =  8.25
      '--------
      AddObject "CommandButton", "cbSave", 48, 104, 68, 20, Form1
      cbSave.Caption = "Save"
      cbSave.FontSize =  8.25
      '--------
      AddObject "CommandButton", "cbDelete", 124, 104, 68, 20, Form1
      cbDelete.Caption = "Delete"
      cbDelete.FontSize =  8.25
      '--------
      AddObject "CommandButton", "cbBack", 12, 104, 20, 20, Form1
      cbBack.Caption = "<"
      cbBack.FontBold = True
      cbBack.FontSize =  8.25
      '--------
      AddObject "CommandButton", "cbForward", 212, 104, 20, 21, Form1
      cbForward.Caption = ">"
      cbForward.FontBold = True
      cbForward.FontSize =  8.25
      '--------
      AddObject "TextBox", "tbStatus", 12, 160, 220, 124, Form1
      tbStatus.BackColor = 16777215
      tbStatus.FontSize =  8.25
      tbStatus.MaxLength = 1000
      tbStatus.Multiline = True
      tbStatus.Scrollbars = 3
      '--------
      AddObject "ComboBox", "cbList", 12, 132, 220, 100, Form1
      cbList.BackColor = 16777215
      cbList.FontSize =  8.25
      '--------
   End If
   Form1.Visible = True
   Form1_Load
End Sub  'Form1_Show

Sub Form1_Hide
   If Not IsObject(Form1_Temp) Then
      Err.Raise 44000, , "Form not loaded"
      Exit Sub
   End If

   On Error Resume Next
   Form1.Visible = False
   Form1_Unload
End Sub  'Form1_Hide
'*** End Generated Code ***