Insert Web Text Field data into Sqlite DB

  1. 8 months ago

    Hello,
    I am a newby to Xojo.
    I am attempting to take Text Field data inputted from a Web Page and insert this into a database with the following code which is not correct.

    Any assistance will be greatly appreciated.

    Thank you and regards
    Craig

    Dim DBFile as FolderItem
    DBFile = Volume(0).Child("Users").Child("craigwyeth"). Child("Documents").Child("Wyzelink").Child("Wyzelink Xojo Projects⁩").Child("ALC.sqlite")
    DB = New SQLiteDatabase
    DB.DatabaseFile = DBFile

    Dim CustomerRecord as New DatabaseRecord

    CustomerRecord.Column("CompanyID") = TextCompanyCode.Text
    CustomerRecord.Column("CompanyName") = TextCompanyName.Text
    CustomerRecord.Column("CompanyAddr1") = TextCompanyAddress1.Text
    CustomerRecord.Column("CompanyAddr2") = TextCompanyAddress2.Text
    CustomerRecord.Column("CompanyAddr3") = TextCompanyAddress3.Text
    CustomerRecord.Column("CompanyTown") = TextCompanyTown.Text
    CustomerRecord.Column("CompanyRegion") = TextCompanyRegion.Text
    CustomerRecord.Column("CompanyPostalCode") = TextCompanyPostalCode.Text
    CustomerRecord.Column("CompanyCountry") = TextCompanyCountry.Text
    CustomerRecord.Column("CompanyRegno") = TextCompanyRegNo.Text
    CustomerRecord.Column("CompanyContactNo") = TextCompanyContactNo.Text
    CustomerRecord.Column("CompanyEmail") = TextCompanyEmail.Text
    CustomerRecord.Column("CompanyWebsite") = TextCompanyWebsite.Text

    DB.InsertRecord("MasterAccount", CustomerRecord)

    If DB.Connect Then
    DB.InsertRecord("MasterAccount", CustomerRecord)
    DB.Commit

    Else
    MsgBox("The database couldn't be opened. Error: " + DB.ErrorMessage)
    DB.Rollback
    End If

  2. Antonio R

    3 Jan 2019 Pre-Release Testers, Xojo Pro Europe (Italy)

    You forgot to connect to the db

  3. Paul S

    3 Jan 2019 Pre-Release Testers, Xojo Pro Europe (Netherlands, Den Haag)
    Edited 8 months ago

    You are doing an insert before you connect to the database.
    The second insert after the db connect is correct.

    DB.InsertRecord("MasterAccount", CustomerRecord)  <-- Remove this line
    
    If DB.Connect Then
      DB.InsertRecord("MasterAccount", CustomerRecord)
      DB.Commit
    
    Else
      MsgBox("The database couldn't be opened. Error: " + DB.ErrorMessage)
      DB.Rollback
    End If
  4. Sascha S

    3 Jan 2019 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary

    @Paul S Else
    MsgBox("The database couldn't be opened. Error: " + DB.ErrorMessage) DB.Rollback <--- Also remove this line End If

    Why Rollback if the DB is NOT connected? :)

  5. Thank you for the assistance.
    I have removed the Insert line before the Connect but I am still not able insert the record.

  6. Sascha S

    4 Jan 2019 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary

    @Craig W Thank you for the assistance.
    I have removed the Insert line before the Connect but I am still not able insert the record.

    What Error does the DB report, please?

  7. It does not Error. The DB.Connect is successful but the Insert does not seem to insert the record.

    Thank you

  8. Jean-Yves P

    4 Jan 2019 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 8 months ago

    what does the db.errormessage show JUST AFTER THE INSERT

    DB.InsertRecord("MasterAccount", CustomerRecord)
    msgbox db.errormessage
    DB.Commit
  9. no such table: MasterAccount

  10. Albin K

    4 Jan 2019 Europe (Sweden)

    @Craig W no such table: MasterAccount

    There's your error right there. The database has no table called MasterAccount :)

  11. I'm clearly missing something as I created the DB with the associated Table.

    -image-

  12. https://i.postimg.cc/L6M3YcPB/Screenshot-2019-01-04-at-15-17-09.png

  13. Lars L

    4 Jan 2019 Pre-Release Testers, Xojo Pro Europe (Germany, Chemnitz)

    open the db in another sqlite manager such as "Datum" or so.

    With this you can check if your changes in the xojo-sqlite-interface are commited correctly.

    The IDE-inbuilt interface for sqlite isn't alway the best choice.

  14. Ralph A

    4 Jan 2019 Santa Monica, California

    I assume this error occurs during a Debug session. If so, is that database also in your Debug folder for that project? If so, does it have a MasterAccount table?

  15. Ralph A

    4 Jan 2019 Santa Monica, California

    @Ralph A I assume this error occurs during a Debug session. If so, is that database also in your Debug folder for that project? If so, does it have a MasterAccount table?

    I'm actually not sure if what I said above is relevant if you have the database in the IDE itself. Not sure how that works during Debug. I only use databases that are not in the IDE.

  16. Jean-Yves P

    4 Jan 2019 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    not sure also the primary key named "ID" is a boolean field.... will certainly not work. you must use an integer.

  17. Richard D

    4 Jan 2019 Pre-Release Testers, Xojo Pro Europe (UK, London)

    @Jean-YvesPochez not sure also the primary key named "ID" is a boolean field.... will certainly not work. you must use an integer.

    that is true, but why say cannot find MasterAccount???

  18. Paul S

    4 Jan 2019 Pre-Release Testers, Xojo Pro Europe (Netherlands, Den Haag)

    I see in your picture that you have added a link to the database in the IDE.
    Is the link in the IDE pointing to the same location as the location that you are using to connect to the database (DBFile)?
    If not then the database you connect to doesn't have the table that you assume it does.

    Furthermore I would advise you to use SpecialFolder to get to the documents folder.

    DBFile = SpecialFolder.Documents.Child("Wyzelink").Child("Wyzelink Xojo Projects⁩").Child("ALC.sqlite")
  19. Thank you for your advice and assistance. It is very much appreciated.

    1. I have removed the ALC DB and MasterAccount table and recreate using DB Browser for SQLite as suggested by @Lars L
    https://i.postimg.cc/Mpj6cVZf/Screenshot-2019-01-06-at-05-47-06.png

    1. I have removed the link to the DB in the IDE and amended the ID Field to type Integer as suggested by @Ralph A, @Jean-YvesPochez and @Paul S
    2. I have amended the link to the DB (ALC.DB) to use the SpecialFolder as advised by @Paul S

    Thank you

  20. Newer ›

or Sign Up to reply!