First Program in B4A

[paul3252]

Code causing me a problem.

Sub SQLTableRead
Dim i As Int
Dim Cursor1 As Cursor
Dim txt, wtxt As String
txt="SELECT * FROM qa WHERE category like '%"&search&"%'"
Cursor1=SQL1.ExecQuery(txt)
lstQuestions.Initialize("lstQuestions")
Activity.AddView(lstQuestions, 0, 0, 100%x, 75%y)
For i=0 To Cursor1.RowCount-1
Cursor1.Position=i
lstQuestions.AddSingleLine(Cursor1.GetString("Question"))
Next
End Sub

I am just experimenting with a very simple questions and answers app., more to test B4A than anything else. I have created an sqlite database and added to my files tab. When I click a button which just sets the 'search' variable and executes the above code I get an error.

no such table: qa:, while compiling: SELECT ... etc.

The table is called qa and is inside a database named fedqa.db.

Elsewhere I declare (variables set accordingly)

If FirstTime Then
SQL1.Initialize(dbfiledir, dbfilename, True)
End If

Clearly it cannot see the table named qa, but how do I know even if my program can see the database?

 

[NJDude]

Are you copying the DB to the device?

If File.Exists(File.DirDefaultExternal, "fedqa.db") = False Then

   File.Copy(File.DirAssets, "fedqa.db", File.DirDefaultExternal, "fedqa.db")
                  
End If

 

[paul3252]

No, I wasn't doing that, I have amended accordingly and now it seems to stop at a different place telling me that there is no such column category:.

The beginning now looks like this;

Dim dbfilename As String
dbfilename="fedqa.db"
Dim dbfiledir As String
dbfiledir=File.DirDefaultExternal
Dim dbtablename As String
dbtablename="qa"
Dim columnname(4) As String
columnname(0)="ID"
columnname(1)="Question"
columnname(2)="Answer"
columnname(3)="Category"
Dim search As String
End Sub
Sub Activity_Create(FirstTime As Boolean)
Activity.LoadLayout("StartUP")
If FirstTime Then
If File.Exists(File.DirDefaultExternal, "fedqa.db") = False Then
File.Copy(File.DirAssets, "fedqa.db", File.DirDefaultExternal, "fedqa.db")
End If
SQL1.Initialize(dbfiledir, dbfilename, True)
End If
End Sub

The searching bit looks like this;

Sub SQLTableRead
Dim i As Int
Dim Cursor1 As Cursor
Dim txt, wtxt As String
txt="SELECT * FROM qa WHERE cat LIKE '"&search&"'"
Cursor1=SQL1.ExecQuery(txt)
lstQuestions.Initialize("lstQuestions")
Activity.AddView(lstQuestions, 0, 0, 100%x, 75%y)
For i=0 To Cursor1.RowCount-1
Cursor1.Position=i
lstQuestions.AddSingleLine(Cursor1.GetString("Question"))
Next
End Sub

I renamed the field to 'cat' instead of 'category' just in case the fieldname was some sort of protected word but it makes no difference. I can search with the same sql outside of B4A and find the match so I know the sql should work.

 

[NJDude]

Can you upload your project? you can export to ZIP in the designer.

 

[paul3252]

I think it is attached.

If I use an sql line as below;

Dim i As Int
Dim Cursor1 As Cursor
Dim txt, wtxt As String
'txt="SELECT * FROM qa WHERE cat LIKE '"&search&"'"
txt="SELECT * FROM qa"

Cursor1=SQL1.ExecQuery(txt)
lstQuestions.Initialize("lstQuestions")
Activity.AddView(lstQuestions, 0, 0, 100%x, 75%y)
For i=0 To Cursor1.RowCount-1
Cursor1.Position=i
lstQuestions.AddSingleLine(Cursor1.GetString("question"))
Next

The listbox populates as expected, very odd, file clearly being found and read.

 

[NJDude]

You are searching for a field that doesn't exit (Category), I looked at the DB and you have only 1 record.

'txt="SELECT * FROM qa WHERE Category = 'DUTY'"  <-- Not found
txt="SELECT * FROM qa WHERE number = '1'" '<-- This works

Look at the screen shot to see what's on your DB.

I'm assuming your app is still incomplete right?

 

[paul3252]

Thanks for your help, I have just realised what the issue was about the same time you replied. The file which is 'embedded' in the project was not the file I was actually amending using my external editor, i.e. the field name change. Your first fix would have sorted it. I have just realised that when I tick the box in the files list to include a file it copies it into the project, it does not refer back to the file I was amending.

Thanks very much for your help and prompt replies. It's slowly sinking in, its sometimes a problem when using a different number of development tools keeping up with how they behave but so far so good, its all learning

 

[NJDude]

Glad I could help.

 

[eps]

I usually edit the actual DB file, but take care and store frequent backups, so that faults can be rolled back.

I use the extension for Firefox, this works well.

I've also taken to sharing CSV files with people that share some of the Development tasks - well the data entry. But then import the CSVs into the DB, again the actual one that the code will use.

Ideally you need to get yourself into a reproduceable procedure for your Development and also ensure that backups are taken fairly regularly to minimise data loss or to allow you to easily undo code issues introduced.