Advice needed

Guido_Cautreels1 · October 6, 2023, 3:19pm

i use xojo 2023 release 1

Guido_Cautreels1 · October 6, 2023, 3:20pm

i added this code

Var candyName As String = TextField_ItemName.Text
Var expirationDate As String = TextField_ExpirationDate.Text
Var desktopFolder As FolderItem = SpecialFolder.Desktop
Var db As New SQLiteDatabase
Var folderName As String = “Database”
Var databaseFileName As String = “snoepdata.db”
Var folderItem As FolderItem = desktopFolder.Child(folderName)
Var databaseFile As FolderItem = folderItem.Child(databaseFileName)

db.DatabaseFile = databaseFile

Dim sql As String = “INSERT INTO Tabellen (TextField_ItemName, TextField_ExpirationDate) VALUES (?, ?);”
Dim preparedStmt As SQLitePreparedStatement = db.Prepare(sql)

preparedStmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
preparedStmt.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
preparedStmt.Bind(0, TextField_ItemName)
preparedStmt.Bind(1, TextField_ExpirationDate)

preparedStmt.SQLExecute

If db.Error Then
MsgBox("Error: " + db.ErrorMessage)
End If

db.close

TimStreater · October 6, 2023, 3:39pm

You can simplify this:

Dim sql As String = “INSERT INTO Tabellen (TextField_ItemName, TextField_ExpirationDate) VALUES (?, ?);”
Dim preparedStmt As SQLitePreparedStatement = db.Prepare(sql)

preparedStmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
preparedStmt.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
preparedStmt.Bind(0, TextField_ItemName)
preparedStmt.Bind(1, TextField_ExpirationDate)

preparedStmt.SQLExecute

.
to:

Dim sql As String = “INSERT INTO Tabellen (TextField_ItemName, TextField_ExpirationDate) VALUES (?, ?);”
db.ExecuteSQL (sql, candyName, expirationDate)

Guido_Cautreels1 · October 6, 2023, 3:47pm

i got this erro

Window1.Button1.Pressed, line 12
This item does not exist
Dim sql As String = “INSERT INTO Tabellen (TextField_ItemName, TextField_ExpirationDate) VALUES (?, ?);”

AlbertoD · October 6, 2023, 3:54pm

If you did a copy paste from the forum then your quotes are not the correct one (curly vs regular)

Ian_Kennedy · October 6, 2023, 4:57pm

When posting code on the forum please sure you highlight it and press the </> button before posting. It not only makes the code a lot more readable it also prevents the quotes from being turned into smart quotes.

"" vs “”

“” do not work in Xojo.

TimStreater · October 6, 2023, 5:35pm

That was probably my fault. I did a copy/paste of the OP’s non-highlighted code and didn’t check the quotes afterwards.

It would help if this Forum only used straight quotes for all purposes.

Guido_Cautreels1 · October 8, 2023, 2:23pm

hello i wrote this code

// Get the desktop folder
Var desktopFolder As FolderItem = SpecialFolder.Desktop

// Specify the name of the folder containing your database file
Var folderName As String = "Database" // Replace with the actual folder name

// Specify the name of your database file
Var databaseFileName As String = "snoepdata.db"

// Create a FolderItem for the folder containing the database file
Var folderItem As FolderItem = desktopFolder.Child(folderName)

// Check if the folder exists
If folderItem <> Nil And folderItem.Exists And folderItem.Directory Then
  // Combine the folder with the database file name to get the full path
  Var databaseFile As FolderItem = folderItem.Child(databaseFileName)
  
  // Check if the database file exists
  If databaseFile <> Nil Then
    // You can now use 'databaseFile' to set the database file path
    Var db As New SQLiteDatabase
    db.DatabaseFile = databaseFile
    
    If db.Connect Then
      MsgBox("Connected to the database")
    Else
      MsgBox("Failed to connect to the database: " + db.ErrorMessage)
    End If
  Else
    MsgBox("Database file not found in the folder on the desktop.")
  End If
Else
  MsgBox("Folder not found on the desktop.")
End If


Var candyName As String = TextField_ItemName.Text
Var expirationDate As String = TextField_ExpirationDate.Text
Var db As New SQLiteDatabase
Var databaseFile As FolderItem = folderItem.Child(databaseFileName)

db.DatabaseFile = databaseFile

Dim sqlCreateTable As String = "CREATE TABLE IF NOT EXISTS SNOEPTABEL (" + _
"id INTEGER PRIMARY KEY AUTOINCREMENT, " + _
"name TEXT, " + _
"date DATE);"
 db.SQLExecute(sqlCreateTable)

 Dim sqlInsertData As String = "INSERT INTOSNOEPTABELe (TextField_ItemName, TextField_ExpirationDate) VALUES (?, ?);"
If db.Error Then
  MsgBox("Fout in maken tabel: " + db.ErrorMessage)
Else
  MsgBox("Tabel succesvol gemaakt of bestaat reeds.")
End If

when i start it says connected to database and then says error database closed

any help with this.

Th,x

Jeff_Tullin · October 8, 2023, 2:48pm

// Get the desktop folder
dim desktopFolder As FolderItem = SpecialFolder.Desktop

// Specify the name of the folder containing your database file
dim folderName As String = "Database" // Replace with the actual folder name

// Specify the name of your database file
dim databaseFileName As String = "snoepdata.db"

// Create a FolderItem for the folder containing the database file
dim folderItem As FolderItem = desktopFolder.Child(folderName)


//MOVED to here
dim db As New SQLiteDatabase


// Check if the folder exists
If folderItem <> Nil And folderItem.Exists And folderItem.Directory Then
  // Combine the folder with the database file name to get the full path
  dim databaseFile As FolderItem = folderItem.Child(databaseFileName)
  
  // Check if the database file EXISTS, not just not Nil
  
  If databaseFile <> Nil and databaseFile.exists Then
    // You can now use 'databaseFile' to set the database file path
    
    db.DatabaseFile = databaseFile
    
    If db.Connect Then
      MsgBox("Connected to the database")
      
      dim candyName As String = "dummy teststring"
      dim expirationDate As String = "dummy expdate"
      //    not needed    dim db As New SQLiteDatabase
      //    not needed  dim databaseFile As FolderItem = folderItem.Child(databaseFileName)
      
      //    not needed  db.DatabaseFile = databaseFile
      
      Dim sqlCreateTable As String = "CREATE TABLE IF NOT EXISTS SNOEPTABEL (" + _
      "id INTEGER PRIMARY KEY AUTOINCREMENT, " + _
      "name TEXT, " + _
      "date DATE);"
      db.SQLExecute(sqlCreateTable)
      
      //   Wrong  Dim sqlInsertData As String = "INSERT INTO SNOEPTABEL (TextField_ItemName, TextField_ExpirationDate) VALUES (?, ?);"
      
      Dim ps As SQLitePreparedStatement
      
      ps = Db.Prepare("INSERT INTO SNOEPTABEL (name, date) VALUES (?, ?)")
      ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
      ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
      
      ps.Bind(0, candyName)
      ps.Bind(1, expirationDate)
      
      
      
      try
        call ps.SQLExecute
      catch
      end try
      
      
      
      If db.Error Then
        MsgBox("Fout in maken tabel: " + db.ErrorMessage)
      Else
        MsgBox("Tabel succesvol gemaakt of bestaat reeds.")
      End If
      
      
      
      

    Else
      MsgBox("Failed to connect to the database: " + db.ErrorMessage)
    End If
  Else
    MsgBox("Database file not found in the folder on the desktop.")
  End If
Else
  MsgBox("Folder not found on the desktop.")
End If

TimStreater · October 8, 2023, 3:18pm

Why not tell him to use API2 ? Then he won’t need all that prepare business.

Jeff_Tullin · October 8, 2023, 3:47pm

Laziness, I confess.
I don’t use API2 myself.

TimStreater · October 8, 2023, 4:02pm

Funny really. Originally my API-1 database code was full of potential SQL-injection opportunities, so when I realised that I needed to do something about that, initially I thought I was faced with having to add extra code to some 400 API-1 database calls, all of which would be just to do this bind/prepare business. As none of my database stuff is particularly execution-time sensitive, I decided instead to add it just the once, inside my shim methods (which do the error detection and logging of any database errors). So I ended up with methods that could safely do the likes of:

db.mydbshim ("insert into mytab (x, y) values (?, ?)", x, y)

provided only that any arguments such as x and y were all strings, which is the important variable type in this context. So I was please to discover for API-2 that Xojo went that route also, although implemented in a more general way. Then to effect a global switch to API-2 for database methods involved only minor changes to my shim methods.

Jeff_Tullin · October 8, 2023, 4:14pm

Good to know.

Guido_Cautreels1 · October 13, 2023, 11:47am

are there any good books or ebook with recent xojo language out?

Gavin_Smith · October 13, 2023, 12:50pm

There’s a new Xojo book coming out from Apress next month, although I can’t say if it’s good or not yet! Xojo Unleashed: A Comprehensive Guide to Mastering Xojo Programming by Apress, Paperback | Barnes & Noble®

There are various tutorials and intro guides on the documentation site: Tutorials — Xojo documentation

The official Xojo blog is a great source of regular info, especially when a new release comes out: https://blog.xojo.com

Finally, while it’s not reading material, the official YouTube Xojo channel is very useful, with some excellent videos recently, all using API 2.0: Xojo: Webinars and Videos on Xojo Programming