REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 45: ALLOWING THE USER TO UPDATE THE states TABLE

Howdy, folks! Or kids, or whoever’s out there...

Up until now, we’ve populated our states Table with data, we’ve queried it, we’ve displayed its values on a Form, both in a DataGridView Control at the top of the Form and in separate Controls at the bottom of the Form -- even displaying the images pointed to by their paths stored in the database -- and the corresponding web page where the book is featured.

As an example of the last named things, here is the Florida image displayed at the bottom of the Form and what displays in your Browser when you click that image:

PREPARE TO ALLOW THE USER TO UPDATE THE states TABLE

Now we are going to allow the user to update certain columns in the states Table. Most of the columns should not be changed, as they contain facts, and facts are immutable (or should be).

Note: The word “immutable” is not unique to programming, but it is a word you will probably hear more in the programming world than most other places. Like a lot of these words, it means something relatively simple, namely “can’t be changed.” So “unchangeable” would work just as well.  

There are three columns, though, that contain data that are specific to any given user, namely haveResidedThere, haveVisited, and notes. If this were a database that would be used by multiple people, this design wouldn’t work very well, because one person would add data specific to them for those columns, and then somebody else would come along and with “one fell swoop” overwrite those values with their own.

For example, if Billygoat Butthead were to enter data for those columns, for Missouri he would enter No to haveResidedThere, Yes to haveVisited, and in notes something like, “Went to Branson to see Dolly Parton and The Shepherd of the Hills.”

Then I would come along, enter Yes to haveResidedThere (which would automatically also select Yes for haveVisited, too) and in notes I would enter “Grandfather Albert L.B. Kollenborn born in DeWitt; Mark Twain born Florida, spent boyhood in Hannibal; lived in Piedmont in the “Bootheel” in the mid-2000s.”

My entries would wipe out B. Butthead’s, as if he had never even bothered to gingerly stamp his cloven hooves on the keyboard at all. The value for the haveVisited column would remain the same, but that only by mere happenstance, so to speak.

So if we wanted to store each user’s individual entries, we would need at least one more Table to do so, probably two: A Users Table with their Name and its ID value, and a PersonalNotes Table (or whatever name you wanted to give it), that would store what they enter for any State they enter data for. This is mainly a discussion for later, when we go into Relational Databases Design, but for now I will just show you a simple version of the structure of these two Tables:

USERS

user_id		int

userFirstName	VarChar(50)

userLastName	VarChar(50)

USERS_ENTRIES

userentries_id	int

fk_statesid		int

fk_user_id		int

haveResided		TinyInt(1)

haveVisited		TinyInt(1)

users_notes		Text

The USERS Table here is what is called a “Lookup Table”; the USERS_ENTRIES Table would store a Record for each State that each user entered data for. Again, we will delve more into this type of structure and relationship later.

For now, we will simply treat the states Database as if only one person will ever use it. It is their own personal data playground. So how to update the states Table if the user enters data into any of those three Controls (haveResidedThere, haveVisited, and notes)?

First, we have to make those Controls visible, so go to the Form’s Load Event Handler, and change this code:

VisibilizeUserControls(false);

...to this:

VisibilizeUserControls(true);

Now when you run the app, those Controls are visible:

The next thing we need to do is to write the code that will enable the Update Button. Then, we will write the Update Button’s Click Event Handler to update the appropriate columns in the states Table for the Row that holds the current State’s data.

Note: There is one more property on the controls we should set first. As for the “read-only” Controls, it doesn’t matter, because there is no reason for the user to be tabbing through them. But they may want to Tab through the controls they can modify. So we will set the TabIndex property values of those Controls. Set the TabIndex of ckbxLived to 1, ckbxVisited to 2, and richTextBoxNotes to 3. You can also set focus to the first checkbox at the time it is made visible (in the VisibilizeUserControls method), by adding this line of code at the end of that method: if (Visibilize) ckbxLived.Focus();

Now we will write some code that enables the Update Button once there is something to update, that is after the user has checked one of the two Checkboxes (“Have Lived There” or “Have Visited”) or entered anything in the “Notes” RichTextBox Control.

2-click ckbxLived. Add the following line of code to its default Event Handler, which opens in the code window, ckbxLived_CheckedChanged:

btnUpdate.Enabled = (sender as CheckBox).Checked;

if (ckbxLived.Checked) ckbxVisited.Checked = true;

Now click ckbxVisited, and follow these steps:

0) Click the Events (Lightning) icon in the Properties pane

1) From the dropdown, select ckbxLived_CheckedChanged, as shown here:

This is in accord with the DRY principle – rather than write the same code for each CheckBox’s CheckedChanged Event Handler, let them share the same code, casting the sender to a generic CheckBox so that it works for any CheckBox that uses the code.

Finally, 2-click richTextBoxNotes and add this code in its default Event Handler, richTextBoxNotes_TextChanged:

btnUpdate.Enabled = !string.IsNullOrEmpty(richTextBoxNotes.Text);

What this is saying in plain English is, “If the Text in the RichTextBox is not empty, enable the Update Button.” In other words, if there is anything in that RichTextBox other than “whitespace” (characters that don’t display, such as by placing the cursor in the RichTextBox and pressing the Space Bar or Tab key), enable the Update Button.

The code we added looks like this:

Note that the ckbxLived_CheckedChanged Event Handler has 2 references (rectangled). This is because that Event Handler is called from two places: from the CheckedChanged Event of ckbxLivedand from the CheckedChanged Event of ckbxVisited.

ALLOW THE USER TO UPDATE THE states TABLE

Now we will add the code that actually updates the states Table. 2-click btnUpdate to open the code window in a Button’s default Event Handler, Click. Add the following code there:

 private void btnUpdate_Click(object sender, EventArgs e)

 {

     bool resided = ckbxLived.Checked;

     bool visited = ckbxVisited.Checked;

     int pseudoboolResided = 0;

     int pseudoboolVisited = 0;

     string notes = richTextBoxNotes.Text;

     string stateName = txtbxStateName.Text.Trim();

     if (resided)

     {

         pseudoboolResided = 1;

     }

     if (visited)

     {

         pseudoboolVisited = 1;

     }

     try

     {

         conn = new MySqlConnection(connstr);

         conn.Open();

         MySqlCommand comm = conn.CreateCommand();

         comm.CommandText =

                 "UPDATE states " +

                 "SET haveResidedThere = @haveResidedThere, haveVisited = 								@haveVisited, notes = @notes " +

                 "WHERE stateName = @stateName";

         comm.Parameters.AddWithValue("@haveResidedThere", pseudoboolResided);

         comm.Parameters.AddWithValue("@haveVisited", pseudoboolVisited);

         comm.Parameters.AddWithValue("@notes", notes);

         comm.Parameters.AddWithValue("@stateName", stateName);

         comm.ExecuteNonQuery();

         MessageBox.Show(string.Format("Record added for {0}", stateName));

         ClearUserModifiableControls();

     }

     finally

     {

         conn.Close();

     }

 }

What this code is doing:

 bool resided = ckbxLived.Checked;

 bool visited = ckbxVisited.Checked;

 int pseudoboolResided = 0;

 int pseudoboolVisited = 0;

 string notes = richTextBoxNotes.Text;

 string stateName = txtbxStateName.Text.Trim();

 if (resided)

 {

     pseudoboolResided = 1;

 }

 if (visited)

 {

     pseudoboolVisited = 1;

 }

This variable declaration portion of the code is interesting because we have to “jump through some hoops” due to the fact that the natural “data type” of a CheckBox is boolean (it’s either true or false, off or on, checked or unchecked). Yet MySql does not have a true boolean type. It uses a TinyInt with a length of 1, where 0 represents false, and 1 represents true.

So we had to have both bool variables (to coincide with the State of the CheckBoxes) and “pseudo-boolean” ints, to coincide with MySqls use of such. The “pseudo-boolean ints” are set to 0 by default, when declared, and only set to 1 (true, checked) if that is the case.

Most of the SQL Update code that follows that is very similar to what we’ve dissected and discussed before, so we won’t dwell on that part of the code. The only real difference is a “nicety” given to the user, to let them know that the record has been inserted into the database:

 MessageBox.Show(string.Format("Record added for {0}", stateName));

...and then there is a call to the ClearUserModifiableControls method, which simply clears the user-modifiable Controls, so that they do not retain their values as the user navigates on to other Records/Rows/States:

private void ClearUserModifiableControls()

 {

     ckbxLived.Checked = false;

     ckbxVisited.Checked = false;

     richTextBoxNotes.Text = string.Empty;

 }

And here are the updated records as shown in DBWB, after I modified all eight States in which I have resided:

I could also check the “Have Visited” CheckBox for all of the States except Hawaii, but this is getting monotonous, and it’s time to move on to our next challenge/adventure.

In the next Step, we will delve into the wonderful world of Relational Database theory and design. See you then!

Earth-shakingly Important Notice: If you have a basic programming question (suitable to an audience of “Kids”), send it to idiolectable@gmail.com, specifying whether you would like your name and location used if it is printed in a future “Step” of this newsletter. If you are a subscriber to the newsletter, you can also leave a question at the bottom of this Step, in the “Comments” section.

If you do not want to give your real name, a nickname is acceptable (the first “Letter to the Editor” of mine that was printed appeared in Rolling Stone magazine, back in the early 1970s, and I signed it “Sylvester” for some reason which I no longer remember).  

Finally, it’s always interesting to see where people are from, so please provide your City or Town and the State it’s in, too (or Province, or whatever the region where you live is called).

To listen to this Step, the audio of it can be found here: