Android Question JdbcSQL 'insert into' problem

[Mostez]

Hello
I'm using this code to insert data into MS SQL table, when I open connection for first time the code display save fail message though the data is saved OK into database table!!, and it works OK after then without any problems for next 'insert into' calls (display saved OK)! any ideas?

thanks

Sub Connect As ResumableSub
    SQLserver.InitializeAsync("MSSQL", SysDeclare.dbDriver, SysDeclare.dbJDBCurl, SysDeclare.dbUID, SysDeclare.dbPassword)
    Wait For MSSQL_Ready (cSuccess As Boolean)
    If cSuccess = False Then
        Log("Check unfiltered logs for JDBC errors.")
    Else
        Log("SQL connected OK")
    End If
    Return cSuccess
End Sub

Sub ExecNonQuery 
    
    Wait For (Connect) Complete (eSuccess As Boolean)
    If eSuccess Then
        Try
            SQLserver.BeginTransaction
            SQLserver.ExecNonQuery (SQLqueryString)
            SQLserver.TransactionSuccessful
            QueryResult = True
            CloseConnection
        Catch
            QueryResult = False
            CloseConnection
            Log(LastException)
        End Try
        'CloseConnection
    End If

[CODE=b4x]Dim SQLcommand As StringBuilder
    SQLcommand.Initialize
    SQLcommand.Append("INSERT INTO [MOBILE_VEHICLES_LOG]")
    SQLcommand.Append(" (")
    SQLcommand.Append("[VehicleID], ")
    SQLcommand.Append("[TrailerID], ")
    SQLcommand.Append("[DriverID], ")
    SQLcommand.Append("[Direction], ")
    SQLcommand.Append("[IsLoaded], ")
    SQLcommand.Append("[ReasonCode], ")
    SQLcommand.Append("[Comment], ")
    SQLcommand.Append("[DeviceID], ")
    SQLcommand.Append("[UID], ")
    SQLcommand.Append("[DateTime]")
    SQLcommand.Append(") ")
    SQLcommand.Append("VALUES ")
    SQLcommand.Append("(")

    SQLcommand.Append("'" & VehicleID & "'" & ",")
    SQLcommand.Append("'" & TrailerID & "'" & ",")
    SQLcommand.Append("'" & DriverID & "'" & ",")
    SQLcommand.Append("'" & Direction & "'" & ",")
    SQLcommand.Append("'" & Isloaded & "'" & ",")
    SQLcommand.Append("'" & ReasonCode & "'" & ",")
    SQLcommand.Append("'" & Comment & "'" & ",")
    SQLcommand.Append("'" & DeviceID & "'" & ",")
    SQLcommand.Append("'" & UID & "'" & ",")
    SQLcommand.Append( dDateTime )
    SQLcommand.Append(" )")
    
    SysDeclare.GetJDBCurl
    Starter.SQLqueryString = SQLcommand.ToString
    Starter.ExecNonQuery
    
    If Starter.QueryResult = True Then
        Toast.Show("e-trip Mobile, Saved OK")
    Else
        Toast.Show("e-trip Mobile, Not Saved")
        PhoneVibrate.Vibrate(200)
    End If

End Sub[/CODE]

 

[Erel]

Very bad way to build the query: [B4X] "Code Smells" - common mistakes and other tips

Post the error message.

 

[Mostez]

there is no error message, i mean the code directs to display error though data is saved (for the first record only)

If Starter.QueryResult = True Then
        Toast.Show("e-trip Mobile, Saved OK")
    Else
        Toast.Show("e-trip Mobile, Not Saved") 'this message is displayed when i insert data for the very first time, though data is saved
        PhoneVibrate.Vibrate(200)
    End If

 

[Mostez]

thanks Erel for your advice, will change the code. I solved the problem for now as follows,
I changed the query sub to ResumableSub with returned boolean, and added wait for to sub call

Sub ExecNonQuery As ResumableSub
    Dim IsOK As Boolean
    Wait For (Connect) Complete (eSuccess As Boolean)
    If eSuccess Then
        Try
            SQLserver.ExecNonQuery (SQLqueryString) ' i want to test result
            IsOK = True
        Catch
            IsOK = False
            CloseConnection
            Log(LastException)
        End Try
        'CloseConnection
    End If
    Return IsOK
End Sub

Wait for (CallSub(Starter,"ExecNonQuery")) complete(Saved As Boolean)
    If Saved = True Then 'Starter.QueryResult = True Then
        Toast.Show("e-trip Mobile, Saved OK")
    Else
        Toast.Show("e-trip Mobile, Not Saved")
        PhoneVibrate.Vibrate(200)
    End If