You can help by commenting or suggesting your edit directly into the transcript. We'll review any changes before posting them. All comments are completely anonymous. For any comments that need a reply, consider emailing firstname.lastname@example.org.
We are experiencing playback issues from our video hosting provider. Please check back shortly.
2:34Querying Data from Database
2:56Filter Data in Table
2:17Add Data to Database
4:21Edit Data in Database
2:18Refreshing SQL Data on Component
3:00Deleting Multi-Selected Rows from Table
Take topic challenge
Edit Data in Database
Learn how to edit data in the database by using a table component for entry selection and a popup window for edits.
Video recorded using: Ignition 7.9
Transcript(open in window)
[00:00] Editing data in the database follows a very similar approach to how we learned to add data in a previous video. The general idea behind this approach is to create your pop-up window for data entry, pass an identifying parameter when opening this window, and then using that parameter to retrieve the specific row in your database that you wish to edit. First, let's take a look at our main Database Manipulation window, which was created in a previous video. We want our currently-selected row in the table to be the record that we are going to edit. Keep track of this, let's add a custom property to the table called id, which you can see I've done right here, or type integer. This is the property that we passed into the edit window when we opened the window. Next, let's create the script on the edit button that's going to open our window and pass in the parameter. You put this on the actionerformed. You to Open and Center, choose the window that you want to open, check Pass Parameters, give your parameter a name that you want to pass in and we bind that to the id property that we just added on our table. Once that's done, you click OK and that's all we have to do for this window right here. Our other window, the Inventory Edit window, contains all the components that will show the data for the different columns of the row that we bring back. First thing we need to do is add our property called id to the root container. This id will be a type integer. The second thing we need to do is add a data property and this is going to be a type dataset. The id is going to be the passed in id from our Database Manipulation window and the data is going to hold all the data from the row that, that id specifies. After adding these, if we go to our data property, we can see that we add a binding, a sequel query binding that says, "Select * from inventory "where id equals," and we reference our id property that's on the Root Container. So this way, when the window gets opened, ids passed in, it's using this where clause and the only row that's brought back is the one that's related to this id right here. The next things we want to do is bind all the component's text properties to rows within the dataset that we just made. So the text property here is going to be bound to the name property from that dataset. Location for the location. Description to the description. And quantity to the quantity. You can see that I'm dribbling down in the data property right here and getting the column called quantity and then returning that value. The last thing we want to do is write the script that's actually going to run the update query. We'll open this up. We'll put this on actionPerformed. And the script that we're going to run has five different variables. This one related to the id property that we created on the Root Container. One to the name, one the location, one to the description, and one for the quantity. The system.db.runPrepUpdate function is what's actually going to run our update query. I have my update query here setting the name, and location, and all the other columns to their appropriate variables within my script. And then after that gets run, I'm going to close the windows since, again, this is a pop-up window. We go OK. Now we can go back to our Database Manipulation window, put the designer in preview mode, select one of our rows here. We're going to click Edit, which opens our Edit Inventory window, and make some changes here. Let's just say this is going to be test. Let's change is quantity to 15 and let's just edit this name right here. We click Save. And now we can see that our changes have been made on our table.