Android Question Sqlite query returning null value for non empty fields

[Ahmad-Ng]

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

 

[imbault]

Hi, send the piece of code causing your pb

 

[Ahmad-Ng]

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

 

[Ahmad-Ng]

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

 

[imbault]

remove the line, else you'll never step in the insert into DB in your code

    If curs2.RowCount > 0 Then 'already registered

Then change your code in order to insert the new login/password...

 

[Ahmad-Ng]

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?

 

[Erel]

1.

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:

 

[Ahmad-Ng]

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

 

[Erel]

Can you post a screenshot of 'mp' content from the debugger?

 

[klaus]

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

 

[Ahmad-Ng]

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

 

[Erel]

What is the output of this code:

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

 

[Ahmad-Ng]

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

 

[klaus]

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

 

[Ahmad-Ng]

Yes. But since you pointed out the issue in your post above, that has been corrected. Thanks

 

[Erel]

How did you create the database? It is probably a compatibility issue.

 

[klaus]

The database in the Assets folder works OK when reading the correct columns!
Look at post#14.

 

[Ahmad-Ng]

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.