Sticky Views?

[Penfound]

Hi again guys.

I have two db's as I found you can't have two tables in one db. However, each set of SQL works the same ( as far as I can tell). Essentially I call one record from, say, the lessons db and transfer the colukmns into label.texts for display on the page, sorry, view.

However, when I do the same thing with the studies db it reads the correct information but when it goes to to next record it prints it over the top of the first one and so on ad infinitum.

Here are the codes for the two sections....

Sub GetNextRecord()
Dim DBCursor As Cursor
LessonSQLText="SELECT * FROM Lesson WHERE board LIKE '" & BoardText & "' AND level LIKE '" & LevelText & "' ORDER BY ID ASC "
DBCursor=SQLLessons.ExecQuery(LessonSQLText)
lblScore.Text="Score: " & Score
lblQuestion.Text="Question: " & i
DBCursor.Position=i
y = DBCursor.RowCount
If DBCursor.Position <> y Then
ID=i
lblQuestion_Text.Text = " Q: " & DBCursor.GetString("question")
lblAnswerA.Text = " A: " & DBCursor.GetString("answerA")
lblAnswerB.Text = " B: " & DBCursor.GetString("answerB")
lblAnswerC.Text = " C: " & DBCursor.GetString("answerC")
lblAnswerD.Text = " D: " & DBCursor.GetString("answerD")
If DBCursor.GetString("answwered")= False Then
fldAnswered = False
ImageView2.Visible = False
ImageView3.Visible = True

Else
fldAnswered = True
ImageView3.Visible = False
ImageView2.Visible = True

End If
CorrectAnswer=DBCursor.GetString("answer")
i=DBCursor+1
DBCursor.Close
Else
Msgbox("End of Questions with a score of '" & Score, "")
DBCursor.Close
End If

End Sub

and the "wrong" un....

Sub GetNextRecord()
Dim DBCursor As Cursor
LessonSQLText="SELECT * FROM Lesson WHERE board LIKE '" & BoardText & "' AND level LIKE '" & LevelText & "' ORDER BY ID ASC "
DBCursor=SQLLessons.ExecQuery(LessonSQLText)
lblScore.Text="Score: " & Score
lblQuestion.Text="Question: " & i
DBCursor.Position=i
y = DBCursor.RowCount
If DBCursor.Position <> y Then
ID=i
lblQuestion_Text.Text = " Q: " & DBCursor.GetString("question")
lblAnswerA.Text = " A: " & DBCursor.GetString("answerA")
lblAnswerB.Text = " B: " & DBCursor.GetString("answerB")
lblAnswerC.Text = " C: " & DBCursor.GetString("answerC")
lblAnswerD.Text = " D: " & DBCursor.GetString("answerD")
If DBCursor.GetString("answwered")= False Then
fldAnswered = False
ImageView2.Visible = False
ImageView3.Visible = True

Else
fldAnswered = True
ImageView3.Visible = False
ImageView2.Visible = True

End If
CorrectAnswer=DBCursor.GetString("answer")
i=DBCursor+1
DBCursor.Close
Else
Msgbox("End of Questions with a score of '" & Score, "")
DBCursor.Close
End If

End Sub

:sign0085: please

 

[Erel]

I have two db's as I found you can't have two tables in one db

That is not correct. You can have many tables in a single database.

 

[Penfound]

I know you should be able to have multiple table in one DB but I couldn't find a way to make it work it kept telling me that the second "Studies" table did not exist.

IThis is the only way I found to access the two db's...

Sub Activity_Create(FirstTime As Boolean)
If File.ExternalWritable=False Then
Msgbox("Unable to write on storage card","")
Return
End If

Activity.LoadLayout("main")

If File.Exists(DBFileDir, DBFilename) = False Then
If FirstTime Then
ProgressDialogShow("Initialising database...")
File.Copy(File.DirAssets, DBFilename, DBFileDir, DBFilename)
SQLLessons.Initialize(DBFileDir, DBFilename, True)
File.Copy(File.DirAssets, DBSFileName, DBFileDir, DBSFileName)
SQLStudies.Initialize(DBFileDir, DBSFileName, True)
ProgressDialogHide
End If
Else
If FirstTime Then
ProgressDialogShow("Initialising database...")
SQLLessons.Initialize(DBFileDir, DBFilename, True)
SQLStudies.Initialize(DBFileDir, DBSFileName, True)
ProgressDialogHide
End If
End If

End Sub

 

[barx]

Did you re-add the database file after you editted to include the new table?

 

[Penfound]

Yes.

I even deleted it, rebuilt it and added the file again with the two tables.

it doesn't matter at the moment though as I really need to clean up these sticky views writing over each other rather switching between them.

 

[poseidon]

re

you have tried to open the .db to windows SQLite browser manager ?

 

[Penfound]

Yes - without problem

 

[poseidon]

re

try this dbase
123.zip

as (9rows):

SELECT [EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo],[PhotoPath] from [Employees];

as (0rows) :

SELECT [CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax] from [Customers];

also copy to dir.external on activity_create and connect there

 

[Woinowski]

You can't have multiple tables with the same name in one db. You need to give every table a different name.

 

[poseidon]

re

new version of SQLite Manager is out.. Introduct generation for insert/update statements for basic4android

SQLite Manager v1.3 | PipisCrew Official Homepage

where ExecuteWithList :

Sub ExecuteWithList(SQLStatement As String,lstVALS As List) As Boolean
   Try
      SQL1.ExecNonQuery2(SQLStatement,lstVALS)
      Return True 
   Catch 
      Msgbox(LastException.Message,"ERROR")
      
      Return False
   End Try
End Sub