Android Question Value inserted to sqlite is wrong

JonnyCav · Dec 27, 2023

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.

B4X:

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 · Dec 27, 2023

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

emexes · Dec 27, 2023

JonnyCav said:
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 · Dec 27, 2023

emexes said:
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 · Dec 27, 2023

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

studentID is String or int ?

if int must Use :

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

emexes · Dec 27, 2023

JonnyCav said:
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 · Dec 28, 2023

emexes said:
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 · Dec 28, 2023

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:

B4X:

  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 · Dec 28, 2023

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 · Dec 28, 2023

JonnyCav said:
Indeed. Ideas?

Here are two ideas:

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

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

JonnyCav · Dec 28, 2023

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 · Dec 28, 2023

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

aeric · Dec 28, 2023

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

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

ilan · Dec 28, 2023

JonnyCav said:
FIXED, I think. You all certainly spurred me on.

was your rs object declared as global?

have you looked at post #8 ?

DonManfred · Dec 29, 2023

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

Mahares · Dec 29, 2023

JonnyCav said:
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.

Android Question Value inserted to sqlite is wrong

JonnyCav

Member

aeric

Expert

emexes

Expert

JonnyCav

Member

Lello1964

Well-Known Member

emexes

Expert

JonnyCav

Member

ilan

Expert

JonnyCav

Member

emexes

Expert

JonnyCav

Member

JonnyCav

Member

aeric

Expert

ilan

Expert

DonManfred

Expert

Mahares

Expert

Similar Threads

Android Question Value inserted to sqlite is wrong

Member

Expert

Expert

Member

Well-Known Member

Expert

Member

Expert

Member

Expert

Member

Member

Expert

Expert

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency