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
    28 KB · Views: 249
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
John W. Miescher said:
Can anyone help resolve the mystery? Would be great!
Click to expand...
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 ...
Click to expand...

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
rraswisak said:
the issue came from here, you get string value with wrong type, you should use rs.GetString("nick") and rs.GetString("vals")
Click to expand...
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
M

Mahares

Expert
Licensed User
Longtime User
John W. Miescher said:
t's that simple,
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Solved
Android Question Solved:Encryption Initialization Creation
3 4 5
Replies
80
Views
4K
Shelby
  • Solved
B4J Question [B4X] B4XCipher how to decrypt the result in vb.net
Replies
5
Views
2K
MicroDrie
G
  • Question
Android Question Number of entries in B4XTable ?
Replies
4
Views
968
Genricke
G
  • Question
Android Question [SOLVED]Error : attemp to re-open an already closed object
Replies
6
Views
1K
incendio
Bug? ExecQueryAsync with "?"
Replies
7
Views
3K
Emme Developer
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…