source db

#Region Project Attributes
#ApplicationLabel: Sql Exercise
#VersionCode: 1
#VersionName:'SupportedOrientations possible values: unspecified, landscape or portrait.
#SupportedOrientations: portrait
#CanInstallToExternalStorage:False

#End Region
#Region Activity Attributes
#FullScreen: False
#IncludeTitle: True
#End Region


Sub Process_Globals
'These global variables will be declared once when the application starts.
'These variables can be accessed from all modules.
Dim SQL1 As SQL
Dim cursor1 As Cursor
End Sub


Sub Globals
'These global variables will be redeclared each time the activity is created.
'These variables can only be accessed from this module.
Private txtUsername As EditText
Private txtPassword As EditText
Private LVDb As ListView
Private cmdAdd As Button
Private cmdDelete As Button
Private cmdEdit As Button
Private cmdExit As Button
Private ID As String
End Sub
Sub Activity_Create(FirstTime As Boolean)

	Activity.LoadLayout("lay1")
	If File.Exists(File.DirInternal,"pbu.sql") = False Then
	File.Copy(File.DirAssets,"pbu.sql",File.DirInternal,"pbu.sql")
	End If
	If SQL1.IsInitialized = False Then
	SQL1.Initialize(File.DirInternal, "pbu.sql", False)
	End If
	DBload' Call Function DBload
	End Sub




Sub DBload
	LVDb.Clear'need to clear the list
	cursor1 = SQL1.ExecQuery("SELECT * FROM tblUser")
	For i =0 To cursor1.RowCount -1
	cursor1.Position = i
	LVDb.AddSingleLine(cursor1.GetString("ID")&"|" &cursor1.GetString("Username")&" | "& cursor1.GetString("Password"))
	LVDb.SingleLineLayout.ItemHeight =40
	LVDb.SingleLineLayout.Label.TextSize =10
	LVDb.SingleLineLayout.label.TextColor =Colors.Black
	LVDb.SingleLineLayout.label.Color =Colors.White
	Next
	End Sub


Sub cmdAdd_Click

	If txtUsername.Text ="" Or txtPassword.Text ="" Then
	Msgbox("You have to enter all fields","Missed data field")
	Else
	'Grab the last ID number which is the highest number
	cursor1 = SQL1.ExecQuery("SELECT ID FROM tblUser")
	If cursor1.RowCount >0 Then
	For i =0 To cursor1.RowCount -1
		cursor1.Position = i
	Dim NewID As Int
	NewID = cursor1.GetInt("ID")
	Next
	End If
	NewID = NewID +1' add 1 to the ID number to make a new ID Field
	SQL1.ExecNonQuery("INSERT INTO tblUser VALUES('"& NewID & "','"& txtUsername.Text&"','"&txtPassword.Text&"')")
	DBload
	txtUsername.Text =" "
	txtPassword.Text =	""
	txtUsername.RequestFocus
	End If

End Sub


Sub cmdDelete_Click
	SQL1.ExecNonQuery("DELETE FROM tblUser where ID = '"& ID &"' ")
	DBload
	txtUsername.Text =""
	txtPassword.Text=""
End Sub


Sub LVDb_ItemClick (Position As Int, Value As Object)' click on the entry in the list

		Dim idvalue As String
		Dim countIt As Int
		idvalue = Value
		countIt = idvalue.IndexOf("|") 'find location of sperator
		idvalue = idvalue.SubString2(0,countIt) 'find first part of label
		ID = idvalue
		cursor1 = SQL1.ExecQuery("SELECT * FROM tblUser where ID = '"& ID &"' ")
		For i =0 To cursor1.RowCount -1
			cursor1.Position = i
		txtUsername.text=cursor1.getString("Username")
		txtPassword.text=cursor1.getString("Password")
		Next

End Sub


Sub cmdEdit_Click

		If txtUsername.Text ="" Or txtPassword.Text ="" Then
		Msgbox("Select item to edit","Missed data item")
		Else
		SQL1.ExecNonQuery("UPDATE tblUser set Username ='"&txtUsername.text&"', Password ='"&txtPassword.text&"' WHERE ID = "& ID)
		DBload

End If

End Sub

Sub cmdExit_Click

	Activity.Finish
End Sub