putting DateDialog into Sqlite db

nrasool

Member
Licensed User
Longtime User
Hey All,

Hope you are all well!, I love this languages, just doing my first app and it is going well, so thanks to Erel. I'm normally a lurker on the forum, there is great information on here, and I have been searching.

I'm been working on this issue, and just want to ask, if you use a datedialog to ask for a date, how are you putting into the sqlite database.

I have set the date in database as

B4X:
m.Put("TreatmentDate", DBUtils.DB_TEXT)

I was doing it like the following as an example

B4X:
TreatementDate = Dd.Show("Treatment Date", "iDialysis", "Save", "Cancel","",Null)
ret1 = (Dd.Year & "-" & Dd.Month & "-" & Dd.DayOfMonth)
Log(ret1)

SQL.ExecNonQuery("INSERT into table values " & ret1 & ")")

But when viewing the results using DBUtils webtable view as follows
B4X:
   WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL, _
      "SELECT date(TreatmentDate / 1000, 'unixepoch', 'localtime') As Date,  FROM table" _
         , Null, 0, True))

It just shows the default

How can I show the user input, I'm thinking I'm putting in the formation wrong, please could I have some advise on this, on how other people put the user input of a date into the database

Much appreciate all your help in advance

Many thanks

Kind Regards
 

nrasool

Member
Licensed User
Longtime User
I have whipped up a quick example for you to see the issue

B4X:
Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.
   Dim SQL As SQL
End Sub

Sub Globals
   'These global variables will be redeclared each time the activity is created.
   'These variables can only be accessed from this module.
   Dim WebView1 As WebView
End Sub

Sub Activity_Create(FirstTime As Boolean)
   'Do not forget to load the layout file created with the visual designer. For example:
   'Activity.LoadLayout("Layout1")
   If FirstTime Then
      SQL.Initialize(File.DirRootExternal, "test.db", True)
   End If
   Activity.LoadLayout("1")
   'This is to show how to use the DBVersion Subs. 
   Dim DBVersion, CurrentDBVersion As Int
   'DBVersion = DBUtils.GetDBVersion(SQL)
   Log("Database Version: " & DBUtils.GetDBVersion(SQL))
   
   If SQL.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='bpmain'") = 0 Then
       'don't exist
      'Creating Tables for 1st time
      Dim m As Map
      m.Initialize
      m.Put("Id", DBUtils.DB_INTEGER)
      m.Put("Date", DBUtils.DB_TEXT)
      DBUtils.CreateTable(SQL, "main", m, "Id")
      Log("Creating main table")
   Else
       'exist
      Log("main table already exists")
   End If
   
   Dim Dd As DateDialog
   Dim ret1 As String
   Dim Cursor As Cursor
   
   Dd.ShowCalendar = False
   Dd.Year = DateTime.GetYear(DateTime.Now)
   Dd.Month = DateTime.GetMonth(DateTime.Now)   
   Dd.DayOfMonth = DateTime.GetDayOfMonth(DateTime.Now)
   Date1 = Dd.Show("Date", "Test", "Save", "Cancel","",Null)
   ret1 = (Dd.Year & "-" & Dd.Month & "-" & Dd.DayOfMonth)
   Log(ret1)
   
   Cursor = SQL.ExecQuery("SELECT Id FROM main")    
    If Cursor.RowCount > 0 Then
    
    For i = 0 To Cursor.RowCount - 1
        Cursor.Position = i
        Dim NewID As Int
        NewID = Cursor.GetInt("Id")
    Next
    End If
    
    NewID = NewID +1
   
   SQL.ExecNonQuery("INSERT into main values (" & NewID & "," & ret1 & ")")
   
   ShowTableInWebView
   

End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub ShowTableInWebView
   WebView1.Visible = True
   WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL, "SELECT * FROM main", Null, 0, True))
End Sub

You need to create a layout with a webview1 object and also use DBUtils, http://www.b4x.com/forum/basic4andr...s-android-databases-now-simple.html#post47359

Kind Regards
 

Attachments

  • sqliteanddatequestion.zip
    6.7 KB · Views: 195
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I corrected your part of the code that was given you trouble. The rest is ok: Here is the corrected section:
B4X:
Dim Dd As DateDialog
   Dim ret1 As String
   Dim Cursor As Cursor
   Dim Date1 As String
   Dd.Year = DateTime.GetYear(DateTime.Now)
   Dd.Month = DateTime.GetMonth(DateTime.Now)   
   Dd.DayOfMonth = DateTime.GetDayOfMonth(DateTime.Now)
   ret1 = Dd.Show("Date", "Test", "Save", "Cancel","",Null)
   Date1 = (Dd.Year & "-" & Dd.Month & "-" & Dd.DayOfMonth)
   Log(ret1)
   
   Cursor = SQL.ExecQuery("SELECT Id FROM main")    
    If Cursor.RowCount > 0 Then
    
    For i = 0 To Cursor.RowCount - 1
        Cursor.Position = i
        Dim NewID As Int
        NewID = Cursor.GetInt("Id")
    Next
    End If
    
    NewID = NewID +1
   
      SQL.ExecNonQuery2("INSERT into main values (?,?)", Array As Object(NewID,Date1))
      ShowTableInWebView
 
Upvote 0
Top