Connecting tables.

Databases
1

With help I’ve created an address book type of application. The user inputs the information for the address book, adds it into the database, and displays it in the listbox. All is well. Now I want to have it so that the user can select the name they’ve put into the database from the listbox and click the Add Address button. This will prompt Window2 (AddAddyWindow) to open full of necessary address info (street, city, etc). Multiple addresses can be added for one person. The addresses will be displayed in the listbox2 (Supaddresslistbox) in Window1, below listbox1(suplistbox) containing the peoples’ names. So you can simply click the names in listbox1 and they display the addresses in listbox2, below.

Here’s my method. Create a database with two tables, Addressbook & SupAddresses. I put this code in the Open Event for Window1 and Window2, respectively. Have them both connected to the same database (C_S_DB). For some reason I can add information into the people database, but not the address database, which is attached to the people. I keep getting “Unable to prepare statement.”. Here’s some of the code with the actual file at the end.

I have somethings commented out because I’ve been doing a lot of trial and error from Window1’s code.

Window2 Open Event code:

[code] // SET THE PATH TO THE FOLDER WHICH WILL CONTAIN THE DATABASE FILE
fi = SpecialFolder.ApplicationData.Child(“Addressbook Data”)

// CHECK TO SEE IF THE FOLDER EXISTS - IF NOT - CREATE IT
If Not fi.Exists Then
fi.CreateAsFolder
End If

// SET THE NAME AND PATH OF THE ACTUAL DATABASE FILE
fi = fi.Child(“AddressBook Information.db”)

C_S_DB = New SQLiteDatabase
C_S_DB.DatabaseFile = fi

// IF THE DATABASE FILE DOES NOT EXIST - CREATE IT AND DEFINE THE SCHEMA
If Not fi.Exists Then
If C_S_DB.CreateDatabaseFile Then
C_S_DB.SQLExecute(“CREATE TABLE supaddresses(NRef INTEGER PRIMARY KEY, id TEXT, company TEXT)”)

        // ERROR EXECUTING THE DATABASE SQL, SO DISPLAY AN ERROR MSGBOX
  If C_S_DB.Error Then
    MsgBox("Error: " + Str(C_S_DB.ErrorCode) + " - " + C_S_DB.ErrorMessage)
    
    'Else
    '// ALL OK - SO POPULATE THE LISTBOX
    'PopulateSupAddy()
    'End If
    
    // DATABASE COULD NOT BE CREATED - DISPLAY ERROR MSGBOX, AND THEN QUIT
  Else
    MsgBox("The addressbook database could not be created. This application will now need to close.")
    Quit
  End If
  
  'Else
  ''// THE DATABASE FILE ALREADY EXISTS, SO POPULATE THE LISTBOX
  ''PopulateSupAddy()
  
End If

End If

'// RETRIEVE THE DATA FROM THE DATABASE TO DISPLAY IN TEXT FIELDS
'If Window1.Suplistbox.ListIndex <> -1 Then
’
'If C_S_DB.connect Then
'Dim ps As SQLitePreparedStatement = C_S_DB.Prepare(“SELECT * FROM supaddresses WHERE street1 = ? AND street2 = ? AND city = ? AND state = ? AND country = ? AND postcode = ?”)
'ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
'ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
'ps.Bind(0, AddAddyWindow.streetfield1.Text)
'ps.Bind(1, AddAddyWindow.streetfield2.Text)
'ps.Bind(2, AddAddyWindow.cityfield.Text)
'ps.Bind(3, AddAddyWindow.countryfield.Text)
'ps.Bind(4, AddAddyWindow.postcodefield.Text)
'ps.Bind(5, AddAddyWindow.statebox.Text)
'ps.SQLExecute

