Android Question SQL query

[harinder]

in the following code, I intend to filter data in my table as per P1 and ACType, for which it goes through each item of a spinner-spnAC. But, I am getting an out of bound exception. Where am I going wrong? thnx..

Dim gal() As String
For j= 0 To spnAC.Size -1
     gal(j) = spnAC.GetItem(j)
     Dim MyQuery As String
     MyQuery="SELECT SUM(dayhr) ||':'|| SUM(daymin) ||':'|| SUM(nitehr) ||':'||         SUM(nitemin) ||':'|| SUM(instrhr) ||':'|| SUM(instrmin)   FROM " & _
     Main.DBTableName &" where capacity = 'P1' and ACType =' "& gal(j) &"' "
NEXT

 

[harinder]

a basic question here..how can I concatenate SQL query, as it goes right into eternity

 

[Mahares]

a basic question here..how can I concatenate SQL query, as it goes right into eternity

The easiest way is is to use smart string literals to construct your statement. I have taken your SQL statement in your post #18 and converted it to smart string literal:

Dim Query2 As String=$"
    SELECT ACType,sum(DualDay), sum(DualNite),sum(P1Day), sum(P1Nite),
    sum( P2Day),sum( P2Nite) from (select * from  ${main.DBTableprior}
    UNION ALL SELECT ACType,0,0,0,0,0,0,0,0,0,0,0,0,P1Dayhr,P1Daymin,P1Nitehr,
    P1Nitemin,0,0,0,0,InstrFlghr,InstrFlgmin,0,0 from ${main.DBTablepee1})
    GROUP BY ACType"$

    Main.SQL1.ExecQuery(Query2)

Here is the link for smart string literal tutorial:
https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/#content

 

[harinder]

Thank You Mahares...

 

[harinder]

The easiest way is is to use smart string literals to construct your statement. I have taken your SQL statement in your post #18 and converted it to smart string literal:

Dim Query2 As String=$"
    SELECT ACType,sum(DualDay), sum(DualNite),sum(P1Day), sum(P1Nite),
    sum( P2Day),sum( P2Nite) from (select * from  ${main.DBTableprior}
    UNION ALL SELECT ACType,0,0,0,0,0,0,0,0,0,0,0,0,P1Dayhr,P1Daymin,P1Nitehr,
    P1Nitemin,0,0,0,0,InstrFlghr,InstrFlgmin,0,0 from ${main.DBTablepee1})
    GROUP BY ACType"$

    Main.SQL1.ExecQuery(Query2)

1. How can I concatenate this code:

Dim Query1 As String= "UPDATE prior Set ACType=?,Dualday=?,Dualnite=?,P1day=?,P1nite=?,P2day=?,P2nite=?,Instrflg=?,TypeTotal=?,DualDayhr=?,Dualdaymin=?,Dualnitehr=?,Dualnitemin=?,P1dayhr=?,P1daymin=?,P1nitehr=?,P1nitemin=?,P2dayhr=?,P2daymin=?,P2nitehr=?,P2nitemin=?,Instrflghr=?,Instrflgmin=? WHERE ACType = "&bb

2. Additionally, is it possible to replace entire row in a db from a list using sql , without naming each column(other than deleting old row and adding new one)? thnx

 

[Mahares]

How can I concatenate this code:

Here are two way to concatenate the sql statement:

Dim bb As String = "harinder"
   Dim Query1 As String= "UPDATE prior Set ACType=?,Dualday=?,Dualnite=?,P1day=?,P1nite=?," _
    & "P2day=?,P2nite=?,Instrflg=?,TypeTotal=?,DualDayhr=?,Dualdaymin=?,Dualnitehr=?," _
    & "Dualnitemin=?,P1dayhr=?,P1daymin=?,P1nitehr=?,P1nitemin=?,P2dayhr=?,P2daymin=?," _
    & "P2nitehr=?,P2nitemin=?,Instrflghr=?,Instrflgmin=? WHERE ACType = " & bb

or using the smart String Literal:

Dim Query1 As String= $"UPDATE prior Set ACType=?,Dualday=?,Dualnite=?,P1day=?,P1nite=?,
    P2day=?,P2nite=?,Instrflg=?,TypeTotal=?,DualDayhr=?,Dualdaymin=?,Dualnitehr=?,
    Dualnitemin=?,P1dayhr=?,P1daymin=?,P1nitehr=?,P1nitemin=?,P2dayhr=?,P2daymin=?,
    P2nitehr=?,P2nitemin=?,Instrflghr=?,Instrflgmin=? WHERE ACType = ${bb} "$

Additionally, is it possible to replace entire row in a db from a list using sq

As for the list, this gives you an idea and hint as we do not know the exact content of your list:

 Dim MyList As List
    MyList.Initialize
    MyList=File.ReadList(File.DirInternal,"mydta.txt")  'folder and file name where the list is saved
    For Each s As String In MyList   'iterate through the list
        Dim strArray() As String =Regex.Split(",",s)  'assuming all list items are separated by commas
        SQL1.ExecNonQuery2(Query1,strArray)    'query1 is one of the above sql statements
    Next

 

[harinder]

Thank you Mahares..

 

[harinder]

Harinder,

Just an observation... I wrote my first logbook program nearly 25 years ago and have written several others since. If you try to store the various times P1/P2/P1(u/s) day/night separately in Sqlite as hours and minutes then the SQL becomes unbelievably complicated as you are finding out! Likewise avoid storing values simply as decimal hours.... you will run into rounding errors when you try and convert back to hh:mm.

Consider storing each category as integer minutes, it becomes so much simpler. OK you have to error trap user input and convert minutes to hh:mm when you present it but single values are infinitely simpler to handle.

Hello Richard..can you plse share the code for converting minutes to HH:MM and vice-versa? Thnx...

 

[harinder]

wondering if this will work for minutes to HH:MM:

 Dim hr, min as int
     hr=hr+ Floor(min/60)
     min=min Mod 60

 

[Erel]

Please start a new thread for a new question.