Android Question Value inserted to sqlite is wrong

[JonnyCav]

I'm inserting records into an sqlite database and every entry works fine EXCEPT to 'studentID' which always submits the last record id value that was in the resultset from my query.

Private Sub Update_Attendance
    
    Activity.RemoveAllViews
    Activity.LoadLayout("AttendanceLayout")
    Activity.Title="Attendance for:  " & selectedGroup
    DateTime.DateFormat="dd-MMM-yyyy"
    Dim cdate As String
    cdate=DateTime.Date(DateTime.Now)
    txtClassDate.Text=cdate
    
    
    get_month
    
    If month="September" Then
        txtClassPeriod.Text=1
    Else If month="October" Then
        txtClassPeriod.Text=2
    Else If month="November" Then
        txtClassPeriod.Text=3
    Else If month="December" Then
        txtClassPeriod.Text=4
    Else If month="Februrary" Then
        txtClassPeriod.Text=5
    End If
    
    period=txtClassPeriod.Text
    
    rs=Starter.sql.ExecQuery2("SELECT studentId, studentName FROM students WHERE groupName =?",Array As String(selectedGroup))
    
        
    Do While rs.NextRow
        
        Dim pa As Panel
        pa.Initialize("")
        pa.LoadLayout("ListOfStudents")
        pa.SetLayoutAnimated(0,0,0,clvAttendance.AsView.Width,60dip)
        
            
        txtStudents.Text = rs.GetString("studentID")
        cbStudents.Text = rs.GetString("studentName")
        
        clvAttendance.Add(pa,"")
    
    Loop
    
End Sub

Private Sub rbGroup_CheckedChange(Checked As Boolean)

    Dim index As Int = clvAttendance.GetItemFromView(Sender)
    Dim p As B4XView =clvAttendance.GetPanel(index)
    Dim r As RadioButton=Sender
    If r.Checked  Then
        selectedGroup=(p.GetView(0).Text)
    End If

    Log(selectedGroup)
    Update_Attendance
'    Attendance_Table
End Sub

Private Sub cbStudents_CheckedChange(Checked As Boolean)
    

    Dim CB As CheckBox = Sender
    Dim sID As Int
    
    sID=txtStudents.Text
    Log(selectedGroup)
    Log (CB.Text & " : " & CB.Checked)
    Log(txtStudents.Text)
    
    Starter.sql.ExecNonQuery2("INSERT INTO attendance VALUES (?, ?, ? ,?, ?, ?)", Array As Object(sID, selectedGroup, CB.Text, txtClassDate.Text, txtClassPeriod.Text, "P"))
    
    ToastMessageShow("Entry added", False)

    
End Sub

Private Sub btnUpdateAttendance_Click
    'PUT THE SQLITE INSERT HERE FOR ALL TICKED BOXES IN A GIVEN GROUP

End Sub

ALSO, it isn't committing the data to the database?

Thanks

 

[aeric]

It's better you upload a small example with the database.

 

[emexes]

I'm inserting records into an sqlite database and every entry works fine EXCEPT to 'studentID' which always submits the last record id value that was in the resultset from my query.

Assuming that "record id value" is "student id", then you are correct.

What is inserted into the first column comes from sID, which comes from txtStudents.Text, which comes from rs.GetString("studentID") of the last row that was retrieved when populating clvAttendance.

Perhaps try updating txtStudents.Text to be the student id of the current selection of clvAttendance, whenever that selection is changed.

 

[JonnyCav]

Assuming that "record id value" is "student id", then you are correct.

What is inserted into the first column comes from sID, which comes from txtStudents.Text, which comes from rs.GetString("studentID") of the last row that was retrieved when populating clvAttendance.

Perhaps try updating txtStudents.Text to be the student id of the current selection of clvAttendance, whenever that selection is changed.

I thought I WAS doing this. If I change the 'classDate' etc. those values are added correctly, as is the name. But not the ID, which is why I posted my frustration!

 

[Lello1964]

txtStudents.Text = rs.GetString("studentID")

studentID is String or int ?

if int must Use :

txtStudents.Text = rs.GetInt("studentID")

 

[emexes]

I thought I WAS doing this. If I change the 'classDate' etc. those values are added correctly, as is the name. But not the ID, which is why I posted my frustration!

I would try logging both these before and after the INSERT line:

Log(txtStudents.Text)
Log(sID)

and make sure they are what they should be, and also match the student id that is INSERTed into the database.

I'd also follow up @Lello1964's observation re: Ints vs Strings ?

 

[JonnyCav]

Assuming that "record id value" is "student id", then you are correct.

What is inserted into the first column comes from sID, which comes from txtStudents.Text, which comes from rs.GetString("studentID") of the last row that was retrieved when populating clvAttendance.

Perhaps try updating txtStudents.Text to be the student id of the current selection of clvAttendance, whenever that selection is changed.

Perhaps try updating txtStudents.Text to be the student id of the current selection of clvAttendance, whenever that selection is changed.

Indeed. Ideas?

 

[ilan]

in your code it looks like rs is a global variable. you should better not set it as a global variable and also i don't see in your code where you call rs.close

you should do the query like this:

  Dim rs As ResultSet = Starter.sql.ExecQuery2( "SELECT studentId, studentName FROM students WHERE groupName =?",Array As String(selectedGroup)) '<---
 
    Do While rs.NextRow
      
        Dim pa As Panel
        pa.Initialize("")
        pa.LoadLayout("ListOfStudents")
        pa.SetLayoutAnimated(0,0,0,clvAttendance.AsView.Width,60dip)
      
          
        txtStudents.Text = rs.GetString("studentID")
        cbStudents.Text = rs.GetString("studentName")
      
        clvAttendance.Add(pa,"")
  
    Loop
  
    rs.Close '<---

if rs is indeed a global variable it can explain why you get older (stored) records

 

[JonnyCav]

I can see the problem but I'm scratching my head!
The checkbox (st. name) and the txt. (which is the student ID) are not 'linked' when I tick the box. They appear correctly in the CLV but the 'click' isn't bringing the same row txtStudents.Text value.

 

[emexes]

Indeed. Ideas?

Here are two ideas:

Perhaps try updating txtStudents.Text to be the student id of the current selection of clvAttendance, whenever that selection is changed.

I would try logging both these before and after the INSERT line:

 

[JonnyCav]

I have tried all of these and now I'm pulling my hair out.
I've (probably very wrongly) now started to see how I can capture the resultset as a map but these efforts have me thinking I'm just going round in circles.

I'm obviously barking up the wrong tree in my approach to what, on paper appears very simple!
Some serious help would be greatly appreciated.

 

[JonnyCav]

FIXED, I think. You all certainly spurred me on.

 

[aeric]

It's better you upload a small example with the database.

You may get your issue solved earlier if you didn't insist.

 

[ilan]

FIXED, I think. You all certainly spurred me on.

was your rs object declared as global?

have you looked at post #8 ?

 

[DonManfred]

It is annoying to get into thread with a lots of Posts (14) and also no single project uploaded which shows the issue.

 

[Mahares]

FIXED

1. As a courtesy to others, you should explain how you solved the problem, so others can beneefit from your solution.
2. You were asked to attach your project, but you ignored the call. Your problem would not have taken these many posts to solve if you did.
3. You were asked regarding whether you declared your resultset as global or local variable, but again, you ignored it.
4. On another thread, you specifically address your question to Erel instead of the community as a whole. There are many other members capable of helping you too.
A little courtesy can go a long way.