Android Question SQL DB not recognising table

Paul Boyle

Member
Licensed User
Longtime User
I keep getting an error that table 'scoring' doesn't exist. The database wasn't being created so I created one with an SQLLite studio and added it to the files tab, but still no success.

MAIN ACTIVITY:

B4X:
Sub Process_Globals

	Dim DS_SQL1 As SQL
	
End Sub

B4X:
Sub Activity_Create(FirstTime As Boolean)
	
	If FirstTime Then
	
		'Check if database exists
		If File.Exists(File.DirAssets, "dartsmart.db") = False Then
			'copy the default DB
			File.Copy(File.DirAssets, "dartsmart.db", File.DirInternal, "dartsmart.db")
			'if not, initialise it
			DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
			'and then create the database tables
			CreateDatabaseTables
			'copy the default DB
			File.Copy(File.DirAssets, "dartsmart.db", File.DirInternal, "dartsmart.db")
		Else
			'initialise it
			DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
		End If 
	End If
	
	
	'Load Layout
		Activity.LoadLayout("lay_Home")
		
End Sub

B4X:
Sub CreateDatabaseTables

	Dim CRE_Scoring As String
	
	CRE_Scoring = "CREATE TABLE IF NOT EXISTS scoring (ID INTEGER PRIMARY KEY,Visits TEXT, Ave TEXT, Points TEXT)"
	
	DS_SQL1.ExecNonQuery(CRE_Scoring)
	

End Sub

B4X:
Sub btn_Scoring_Click

	'Load Layout/Activity
		StartActivity(Page_Scoring)
	
End Sub

In an activity named 'Page_Scoring' I have a 'Save' button to insert data from views into the table 'scoring', this is where it fails saying there is no table called 'scoring.'.

B4X:
Sub btn_SaveGame_Click

	Dim INS_scoring As String
	
	INS_scoring = "INSERT INTO scoring VALUES (NULL, ?, ?, ?)"
	
	Main.DS_SQL1.ExecNonQuery2(INS_scoring, Array As String( lbl_Visits.Text, lbl_3DA.Text, lbl_Total.text))
	
End Sub

Any help appreciated.

Many Thanks

P
 

Reviewnow

Active Member
Licensed User
Longtime User
You overwrote your database after you created the tables
B4X:
 If FirstTime Then
   
        'Check if database exists
        If File.Exists(File.DirAssets, "dartsmart.db") = False Then
            'copy the default DB
            File.Copy(File.DirAssets, "dartsmart.db", File.DirInternal, "dartsmart.db")
            'if not, initialise it
            DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
            'and then create the database tables
            CreateDatabaseTables
            'copy the default DB
           'This is where you overwrote your database remove this line as you copied the database already above
           ' File.Copy(File.DirAssets, "dartsmart.db", File.DirInternal, "dartsmart.db")
        Else
            'initialise it
            DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
        End If
    End If
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
B4X:
    If FirstTime Then
   
        'Check if database exists
        If File.Exists(File.DirInternal, "dartsmart.db") = False Then
          ' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Here was the problem

            'copy the default DB
            File.Copy(File.DirAssets, "dartsmart.db", File.DirInternal, "dartsmart.db")
            'if not, initialise it
            DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
            'and then create the database tables
            CreateDatabaseTables
            'copy the default DB
            File.Copy(File.DirAssets, "dartsmart.db", File.DirInternal, "dartsmart.db")
        Else
            'initialise it
            DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
        End If
    End If
 
Upvote 0

Paul Boyle

Member
Licensed User
Longtime User
I changed to the below, but still getting the same error.

"An error has occurred in sub: android.database.sqllite.SQLiteException:no such table:scoring (code 1):; while compiling: INSERT INTO scoring VALUES(NULL,?,?,?)"

You overwrote your database after you created the tables
B4X:
 If FirstTime Then
 
        'Check if database exists
        If File.Exists(File.DirInternal, "dartsmart.db") = False Then
            'copy the default DB
            File.Copy(File.DirAssets, "dartsmart.db", File.DirInternal, "dartsmart.db")
            'if not, initialise it
            DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
            'and then create the database tables
            CreateDatabaseTables
        Else
            'initialise it
            DS_SQL1.Initialize(File.DirInternal, "dartsmart.db", True)
        End If
    End If
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
Did you take in @DonManfred 's post ..

