Is something like this possible?

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

Is something like this possible?

Post by Slyde »

Hi Forum. I mess around with Lazarus a little. I'm not a good programmer, but Lazarus makes it easy to create database apps. I'm working with Code::Blocks, wxSQLite3 and wxWidgets now, trying to create one of those Laz apps. And I'm having hell with it.

On a form, I have a Listbox, multiline textbox, and a wxGrid. Same as mentioned in my prior post, this is a little program for my girlfriend, one for all of her recipes, etc.

The Listbox will hold the recipe names, textbox the recipe note, and the grid will house the ingredients/amounts.

I'm able to load at startup the recipe names in the Listbox, and the corresponding note and ingredients/amounts.

My problem is that I can't figure out how to change the note and ingredients/amount when the Listbox selection changes to a new recipe name. Here is the code I'm using for this:

Code: Select all

void Angela001Frame::OnListBox1Select(wxCommandEvent& event)
{
    try
    {
        db->Open("zyx.db");
        wxSQLite3Statement stmt1 = db->PrepareStatement(" SELECT recipe_id, recipe_name, note FROM recipes WHERE recipe_name = ?");
        stmt1.Bind(1, ListBox1->GetSelection()); // I'M TRYING TO BIND THE LISTBOX recipe_name HERE
        wxSQLite3ResultSet result = stmt1.ExecuteQuery();
        //int colCount = result.GetColumnCount();
        int set_id = result.GetInt(0);
        int newRow = 0;
        wxMessageBox("while loop next","");
        while (result.NextRow())
        {
            wxMessageBox("Inside while loop", "");  // I NEVER MAKE IT THIS FAR
            ListBox1->AppendAndEnsureVisible(result.GetAsString(1));
            if (newRow > 0){
                newRow++;
            } else {
                ListBox1->SetSelection(0);
                TextCtrl1->SetValue(result.GetAsString(2));
                wxSQLite3Statement stmt2 = db->PrepareStatement("SELECT ingredient, amount "
                                                                "FROM recipe_ingredients "
                                                                "INNER JOIN recipes ON recipes.recipe_id = recipe_ingredients.recipe_id "
                                                                "WHERE recipe_ingredients.recipe_id = ?");
                stmt2.Bind(1, set_id);
                wxSQLite3ResultSet result1 = stmt2.ExecuteQuery();
                int colCount1 = result1.GetColumnCount();
                while (result1.NextRow())
                {
                    for (int i = 0; i < colCount1; i++)
                    {
                        Grid_View_Recipes->SetCellValue(newRow, i, result1.GetAsString(i));
                    }
                newRow++;
                }
                stmt2.Finalize();
            }
        }
        stmt1.Finalize();
    } catch(wxSQLite3Exception& e) {

    }
    db->Close();
}
As you can see in the first query, I'm checking for matching names because I don't know how to store the recipe_id with the recipe_name in the Listbox, or if that's even possible. But this way's doable. My main issue is not being able to keep the textbox and wxGris in sync with the Listbox.

Here are my two tables:

Code: Select all

CREATE TABLE recipes(
   recipe_id INTEGER PRIMARY KEY AUTOINCREMENT,
   recipe_name TEXT,
   note TEXT);

Code: Select all

CREATE TABLE recipe_ingredients(
   ingredient_id INTEGER PRIMARY KEY AUTOINCREMENT,
   recipe_id INTEGER,
   ingredient TEXT,
   amount INTEGER,
   FOREIGN KEY(recipe_id) REFERENCES recipes(recipe_id) ON DELETE CASCADE);
i guess this is what's called relational database programming. Not sure. All I know is that being able to do something like this, seems so fundamental, opens a lot of doors.

And help on this would be cool. Thanks.
Linux Mint 21.3 | wxWidgets-3.2.4
User avatar
doublemax
Moderator
Moderator
Posts: 19103
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: Is something like this possible?

Post by doublemax »

If you don't reach the outer while loop (BTW: why is it a loop, you only expect to process one item?), there are only two possible reasons. Either there is an error in the SQL command, or there really is no entry that matches the SELECT:

1) put a message box in the catch handler, so that you know when something is wrong with the sql command.
2) print out the content of ListBox1->GetSelection() and confirm that it returns what you expect
I don't know how to store the recipe_id with the recipe_name in the Listbox
You can store additional data with each item: http://docs.wxwidgets.org/trunk/classwx ... 457e16a8d9
(cast the entry ID to (void*))
Use the source, Luke!
Slyde
Earned some good credits
Earned some good credits
Posts: 130
Joined: Mon Apr 09, 2018 11:08 pm

Re: Is something like this possible?

Post by Slyde »

Hello, Ma..Ma...Ma...Max Headroom =) Not many know about that, I bet. Anyway...

There are no SQL errors. Since I didn't know how to call up the recipe_id from the Listbox, the first query simply tries to match the selected text to a recipe_name.

Inner loop takes the recipe_id (retrieved from first query) and pulls out the corresponding ingredients/amounts.

I really like what I'm working with here, the C++, wxWidgets and wxSQLite3. It's just new to me. I'm used to working with the data-aware components in Lazarus that would make getting the field ID from a DBGrid to use on another a breeze. This is just different. If you guys don't mind my questions, I'll get to know what you have here a lot better. I don't know if I prefer it over the other, but if I'm ultimately able to do with wxWidgets/wxSQLite3 all I can do with Lazarus, then I'm all for it. I just hate asking questions. Even the forum here is just 2 pages of history. Not a lot to learn from.

I'll look at what you provided. Thanks for that. I'll be back...
Last edited by Slyde on Sun Jul 14, 2019 3:04 am, edited 1 time in total.
Linux Mint 21.3 | wxWidgets-3.2.4
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Is something like this possible?

Post by utelle »

As far as I can tell your problem is related to parameterizing the select SQL statement:

Code: Select all

        stmt1.Bind(1, ListBox1->GetSelection()); // I'M TRYING TO BIND THE LISTBOX recipe_name HERE
Here you call method GetSelection of your listbox to bind the selected recipe name to your SQL statement. However, method GetSelection returns an integer index into the associated recipe name list, not the recipe name. You will have to use method GetString in addition. That is, the following code should work:

Code: Select all

        stmt1.Bind(1, ListBox1->GetString(ListBox1->GetSelection())); // I'M TRYING TO BIND THE LISTBOX recipe_name HERE
However, I didn't check the rest of your code, so there might be further glitches.

Regards,

Ulrich
Post Reply