Android Question SQL insert into enigma

[johnmie]

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.

 

[Mahares]

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:

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

 

[mc73]

Perhaps you're trying to insert using the same id where originally you had the (0,0) field values.

 

[drgottjr]

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.

 

[johnmie]

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)

 

[drgottjr]

primary key is unique. history.id is your primary key. how many times are you trying to insert it?

 

[johnmie]

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.

 

[rraswisak]

rs.GetInt("nick")&", "&rs.GetInt("vals")

the issue came from here, you get string value with wrong type, you should use rs.GetString("nick") and rs.GetString("vals")

 

[johnmie]

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.

 

[Mahares]

t's that simple,

That is what happens when the OP does not post enough code. It becomes a guessing game even for a simple problem. We could have avoided several posts if the OP wrote the relevant code. By the way: Your log statement will look a lot neater if you use Smart String Literals

 

[johnmie]

Noted. Thank you for your help and please be indulgent with amateurs.