Android Question SQL DB not recognising table

Discussion in 'Android Questions' started by Paul Boyle, Mar 24, 2015.

  1. Paul Boyle

    Paul Boyle Member Licensed 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:

    Code:
    Sub Process_Globals

        
    Dim DS_SQL1 As SQL
        
    End Sub
    Code:
    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
    Code:
    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
    Code:
    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.'.

    Code:
    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
     
  2. Reviewnow

    Reviewnow Active Member Licensed User

    You overwrote your database after you created the tables
    Code:
    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
     
    Paul Boyle likes this.
  3. DonManfred

    DonManfred Expert Licensed User

    Code:
    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
     
    Paul Boyle likes this.
  4. Paul Boyle

    Paul Boyle Member Licensed 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,?,?,?)"

     
  5. mangojack

    mangojack Well-Known Member Licensed User

    Did you take in @DonManfred 's post ..

    your first line .. checking if db exists should be ..
    Code:
    'Check if database exists
    If File.Exists(File.DirInternal, "dartsmart.db") = False Then  ' @@ File.DirInternal !
     
    Paul Boyle likes this.
  6. Paul Boyle

    Paul Boyle Member Licensed 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.

    Code:
    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.
     
  7. DonManfred

    DonManfred Expert Licensed User

    what did you do? you used the SQL-dump as Databasefile?
     
  8. Paul Boyle

    Paul Boyle Member Licensed User

    1. Created the table in SQL Lite studio.
    2. Tools>>Export>>'A Database'>>Format = SQL (*.sql) >> saved local then imported from there to Dir Assets folder.
     
  9. DonManfred

    DonManfred Expert Licensed User

    and you are telling me this works? The exported SQL-File is NOT a Databasefile you can use in Android.
     
  10. Paul Boyle

    Paul Boyle Member Licensed User

    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.

    Code:
    #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, Null0True))

      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.
     

    Attached Files:

    Last edited: Mar 24, 2015
  11. Paul Boyle

    Paul Boyle Member Licensed 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:
     
  12. Paul Boyle

    Paul Boyle Member Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice