First Program in B4A

paul3252 · Jan 25, 2012

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 · Jan 25, 2012

Are you copying the DB to the device?

B4X:

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

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

paul3252 · Jan 25, 2012

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 · Jan 25, 2012

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

paul3252 · Jan 25, 2012

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 · Jan 25, 2012

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

B4X:

'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 · Jan 25, 2012

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 · Jan 25, 2012

Glad I could help.

eps · Jan 25, 2012

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.

First Program in B4A

paul3252

Member

NJDude

Expert

paul3252

Member

NJDude

Expert

paul3252

Member

Attachments

NJDude

Expert

Attachments

paul3252

Member

NJDude

Expert

eps

Expert

First Program in B4A

Member

Expert

Member

Expert

Member

Attachments

Expert

Attachments

Member

Expert

Expert

Privacy & Transparency

Privacy & Transparency