Android Question Sqlite query returning null value for non empty fields

Ahmad-Ng

Member
Licensed User
Hi! My first post and seems like it is so mundane. I am new to sqlite. Am practicing with it but getting some non null fields being returned as null. What am I doing wrong - anyone? Thanks
 

Ahmad-Ng

Member
Licensed User
Thanks. Here is the offending pack in its entirety - not to miss anything.
 

Attachments

  • my_sqlitepb.zip
    44.5 KB · Views: 109
Upvote 0

Ahmad-Ng

Member
Licensed User
imbault pls log in with membership no 14926 and password 12345678 to check if you wish. Yet query will return null for the password in the returned cursor even though browsing the dba will show the value of 12345678
 
Upvote 0

imbault

Well-Known Member
Licensed User
remove the line, else you'll never step in the insert into DB in your code
B4X:
    If curs2.RowCount > 0 Then 'already registered
Then change your code in order to insert the new login/password...
 
Upvote 0

Ahmad-Ng

Member
Licensed User
Pardon me, but the db gets actually opened and the record with that membership no and password gets fetched. The issue is in the result set - some fields bring back values while a few return null! But these are not null as can be confirmed by viewing with sqlite browser. So why do they get returned as null?
 
Upvote 0

Erel

Administrator
Staff member
Licensed User
1.
B4X:
If File.Exists(File.DirDefaultExternal, DBFileName) = False Then
       DBFileDir = DBUtils.CopyDBFromAssets(DBFileName)
     End If
You shouldn't check whether the file exists or not as there is a similar check, which is more correct, inside DBUtils.CopyDBFromAssets.

2. You should use anchors to make the layout fill the available screen size.

3. Your code can be simplified a lot. You are reading all columns to an array and then back to a map. Why? Work with the Cursor directly.

4. I don't see any Null value:

SS-2017-03-20_11.50.47.png
 
Upvote 0

Ahmad-Ng

Member
Licensed User
Thanks Erel for your comments. Will learn from them. I must be doing something wrong cos I set pword_ass as 12345678 for membership no 14926 while testing. The value showing above for pword_ass, pword_set and svrid all show as null on my log! What will my problem stem from? Thanks
 
Upvote 0

klaus

Expert
Licensed User
I tested your program and I get no null values with 14926 and 12345678 !
But, this line
If values(7) <> edtPassword.Text Then
is wrong it must be!
If values(9) <> edtPassword.Text Then
 
Upvote 0

Ahmad-Ng

Member
Licensed User
Thanks to Erel and Klaus. I noted the index and made the correction Klaus pointed out. But still. Attached is the screen shot of the np content copied and pasted into notepad to wrap it around as it was too long
 

Attachments

  • screen shot of mp content.png
    screen shot of mp content.png
    17.8 KB · Views: 148
Upvote 0

Erel

Administrator
Staff member
Licensed User
What is the output of this code:
B4X:
Sub CheckRegistration
   Dim curs2 As Cursor = SQL1.ExecQuery2("SELECT * FROM membersinfo WHERE MembershipNo =?",Array As String(edtMemNo.Text))
   For i = 0 To curs2.RowCount - 1
     curs2.Position = i
     For c = 0 To curs2.ColumnCount - 1
       Log(curs2.GetColumnName(c) & ": " & curs2.GetString2(c))
     Next
   Next
   curs2.Close
End Sub
 
Upvote 0

Ahmad-Ng

Member
Licensed User
Attached is screen shot of the log. Thanks for the assistance. Truly appreciate the wonderful support
 

Attachments

  • screen shot of log content 2.png
    screen shot of log content 2.png
    15.9 KB · Views: 188
Upvote 0

klaus

Expert
Licensed User
This is wrong too!
mp.Put( "pword_ass", values(10))
It must be
mp.Put( "pword_ass", values(9))!!!
values(10) is pword_set and not pword_ass.
The column numbers begin with 0 not with 1!

Are you sure that the database you are using is the same as in the Assets folder?
I get this with Erels code:
1
id: 154
membershipno: 14926
fullname: ABDULSALAM AHMAD ABUBAKR
status: CNA
phone_no: 803514056
official_add: BURSARY DEPARTMENT, KADUNA POLYTECHNIC
residential_add: NO. 22A BISAU CRESCENT NORTH, MALALI LOW COST, KADUNA
email: Not Available
duesos: 45500
pword_ass: 12345678
pword_set: NA
SvrID: 1
154,14926,ABDULSALAM AHMAD ABUBAKR,CNA,803514056,BURSARY DEPARTMENT, KADUNA POLYTECHNIC,NO. 22A BISAU CRESCENT NORTH, MALALI LOW COST, KADUNA,Not Available,45500,12345678,NA,1
 
Last edited:
Upvote 0

Ahmad-Ng

Member
Licensed User
Thanks Erel. Thanks Klaus. It is now working!!! I had it open in a browser for sqlite which maintained some sort of copy I suppose. Compacting and closing that and removing older copy from the emulator and syncing a few times from file manager - all due to eliminate compatibility/ corruption issues worked. Attached is screenshot showing the result. Thanks for the wonderful responses and support.
 

Attachments

  • Yes, Now Working Screen Shot.png
    Yes, Now Working Screen Shot.png
    22.5 KB · Views: 142
Upvote 0
Top