your first line .. checking if db exists should be ..
B4X:
'Check if database exists
If File.Exists(File.DirInternal, "dartsmart.db") = False Then  ' @@ File.DirInternal !
 
Upvote 0

Paul Boyle

Member
Licensed User
Longtime User
I have gotten it to work. I exported the database from SQL Studio as an SQL file (dartsmart.sql) rather than dartsmart.db, and added it to the files in B4A. Then altered the code to dartsmart.sql instead of dartsmart.db.

B4X:
If FirstTime Then

    'Check if database exists
   If File.Exists(File.DirInternal, "dartsmart.sql") = False Then
   'copy the default DB
   File.Copy(File.DirAssets, "dartsmart.sql", File.DirInternal, "dartsmart.sql")
   'if not, initialise it 
   DS_SQL1.Initialize(File.DirInternal, "dartsmart.sql", True)
   'and then create the database tables 
   CreateDatabaseTables
Else
   'initialise it 
   DS_SQL1.Initialize(File.DirInternal, "dartsmart.sql", True)
   EndIf
EndIf

Thanks for your help guys.
 
Upvote 0

Paul Boyle

Member
Licensed User
Longtime User
and you are telling me this works? The exported SQL-File is NOT a Databasefile you can use in Android.

Works on my S5. The btn_Save_click inserts into the table. I then have a separate activity "Page_Scoring_Table" for displaying the table in a webview.

Code below, screenshot of table on S5 attached.

B4X:
#Region  Activity Attributes
  #FullScreen: False
  #IncludeTitle: True
#End Region


Sub Process_Globals

'  These global variables will be declared once when the application starts.
'  These variables can be accessed from all modules.

  DimCurrentIndex = -1AsInt      ' index of the current entry
  DimRowNumber = 0AsInt        ' number of rows


  DimIDListAsList                      ' list containing the IDs of the database
                                             'we need it because the IDs can be different from the List indexes
                                             'If we delete an entry its ID Is lost
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.

  'Declare views
     Privatewv_ScoringTableAsWebView
     Privatelbl_ScoringIDAsLabel
     Privatebtn_ScoringDelAsButton, btn_ScoringFilterAsButton, btn_ScoringDelAllAsButton


' Used In ExecuteHtml

  DimHtmlCSSAsString

  HtmlCSS = "table {width: 100%;border: 1px solid #cef;text-align: left; }" _
  & " th { font-weight: bold;  background-color: #acf; border-bottom: 1px solid #cef; }" _
  & "td,th {  padding: 4px 5px; }" _
  & ".odd {background-color: #def; } .odd td {border-bottom: 1px solid #cef; }" _
  & "a { text-decoration:none; color: #000;}"

End Sub


Sub Activity_Create(FirstTime AsBoolean)

  'Load Layout
     Activity.LoadLayout("lay_Scoring_Table")
     Activity.Title = "Scoring Records"

  ShowTable

End Sub

Sub Activity_Resume

End Sub


Sub Activity_Pause (UserClosed AsBoolean)

End Sub


Sub ShowTable

  'Shows the scoring stats In a table In a WebView

  Dim SEL_ScoringTable AsString

  SEL_ScoringTable = "SELECT ID, Visits As [Visits], Ave As [3 Dart Ave], Points As [Points] FROM scoring"

  'displays the database In a table
  wv_ScoringTable.LoadHtml(ExecuteHtml(Main.DS_SQL1, SEL_ScoringTable, Null, 0, True))

  ReadDataBaseIDs

  UpdateScoringID

End Sub



