Android Question sqlite UPDATE

[JonnyCav]

Happy New Year!
Could someone please explain why my 'UPDATE' gives me one of two errors; either 'missing ','' or 'Column count does not exist'?

Private Sub btnUpdateAttendance_Click
    
            rs=Starter.sql.ExecQuery2 _
    ("SELECT countID,classes FROM classCount WHERE groupName = ? AND classPeriod = ? AND classYear= ?" _
    ,Array As String(selectedGroup,txtClassPeriod.Text,AcYear))



    Dim count As Int
    Dim CID As Int
    
    
    Do While rs.NextRow
        
        count=(rs.GetString("classes"))
    Loop

    count=count+1
    Log(count)
    
    If count=1 Then   
    Starter.sql.ExecNonQuery2("INSERT INTO classCount VALUES (null,?,?,?,?)",Array As Object(selectedGroup, AcYear, txtClassPeriod.Text, count))
    
    Else
'        Starter.sql.ExecNonQuery("UPDATE classCount SET classes = count WHERE countID = CID")
        
            
                
'        Starter.sql.ExecNonQuery2 _
'    ("UPDATE classCount SET classes = count WHERE groupName = ? AND classPeriod = ? AND classYear= ?" _
'    ,Array As String(selectedGroup,txtClassPeriod.Text,AcYear))
        
    End If   

    Activity.RemoveAllViews
    Activity.LoadLayout("MainLayout")
    Activity.Title="Scores and Attendance"
End Sub

The INSERT and evrything else works as expected. It's just the UPDATE (two alternatives both presently commented)

Thanks

 

[Lucas Siqueira]

Your first update should be something like:
because you are not concatenating the previously calculated value:

 Starter.sql.ExecNonQuery("UPDATE classCount SET classes = '" & count & "' WHERE countID = '" & CID & "' CID")

' or

 Starter.sql.ExecNonQuery($"UPDATE classCount SET classes = '${count}' WHERE countID = '${CID}' "$)

' or

Starter.sql.ExecNonQuery2 _
    ("UPDATE classCount SET classes = ? WHERE groupName = ? AND classPeriod = ? AND classYear= ?" _
    ,Array As String(count,selectedGroup,txtClassPeriod.Text,AcYear))

where are you populating the CID variable???

 

[aeric]

Happy New Year!
Could someone please explain why my 'UPDATE' gives me one of two errors; either 'missing ','' or 'Column count does not exist'?

Private Sub btnUpdateAttendance_Click
   
            rs=Starter.sql.ExecQuery2 _
    ("SELECT countID,classes FROM classCount WHERE groupName = ? AND classPeriod = ? AND classYear= ?" _
    ,Array As String(selectedGroup,txtClassPeriod.Text,AcYear))



    Dim count As Int
    Dim CID As Int
   
   
    Do While rs.NextRow
       
        count=(rs.GetString("classes"))
    Loop

    count=count+1
    Log(count)
   
    If count=1 Then  
    Starter.sql.ExecNonQuery2("INSERT INTO classCount VALUES (null,?,?,?,?)",Array As Object(selectedGroup, AcYear, txtClassPeriod.Text, count))
   
    Else
'        Starter.sql.ExecNonQuery("UPDATE classCount SET classes = count WHERE countID = CID")
       
           
               
'        Starter.sql.ExecNonQuery2 _
'    ("UPDATE classCount SET classes = count WHERE groupName = ? AND classPeriod = ? AND classYear= ?" _
'    ,Array As String(selectedGroup,txtClassPeriod.Text,AcYear))
       
    End If  

    Activity.RemoveAllViews
    Activity.LoadLayout("MainLayout")
    Activity.Title="Scores and Attendance"
End Sub

The INSERT and evrything else works as expected. It's just the UPDATE (two alternatives both presently commented)

Thanks

I assume you have autoincrement column as countID.

Private Sub btnUpdateAttendance_Click
    Dim blnExist As Boolean
    Dim qry As String = "SELECT countID FROM classCount WHERE groupName = ? AND classPeriod = ? AND classYear = ?"
    Dim rs As ResultSet = Starter.sql.ExecQuery2(qry, Array As String(selectedGroup, txtClassPeriod.Text, AcYear))
    
    Do While rs.NextRow
        blnExist = True
    Loop
    rs.Close

    If blnExist Then
        Dim qry As String = "UPDATE classCount SET classes = classes + 1 WHERE groupName = ? AND classYear = ? AND classPeriod = ?"
    Else
        Dim qry As String = "INSERT INTO classCount (classes, groupName, classYear, classPeriod) VALUES (1, ?, ?, ?)"
    End If
    Starter.sql.ExecNonQuery2(qry, Array As Object(selectedGroup, AcYear, txtClassPeriod.Text))
End Sub

 

[netsistemas]

case sensisitive
CASE SENSITIVE?

The name of fields are case sensitive?
try access by id, and return the name for see in debug

 

[aeric]

'Column count does not exist'

count is expected to be a column name in your UPDATE query. You should replace it with a ? mark and pass the value using parameter.

 

[JonnyCav]

All fixed. I need new glasses!