Android Question sqlite cursor.getint("ID") error

Charles

Member
Licensed User
Longtime User
I have a database of trip tickets for our trucking company with a primary key "ID". When I use a select query to eliminate duplicate ticket numbers all is well until I try to get the primary key into a local variable.
Here is my code:

Dim strSQL As String
Dim crsr As Cursor
Dim ID As Int
' test for duplicate ticket number
strSQL = "SELECT * FROM ticket WHERE "
strSQL = strSQL & "TicketNumber = " & txtTicketNumber.Text & ";"
crsr = SQL.ExecQuery(strSQL)
ID = crsr.GetInt("ID")

The last line crashes my app with no error codes.
I tested for crsr.rowcount > 0 and know that there is at least one row in there.
I'm at my wits end.
Here is my database.
upload_2015-3-8_23-9-54.png


Can anyone see where I'm going wrong?
 

edgar_ortiz

Active Member
Licensed User
Longtime User
Hi,

You need to specify the record in the cursor:

crsr.position = 0

And then you can use

ID = crsr.GetInt("ID")

Regards,

Edgar
 
Upvote 0

KMatle

Expert
Licensed User
Longtime User
Upvote 0

Charles

Member
Licensed User
Longtime User
Thanks, that worked!

I used to use PK as a primary key column name but lost my way here. I think I'll follow your advise and change ID to PK.

Thanks again, you saved my sanity. :)

BWY: Nice brew. Did you make it yourself?
 
Upvote 0

Charles

Member
Licensed User
Longtime User
kMatel:

I did as you suggested and my query which was sorting on "ID" now won't sort on "pk". haha, what next. :)
 
Upvote 0

schemer

Active Member
Licensed User
Longtime User
kMatel:

I did as you suggested and my query which was sorting on "ID" now won't sort on "pk". haha, what next. :)

I am a db noob but I think I ran into that problem too. The cursor will not load the autonumber primary key field. In my database I created another column next to the autonumber field and just put the same numbers into it as the RecNo PK field and it pulls it right up and sorts it. Furthermore, I think I researched this and it is normal not to be able to load that PK field (because it is an index). I don't know or think that is the correct way to fix it but it worked for me at least for my test.
schemer

p.s. I am not kMatel. :) (and I didn't even stay at a Holiday Inn Express last night!)
 
Upvote 0

Charles

Member
Licensed User
Longtime User
What's odd is that it worked fine when the primary key column name was "ID" ( Integer Primary Key Autoincrement ). As soon as I changed it to "pk" it crapped on me. (??)
I thought about a record number field but it would add more complexity to my code. I may have to resort to that solution though, if nothing else turns up.

I have a friend who owns two Holiday Inn Express' if you need a room for the night. :p
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
1. There was no reason for you to change the column name from ID to PK. ID works just fine. The problem you had was that you needed to position the cursor before you can display data, which Edgar pointed out to you in post #2.
2. Even if you changed the column name from ID to PK, you should not have had any issues unless it was not done properly. If you want to change a column name from ID to PK you can use this below code as a guideline:

B4X:
txt="CREATE TABLE IF NOT EXISTS ticket (ID INTEGER PRIMARY KEY AUTOINCREMENT, DATE TEXT, CONTRACTOR TEXT)"
    SQL1.ExecNonQuery(txt)  'create the ticket table

txt="INSERT INTO ticket  VALUES(?,?,?)"
    SQL1.ExecNonQuery2(txt, Array As Object(Null,"20150310", "Smith"))
    SQL1.ExecNonQuery2(txt, Array As Object(Null,"20150309", "Taylor"))   'Insert in ticket table

'Copy ticket table To a new temp table while changing a column name from ID To PK
txt="CREATE TABLE tempticket As Select ID 'PK', DATE, CONTRACTOR FROM ticket"
SQL1.ExecNonQuery(txt)

txt="ALTER TABLE ticket RENAME TO ticketOLD"   'renamed ticket To ticketOLD
SQL1.ExecNonQuery(txt)

txt="ALTER TABLE tempticket RENAME TO ticket"   'renamed tempticket to ticket
SQL1.ExecNonQuery(txt)

Dim ID As Int
Cursor1 = SQL1.ExecQuery("SELECT * FROM ticket WHERE CONTRACTOR= 'Taylor'")
Cursor1.Position=0
ID=Cursor1.GetInt("PK")
Log(ID)  'displays 2
 
Upvote 0

Charles

Member
Licensed User
Longtime User
Thanks Mahares.
here is my problem with your solution.

txt="INSERT INTO ticket VALUES(?,?,?)"
SQL1.ExecNonQuery2(txt, ArrayAs Object(Null,"20150310", "Smith"))
SQL1.ExecNonQuery2(txt, ArrayAs Object(Null,"20150309", "Taylor"))

I have 392 tickets to install and really don't want to do it one at a time. haha. Not lazy just prefer speed. :)

WHen I was using Mysql the manager I used allowed multiple queries to be linked together but I can't find that feature in sqlite studio. I'm looking at sqlite2009 pro now and hope to find a more effecient method of uploading my data.

Charles
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I only inserted those 2 records just to illustrate a point. I do not expect you to enter 392 tickets manually. If you have the records in a text file, say csv file, you can easily import them into the ticket table. B4a and SQlite allow that. If you have problem, post your text file and someone will certainly help you for such quite common task.

The main point I was making from my previous post was that you did not have to change the name of your field from ID to PK. Neither is a reserved word. But if you did, I showed the way of doing it.
 
Last edited:
Upvote 0

Charles

Member
Licensed User
Longtime User
Ok, I understand your point in bold. I was not criticizing your answer.
Thank you for the effort you put into your answer. It was very helpful.
My sort is now working and I'm on to other issues.
 
Upvote 0
Top