'// DATABASE ERROR
'If C_S_DB.Error Then
'MsgBox("Error: " + Str(C_S_DB.ErrorCode) + " - " + C_S_DB.ErrorMessage)
’
'Else
'// NO MATCHING RECORDS FOUND, SO DISPLAY AN ERROR MSGBOX
'If rs.eof Then
'MsgBox “No data found.”
’
'Else
'// RECORDS FOUND, SO DISPLAY THEM
'streetfield1.Text = rs.field(“street1”).StringValue
'streetfield2.Text = rs.field(“street2”).StringValue
'cityfield.Text = rs.field(“city”).StringValue
'countryfield.Text = rs.field(“country”).StringValue
'postcodefield.Text = rs.field(“postcode”).StringValue
'statebox.Text = rs.field(“state”).StringValue
'End If
'End If
'End If
'End If
[/code]

Window2 AddButton Action event:

[code] // NAME FIELD OR EMAIL FIELD CONTAINS TEXT, SO CONTINUE
If streetfield1.Text <> ""OR streetfield2.Text <> “” OR cityfield.Text <> ""OR countryfield.Text <> “” OR postcodefield.Text <> “” OR statebox.Text <> “” Then

//CONNECT TO DATABASE AND INSERT THE NEW VALUES
If C_S_DB.connect Then
  Dim ps As SQLitePreparedStatement = C_S_DB.Prepare("INSERT INTO supaddresses(street1, street2, city, country, postcode, state) VALUES (?,?,?);")
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
  ps.Bind(0, AddAddyWindow.streetfield1.Text)
  ps.Bind(1, AddAddyWindow.streetfield2.Text)
  ps.Bind(2, AddAddyWindow.cityfield.Text)
  ps.Bind(3, AddAddyWindow.countryfield.Text)
  ps.Bind(4, AddAddyWindow.postcodefield.Text)
  ps.Bind(5, AddAddyWindow.statebox.Text)
  ps.SQLExecute
  
  // DATABASE WRITE ERROR, SO DISPLAY THE ERROR MSGBOX
  If C_S_DB.Error Then
    MsgBox("Error: " + Str(C_S_DB.ErrorCode) + " - " + C_S_DB.ErrorMessage)
    Return
    
    'Else
    '// POPULATE THE LISTBOX WITH THE NEW VALUES
    'PopulateSupAddy()
    
    '// EMPTY THE NAME, EMAIL  AND SUPPLIER INFO TEXT FIELDS
    'streetfield1.text = ""
    'streetfield2.text = ""
    'cityfield.text = ""
    'countryfield.text = ""
    'postcodefield.text = ""
    'statebox.text = ""
  End If
  
  // ERROR CONNECTING TO DATABASE, SO DISPLAY THE ERROR MSGBOX
Else
  MsgBox("Could not connect to the database - new values not saved!")
End If

End If
[/code]

PopulateSupAddy Method:

[code] If C_S_DB.Connect Then

// SET THE DEFAULT sql QUERY
sql = "SELECT NRef, id FROM supaddresses"

'// CHECK IF SearchField IS EMPTY - IF NOT - CHANGE THE sql QUERY
'If Window1.SupplierSearch.Text <> "" Then
'sql = sql + " WHERE LOWER(id) LIKE LOWER('%" + SQLify(Window1.SupplierSearch.Text) + "%')"+ " OR LOWER(company) LIKE LOWER('%" + SQLify(Window1.SupplierSearch.Text) + "%')"+ " OR LOWER(contactname) LIKE LOWER('%" + SQLify(Window1.SupplierSearch.Text) + "%')"+ " ORDER BY id"
'End If

// RETRIEVE THE RECORDSET RESULTS
rs = C_S_DB.SQLSelect(sql)

