Android Question SQL insert into enigma

John W. Miescher

Member
Licensed 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: 74

Mahares

Expert
Licensed 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
Perhaps you're trying to insert using the same id where originally you had the (0,0) field values.
 
Upvote 0

drgottjr

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

John W. Miescher

Member
Licensed 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

Well-Known Member
Licensed User
primary key is unique. history.id is your primary key. how many times are you trying to insert it?
 
Upvote 0

John W. Miescher

Member
Licensed 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
Top