Android Question SQLite Update Query not working

[MrKim]

What am I missing?
The following does not throw any errors but does not update the table either???

If Main.SQL1.IsInitialized = False Then Main.SQL1.Initialize(File.DirInternal, "PartsAndTime.db", False)

Main.SQL1.ExecNonQuery("UPDATE TKRecordTemp SET Sent=1, Success=1 WHERE OSID = 101130;")

Tried with and without the debugger.
The Select Queries on the table all work correctly.

Thanks,
Kim

 

[Mrjoey]

u should use this:

Main.SQL1.ExecNonQuery2("UPDATE TKRecordTemp SET Sent=?, Success=? WHERE OSID = ?",array as string(1,1,1231234105))

 

[eps]

If you execute this SQL in a SQL editor (I use the Firefox add-on, but others are available) what happens? Does the code work? Are you sure you need the ; at the end?

Are you really sure that you aren't making the change, but then somehow overwriting the DB, this can be a common issue in code. Especially as you have to copy the DB from Assets to Internal so that you can make changes to it. What does that part of your code look like?

 

[eps]

u should use this:

Main.SQL1.ExecNonQuery2("UPDATE TKRecordTemp SET Sent=?, Success=? WHERE OSID = ?",array as string(1,1,1231234105))

You shouldn't need to do that.

OP, are Sent and Success numerics? They're not defined as char or string are they?

I would execute the SQL as you have it in a SQL Editor on the DB on your machine and see what the result is. That should give you a clue about the problem you are having.

HTH

 

[MrKim]

If you execute this SQL in a SQL editor (I use the Firefox add-on, but others are available) what happens? Does the code work? Are you sure you need the ; at the end?

Are you really sure that you aren't making the change, but then somehow overwriting the DB, this can be a common issue in code. Especially as you have to copy the DB from Assets to Internal so that you can make changes to it. What does that part of your code look like?

I know the change is not being made as a subsequent query to retrieve the records finds no records. A query with the old criteria does.

RE. the semicolon. IT is my understanding that a ; on the end is good programming practice. It indicates the end of an SQL statement. As a practical matter for single queries it does seem to make no difference. I have never had it be an issue, and I tried with and without.

 

[sorex]

is there any delay between the update and the select after it?

it might run async since it doesn't need to pull data back to your app.

 

[sorex]

by the way are you sure there is a record with OSID = 101130 ?

if not it won't throw any errors either.

 

[MrKim]

Yes, I even tried no criteria. Still didn't work. So I tried a delete query thinking the table was locked for some reason and it deleted them all.

 

[Erel]

Have you explicitly started transaction? If yes then you should make sure that it is closed properly.

 

[MrKim]

Sigh, I figured it out. SQL Lite is not explicitly typed but says it stores things as what they, I guess what that really means is that if you don't specify a type it stores a string. Even though the field is ALWAYS a long when I wrapped it in single quotes it worked

 WHERE OSID = '101130';

. So I went back and specified the type and it worked as it should. I was in a hurry to get things working and it came back and bit me .