Sub ReadDataBaseIDs


  'Reads the database IDs In IDList
  Dim Row AsInt
  Dim Cursor1 AsCursor

  IDList.Initialize  'initialize the ID list

  'We read only the ID column AND put them In a List
  Cursor1 = Main.DS_SQL1.ExecQuery("SELECT ID FROM scoring")

  If Cursor1.RowCount > 0Then  'check if entries exist

     RowNumber = Cursor1.RowCount  'set the row count variable
     IDList.Initialize  'initialize the ID list

     For Row = 0To  RowNumber - 1

        Cursor1.Position = Row  'set the Cursor to each row

        IDList.Add(Cursor1.GetInt("ID"))  'add the ID's to the ID list

     Next

     CurrentIndex = 0  'set the current index to 0

  Else

     CurrentIndex = -1  'set the current index to -1, no selected item

     ToastMessageShow("No items found", False)

  EndIf

  Cursor1.Close  'close the cursor, we don't need it anymore

End Sub


Sub UpdateScoringID


  Dim Query AsString
  Dim Curs AsCursor

  Query = "SELECT ID FROM scoring WHERE ID = " & IDList.Get(CurrentIndex)

  Curs = Main.DS_SQL1.ExecQuery(Query)

  Curs.Position = 0

  lbl_ScoringID.Text = Curs.GetString("ID")

  Curs.Close

End Sub


Sub ExecuteHtml(SQLAsSQL, Query AsString, StringArgs() AsString, Limit AsInt, Clickable AsBoolean) AsString

  'This routine is extracted from the DBUtils code module
  'Creates a html text that displays the data in a table.
  'The style of the table can be changed by modifying HtmlCSS variable.

  Dim cur AsCursor

  If StringArgs <> NullThen
     cur = SQL.ExecQuery2(Query, StringArgs)
  Else
     cur = SQL.ExecQuery(Query)
  EndIf

  Log("ExecuteHtml: " & Query)

  If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount

  Dim sb AsStringBuilder

  sb.Initialize

  sb.Append("<html><body>").Append(CRLF)
  sb.Append("<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
  sb.Append("<table><tr>").Append(CRLF)

  For i = 0To cur.ColumnCount - 1
     sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
  Next

  sb.Append("</tr>").Append(CRLF)

  For row = 0To Limit - 1
     cur.Position = row

     If row Mod2 = 0Then
        sb.Append("<tr>")
     Else
       sb.Append("<tr class='odd'>")
     EndIf

     For i = 0To cur.ColumnCount - 1

        sb.Append("<td>")

           If Clickable Then
              sb.Append("<a href='http://").Append(i).Append(".")
              sb.Append(row)
              sb.Append(".com'>").Append(cur.GetString2(i)).Append("</a>")
           Else
              sb.Append(cur.GetString2(i))
           EndIf

        sb.Append("</td>")
    Next

     sb.Append("</tr>").Append(CRLF)
  Next

  cur.Close
  sb.Append("</table></body></html>")
  Return sb.ToString

End Sub


Sub wbvTable_OverrideUrl (Url AsString) AsBoolean

  'parse the row and column numbers from the URL
  Dim values() AsString

  values = Regex.Split("[.]", Url.SubString(7))

  Dim col, row AsInt

  col = values(0)
  row = values(1)
  CurrentIndex = row

  UpdateScoringID

  ReturnTrue      'Don't try to navigate to this URL

End Sub




EDIT: Only thing is, if I touch the screen when the table is displayed it tries to navigate to the URL and errors with a web browser error screen. Would like to stop this if possible.
 

Attachments

  • Screenshot_2015-03-24-11-32-40.png
    Screenshot_2015-03-24-11-32-40.png
    159.5 KB · Views: 109
Last edited:
Upvote 0

Paul Boyle

Member
Licensed User
Longtime User
I tried adding extra columns and overwriting the existing dartsmart.sql file with an updated version, now i'm back to square one, no such table exists :(. Round 2.:mad:
 
Upvote 0

Paul Boyle

Member
Licensed User
Longtime User
and you are telling me this works? The exported SQL-File is NOT a Databasefile you can use in Android.

It also reads the .db file extension. When I changed the table layout I needed to delete the DirInternal .db file and then load the new layout .db file and copy from DirAssets to DirExternal.

Working again now with a .db file, but it worked before with only an .sql file in the DirAssets folder. I'll stick with the .db file though.
 
Upvote 0
Top