Allow user to edit (CRUD method)

Eric_Fernee · February 7, 2016, 5:53pm

Hello,

I have written an app which allows an user to register and login. The data will be automatically stored in the SQLite database.

What I would achieve is to allow the user to view/edit its own credentials / contact information. In other words, retrieving data for that specific user.

Is there a way to accomplish this ?

Thank you.

Kind regards,
Eric

DaveS · February 7, 2016, 6:12pm

Select the record from the database
Display it in some type of form using TextFields or listbox
Update the record with the altered values

Not sure what more you are asking?

Eric_Fernee · February 7, 2016, 6:20pm

That’s exactly what I would like to achieve but I don’t know how.

I have created a separate webpage, which is considered to be the ‘my profile’ page and I have added a Textfield, even though I don’t know how to retrieve / bind the data from the database to reflect in the textfield.

brian_franco · February 7, 2016, 6:47pm

Eric,

There are a couple of videos on using SQLite at the Xojo Dev Center Videos

There is some example projects in the Xojo install directory under Example Projects\Database

Hope that helps

Eric_Fernee · February 7, 2016, 6:52pm

Thanks Brian,

I have already checked those, unfortunately without any luck.

Helge_Tjelta · February 7, 2016, 7:34pm

normaly you do a select command to the db, the result is then displayed. I use cursors and these have:

texfield1.text = cursor.field(“nameoffiled”).getString

this will get the text from my database and into my view on my webapp…

the cursor is normaly like this:

cursor = db.sqlselect(“Select field1, field2 FROM Table”)

Eric_Fernee · February 7, 2016, 7:49pm

Thanks Helge. How do you get this for a specific TextField ? And where do you put the code, into a method / event handler ?

Helge_Tjelta · February 7, 2016, 7:52pm

I normaly use a method, in this I read from the database and then populate all the textfiels, textareas or listbox i have.

Eric_Fernee · February 10, 2016, 6:17pm

Thanks Helge. I have a tried this, unfortunately without any luck.

Albin_Kiland · February 10, 2016, 6:36pm

You do the SELECT statement and put the returned value into the textfield.

(Pseudo code…)

  Dim rs as RecordSet
  Dim ps as SQLitePreparedStatement
  
  rs = db.SQLSelect("SELECT firstname, lastname FROM users WHERE userid=?")

// These two lines of code defines what the '?' in the statement represents.
  ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
  ps.Bind(0, userid)
'<--
  
  rs = ps.SQLSelect()
  
  //check for errors and all that...
  
  fld_firstname.Text = rs.Field("firstname").StringValue
  fld_lastname.Text = rs.Field("lastname").StringValue

This will get you started at least, right?

Eric_Fernee · February 10, 2016, 6:54pm

Thanks Albin, I will go ahead with this

Albin_Kiland · February 10, 2016, 7:06pm

I see there was an error therecorrected.

  Dim rs as RecordSet
  Dim ps as SQLitePreparedStatement
  
  ps = SQLitePreparedStatement(db.Prepare("SELECT firstname, lastname FROM users WHERE userid=?"))

// These two lines of code defines what the '?' in the statement represents.
  ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
  ps.Bind(0, userid)
'<--
  
  rs = ps.SQLSelect()
  
  //check for errors and all that...
  
  fld_firstname.Text = rs.Field("firstname").StringValue
  fld_lastname.Text = rs.Field("lastname").StringValue

Eric_Fernee · February 10, 2016, 7:38pm

Thanks Albin for the corrected version.

In this specific case I have made a separate webpage (within Xojo of course ;-)), which must view the first name from the database, when the user has logged in (for this time only the first name to keep it simple).

My database is called: ‘mydatabase.sqlite’ and the table is called: ‘Gebruikers’ (Dutch for users), the column in the database of first name is called: ‘FirstName’ and it knows a primary key called: ‘UserID’

When I go to the added webpage and add a method and re-write your code into the one below: (I have marked the modified fields bold and the last sentence deleted).

Dim rs as RecordSet
Dim ps as SQLitePreparedStatement

ps = SQLitePreparedStatement(db.Prepare(“SELECT FirstName FROM Gebruikers WHERE UserID=?”))

// These two lines of code defines what the ‘?’ in the statement represents.
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, UserID)
'<–

rs = ps.SQLSelect()

//check for errors and all that…

fld_firstname.Text = rs.Field(“FirstName”).StringValue

Even though I get several errors. Did I overlooked something ?

Albin_Kiland · February 10, 2016, 7:44pm

What errors do you get?

Eric_Fernee · February 10, 2016, 7:51pm

I get the following errors:

This item does not exist ps = SQLitePreparedStatement(db.Prepare(“SELECT FirstName FROM Gebruikers WHERE UserID=?”))

This item does not exist ps.Bind(0, UserID)

The bold marked items where highlighted in yellow.

Albin_Kiland · February 10, 2016, 8:06pm

‘db’ should be your database property.
UserID should be an integer that holds the ID for the user in the db.

Have you successfully connected your database?

Eric_Fernee · February 11, 2016, 5:25pm

I can imagine that this would be an odd question, but where can I find the database property ? I already looked into the ‘Properties’ area in the ‘CONTENTS’ bar and I found a property called ‘DB’ and a the type is ‘SQLiteDatabase’ I have tried to replace ‘db’ into the aforementioned names.

The UserID field is indeed an integer in the SQLite database. I guess the database has been successfully connected as I am able to ad da user to the database (when the web app runs, without the code above).

Albin_Kiland · February 12, 2016, 5:43am

How do you connect to the database?

ps.Bind(0, UserID)

UserID here is not the column in the database. It’s a variable holding the actual ID of the user in the database.

I think you should look at the database videos again like Brian suggested
https://www.youtube.com/watch?v=ut9bMJWXb_M

And maybe this one to learn a bit about variables, constants and other useful things.
https://www.youtube.com/watch?v=bNrcimB-NBw

Eric_Fernee · February 12, 2016, 5:51am

Thank you Albin, I will watch the videos again I hope I am be able to fix this based on the tutorials.

The database is connected with a method called ‘ConnectToDB’

Eric_Fernee · February 14, 2016, 11:19am

I have watched the videos again and the aforementioned issues are resolved. Thank you for your help !

However when I login the FirstName would not show up in the TextField1 area.

The purpose of the code below would be to retrieve the ‘FirstName’ from the user logged in and show it into TextField1.

This code has been added as a Method.

[i] Dim rs as RecordSet
Dim ps as SQLitePreparedStatement

ps = SQLitePreparedStatement(db.Prepare(“SELECT FirstName FROM Gebruikers WHERE UserID=?”))

// These two lines of code defines what the ‘?’ in the statement represents.
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, UserID)
'<–

rs = ps.SQLSelect()

//check for errors and all that…

TextField1.Text = rs.Field(“FirstName”).StringValue[/i]

Despite the videos I could not figure out what I have overlooked. Would you please help me with this part ?