// IF RECORDSET RETURNS RESULTS - DELETE ALL ROWS AND REPOPULATE WITH THE RECORDSET RESULTS
If rs <> Nil Then
  Window1.Supaddresslistbox.DeleteAllRows
  While Not rs.EOF
    Window1.Supaddresslistbox.AddRow(rs.Field("street1").StringValue)
    Window1.Supaddresslistbox.Cell(Window1.Supaddresslistbox.LastIndex,1) = rs.Field("street2").StringValue
    Window1.Supaddresslistbox.Cell(Window1.Supaddresslistbox.LastIndex,2) = rs.Field("city").StringValue
    Window1.Supaddresslistbox.Cell(Window1.Supaddresslistbox.LastIndex,3) = rs.Field("state").StringValue
    Window1.Supaddresslistbox.Cell(Window1.Supaddresslistbox.LastIndex,4) = rs.Field("country").StringValue
    Window1.Supaddresslistbox.Cell(Window1.Supaddresslistbox.LastIndex,5) = rs.Field("postcode").StringValue
    Window1.Supaddresslistbox.RowTag(Window1.Supaddresslistbox.LastIndex) = rs.Field("NRef").StringValue
    rs.MoveNext
  Wend
End If

Else
MsgBox(“Could not connect to the addressbook database”)

End If
[/code]

Here’s the file link.

Thanks in advance for any advice!

2

Quick look - I found the following:

You have not created the supAddresses table in the database

create table supaddresses (street1 text, street2 text, city text, country text, postcode text, state text)

In your add button for adding addresses your prepared statement sql needs 3 more “?” as you are inserting 6 fields but only using placeholders for 3.

The inserts work after correcting that. Now you should be able to display the data in the lower listbox. You can add a method called from the change event in the supplier listbox to clear the address listbox, query the supaddresses table, and then list the addresses for that supplier.

3

@Peter Fargo

Thanks for bringing the question marks to my attention! I fixed them.

I changed the code to what you recommend above. Now my Open Event looks like this:

// IF THE DATABASE FILE DOES NOT EXIST - CREATE IT AND DEFINE THE SCHEMA If Not fi.Exists Then If C_S_DB.CreateDatabaseFile Then C_S_DB.SQLExecute("CREATE TABLE supaddresses(street1 TEXT, street2 TEXT, city TEXT, country TEXT, postcode TEXT, state TEXT)")

But I’m still getting the error message. Is this the correct way to create the supAddresses table in the database, a part from this line of code?

4

Hey Kayla, in your code, since the Addressbook Data already exists, it’s not executing the code to create the supaddresses table…You could just create that table at the same time you’re creating the addressbook table…Hope this helps

5

Yes, As Kenneth said - create both tables at the same time, otherwise your app uses the already existing database (without the new table).

6

[quote=194604:@Kayla Gordon]@Peter Fargo
I changed the code to what you recommend above. Now my Open Event looks like this:

// IF THE DATABASE FILE DOES NOT EXIST - CREATE IT AND DEFINE THE SCHEMA If Not fi.Exists Then If C_S_DB.CreateDatabaseFile Then C_S_DB.SQLExecute("CREATE TABLE supaddresses(street1 TEXT, street2 TEXT, city TEXT, country TEXT, postcode TEXT, state TEXT)")

But I’m still getting the error message. Is this the correct way to create the supAddresses table in the database, a part from this line of code?[/quote]

I’m just a bit confused on how to create the table.

7

Hey Kayla, I added supaddresses create method to your open event on Window1.
If Not fi.Exists Then
If C_S_DB.CreateDatabaseFile Then
C_S_DB.SQLExecute(“CREATE TABLE addressbook(NRef INTEGER PRIMARY KEY, id TEXT, company TEXT, contactname TEXT)”)

C_S_DB.SQLExecute(“CREATE TABLE supaddresses(street1 TEXT, street2 TEXT, city TEXT, country TEXT, postcode TEXT, state TEXT)”)

  // ERROR EXECUTING THE DATABASE SQL, SO DISPLAY AN ERROR MSGBOX
  If C_S_DB.Error Then
    MsgBox("Error: " + Str(C_S_DB.ErrorCode) + " - " + C_S_DB.ErrorMessage)
    
  Else
    // ALL OK - SO POPULATE THE LISTBOX
    Populate()
  End If

And it created both tables…

8

[quote=194611:@Kenneth Lossman]Hey Kayla, I added supaddresses create method to your open event on Window1.
If Not fi.Exists Then
If C_S_DB.CreateDatabaseFile Then
C_S_DB.SQLExecute(“CREATE TABLE addressbook(NRef INTEGER PRIMARY KEY, id TEXT, company TEXT, contactname TEXT)”)

