Hello.
Making progress here, inch by inch. Thanks all to the help.
New problem.
I’d like to gather text into a text area. The texts materials will come from a database table. For example, I have an array, thus: [“alpha”, “bravo”, “charile”] which will serve as search values to a database table. I need to put the search word and the meaning into a text area. The table records consists of two columns: [“keyword”, “worddefinition”]
I’m getting “InvalidArgumentException” error in adding the “txtAreaPrefixDefi”. How shall I code the add-text-to-text-area loop. Thanks in advance.
var rw as RowSet = db.SelectSQL(“select * from master_words where word = '” + curWord + “'”)
if rw <> Nil and not rw.AfterLastRow then
For each row as DatabaseRow in rw
txtAreaPrefixDefi.AddText row.Column(“keyword”).StringValue
txtAreaPrefixDefi.AddText row.Column(“word definition”).StringValue
[in the Next iteration, should append word and definition to text string area.]
next
end if
You’re right, no space between “worddefinition.” Actually, while in a listbox, columns can be specified, in a textArea I can’t find a place to specify columns.
Thanks for the tip to use " </> " to format code in forum messages. I was looking for something like “code” to do the formatting. Didn’t occur to me that " </> " will do it!
So how does one assign to a varialbe the database table row that is read so that the value of the vaariable can be added to the text area? neither of the two lines below work.
txtArea.AddText = rw
txtArea.AddText = row
Thanks.
Yes, that’s the problem – how is the row from a database table, with multi-columns, assigned to variables so that the text values can be assigned to the text area?
Something like
for each database row:
assign the value of the fist column to a variable1
assign the value of the next column to a variable2
Then:
add variable1 + variable2 to txtArea
Or assign each column in the RowSet to an 2D array of values that you can reuse to update changed values in the text fields and then save the array back to the database.
Here is some code from a method I use to retrieve data and populate an array from a MySQL Database
Var rs As RowSet
Var row,col As Integer
Var mArray(-1,-1) As String
rs = MySQLDB.SelectSQL(sqlString )
If MySQLDB.Error Then
Var desc,err As String
desc = "DatabaseMethods.DBReturnRowSetArrary2D.DatabaseError on " + MySQLDB_Name
err = "Error Code: " + Str(MySQLDB.ErrorCode) + " - Error Message: " + MySQLDB.ErrorMessage
// Log the Exception
LogExceptions(desc + err)
Return mArray
End If
If Not (rs = NIL) Then
mArray.ResizeTo(rs.RowCount - 1,rs.ColumnCount - 1)
rs.MoveToFirstRow
row = 0
If mArray.Lastindex(1) > -1 Then
While Not rs.AfterLastRow
For col = 0 to rs.ColumnCount -1
mArray(row,col) = rs.ColumnAt(col).StringValue
Next
row = row + 1
rs.MoveToNextRow
wend
End If
Return mArray
Else
Var desc,err As String
desc = "DatabaseMethods.DBReturnRowSetArrary2D.DatabaseError on " + MySQLDB_Name
err = "The rowset returned Nil using:" + EndOfLine + sqlString
// Log the Exception
LogExceptions(desc + err)
Return mArray
End If
You loop through the array to assign the values to the text fields.
OK. It really isn’t that hard, but perhaps we can approach it a different way. Maybe we should start with your initial post and just keep it simple.
I’m not sure where the code you are using currently resides so I’m going to make an assumption that it is in a button and the code is in the Button.Pressed event.
Assuming that your query may return more than one database row , I would change your code to be:
// Dimension an integer variable that will keep track of the row number in the RowSet
Var col As Integer
// Dimension a string variable that is your query.
Var sqlstring = "SELECT * from master_words WHERE word = '" + curWord + "'"
// Dimension the RowSet variable
Var rs As RowSet
// Query the database and return the result into the RowSet variable
rs = db.SelectSQL(sqlstring)
// If the RowSet is not Nil Then move to the first row in the RowSet
If Not (rs = NIL) Then
rs.MoveToFirstRow
// Loop through the rows in the RowSet until after the last row
While Not rs.AfterLastRow
//Loop through the columns in the row and add thecolumn contents to txtArea
For col = 0 to rs.ColumnCount -1
txtAreaPrefixDefi.Text = txtAreaPrefixDefi.Text + rs.ColumnAt(col).StringValue
Next
// Move to the next row in the RowSet
rs.MoveToNextRow
Wend
End If
Hi Resty, I’m glad this worked for you. Let’s take it to the next level.
Instead of making the query and populating the TextArea together in one bit of code, you might want to split the query off to a reusable method in a module that you can call whenever you need to query rows and columns of data from the database. The module would return an array of values.
Let’s start with changing the code in what I gave in my last post to calling a method called DBReturnRecordSetArrary2D and using the array of data that is returned. Here is how that would look:
// Dimension integer variables that will keep track of the row numbers and Column numbers in the RowSet
Var row,col As Integer
// Dimension a string variable that is your query.
Var sqlstring As String = "SELECT * from master_words WHERE word = '" + curWord + "'"
// Dimension an empty 2D array to hold the returned data from the method
Var dbData(-1,---1) As String
// Call the Method to do the query using the sqlstring variable
// The format for the method is DBReturnRecordSetArrary2D(sqlString As String) As String(,)
dbData = DBReturnRecordSetArrary2D(sqlstring)
// Check to make sure the returned array is not empty by checking to see that there are rows
// The first row is zero so the LastIndex must be greater than -1
If dbData.Lastindex(1) > -1 Then
// Loop through the rows in the Array
For row = 0 to dbData.Lastindex(1)
//Loop through the columns in the row and add the column contents to txtArea
For col = 0 to dbData.Lastindex(2)
txtAreaPrefixDefi.Text = txtAreaPrefixDefi.Text + dbData(row,col)
Next
Next
// The array was returned empty so handle the error
Else
// Notify the user of the error
MessageBox("The Database Query returned no data")
End If
Next you would create a module and add a method to it like so:
And the code inside the method would look like this:
// Dimension the RowSet variable
Var rs As RowSet
// Dimension integer variables that will keep track of the row numbers and Column numbers in the RowSet
Var row,col As Integer
//Dimension a 2D array to store and return the RowSet Query Result
Var mArray(-1,-1) As String
// Query the database and return the result into the RowSet variable
rs = db.SelectSQL(sqlString )
// Check to see if the query caused a Databade error
If db.Error Then
// Dimension the error variables
Var desc,err As String
// Define the database error information
desc = "DatabaseMethods.DBReturnRowSetArrary2D.DatabaseError on " + db_Name
err = "Error Code: " + Str(db.ErrorCode) + " - Error Message: " + db.ErrorMessage
// Notify the user of the error
MessageBox(desc + err)
// Return an empty array
Return mArray
End If
// If the RowSet is not Nil
If Not (rs = NIL) Then
// Resize the dimensions of the 2D array to equal the contents of the RowSet
mArray.ResizeTo(rs.RowCount - 1,rs.ColumnCount - 1)
// Move to the first row in the RowSet and
rs.MoveToFirstRow
// Set the row variable to the first row in the array
row = 0
// Loop through the rows in the RowSet until after the last row
While Not rs.AfterLastRow
//Loop through the columns in the row and add the column contents to array
For col = 0 to rs.ColumnCount -1
mArray(row,col) = rs.ColumnAt(col).StringValue
Next
// Move to the next row in the RowSet
rs.MoveToNextRow
// Set the row variable to the next row in the array
row = row + 1
wend
// Return the array of Database values
Return mArray
// If the RowSet is Nil handle the error and Notify the user of the error
Else
// Dimension the error variables
Var desc,err As String
// Define the database error information
desc = "DatabaseMethods.DBReturnRowSetArrary2D.DatabaseError on " + db_Name
err = "The rowset returned Nil using:" + EndOfLine + sqlString
// Notify the user of the error
MessageBox(desc + err)
// Return an empty array
Return mArray
End If
I included some error handling in this version which is something you should always consider doing when writing code and particularly with database queries. Hopefully this doesn’t seem as complex as the first time I tried to supply you with a reusable database query and you are able to follow the logic. This gives you a way to create Inserts, Updates and other types of reusable queries by making them more generic and just supplying the specifics to execute them.
This is fantastic. And thank you very much for taking the time, of which we all have in short supply, to comment the code to make it easier to comprehend,. And above all, for sharing the code. The value of this facility to a beginner like me is incalculable. I shall attempt right away to incorporate it in my current project. It’s help like this that makes me think twice before going back to my old (though unmastered) development environment (LiveCode). Thanks again.
(Addendum)
I quickly looked at the code, and, yes, the logic and the comments put it within my comfort level. For my needs, Xojo is a table-and-record application (it can of course ably do many other types of apps) and your post makes me look forward to Xojo’s ‘modern’ interface to databases; I’m sure that will make life for Xojo beginners even more worth waking and getting up to in the morning. Ciao!
