Android Question "Check for entry "routine in SQLExample

[Bernhard Svavarsson]

Hi
Been playing with the SqIExample and am trying to implement " check for entry" in this example.
Added the following code:

Sub btnEditAdd_Click
..........................
................................
Dim Query As String
Dim Cursor1 As Cursor
'Dim ID As Int
'first we check if the entry already does exist
Query = "SELECT * FROM persons WHERE FirstName = ? AND LastName = ? AND City = ?"
Cursor1 = SQL1.ExecQuery2(Query, Array As String (edtFirstName.Text, edtLastName.Text,
edtCity.Text))
If Cursor1.RowCount > 0 Then
'if it exists show a message and do nothing else
ToastMessageShow("This entry already exists", False)
...............................
.................................

It compiles fine but when 'checking the entry' it bumps out on

Cursor1 = SQL1.ExecQuery2(Query, Array As String (edtFirstName.Text, edtLastName.Text,
edtCity.Text))

The logerror is:

java.lang.RuntimeException: Object should first be initialized (EditText).

Anyone tested this routine on SQLExample?

Regards

Benni

 

[mangojack]

Hi .. Please use [ Code ] [/Code] tags .. (without Spaces) it makes it much more easier to read your code.

are you trying the SQLiteLight1 Example by Klaus
By your posted code it looks like it. In its original form the example works OK.
It appears you have made some changes ... ( btnEditAdd not in original example)
Have you added another EditText ?.

the only EditTexts in the example where the ones encased in the above ExecQuery2 statement. Are they still declared in Sub Globals.

Sub Globals
   Dim edtID, edtFirstName, edtLastName, edtCity As EditText
   Dim btnAdd, btnDelete, btnUpdate, btnPrevious, btnNext As Button
End Sub

 

[Bernhard Svavarsson]

Hi mj
Yes this code is from SQLiteLight1 Example and I was testing this on SQLExample as SQLExample does not have if this "check for entry" feature.
The 'btnEditAdd' is in the SQLExample (edit activity). As said just trying to add the missing feature "check for entry" into SQLExample.
Yes I added in the Globals:
Dim edtID, edtFirstName, edtLastName, edtCity AsEditTex
Perhabs regarding the SQLExample it's not so simple to implement the "check for entry" - needs maybe total different approach.

Regards and thanks

Benni

 

[mangojack]

I see what your attempting .. inserting code from the SQLLitelite1 example into the example in the users guide in an effort to check if data already exists in the DB before adding new entry. No need to do that , just have to modify users guide example.

leaving for work , hopefully someone can supply correct code .. will check thread tonight.

Sub btnEditAdd_Click
  Dim row(NumberOfColumns) As String
  Dim i, hh As Int
  Dim txt As String
 
txt = "SELECT max(" & ColumnName(0) & ") FROM " & DBTableName
row(0) = SQL1.ExecQuerySingleResult(txt) + 1

'we set SELECT max( ColumnName(0)) FROM DBTableName equivalent to SELECT max(ID)FROM personsand get row(0) 
txt = "INSERT INTO "&DBTableName&" VALUES (NULL"
For i = 1 To NumberOfColumns - 1
  Dim e As EditText
  e = scvEdit.Panel.GetView(i * 2 + 1)
  row(e.Tag) = e.Text
  txt = txt&" , '"&e.Text&"'"
  If e.Text = "" Then
    hh = hh + 1
  End If
Next
txt = txt & ")"

'The final query text could look like this:
INSERT INTO persons VALUES(NULL, 'Jules', 'VERNE', 'Rue St.Honoré', 'Paris') 
'The NULL value is entered for the autoincrement of the ID column.
  If hh > 0 Then
    Msgbox("There are "&hh&" data missing ","Attention")
  Return
  End If

'---------------------------------
'check for existing entry first

'SQl1.ExecQuery (Select * from "&DBTableName&" WHERE ColumnName1 = row(1) And ColumnName2 = (row2) ") 
'@@ Only guessing at above statement !!
'if there is a Result then advise user and do not continue ...
'---------------------------------

'if entry not exists then .......
AddRow(row)
  If NumberOfRows > 0 Then
  btnEditModif.Visible = True
  End If
'Then we call the AddRow(row) routine to add the new data set to the Scrollview.
'And set the Edit button to visible if there is at least one data set in the database.
 
SQL1.ExecNonQuery(txt)

'..............................
End Sub

 

[klaus]

In the SQLExample from the User's Guide add this code at the beginning of the btnEditAdd_Click routine.

    'check if entry already exists
    ' constructs the SQL query
    query = "SELECT * FROM " & Main.DBTableName & " WHERE "
    Dim vals(Main.NumberOfColumns - 1) As String
    For col = 1 To Main.NumberOfColumns - 1
        Dim edt As EditText
        edt = scvEdit.Panel.GetView(col * 2 + 1)
        row(edt.Tag) = edt.Text
        If col = 1 Then
            query = query & Main.ColumnName(col) & " = ?"
        Else
            query = query & " AND " & Main.ColumnName(col) & " = ?"
        End If
        vals(col - 1) = edt.Text
    Next
    Dim Curs As Cursor
    Curs = Main.SQL1.ExecQuery2(query, vals)
    If Curs.RowCount > 0 Then
        Msgbox("This entry already exists ! ", "Attention")
        Curs.Close
        Return
    End If
    Curs.Close

 

[Bernhard Svavarsson]

Hi Klaus
This worked fine - thanks. Had already seen this code and tested it but I had the "vals(col - 1) = edt.Text" wrong.
Thanks again

Regards

Benni