Android Question SQL insert into enigma

johnmie

Active Member
Licensed User
Longtime User
My DB table has 5 fields (see attached)
When I try to add a record like this **200915, 862, 122, Anves3, ka¦116¶pr¦25¶aq¦89¶ch¦64¶ft¦1¶ca¦5**

with sql2.ExecNonQuery2("INSERT INTO history VALUES (?, ?, ?, ?, ?)", _
Array (Q.heute, Q.currentID, wtS.text, nName, A))

and then read it back I get the first three fields correctly, the fourth and the fifth come always back as 0 (e.g. 200915, 862, 122, 0, 0)

Can anyone help resolve the mystery? Would be great!

Thanks in advance, john m.
 

Attachments

  • TableDef.PNG
    TableDef.PNG
    28 KB · Views: 176

Mahares

Expert
Licensed User
Longtime User
Can anyone help resolve the mystery? Would be great!
1. You may want to try to insert the record like this and see what happens:
B4X:
strQuery="INSERT INTO History VALUES(?,?,?,?,?)" 
Starter.SQL1.ExecNonQuery2(strQuery, Array ("200915", 862, 122, "Anves3", "ka¦116¶pr¦25¶aq¦89¶ch¦64¶ft¦1¶ca¦5"))
2. Try to check the value of of each of these variables first to make sure you are inserting the correct values: Q.heute, Q.currentID, wtS.text, nName, A
3. Can you show the SELECT query you are using to extract the data.
4. When you post code, it is easier to read when you use code tags. Please use code tags.
I think, your problem may be in these 2 variables: nName, A
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
Perhaps you're trying to insert using the same id where originally you had the (0,0) field values.
 
Upvote 0

drgottjr

Expert
Licensed User
Longtime User
Array (Q.heute, Q.currentID, wtS.text, nName, A))

and then read it back ...

we have no way of knowing exactly what those values really are. and we have no way of knowing exactly how you are reading the record back. please provide those details.

and what is that tool you're using to display the db? is it that tool that's telling you the read is incorrect? or is it the app? depending on the tool, it wouldn't be the first time there was an issue between sqlite on android and a third party sqlite viewer.
 
Upvote 0

johnmie

Active Member
Licensed User
Longtime User
Thank you all very much for your prompt responses. I really appreciate that.

to Mahares
I did exactly what you suggested:
query = "INSERT INTO History VALUES(?,?,?,?,?)"
sql2.ExecNonQuery2(query,Array("200915", 862, 122, "Anves3", "ka¦116¶pr¦25¶aq¦89¶ch¦64¶ft¦1¶ca¦5"))
but got this error:
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: history.id (code 1555)
 
Upvote 0

drgottjr

Expert
Licensed User
Longtime User
primary key is unique. history.id is your primary key. how many times are you trying to insert it?
 
Upvote 0

johnmie

Active Member
Licensed User
Longtime User
to mc73
Then I removed the Primary Key check from field id - no error this time, but result still 200915, 862, 122, 0, 0
Tried again with my original Array (Q.heute, Q.currentID, wtS.text, nName, A)) - same result.
none of the five fields is Unique, can indeed repeat itself, particularly the first one (date) for several records in sequence.

to drgottjr
the values are exactly as listed in the previous post: string, number, number, string, string

The tool I use to write and read is the one that comes with B4A and the code I learned from Erel:
query = "SELECT * FROM history"
Dim rs As ResultSet = sql2.ExecQuery(query)
Dim D As String
Do While rs.NextRow
D =rs.GetInt("date")
Log(D&", "&rs.GetInt("id")&", "&rs.getint("weight")&", "&rs.GetInt("nick")&", "&rs.GetInt("vals"))
Loop
rs.Close

Maybe this provides some clues, I'm sure together we can solve the riddle.

best, john m.
 
Upvote 0

johnmie

Active Member
Licensed User
Longtime User
the issue came from here, you get string value with wrong type, you should use rs.GetString("nick") and rs.GetString("vals")
BRAVO, that did it!
It's that simple, just too bad I did not think of it myself. Sorry for having wasted your time.

Thanks a lot,
john m.
 
Upvote 0
Top