Hi all,
I have some issues with inserting and updateing values with the SQL library on SQLite database.
To sum it up, the user Scans a barcode, i check on the database to see if the barcode is present, then i save the information in the database useing:
depending on the situation , I'll go to one of two:
or
After inserting a few lines, i'll send the data to the main database, via a web service, all is good there, the web service returns an ID that needs to be updated on the local database.
The id is returned, i check with 2 sql put in "Log()" if the records are on the local databes(on android), and only the one not updated is, i update the value, and check again if the old id is on the database, and it is not, and if the new one is, and it is.
The problems start if I shut down the app and reopen it. All data, except the first the record, from my las scans/inserts are missing.
Also i tryed to transfer the local database via TeamViewer, and ended up with the same result, only the first value i inserted is present and with the id not updated with the value received, value that i have updated and confirmed before the restart of the app.
Thanks,
Bogdan
I have some issues with inserting and updateing values with the SQL library on SQLite database.
To sum it up, the user Scans a barcode, i check on the database to see if the barcode is present, then i save the information in the database useing:
UpdateOrInsert_Map:
Public Sub UpdateOrInsert_Map(TableName As String, MapToSave As Map, ID As String, IDField As String)
Try
If ID = Globals.SQLDataProvider.SQL_GetScalar("SELECT ID FROM " & TableName & " WHERE " & IDField & " = " & ID) Then'UPDATE DB ROW
Log("Update item")
Globals.SQLDataProvider.Update_Map(TableName,MapToSave,ID,IDField)
Else
Log("Insert item")
'INSERT ROW INTO DB
Globals.SQLDataProvider.Insert_Map(TableName,MapToSave)
End If
Catch
Log(LastException)
End Try
End Subdepending on the situation , I'll go to one of two:
UpdateMapByID:
public Sub UpdateMapByID(SQL As SQL, TableName As String, Map1 As Map, IDField As String, IDValue As String)
Dim sb, set As StringBuilder
SQL.BeginTransaction
Try
sb.Initialize
set.Initialize
Dim listOfValues As List
listOfValues.Initialize
For Each col As String In Map1.Keys
Dim value As Object = Map1.Get(col)
If set.ToString <> "" Then
set.Append (", ")
End If
set.Append (EscapeField(col) & " = '" & value & "'" & CRLF)
Next
sb.Append("UPDATE [" & TableName & "] " & CRLF)
sb.Append(" SET " & CRLF)
sb.Append(set)
sb.Append("WHERE " & IDField & " = " & IDValue )
SQL.ExecNonQuery(sb)
SQL.TransactionSuccessful
Catch
Log(LastException)
#If B4i OR B4J
SQL.Rollback
#End If
End Try
#If B4A
SQL.EndTransaction
#End If
End Sub
InsertMap:
public Sub InsertMap(SQL As SQL, TableName As String, Map1 As Map)
Dim sb, columns, values As StringBuilder
SQL.BeginTransaction
Try
sb.Initialize
columns.Initialize
values.Initialize
Dim listOfValues As List
listOfValues.Initialize
sb.Append("INSERT INTO [" & TableName & "] (")
'Dim m As Map = ListOfMaps.Get(i1)
For Each col As String In Map1.Keys
Dim value As Object = Map1.Get(col)
If listOfValues.Size > 0 Then
columns.Append(", ")
values.Append(", ")
End If
columns.Append(EscapeField(col))
values.Append("?")
listOfValues.Add(value)
Next
sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(");").Append(" COMMIT;")
SQL.ExecNonQuery2(sb.ToString, listOfValues)
SQL.TransactionSuccessful
Catch
Log(LastException)
Log("error insert map")
#If B4i OR B4J
SQL.Rollback
#End If
End Try
#If B4A
SQL.EndTransaction
Log("insert map ok")
#End If
End SubAfter inserting a few lines, i'll send the data to the main database, via a web service, all is good there, the web service returns an ID that needs to be updated on the local database.
The id is returned, i check with 2 sql put in "Log()" if the records are on the local databes(on android), and only the one not updated is, i update the value, and check again if the old id is on the database, and it is not, and if the new one is, and it is.
The problems start if I shut down the app and reopen it. All data, except the first the record, from my las scans/inserts are missing.
Also i tryed to transfer the local database via TeamViewer, and ended up with the same result, only the first value i inserted is present and with the id not updated with the value received, value that i have updated and confirmed before the restart of the app.
Thanks,
Bogdan