Android Question Insert data into sqlite database table

G

gmustafa

New Member
Licensed User
Longtime User
I can count rows form table but can't save data

IfFile.Exists(File.DirDefaultExternal,"HDSS.db")=FalseThen

File.Copy(File.DirAssets,"HDSS.db",File.DirRootExternal,"HDSS.db")

EndIf

SQL1.Initialize(File.DirRootExternal,"HDSS.db",True)


SQL1.ExecNonQuery("INSERT INTO BIRTH (UNIT,SEQ_NO,SRNO,REP_VILL)

VALUES

('"& unittxt.Text &"','"& pcnotxt.Text &"','"&slnotxt.Text&"','"&repvilltxt.Text&"')")


Msgbox("Birth Record Saved ","")
 
Sort by date Sort by votes
M

Merlot2309

Active Member
Licensed User
Longtime User
Hello,

B4X: 
IfFile.Exists(File.DirDefaultExternal,"HDSS.db")=FalseThen

File.Copy(File.DirAssets,"HDSS.db",File.DirRootExternal,"HDSS.db")

EndIf

SQL1.Initialize(File.DirRootExternal,"HDSS.db",True)


SQL1.ExecNonQuery("INSERT INTO BIRTH (UNIT,SEQ_NO,SRNO,REP_VILL)

VALUES

('"& unittxt.Text &"','"& pcnotxt.Text &"','"&slnotxt.Text&"','"&repvilltxt.Text&"')")

You check if file exists in DirDefaultExternal and, if not, then copy it to DirRootExternal.
Shouldn't that be DirDefaultExternal?
 
Upvote 0
M

Merlot2309

Active Member
Licensed User
Longtime User
Change the following:
SQL1.ExecNonQuery ...................

into:
B4X: 
SQL1.BeginTransaction
SQL1.ExecNonQuery...........................
SQL1.TransactionSuccessful
SQL1.EndTransaction
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
It is not required. A transaction is created internally when there isn't an active one.
Click to expand...
Are you saying that a begin and end transaction is no longer required when dealing with SQL action queries? I thought that was only applicable to: SQL.ExecNonQueryBatch where a begin/End transaction is automatically created for you.
 
Upvote 0
A

Aldy009

Member
Hi Erel,

I am new to B4A. And I encountered the same problem here. I have tried all of the above but still not working. Hope you can assist me. Thanks.
 
Upvote 0
A

Aldy009

Member
Erel said:
You should post your code and the error message.
Click to expand...
B4X: 
Dim SQL1 As SQL
   
    Dim DBFileName As String                : DBFileName = "reksadana.db"
'    Dim DBFileDir As String                    : DBFileDir = File.DirInternal
    Dim DBFileDir As String                    : DBFileDir = File.DirRootExternal
'    Dim DBFileDir As String                    : DBFileDir = File.DirDefaultExternal

If FirstTime Then
        If File.Exists(DBFileDir, DBFileName) = False  Then
            File.Copy(File.DirAssets, DBFileName, DBFileDir, DBFileName)
        End If
        SQL1.Initialize(DBFileDir, DBFileName, True)
    End If

SQL1.ExecNonQuery("INSERT INTO Portofolio VALUES (" & "'"& lblnama.Text &"'" & "," & "'"& txtunit.Text &"'" & ","& "'"& lblnab.Text &"')")

Basically there's no Error Message but nothing was inserted into the database. I tried replacing DBFileDir with those 3 but still no different.

I tried using DBUtils.CopyDBFromAssets and got this error message:

DBUtils.CopyDBFromAssets(DBFileName) = \
javac 1.7.0_25
src\b4a\reksadana\pendapatantetap.java:1385: error: unexpected type
mostCurrent._dbutils._copydbfromassets(mostCurrent.activityBA,_dbfilename) = "Reksadana.db";
^
required: variable
found: value
1 error
Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m

I am still new to B4A and just following the UserGuides and Examples but still don't know what to do.

Thanks before.

Regards

Aldy
 
Upvote 0
A

Aldy009

Member
I opened the 'reksadana.db' via SQLite Manager. Execute SELECT and no data was inserted.
B4X: 
#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    Dim SQL1 As SQL
   
    Dim DBFileName As String                : DBFileName = "reksadana.db"
'    Dim DBFileDir As String                    : DBFileDir = File.DirInternal
    Dim DBFileDir As String                    : DBFileDir = File.DirRootExternal
'    Dim DBFileDir As String                    : DBFileDir = File.DirDefaultExternal
    Dim StringUtils1 As StringUtils

    Dim NumberOfColumns As Int            : NumberOfColumns = 2
    Dim NumberOfRows As Int
    Dim RowHeight As Int                        : RowHeight = 30dip
    Dim RowLineWidth As Int                    : RowLineWidth = 1dip
    Dim RowHeight_1 As Int                    : RowHeight_1 = RowHeight - RowLineWidth
    Dim ColLineWidth As Int                    : ColLineWidth = 1dip
    Dim ColumnWidth(NumberOfColumns) As Int
        ColumnWidth(0) = 300dip
        ColumnWidth(1) = 125dip
    Dim ColumnWidth_1(NumberOfColumns) As Int
    Dim TotalColumnWidth(NumberOfColumns + 1) As Int
   
   
    Dim HeaderColor As Int                    : HeaderColor = Colors.Blue
    Dim HeaderFontColor As Int            : HeaderFontColor = Colors.Yellow
    Dim HeaderLineColor As Int            : HeaderLineColor = Colors.Yellow
    Dim LineColor As Int                        : LineColor = Colors.Black
    Dim CellColor As Int                        : CellColor = Colors.RGB(255,255,220)
    Dim FontColor As Int                        : FontColor = Colors.Black
    Dim FontSize As Float                        : FontSize = 14
    Dim Alignment As Int                        : Alignment = Gravity.CENTER
    Dim SelectedRow As Int                    : SelectedRow = -1
    Dim SelectedRowColor As Int            : SelectedRowColor=Colors.RGB(255,196,255)
    Dim SelectedCellColor As Int        : SelectedCellColor=Colors.RGB(255,150,255)
    Type RowCol (Row As Int, Col As Int)
    Dim MoveLeft0, MoveX0, MoveX1, DeltaScroll, DeltaX As Float
    Dim Time0 As Long
    Dim Timer1 As Timer
End Sub

Sub Globals
'    Dim DBTableName1 As String                : DBTableName1 = "Portofolio"
'    Dim DBTableName2 As String                : DBTableName2 = "Riwayat"

    Dim scvPersons As ScrollView
    Dim pnlHeader As Panel
    Dim skbScroll As SeekBar
    Dim pnlScroll As Panel
    Dim btnbeli As Button
    Dim btnkembali As Button
    Dim lblnab As Label
    Dim lblnama As Label
    Dim lblnilai As Label
    Dim pnlbeli As Panel
    Dim txtagen As EditText
    Dim txtunit As EditText
   
   
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Dim i As Int
   
    pnlScroll.Initialize("pnlScroll")    ' initialize the scroll panel
    Activity.AddView(pnlScroll, 0, Activity.Height - 40dip, 100%x, 40dip)
    pnlScroll.Color = Colors.Blue
   
    skbScroll.Initialize("skbScroll")    ' initilaize the Seekbar
    Activity.AddView(skbScroll, 0, Activity.Height - 40dip, 100%x, 40dip)
'    skbScroll.Visible = True
    skbScroll.Visible = False
   
    scvPersons.Initialize(0)    ' initialize the ScrollView
    scvPersons.Panel.Color = LineColor
    Activity.AddView(scvPersons,0,RowHeight,100%x,pnlScroll.Top-RowHeight)
   
    ' initialze the internal column width and left coordinates
    TotalColumnWidth(0) = ColLineWidth
    For i = 0 To NumberOfColumns - 1
        ColumnWidth_1(i) = ColumnWidth(i) - ColLineWidth
        TotalColumnWidth(i + 1) = TotalColumnWidth(i) + ColumnWidth(i)
    Next
   
    ' initializes the ScrollView width
    scvPersons.Width = TotalColumnWidth(NumberOfColumns)
   
    ' initializes the Seekbar max value
    skbScroll.Max = scvPersons.Width - Activity.Width
   
    SelectedRow = -1        ' sets the selected row index to -1
   
    ' loads the csv file
    LoadTableFromCSV(File.DirAssets, "tetap.csv", True)
'    SaveTableToCSV(File.DirRootExternal, "persons.csv")

    Timer1.Initialize("Timer1",100)
   
    Activity.LoadLayout("Beli")
    pnlbeli.Enabled = False
    pnlbeli.Visible = False   
   
'    File.Delete(DBFileDir, DBFileName)    ' used for testing, deletes the existing database
'   
        If File.Exists(DBFileDir, DBFileName) = False  Then
            File.Copy(File.DirAssets, DBFileName, DBFileDir, DBFileName)
        End If
'        File.OpenOutput(DBFileDir, DBFileName, True)
        SQL1.Initialize(DBFileDir, DBFileName, True)
   
   
'    If FirstTime = True Then
'        Timer1.Initialize("Timer1",100)
'    End If
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub LoadTableFromCSV(Dir As String,Filename As String,HeadersExist As Boolean)
    ClearAll    'Clears the previous table and loads the CSV file to the table
    Dim List1 As List
    Dim h() As String
    If HeadersExist Then
        ' Reads the csv file
        Dim headers As List
        List1 = StringUtils1.LoadCSV2(Dir, Filename, ",", headers)
        ' Sets the header names of the columns
        Dim h(headers.Size) As String
        For i = 0 To headers.Size - 1
            h(i) = headers.Get(i)
        Next
    Else
        ' Reads the csv file
        List1 = StringUtils1.LoadCSV(Dir, Filename, ",")
        ' Sets default header names
        Dim firstRow() As String
        firstRow = List1.Get(0)
        Dim h(firstRow.Length) As String
        For i = 0 To firstRow.Length - 1
            h(i) = "Col" & (i + 1)
        Next
    End If
    NumberOfColumns = h.Length    ' Gets the number of columns
    SetHeader(h)        ' Sets the headers
   
    NumberOfRows = 0
    For i = 0 To List1.Size - 1
        ' Fills the table
        Dim row() As String
        row = List1.Get(i)
        AddRow(row)
    Next
End Sub

Sub SaveTableToCSV(Dir As String, Filename As String)
    'Saves the current table into a csv file
    Dim headers(NumberOfColumns) As String
    For i = 0 To headers.Length - 1
        Dim l As Label
        l = pnlHeader.GetView(i)
        headers(i) = l.Text
    Next
    Dim list1 As List
    list1.Initialize
    For i = 0 To NumberOfRows - 1
        Dim row(NumberOfColumns) As String
        For c = 0 To NumberOfColumns - 1
            row(c) = GetCell(i, c)
        Next
        list1.Add(row)
    Next
    StringUtils1.SaveCSV2(Dir, Filename, ",", list1, headers)
End Sub

Sub Cell_Click
    Dim rc As RowCol
    Dim l As Label
   
    l = Sender
    rc = l.Tag
    SelectRow(rc)
    Activity.Title = "Cell: ("&rc.Row&", "& rc.Col&") "&GetCell(rc.Row, rc.Col)
   
    Dim tampungnama As String
    Dim tampungnab As String
   
    tampungnama = GetCell(rc.Row, 0)
    tampungnab = GetCell(rc.Row, 1)
   
    scvPersons.Visible = False
    pnlbeli.Enabled = True
    pnlbeli.Visible = True
    lblnama.Text = tampungnama
    lblnab.Text = tampungnab
   
End Sub

Sub SQLSaveBeli
DateTime.SetTimeZone(0)
Dim date, time As Long
date = DateTime.Now
time = date Mod DateTime.TicksPerDay
date = date - time

'    File.Delete(DBFileDir, DBFileName)    ' used for testing, deletes the existing database
   
'        If File.Exists(DBFileDir, DBFileName) = False  Then
        DBUtils.CopyDBFromAssets(DBFileName) = "Reksadana.db"
''        File.Copy(File.DirAssets, DBFileName, DBFileDir, DBFileName)
'        End If
       
'       
       
'   
'    SQL1.BeginTransaction
'    SQL1.ExecNonQuery("INSERT INTO Portofolio VALUES(A,1,1)")
    SQL1.ExecNonQuery("INSERT INTO Portofolio VALUES (" & "'"& lblnama.Text &"'" & "," & "'"& txtunit.Text &"'" & ","& "'"& lblnab.Text &"')")
'        SQL1.ExecNonQuery2("INSERT INTO " & DBTableName1 & " VALUES (nama,unit,nab)", Array As Object(lblnama.Text, txtunit.Text, lblnab.Text))
'        SQL1.TransactionSuccessful
'    SQL1.EndTransaction
   
'   
'    SQL1.BeginTransaction
''    SQL1.ExecNonQuery("INSERT INTO " & DBTableName2 & " VALUES " & (lblnama.Text, txtunit.Text, "BELI", lblnilai.Text. tgl, txtagen.Text)))
''    SQL1.ExecNonQuery2("INSERT INTO " & DBTableName2 & " VALUES (nama,unit,status,nilai,tgl,agen)", Array As Object(lblnama.Text, txtunit.Text, "BELI", lblnilai.Text, date, txtagen.Text))
'    SQL1.TransactionSuccessful
'    SQL1.EndTransaction

End Sub

Sub Header_Click
    Dim l As Label
    Dim col As Int
   
    l = Sender
    col = l.Tag
    Activity.Title = "Header clicked: " & col
End Sub

Sub SelectRow(rc As RowCol)
    Dim col As Int

    'Removes the color of previously selected row
    If SelectedRow > -1 Then
        For col = 0 To NumberOfColumns - 1
            GetView(SelectedRow, col).Color = CellColor
        Next
    End If

    SelectedRow = rc.Row
   
    'Sets the color of the selected row and selected cell
    For col = 0 To NumberOfColumns - 1
        If col = rc.col Then
            GetView(rc.Row, col).Color = SelectedCellColor
        Else
            GetView(rc.Row, col).Color = SelectedRowColor
        End If
    Next
End Sub

Sub GetView(Row As Int, Col As Int) As Label
    'Returns the label in the specific cell
    Dim l As Label
   
    l = scvPersons.Panel.GetView(Row * NumberOfColumns + Col)
    Return l
End Sub

Sub AddRow(Values() As String)
    'Adds a row to the table
    If Values.Length <> NumberOfColumns Then
        Log("Wrong number of values.")
        Return
    End If

    For i = 0 To NumberOfColumns - 1
        Dim l As Label
        l.Initialize("cell")
        l.Text = Values(i)
        l.Gravity = Alignment
        l.TextSize = FontSize
        l.TextColor = FontColor
        l.Color=CellColor
        Dim rc As RowCol
        rc.Initialize
        rc.Col = i
        rc.Row = NumberOfRows
        l.Tag = rc
        scvPersons.Panel.AddView(l, TotalColumnWidth(i), RowHeight * NumberOfRows, _
        ColumnWidth_1(i), RowHeight_1)
    Next
    NumberOfRows = NumberOfRows + 1
    scvPersons.Panel.Height = NumberOfRows * RowHeight
End Sub

Sub SetHeader(Values() As String)
    'Set the headers values
    If pnlHeader.IsInitialized Then Return 'should only be called once
    pnlHeader.Initialize("")
    pnlHeader.Color = HeaderLineColor
    For i = 0 To NumberOfColumns - 1
        Dim l As Label
        l.Initialize("Header")
        l.Text = Values(i)
        l.Gravity = Gravity.CENTER
        l.TextSize = FontSize
        l.Color = HeaderColor
        l.TextColor = HeaderFontColor
        l.Tag = i
        pnlHeader.AddView(l,TotalColumnWidth(i),0,ColumnWidth_1(i),RowHeight_1)
    Next
    Activity.AddView(pnlHeader,scvPersons.Left,0,scvPersons.Width,RowHeight)
End Sub

Sub SetCell(Row As Int, Col As Int, Value As String)
    'Sets the value of the given cell
    GetView(Row, Col).Text = Value
End Sub

Sub GetCell(Row As Int, Col As Int) As String
    'Gets the value of the given cell
    Return GetView(Row, Col).Text
End Sub

Sub ClearAll
    'Clears the table
    For i = scvPersons.Panel.NumberOfViews -1 To 0 Step -1
        scvPersons.Panel.RemoveViewAt(i)
    Next
    scvPersons.Panel.Height = 0
    SelectedRow = -1
End Sub

Sub skbScroll_ValueChanged (Value As Int, UserChanged As Boolean)
    'Moves the ScrollView horizontally
    pnlHeader.Left = - Value
    scvPersons.Left = - Value
End Sub

Sub pnlScroll_Touch (Action As Int, X As Float, Y As Float)
    Dim Left As Float
   
    Select Action
    Case Activity.ACTION_DOWN
        MoveLeft0 = -scvPersons.Left
        MoveX0 = X
        MoveX1 = X
        Time0 = DateTime.Now
    Case Activity.ACTION_MOVE
        Left = MoveLeft0 + (MoveX0 - X)
        Left = Max(Left, 0)
        Left = Min(Left, TotalColumnWidth(NumberOfColumns) - Activity.Width)
        scvPersons.Left = -Left
        pnlHeader.Left = -Left
        DeltaX = X - MoveX1
        MoveX1 = X
    Case Activity.ACTION_UP
        Time0 = (DateTime.Now - Time0)
        DeltaScroll = (X - MoveX0) / Time0 * 80
        If Abs(DeltaScroll) > 5dip AND Abs(DeltaX) > 10dip Then
            Timer1.Enabled = True
        End If
    End Select
End Sub

Sub Timer1_Tick
    Dim Left As Float
   
    Left = scvPersons.Left + DeltaScroll
    If Left > 0 Then
        Left = 0
        Timer1.Enabled = False
    End If
    If Left < -(TotalColumnWidth(NumberOfColumns) - Activity.Width) Then
        Left = -(TotalColumnWidth(NumberOfColumns) - Activity.Width)
        Timer1.Enabled = False
    End If
   
    scvPersons.Left = Left
    pnlHeader.Left = Left
   
    DeltaScroll = DeltaScroll / 1.2
    If Abs(DeltaScroll) < 5dip Then
        Timer1.Enabled = False
    End If
End Sub


Sub btnkembali_Click
    pnlbeli.Enabled = False
    pnlbeli.Visible = False
    scvPersons.Enabled = True
    scvPersons.Visible = True
    txtunit.Text = ""
    lblnilai.Text = ""
End Sub
Sub btnbeli_Click
    SQLSaveBeli
    StartActivity("TambahInvestasi")
End Sub
Sub txtunit_TextChanged (Old As String, New As String)
    Dim nilai As String
    Dim nab As String
    Dim xval As Double
    Dim yval As Double
    Dim hasil As Double
    Try
    nab = lblnab.Text
    nilai = txtunit.Text
    xval = nab
    yval = nilai
    hasil = xval * yval
   
    lblnilai.Text = hasil
    Catch
   
    End Try
   
'    lblnilai.Text = txtunit.Text * lblnab.Text
End Sub

Basically what I am doing is reading a .csv file (the result of my web scraping) in a ScrollView. After that If you cell_click the SCV than you end up with a panel 'pnlbeli' containing data from the cell_click and EditText(s) for Users to input by themselves. Then I want to save all the text in the panel in 'Reksadana.db' which is now already in my Files tab. That is when the query didn't work (without error messages).

As for the DBUtils, I do not know whether that is the right code to call CopyDBFromAssets.

Thanks before.

Regards

Aldy
 
Upvote 0
A

Aldy009

Member
I made the database from SQLite Manager ads on in Mozilla Firefox. I haven't used this app on device yet. The only time I used device is to Debug using a B4A Bridge app.
 
Upvote 0
A

Aldy009

Member
From the database on the Files tab and opened it with SQLite Manager? Or Am I wrong? Should there be another database copied in a different directory that I should have known?
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…