MySQL Display Row Data Into Text Field

Databases
1

Hey, how are you guys doing? I am sending you all a message because for some reason I cannot get my code to display from MySQL database row to a text field or areabox and not to sure what I am doing wrong? Thanks for taking the time to look at this for me and to help me out thanks so much. I am new to Xojo and programming just trying to learn this thanks.

Var db As New MySQLCommunityServer
Var sql As RowSet

db.Host = “127.0.0.1”
db.Port = 3306
db.UserName = “codeman”
db.Password = “cliverriver”
db.DatabaseName = “gaming_minds”

If db.Connect then
MessageBox “Connected!”
sql = db.SelectSQL(“SELECT news_update FROM news”)
NewsArea.AddText = db.TableColumns(“news_update”)
else
MessageBox (“Cannot Connect!”)
end if

2

See:

https://documentation.xojo.com/api/databases/database.html#database-tablecolumns

for an example.

3

When I do this it runs the application now but it doesn’t like the Next Statement because it says NilObjectException not sure why

Var db As New MySQLCommunityServer
Var sql As RowSet

db.Host = “127.0.0.1”
db.Port = 3306
db.UserName = “codeman”
db.Password = “cliverriver”
db.DatabaseName = “gaming_minds”

Try
MessageBox “Connected!”
sql = db.SelectSQL(“SELECT news_update FROM news”)

Var columns As RowSet = db.TableColumns(“news_update”)

For Each c As DatabaseRow In columns
NewsArea.Text = (c.Column(“news_update”).StringValue)
Next
MessageBox (“Cannot Connect!”)
End Try

4

you need the databaserow of the sql rowset
for each c as databaserow in sql

5

Yeah I tried changing it to sql instead of columns but it doesn’t like the next statement not sure what is giving an object cannot be found error because the columns are there, and I also cleaned up my code some and changed some things around, still getting nilobjectexception for Next

Var db As New MySQLCommunityServer
Var code As String = “0”
Var sql As RowSet

Try
db.Host = “127.0.0.1”
db.Port = 3306
db.UserName = “codeman”
db.Password = “cliverriver”
db.DatabaseName = “gaming_minds”

MessageBox “Connected!”

sql = db.SelectSQL(“SELECT news_update FROM news WHERE ID=?”, code)

For Each c As DatabaseRow In sql
NewsArea.AddText(c.Column(“news_update”).StringValue)
Next
sql.Close
End Try

6

Place a breakpoint on the For statement. When the code stops, check the value of sql - it might be nil. If so, there’s a problem with your query. Maybe code needs to be an integer 0 instead of a string?

7

You are right though sql comes back as nil but when I try to convert it to integer it says it is excepting string and not integer so maybe I am missing something that I am not currently changing it to

8

Yeah for some reason it is still saying nil for sql so not sure what I am missing but it is for sure nil though

9

You should move everything out of the Try that doesn’t need to be there - only the SelectSQL actually needs to be in the Try.

You shouldnt even start the For if sql is Nil.

Surely code should be an integer, not a string. Of value 0.

10

I did I put that where it should be after your message and then moved error testing around and it keeps pointing back to this line without the For Statement also. Also I took code out so that isn’t even an issue anymore was doing that for other testing.

Var db As New MySQLCommunityServer
Var sql As RowSet
Var c As DatabaseRow

db.Host = “127.0.0.1”
db.Port = 3306
db.UserName = “codeman”
db.Password = “cliverriver”
db.DatabaseName = “gaming_minds”

MessageBox “Connected!”

Try
sql = db.SelectSQL(“SELECT news_update FROM news”)
End Try

NewsArea.AddText(c.Column(“news_update”).StringValue) <------- Doesn’t like this ----> Nil Error

11

If you haven’t already, use MySQL Workbench or another query tool and make sure your query runs and returns the results you expect. If it doesn’t fix that first.

In your latest code example, c is declared as DatabaseRow but not initialized, so it would be nil.

Here’s how I use MySQL records. (Note there are other ways, and it should be wrapped in a Try/Catch but trying to keep it simple for now so you can get it working.)

Var rs As RowSet
rs = db.SelectSQL(“SELECT …”)
If rs = Nil Then
// issue error
Return
End
While Not rs.AfterLastRow
TextBox.Value = rs.Column(“ColName”).StringValue
rs.MoveToNextRecord
Wend
rs.Close
Once you get the basics working, read up on precautions having the database server exposed to the internet, and handling dropped connections and other errors.

12

I told you: you shouldn’t be doing the For loop if sql is Nil. So, you test sql to be not-Nil BEFORE the for-loop.

13

Yes the query is fine because it works just fine so it cannot be that and I am not using a For Loop I already told you I removed it, unless your talking about the Try statement I didn’t know Try was a For Loop. Maybe it’s just me or my computer then because I have no clue I am lost now. It says it is this row but I have no clue how it can be since everything is there.

NewsArea.AddText(c.Column(“news_update”).StringValue)

14

So you’re saying that after the query, sql is not-Nil ?

15

You seem to have dropped the db.Connect from your code. Is that the case? If so, that would explain the Nil result.

16

When I run the sql in a program like HeidiSQL it pulls up the info for that row called news_update but when I go to run the app in xojo it gives me nilobjectexception but I cannot get the text to display in the TextArea because it says I have a nilobjectexception in this line

NewsArea.AddText(c.Column(“news_update”).StringValue)

yeah it is still there db.connect just didn’t post it sorry, because sql is showing RowSet on the debug but it is saying the c in c.Column is nil I do believe is what it is saying

17

data
data978×885 42.6 KB

18

Just use the RowSet instead of DatabaseRow. Forget c.Column
Like this
NewsArea.AddText(sql.column(“news_update”).stringvalue)
You can put that inside of a loop and use movetonextrow.

19

In this code, c will be nil because you haven’t set it to anything.

20

Perhaps that should be sql.Column("news_update").StringValue.