C_S_DB.SQLExecute(“CREATE TABLE supaddresses(street1 TEXT, street2 TEXT, city TEXT, country TEXT, postcode TEXT, state TEXT)”)

  // ERROR EXECUTING THE DATABASE SQL, SO DISPLAY AN ERROR MSGBOX
  If C_S_DB.Error Then
    MsgBox("Error: " + Str(C_S_DB.ErrorCode) + " - " + C_S_DB.ErrorMessage)
    
  Else
    // ALL OK - SO POPULATE THE LISTBOX
    Populate()
  End If

And it created both tables…[/quote]

Thanks @Kenneth Lossman!
Okay, I made the edit to the Open Event in Window1. I adjusted the code to meet what Peter said earlier.

[code] // IF THE DATABASE FILE DOES NOT EXIST - CREATE IT AND DEFINE THE SCHEMA
If Not fi.Exists Then
If C_S_DB.CreateDatabaseFile Then
C_S_DB.SQLExecute(“CREATE TABLE addressbook(NRef INTEGER PRIMARY KEY, id TEXT, company TEXT, contactname TEXT)”)

  C_S_DB.SQLExecute("CREATE TABLE supaddresses(street1 TEXT, street2 TEXT, city TEXT, country TEXT, postcode TEXT, state TEXT)")
  
  // ERROR EXECUTING THE DATABASE SQL, SO DISPLAY AN ERROR MSGBOX
  If C_S_DB.Error Then
    MsgBox("Error: " + Str(C_S_DB.ErrorCode) + " - " + C_S_DB.ErrorMessage)
    
  Else
    // ALL OK - SO POPULATE THE LISTBOX
    Populate()
  End If[/code]

For some reason, it still sends the error message.

9

can you send another link to your program and I will take another look…not sure of what’s been modified at this point

10

Kayla, after those changes, I was able to add an address and it saved it to the supaddresses table…what error are you getting??

11

Sure @Kenneth Lossman

Thanks for the help! The File is Here.

12

I was able to add a supplier and then add an address with no errors and the data showed up in the tables…

13

Ahhhhh…the error message was continuous because of the old “I forgot to delete the DB folder” trick. The good news is, I can now click the add button without the error message, but I don’t see anything in the list box below…yet!

14

ok great…just a suggestion for your addresses table…I would add a column that corresponds to the primary key id of the supplier, so when you click on a supplier, you can query the addresses for that id and display your addresses…Just a suggestion

15

It finally started to work! That’s a relief! Thank you so much for your help! @Kenneth Lossman

I have another question though. Is it possible to only see the addresses related to that specific supplier? For example, I click one of the names in the top listbox and the address box displays the addresses for that specific supplier. Click another supplier, see only their addresses in the box. That’s why I made it required in the application to have one of the suppliers in the top listbox to be clicked before the Add Address button would activate.

16

Yes there is…if you add a column to your addresses table and save the primary id of your supplier in that table, then you could query your addresses for all of the records with that supplier id.

17

The rowtag property of the listbox is a good place to store the id. It does not involve modifying the database. Another option is to create classes that correspond to your database tables and store instances (records) in the listbox rowtag. Many ways to do it depending on the amount of data and how you need to access it.

18

@Peter Fargo
If I use the rowtag property, would I still need to have the supplier id in the address table, as @Kenneth Lossman suggested?

Do you know of any examples of this (rowtag property), Peter? Anyone?

19

Kayla,
check the SQLiteBlob.xojo_binary_project in the examples folder of Xojo,
the example shows how to add rows with blob data on a table.
every time the file list is reloaded on the listbox, the id is inserted in the RowTag of the list row.
finally, the ‘Delete file from DB’ button deleted the selected row from the database using the ID to perform the query
hope this helps,

 Giulio
20

Peter is right, this is exactly how I do this…I use rowtag and classes…