Android Question Database query

anaylor01 said:

I am just trying to see if there is a duplicate. But I need it to return a value. Right now it returns nothing. Not a null or a zero.

Click to expand...

Dim MyQuery As String = "Select  ifnull(COUNT(*),'0') AS MyCount FROM  GamePlay GROUP BY  teamscore, teamround HAVING  COUNT(*) > 1"   
    curs=SQL1.ExecQuery(MyQuery)
    For i=0 To curs.RowCount-1
        curs.Position=i
        Log($"my count is:  ${curs.Getint("MyCount")}"$ )
    Next
    curs.Close

Mahares said:

I tested the concept on one of my databases, with different columns of course and I was able to view results in the cursor

Click to expand...

anaylor01 said:

Yes it does if there are duplicates. But it doesn't if there aren't any duplicates and I need it to if it doesn't have duplicates. Or should I say it shouldn't error out if there aren't any duplicates.

Click to expand...

Dim ThereAreDuplicates As Boolean

Dim MyQuery AsString = "Select COUNT(*) AS MyCount FROM GamePlay GROUP BY teamscore, teamround HAVING MyCount > 1"
curs = SQL1.ExecQuery(MyQuery)
ThereAreDuplicates = (curs.RowCount > 0)
curs.Close

aeric said:

Log("Error: " & LastException.Message)
Return 0

Click to expand...

Sub CountDuplicate() As Int
    Dim qry As String
    Dim cur As Cursor
    qry = "SELECT COUNT(*) AS duplicate_count FROM GamePlay GROUP BY teamscore, teamround HAVING duplicate_count > 1"
    cur = SQL.ExecQuery(qry)
    Return cur.RowCount  
End Sub

Mahares said:

I do not think you need the catch block because there is no error when the count is 0. The code can be simply as follows:

B4X:

Sub CountDuplicate() As Int
    Dim qry As String
    Dim cur As Cursor
    qry = "SELECT COUNT(*) AS duplicate_count FROM GamePlay GROUP BY teamscore, teamround HAVING duplicate_count > 1"
    cur = SQL.ExecQuery(qry)
    Return cur.RowCount 
End Sub

If no records, log(CountDuplicate) will display 0

Click to expand...