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

Discussion in 'Android Questions' started by Charles, Mar 9, 2015.

  1. Charles

    Charles Member Licensed 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?
     
  2. edgar_ortiz

    edgar_ortiz Active Member Licensed 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
     
  3. KMatle

    KMatle Expert Licensed User

    Additionally: Be careful to use such row names (here: "ID") as they often are reserved words. Some functions work, others will not. F.e. I user "order" for a table name :confused: the other day and it took me some time...
     
  4. Charles

    Charles Member Licensed 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?
     
  5. Charles

    Charles Member Licensed User

    kMatel:

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

    schemer Active Member Licensed User

    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!)
     
  7. Charles

    Charles Member Licensed 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
     
  8. Mahares

    Mahares Well Known Member Licensed 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:

    Code:
    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
     
    DonManfred likes this.
  9. Charles

    Charles Member Licensed 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
     
  10. Mahares

    Mahares Well Known Member Licensed 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: Mar 11, 2015
    DonManfred likes this.
  11. Charles

    Charles Member Licensed 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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice