MYSQL ID Auto Filling In The Information

If I am trying to write something that will allow me to take what is in the column id and what ever it is 0 or 1 or what ever number and match it to the username and fill out in the text fields I have setup the data from the id. Something like this.

if db.Connect Then
  rs = db.SelectSQL("SELECT * FROM login_server WHERE id = ?", id.text)
  id.AddText(rs.Column("id").StringValue)
  FirstName.AddText(rs.Column("first_name").StringValue)
  LastName.AddText(rs.Column("last_name").StringValue)
  Username.AddText(rs.Column("username").StringValue)
  Password.AddText(rs.Column("password").StringValue)
  Email.AddText(rs.Column("email").StringValue)
  Gamertag.AddText(rs.Column("gamertag").StringValue)
  Level.AddText(rs.Column("level").StringValue)
  UserNotes.AddText(rs.Column("user_notes").StringValue)
  AdminNotes.AddText(rs.Column("admin_notes").StringValue)
else
  App.ErrorDatabase
end if

The Question Is What Is the Question?

Let say id is 0 for username: codeman I want the code to be able to fill out the username, name and all the information depending on what the id is but I do not know how to change the code to make it work not sure what I am missing or what I need to add.

ah ok look you want to fetch data based on the id from the text field.
after firing your select statement you need to check if you have match

if row.columnat(0).value <> 0 then
//you have a match and you can fill all your text field
else
//no match
//do something
next

It’s worth mentioning too that MySQL will return a “row” even if your Select Statement yields NO results. So in addition to checking if returned rows <> Nil, you should make sure to verify there are actual rows to act on.

If rows <> Nil Then
  
If rows.RowCount > 0 Then

Well see that is one of my issues even if I login with a different username the id stays 0 and does not change and not sure why because the next login name is id = 1 so not sure why it is not changing. The code seemed right to me but now I don’t know.

I was figuring how I was doing it was going to work but for some reason it is not working so I am not 100% sure what I am missing here to get it fully.

Is your id (in Database) string or Integer? You du somthing like if "0" = 0 then

What I am saying though is I don’t think it has anything to do with the if statement because I think the sql call is wrong maybe because the number never changes from 0 it stays 0 and will not change from 0. Yes I am using integer. I think I have to change the sql statement but not sure what to make it work.

So you should not compare integer (in DB) with text/string in the Statement.
Or to say in SQL. You do:
select * from table where 1 = ‘1’;
As a result you get an empty recordset, so what you get is correct. Your Statement is wrong.
so something like:

rs = db.SelectSQL(“SELECT * FROM login_server WHERE id = ?”, id.integerValue)

That still doesn’t change the value though

Ähm, which version of Xojo do you use?
db.connect does not return true since 2019r2. db.SelectSQL is post 2019r2 code.
Perhaps your code will never be executed?!

If not: Put "“SELECT * FROM login_server WHERE id = 1” (if 1 is an existing id in the db-table) and have a look, if you get something.

What is the value of id.text?

It is staying 0 and it doesn’t matter who I am logging into but when I login into jsmith it should be 1 not 0 still because id 0 is codeman

BTW: Better try to avoid nesting.

Try

  If Not db.Connect Then Return

  rows = db.SelectSQL("SELECT * FROM login_server WHERE id = ?", id)

  If rows = Nil Or rows.Count = 0 Then Return

    // Now handle the data

Catch err As DatabaseException
    // Handle Database Errors
End Try

Is it possible that your id Object is Nil and therefore it’s always 0 ?

BTW: I’d recommend to make your User Data an Object. Makes it easier to manage:

Bildschirmfoto 2022-01-22 um 08.13.31

and then

Var Users(-1) As UserID

Var User As New UserID
User.id = rows.Column("id").IntegerValue
User.Name_First = rows.Column("first_name").StringValue
...
Users.Add User

Well I can say for sure it is not nil because I send a messagebox for that and I also setup the number 0 for user codeman and it cannot be nil so I also check for that. but you have a point though I could add them as properties but even holding them into var should still do the same thing for the most part unless it is losing data but don’t think it is. I still think I have to be missing something with the sql statement for the reason it is not changing the id since the id is not null.

  1. That should be id.text.ToInteger - assuming id is an integer in the database

  2. You haven’t told us what actually happens when you run your code.

  3. id , FirstName, etc - are these all TextAreas?

  4. How does id get an initial value such that you can then use it in the SelectSQL statement?

  5. If it already has an initial value why are you then filling it in as the first action after the SelectSQL?

  6. Why use AddText instead of saying such as:

  FirstName.Text = rs.Column("first_name").StringValue

I am using .AddText because it is an array, and it will not let me use .Text without changing it. Yes, I changed it and yes id is an Integer. When I run the code it adds the values into each textfield when I login with codeman which his id is 0 and jsmith id is 1 but it does not post his information in the textfield nor does it change the id number to 1 which it should. The reason I am using the first action is because I wanted to see if the number would change, and it did not.