Simple SQL Deletion Query

[bishmedia]

Ok im new to this but have some experience in VB.

I have a listbox which shows Surname, First name merged together and now i want to add a deletion on the long hold but however i position the "" or () i get an error, this is what i have so far.

Sub ListView1_ItemLongClick (Position As Int, Value As Object)
cur = SQL1.ExecQuery(sqltxt)
SQL1.ExecNonQuery("DELETE FROM People WHERE("cur.GetString("FirstName") & ", " & (cur.GetString("surname")") = Value
End Sub

I tried looking through the posts on here but coulnt find anything like this

Look forward to some help

 

[Mahares]

I did not get to test the full syntax, but something like this:

Sub ListView1_ItemLongClick (Position As Int, Value As Object)
   Dim Name() As String
   Name=Regex.Split(",",Value)  'Name(0) is first name, Name(1) is Surname in that order
   SQL1.ExecNonQuery("DELETE FROM People WHERE FirstName= ? AND Surname= ?", Array As String(Name(0),Name(1)))
End Sub

Here is an equivalent to the line of SQL1.ExecNonQuery:

SQL1.ExecNonQuery("DELETE FROM People WHERE FirstName= ? AND Surname= ?", Name)

 

[bishmedia]

hmmm this is with yours added but i get an error trying to run it

'Activity module
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

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<Begin - Change for Database>>>>>>>>>>>>>>>>>>>>>>>>>>
Dim DBFileName As String : DBFileName = "Contacts.db"
Dim DBTableName As String : DBTableName = "People"
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<End - Change for Database>>>>>>>>>>>>>>>>>>>>>>>>>>

' Dim DBFileDir As String : DBFileDir = File.DirInternal
Dim DBFileDir As String : DBFileDir = File.DirDefaultExternal
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.

Dim ListView1 As ListView
Dim sqltxt As String
Dim cur As Cursor

Dim Label1 As Label
Dim btnAdd As Button
Dim txtName As EditText
Dim txtSurname As EditText
Dim txtTown As EditText
End Sub

Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
Dim TargetDir As String
If File.ExternalWritable Then TargetDir = File.DirDefaultExternal Else TargetDir = File.DirInternal
If File.Exists(TargetDir, DBFileName) = False Then
File.Copy(File.DirAssets, DBFileName, TargetDir, DBFileName)
End If

End If

SQL1.Initialize(DBFileDir, DBFileName, True)

Activity.LoadLayout("listviewbb")

sqltxt = "SELECT * FROM People ORDER by FirstName"
ListViewInit
ListViewFill

End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub ListViewInit

ListView1.SingleLineLayout.ItemHeight = 40dip
ListView1.SingleLineLayout.Label.Left = 0
ListView1.SingleLineLayout.Label.Height = 40dip
ListView1.SingleLineLayout.Label.Gravity = Gravity.CENTER_VERTICAL
ListView1.SingleLineLayout.Label.Color = Colors.White
ListView1.SingleLineLayout.Label.TextSize = 15
ListView1.SingleLineLayout.Label.TextColor = Colors.Black
ListView1.FastScrollEnabled = True

End Sub

Sub ListViewFill

ListView1.Clear
cur = SQL1.ExecQuery(sqltxt)

For i = 0 To cur.RowCount - 1
cur.Position = i
ListView1.AddSingleLine(cur.GetString("FirstName") & ", " & (cur.GetString("Surname")))

Next

End Sub

Sub ListView1_ItemClick (Position As Int, Value As Object)
Dim townlabel As String

Label1.Text = Value

cur = SQL1.ExecQuery(sqltxt)

For i = 0 To cur.RowCount - 1
cur.Position = i
If cur.GetString("FirstName") & ", " & (cur.GetString("Surname")) = Value Then
townlabel = (cur.GetString("Town"))
Label1.Text = townlabel
End If
Next

End Sub

Sub ListView1_ItemLongClick (Position As Int, Value As Object)
Dim Name() As String
Name=Regex.Split(",",Value) 'Name(0) is first name, Name(1) is Surname in that order
SQL1.ExecNonQuery("DELETE FROM People WHERE FirstName= ? AND Surname= ?", Array As String(Name(0),Name(1)))
End Sub

Sub btnAdd_Click
SQL1.ExecNonQuery("INSERT INTO People VALUES('" & txtSurname.Text & "','" & txtName.text & "','" & txtTown.Text & "')")
ListViewFill
End Sub

 

[Mahares]

We need to know what exact error message you get and what line you get it at. You have a lot more code that the portion regarding the DELETE I suggested. Since you have a complete database and a layout, if you cannot figure it out, it might be helpful to the rest of us if you can zip from IDE your project and export it as zip. Usually, someone like Klaus suggests the export.

 

[bishmedia]

Ive attached the program file all i would like to do is delete a record when the user selects on the list view.

 

[Mahares]

I have not tested it yet, but this line should be:

SQL1.ExecNonQuery2("DELETE FROM People WHERE FirstName= ? AND Surname= ?", Name)

SQL1.ExecNonQuery2 instead of SQL1.ExecNonQuery
EDIT: If you make the above change in the code, your program will run without any errors.

EDIT: I finally tested your code. If you make the above change and also change this line:

ListView1.AddSingleLine(cur.GetString("FirstName") & ", " & (cur.GetString("Surname")))

to

ListView1.AddSingleLine(cur.GetString("FirstName") & "," & (cur.GetString("Surname")))

You will be able to delete records. Notice that the only change in the above line was that I removed the space after the comma. You cannot have that space since we are using the RegEx to parse the string.

 

[Mahares]

Here is a fully functional application of yours after I made the necessary changes. You can display or add records. Also, you can delete any record. Once a record is deleted, the listview automatically refreshes to reflect the deletion.

 

[bishmedia]

Oh this is brilliant, im just picking away at the general basics and things like this really help when building larger programs, i guess next would be append

Many many thanks for your time