Hey guys, this is just a quick video to show you a couple of options you have for visualizing data.
If you are in an earlier version of Winrk, say 7.5 or earlier, you don't have the chromium control
that I used in the previous assignment.
So this is the Winrk web browser control.
It works very similar, but there's a few differences.
I'll show you how to make this work.
And there is also the Microsoft Hierarchical Flex grid control.
And it's a database control.
It's an ActiveX and it's got click events.
So if I click on this notice that I can update my tags.
I don't have to manually enter this stuff.
So in this video I'm going to show you how to use both of these.
I'm going to give you these screens and also the scripts behind them.
And you can use whatever you would like going forward in this course and in your projects to visualize
your database data.
So let's get started.
So in the previous lecture we were using the chromium web browser.
But if you're in CC version 7.5, Service pack two or lower, you do not have this control, but you
should have the CC web browser control.
And we can use that as well.
There's just one change to the script that we have to make.
And so basically what I've done is I've just made a copy and I've replaced the chromium control with
the CC web browser control.
It is still named web browser.
I do that so that the script still works.
And if you double click, you don't get the properties interface.
It doesn't have one and I've just set under control properties.
I've turned off the status and the navigation and my page, which is the same as address by default
is set to Aboutblank.
And that just shows a blank page.
And that's fine.
One difference between this control and chromium is that it doesn't automatically update if you just
give it an updated page with the same name.
So on the insert script.
We had to add a line of code.
And basically when we refresh the browser, the address has to change to a completely different page.
It's not enough to just give it an updated version of the same file.
So once we build our staff table dot HTML, since it's already set to that, we're going to first set
it to about blank, and then we're going to set it back to our file name here, which is going to be
staff table dot HTML.
And that does constitute a page change and it will refresh.
If you don't do this step you won't see the update once you insert something until you change the wink
screen to something else and come back.
And so just looking at this in runtime, the browser looks a little bit different.
This is the web browser control.
But let's say that we want to add a new person.
I'll just add Kenny Rogers as a supervisor.
We hit insert and you see that it does update the web browser after we do the insert.
And of course update and delete are done the same way.
So just to sum up, it's okay to use the wink web browser control.
You just have to make sure you add that extra line of code when you are making changes to the table,
you're just going to change it to about blank and then put in your regular HTML screen.
Another way to visualize data in wink is using the MSH flex DB grid control, and this is an ActiveX
control, and it typically installs with wink.
It comes from Visual Basic six, and you don't automatically have it in your controls palette.
So if you don't have it, then right click on ActiveX and do Add remove.
And this will list all the ActiveX controls on your system.
And you can see that I have Microsoft Hierarchical Flex Grid Control 6.0.
That's kind of a mouthful.
I have it checked, so originally it's unchecked.
What you'll have to do is come down here in this list, look under Microsoft and you will see the flex
grid control or the make sure you get the MSH.
So there's one that does not have that.
That's not the one you want.
So you'll find this one and you'll just check it and click okay.
And it's going to show up in this list.
And then you can just put it on the screen.
So let's take a look at how it works.
And then I'll go through the scripts that make it work.
And I'll make these available to you if you would like to use it in your projects.
So taking a look at the flex DB grid control in runtime, it looks a lot like a database grid that you
may have seen in other applications.
The cool thing about this is since it's an ActiveX, as I click on it, I can get an event and I can
automatically populate our fields, which is a step up when using the web browser.
And if I want to make a change, let's say that I am going to make a change here to Georgia's name.
And let's say that it's spelled differently.
I can do an update and it does update this window.
I can of course add my staff members here and use our regular controls to insert.
And you can also do the alternating colors and things like that.
It's a little more involved in doing it with this control than it is the way that we're doing the web
browser, but this does certainly have some advantages in using this.
So let's go look at the scripts that make all this work, and I will make those available to you.
And we'll have an assignment where you work with this control.
So going back to graphics designer, first let's take a look at the open picture function.
And that's what initially loads our control.
Now the control itself is named my MSH Flex Grid Control.
And it does have a lot of properties.
You can set your number of columns and rows and you can do your colors and things like that.
So you have some flexibility in how this thing actually looks.
But going back to open picture, it's very simple.
We create a SQL string.
And so we're just getting the ID first name, last name, role ID ordering it by our ID.
And I have written an update DB grid control function.
We're going to go through that next.
And it's a lot like the function we wrote to create the HTML document from a SQL string.
Here we are just assigning a record set to this control which will load it.
And you can also do it manually row by row, column by column.
But doing a record set actually does pretty well.
So we send it the name of the screen that we're on, and we're sending it the name of the control that
we want to update.
And we're sending it the SQL command that we want to return as a record set.
And so going over to Wink Explorer in our global scripts, we'll go to our VBS editor.
And here in our SQL functions I have added the update DB grid.
And again it's a lot like your load combo box and build HTML.
So looking at this function here we're sending in the screen name, the name of the control we want
to update and the SQL.
And all of this should be familiar to you.
We are creating a connection and a record set.
We are setting our connection to our global connection string.
We are disabling errors so that we can handle this opening the connection.
We are executing the record set.
So now the record set has all the records.
So we've selected all of our staff members.
And then basically as long as we have records we set a variable called DG for data grid.
And we are setting it to the MSH flex grid control on this, on this screen that we pass in and the
name of the control.
And so now this DG object is referencing our data grid control on our screen.
And then once we have records we just use a with command to set the properties of the data grid.
So we just take the record set and throw it right into the data source property of the data grid that
shows the records.
We hit refresh, which ensures that it updates and here we're just setting the column widths.
And I just manually set these based on the data we had in the staff table.
You can make these dynamic.
You can also set the back color in the four color and do some code to alternate if you want.
You could get really complex with it and make it look really nice, but basically this is the code that
populates that.
Now this is not an active connection here.
This is kind of a one time thing.
So once I assign this record set to the data source, then the control renders it.
So I can go ahead and close and get rid of that record set.
It's not going to be retained in memory.
So we don't have a memory leak hanging out there.
And of course if we do run into an error, then we just print out that error and clear that error.
So this is very similar to our build HTML, where we're sending in a table name and a file name and
a sequel.
But here we are just sending it control to update.
So going back to Graphics Designer, there's a few other things that we've done here.
So on the control itself there is an object event and it is called cell change.
So that's any time you make a different selection.
And we have a VB script on that.
And basically we are updating our interface tag.
So when you select a given row then it gets the row.
Remember item is a pointer to whatever object the script is on.
So since this script is on our data grid control item represents the data grid control.
So we're just getting the current row.
And then this is the column.
So basically we are taking the first column value making it an integer.
That's what cnt does.
It casts it to an integer.
And we're writing it into our staff selected ID.
The reason we're casting it to an integer is because this is an integer tag.
You have to remember this.
The next thing is we have our staff first name.
This is zero column.
This is one column.
This is two column.
This is three column.
So our first name is in our column number one which is the second column.
We write that as a string.
So we cast it to a string put it in our string tag.
Same thing with our last name which is our third column.
Our fourth column is a row ID and we cast that to an integer.
So the end result of that is when we make a new selection, it fires that event, and it pulls the column
information out of the selected row and puts it into these tags.
And that's a pretty nice interface.
Now we need to look at our insert and update and delete tags.
So if we look at our insert button, this is pretty much the same as it is on our staff screen with
the web control.
So we are grabbing the information from our variables.
We are building a SQL string here for our insert, and we're just using our very handy exec SQL function
to insert that into the database.
Then we're resetting our tags, and then we are just calling our refresh code.
So we're rebuilding our SQL string.
We're calling our update DB grid.
And for the screen name I'm using item dot parent dot object name.
Now we've done this before, but basically the item is our button that we're on and its parent is the
screen that we're on.
So that's going to be manage staff DB grid.
And then the object name is the actual name of the screen.
Then of course the name of the flex grid control that we want to update.
And our SQL string that we built.
So that's pretty simple.
Same thing with update.
If we look at update and we're using a command object with a parameterized SQL string.
So we're filling in last name, first name role ID that we want to update, and then sent a message
box to verify.
And then if they hit anything other than yes then we cancel the action.
If not, then we execute our command object and print out any errors.
And then of course we call our update code.
Same thing on delete.
It should be pretty familiar to you.
We are just creating a parameterized string, executing that and then updating our control.
Here I will give these scripts to you as well as the update db grid function.
And the next assignment.
You'll be able to kind of play around with this, but you should be able to look at this and understand
it and hopefully write this on your own using the code we've written previously as an example.
And there's one other change I want to make.
I'm going to give you a quick tip.
Notice how when I click into this field, it blows everything away.
So I have to retype everything.
What if I just want to change a couple of letters?
Well, that's just a property on I o fields.
So if I select this I o field and look at output input, there is a property called clear on new input.
And I've showed you this before.
But if you do right click.
And what's this?
This HTML help tells you exactly what it does.
And this one if you have it set to yes, it blows away the contents.
If you say no, it doesn't.
What I will do is set this to no and set this one also to no.
Save this.
And then when we go to runtime, it does not blow away everything and we can just change only the things
that we want.
So there's an extra tip for you.
So now you have several ways to visualize database data on your work screens.
Next is going to be an assignment where you will use the work web browser control, as well as the MSH
flex DB grid control.
See you in the next assignment.