1 00:00:05,390 --> 00:00:10,790 Hey guys, this is just a quick video to show you a couple of options you have for visualizing data. 2 00:00:10,790 --> 00:00:17,750 If you are in an earlier version of Winrk, say 7.5 or earlier, you don't have the chromium control 3 00:00:17,750 --> 00:00:20,420 that I used in the previous assignment. 4 00:00:20,420 --> 00:00:22,700 So this is the Winrk web browser control. 5 00:00:22,700 --> 00:00:25,220 It works very similar, but there's a few differences. 6 00:00:25,220 --> 00:00:27,440 I'll show you how to make this work. 7 00:00:27,440 --> 00:00:32,690 And there is also the Microsoft Hierarchical Flex grid control. 8 00:00:32,690 --> 00:00:34,430 And it's a database control. 9 00:00:34,430 --> 00:00:38,060 It's an ActiveX and it's got click events. 10 00:00:38,060 --> 00:00:41,330 So if I click on this notice that I can update my tags. 11 00:00:41,330 --> 00:00:43,520 I don't have to manually enter this stuff. 12 00:00:43,520 --> 00:00:46,940 So in this video I'm going to show you how to use both of these. 13 00:00:46,940 --> 00:00:51,320 I'm going to give you these screens and also the scripts behind them. 14 00:00:51,320 --> 00:00:57,050 And you can use whatever you would like going forward in this course and in your projects to visualize 15 00:00:57,050 --> 00:00:58,010 your database data. 16 00:00:58,010 --> 00:00:59,630 So let's get started. 17 00:00:59,630 --> 00:01:04,400 So in the previous lecture we were using the chromium web browser. 18 00:01:04,400 --> 00:01:11,690 But if you're in CC version 7.5, Service pack two or lower, you do not have this control, but you 19 00:01:11,690 --> 00:01:14,150 should have the CC web browser control. 20 00:01:14,150 --> 00:01:16,400 And we can use that as well. 21 00:01:16,400 --> 00:01:19,100 There's just one change to the script that we have to make. 22 00:01:19,100 --> 00:01:25,970 And so basically what I've done is I've just made a copy and I've replaced the chromium control with 23 00:01:25,970 --> 00:01:27,980 the CC web browser control. 24 00:01:27,980 --> 00:01:29,930 It is still named web browser. 25 00:01:29,930 --> 00:01:32,210 I do that so that the script still works. 26 00:01:32,210 --> 00:01:35,180 And if you double click, you don't get the properties interface. 27 00:01:35,180 --> 00:01:38,570 It doesn't have one and I've just set under control properties. 28 00:01:38,570 --> 00:01:45,920 I've turned off the status and the navigation and my page, which is the same as address by default 29 00:01:45,920 --> 00:01:47,450 is set to Aboutblank. 30 00:01:47,450 --> 00:01:50,510 And that just shows a blank page. 31 00:01:50,510 --> 00:01:51,890 And that's fine. 32 00:01:51,890 --> 00:01:58,790 One difference between this control and chromium is that it doesn't automatically update if you just 33 00:01:58,790 --> 00:02:01,250 give it an updated page with the same name. 34 00:02:01,250 --> 00:02:04,430 So on the insert script. 35 00:02:05,010 --> 00:02:07,380 We had to add a line of code. 36 00:02:07,380 --> 00:02:13,620 And basically when we refresh the browser, the address has to change to a completely different page. 37 00:02:13,620 --> 00:02:17,940 It's not enough to just give it an updated version of the same file. 38 00:02:17,940 --> 00:02:25,020 So once we build our staff table dot HTML, since it's already set to that, we're going to first set 39 00:02:25,020 --> 00:02:30,600 it to about blank, and then we're going to set it back to our file name here, which is going to be 40 00:02:30,600 --> 00:02:32,340 staff table dot HTML. 41 00:02:32,340 --> 00:02:36,840 And that does constitute a page change and it will refresh. 42 00:02:36,840 --> 00:02:44,340 If you don't do this step you won't see the update once you insert something until you change the wink 43 00:02:44,340 --> 00:02:47,250 screen to something else and come back. 44 00:02:47,250 --> 00:02:52,710 And so just looking at this in runtime, the browser looks a little bit different. 45 00:02:52,710 --> 00:02:54,450 This is the web browser control. 46 00:02:54,450 --> 00:03:00,240 But let's say that we want to add a new person. 47 00:03:00,720 --> 00:03:04,170 I'll just add Kenny Rogers as a supervisor. 48 00:03:04,170 --> 00:03:12,480 We hit insert and you see that it does update the web browser after we do the insert. 49 00:03:12,480 --> 00:03:15,570 And of course update and delete are done the same way. 50 00:03:15,570 --> 00:03:20,100 So just to sum up, it's okay to use the wink web browser control. 51 00:03:20,100 --> 00:03:26,220 You just have to make sure you add that extra line of code when you are making changes to the table, 52 00:03:26,220 --> 00:03:31,230 you're just going to change it to about blank and then put in your regular HTML screen. 53 00:03:32,010 --> 00:03:40,560 Another way to visualize data in wink is using the MSH flex DB grid control, and this is an ActiveX 54 00:03:40,560 --> 00:03:44,280 control, and it typically installs with wink. 55 00:03:44,310 --> 00:03:50,070 It comes from Visual Basic six, and you don't automatically have it in your controls palette. 56 00:03:50,070 --> 00:03:54,900 So if you don't have it, then right click on ActiveX and do Add remove. 57 00:03:54,900 --> 00:03:58,680 And this will list all the ActiveX controls on your system. 58 00:03:58,680 --> 00:04:04,410 And you can see that I have Microsoft Hierarchical Flex Grid Control 6.0. 59 00:04:04,440 --> 00:04:05,790 That's kind of a mouthful. 60 00:04:05,790 --> 00:04:08,760 I have it checked, so originally it's unchecked. 61 00:04:08,760 --> 00:04:14,700 What you'll have to do is come down here in this list, look under Microsoft and you will see the flex 62 00:04:14,700 --> 00:04:18,030 grid control or the make sure you get the MSH. 63 00:04:18,030 --> 00:04:20,490 So there's one that does not have that. 64 00:04:20,490 --> 00:04:21,720 That's not the one you want. 65 00:04:21,720 --> 00:04:26,130 So you'll find this one and you'll just check it and click okay. 66 00:04:26,130 --> 00:04:29,280 And it's going to show up in this list. 67 00:04:29,280 --> 00:04:32,010 And then you can just put it on the screen. 68 00:04:32,010 --> 00:04:34,920 So let's take a look at how it works. 69 00:04:34,920 --> 00:04:37,590 And then I'll go through the scripts that make it work. 70 00:04:37,590 --> 00:04:42,450 And I'll make these available to you if you would like to use it in your projects. 71 00:04:42,540 --> 00:04:50,520 So taking a look at the flex DB grid control in runtime, it looks a lot like a database grid that you 72 00:04:50,520 --> 00:04:52,410 may have seen in other applications. 73 00:04:52,410 --> 00:04:59,520 The cool thing about this is since it's an ActiveX, as I click on it, I can get an event and I can 74 00:04:59,520 --> 00:05:05,220 automatically populate our fields, which is a step up when using the web browser. 75 00:05:05,220 --> 00:05:12,390 And if I want to make a change, let's say that I am going to make a change here to Georgia's name. 76 00:05:12,390 --> 00:05:15,180 And let's say that it's spelled differently. 77 00:05:15,180 --> 00:05:19,620 I can do an update and it does update this window. 78 00:05:19,620 --> 00:05:27,840 I can of course add my staff members here and use our regular controls to insert. 79 00:05:27,840 --> 00:05:31,140 And you can also do the alternating colors and things like that. 80 00:05:31,140 --> 00:05:37,560 It's a little more involved in doing it with this control than it is the way that we're doing the web 81 00:05:37,560 --> 00:05:42,870 browser, but this does certainly have some advantages in using this. 82 00:05:42,870 --> 00:05:48,600 So let's go look at the scripts that make all this work, and I will make those available to you. 83 00:05:48,600 --> 00:05:52,020 And we'll have an assignment where you work with this control. 84 00:05:52,380 --> 00:05:57,660 So going back to graphics designer, first let's take a look at the open picture function. 85 00:05:57,660 --> 00:06:00,990 And that's what initially loads our control. 86 00:06:00,990 --> 00:06:06,030 Now the control itself is named my MSH Flex Grid Control. 87 00:06:06,030 --> 00:06:07,950 And it does have a lot of properties. 88 00:06:07,950 --> 00:06:14,010 You can set your number of columns and rows and you can do your colors and things like that. 89 00:06:14,010 --> 00:06:18,210 So you have some flexibility in how this thing actually looks. 90 00:06:18,210 --> 00:06:21,780 But going back to open picture, it's very simple. 91 00:06:21,780 --> 00:06:24,630 We create a SQL string. 92 00:06:24,630 --> 00:06:31,260 And so we're just getting the ID first name, last name, role ID ordering it by our ID. 93 00:06:31,260 --> 00:06:35,940 And I have written an update DB grid control function. 94 00:06:35,940 --> 00:06:37,530 We're going to go through that next. 95 00:06:37,530 --> 00:06:43,680 And it's a lot like the function we wrote to create the HTML document from a SQL string. 96 00:06:43,680 --> 00:06:51,780 Here we are just assigning a record set to this control which will load it. 97 00:06:51,780 --> 00:06:55,410 And you can also do it manually row by row, column by column. 98 00:06:55,410 --> 00:06:58,230 But doing a record set actually does pretty well. 99 00:06:58,230 --> 00:07:03,720 So we send it the name of the screen that we're on, and we're sending it the name of the control that 100 00:07:03,720 --> 00:07:04,950 we want to update. 101 00:07:04,950 --> 00:07:11,310 And we're sending it the SQL command that we want to return as a record set. 102 00:07:11,310 --> 00:07:17,340 And so going over to Wink Explorer in our global scripts, we'll go to our VBS editor. 103 00:07:17,550 --> 00:07:22,950 And here in our SQL functions I have added the update DB grid. 104 00:07:22,950 --> 00:07:27,180 And again it's a lot like your load combo box and build HTML. 105 00:07:27,180 --> 00:07:33,720 So looking at this function here we're sending in the screen name, the name of the control we want 106 00:07:33,720 --> 00:07:35,430 to update and the SQL. 107 00:07:35,430 --> 00:07:37,920 And all of this should be familiar to you. 108 00:07:37,950 --> 00:07:40,950 We are creating a connection and a record set. 109 00:07:41,530 --> 00:07:45,430 We are setting our connection to our global connection string. 110 00:07:45,430 --> 00:07:50,680 We are disabling errors so that we can handle this opening the connection. 111 00:07:50,680 --> 00:07:52,540 We are executing the record set. 112 00:07:52,540 --> 00:07:55,090 So now the record set has all the records. 113 00:07:55,090 --> 00:07:57,730 So we've selected all of our staff members. 114 00:07:57,730 --> 00:08:06,160 And then basically as long as we have records we set a variable called DG for data grid. 115 00:08:06,160 --> 00:08:14,140 And we are setting it to the MSH flex grid control on this, on this screen that we pass in and the 116 00:08:14,140 --> 00:08:15,400 name of the control. 117 00:08:15,640 --> 00:08:23,590 And so now this DG object is referencing our data grid control on our screen. 118 00:08:23,590 --> 00:08:30,460 And then once we have records we just use a with command to set the properties of the data grid. 119 00:08:30,460 --> 00:08:36,580 So we just take the record set and throw it right into the data source property of the data grid that 120 00:08:36,580 --> 00:08:37,540 shows the records. 121 00:08:37,540 --> 00:08:43,510 We hit refresh, which ensures that it updates and here we're just setting the column widths. 122 00:08:43,510 --> 00:08:48,010 And I just manually set these based on the data we had in the staff table. 123 00:08:48,010 --> 00:08:49,450 You can make these dynamic. 124 00:08:49,480 --> 00:08:55,150 You can also set the back color in the four color and do some code to alternate if you want. 125 00:08:55,150 --> 00:09:01,150 You could get really complex with it and make it look really nice, but basically this is the code that 126 00:09:01,150 --> 00:09:02,290 populates that. 127 00:09:02,290 --> 00:09:05,500 Now this is not an active connection here. 128 00:09:05,500 --> 00:09:07,480 This is kind of a one time thing. 129 00:09:07,480 --> 00:09:12,790 So once I assign this record set to the data source, then the control renders it. 130 00:09:12,790 --> 00:09:16,570 So I can go ahead and close and get rid of that record set. 131 00:09:16,570 --> 00:09:18,640 It's not going to be retained in memory. 132 00:09:18,640 --> 00:09:21,280 So we don't have a memory leak hanging out there. 133 00:09:21,280 --> 00:09:29,680 And of course if we do run into an error, then we just print out that error and clear that error. 134 00:09:29,680 --> 00:09:36,370 So this is very similar to our build HTML, where we're sending in a table name and a file name and 135 00:09:36,370 --> 00:09:37,060 a sequel. 136 00:09:37,060 --> 00:09:41,020 But here we are just sending it control to update. 137 00:09:41,020 --> 00:09:45,850 So going back to Graphics Designer, there's a few other things that we've done here. 138 00:09:45,850 --> 00:09:50,890 So on the control itself there is an object event and it is called cell change. 139 00:09:50,890 --> 00:09:54,640 So that's any time you make a different selection. 140 00:09:54,640 --> 00:09:56,860 And we have a VB script on that. 141 00:09:56,860 --> 00:10:00,370 And basically we are updating our interface tag. 142 00:10:00,370 --> 00:10:04,960 So when you select a given row then it gets the row. 143 00:10:04,960 --> 00:10:09,370 Remember item is a pointer to whatever object the script is on. 144 00:10:09,370 --> 00:10:14,410 So since this script is on our data grid control item represents the data grid control. 145 00:10:14,410 --> 00:10:16,270 So we're just getting the current row. 146 00:10:16,270 --> 00:10:17,830 And then this is the column. 147 00:10:17,830 --> 00:10:24,940 So basically we are taking the first column value making it an integer. 148 00:10:24,940 --> 00:10:26,470 That's what cnt does. 149 00:10:26,470 --> 00:10:28,240 It casts it to an integer. 150 00:10:28,240 --> 00:10:31,210 And we're writing it into our staff selected ID. 151 00:10:31,360 --> 00:10:35,710 The reason we're casting it to an integer is because this is an integer tag. 152 00:10:35,710 --> 00:10:37,000 You have to remember this. 153 00:10:37,000 --> 00:10:40,360 The next thing is we have our staff first name. 154 00:10:40,360 --> 00:10:41,590 This is zero column. 155 00:10:41,590 --> 00:10:42,700 This is one column. 156 00:10:42,700 --> 00:10:43,600 This is two column. 157 00:10:43,600 --> 00:10:44,710 This is three column. 158 00:10:44,710 --> 00:10:49,930 So our first name is in our column number one which is the second column. 159 00:10:49,930 --> 00:10:52,900 We write that as a string. 160 00:10:52,900 --> 00:10:56,350 So we cast it to a string put it in our string tag. 161 00:10:56,350 --> 00:10:59,770 Same thing with our last name which is our third column. 162 00:10:59,770 --> 00:11:06,190 Our fourth column is a row ID and we cast that to an integer. 163 00:11:06,190 --> 00:11:14,590 So the end result of that is when we make a new selection, it fires that event, and it pulls the column 164 00:11:14,590 --> 00:11:19,840 information out of the selected row and puts it into these tags. 165 00:11:19,840 --> 00:11:21,910 And that's a pretty nice interface. 166 00:11:22,730 --> 00:11:26,690 Now we need to look at our insert and update and delete tags. 167 00:11:26,690 --> 00:11:34,580 So if we look at our insert button, this is pretty much the same as it is on our staff screen with 168 00:11:34,580 --> 00:11:35,720 the web control. 169 00:11:35,720 --> 00:11:40,100 So we are grabbing the information from our variables. 170 00:11:40,100 --> 00:11:48,110 We are building a SQL string here for our insert, and we're just using our very handy exec SQL function 171 00:11:48,110 --> 00:11:50,720 to insert that into the database. 172 00:11:50,720 --> 00:11:56,780 Then we're resetting our tags, and then we are just calling our refresh code. 173 00:11:56,780 --> 00:11:59,420 So we're rebuilding our SQL string. 174 00:11:59,420 --> 00:12:01,580 We're calling our update DB grid. 175 00:12:01,580 --> 00:12:06,440 And for the screen name I'm using item dot parent dot object name. 176 00:12:06,440 --> 00:12:14,900 Now we've done this before, but basically the item is our button that we're on and its parent is the 177 00:12:14,900 --> 00:12:16,190 screen that we're on. 178 00:12:16,190 --> 00:12:19,340 So that's going to be manage staff DB grid. 179 00:12:19,340 --> 00:12:23,120 And then the object name is the actual name of the screen. 180 00:12:23,120 --> 00:12:27,740 Then of course the name of the flex grid control that we want to update. 181 00:12:27,740 --> 00:12:29,660 And our SQL string that we built. 182 00:12:29,660 --> 00:12:31,400 So that's pretty simple. 183 00:12:32,160 --> 00:12:33,900 Same thing with update. 184 00:12:33,900 --> 00:12:40,560 If we look at update and we're using a command object with a parameterized SQL string. 185 00:12:40,560 --> 00:12:46,590 So we're filling in last name, first name role ID that we want to update, and then sent a message 186 00:12:46,590 --> 00:12:47,940 box to verify. 187 00:12:47,940 --> 00:12:53,100 And then if they hit anything other than yes then we cancel the action. 188 00:12:53,100 --> 00:13:00,120 If not, then we execute our command object and print out any errors. 189 00:13:00,120 --> 00:13:03,480 And then of course we call our update code. 190 00:13:03,480 --> 00:13:04,800 Same thing on delete. 191 00:13:04,800 --> 00:13:07,080 It should be pretty familiar to you. 192 00:13:07,110 --> 00:13:15,900 We are just creating a parameterized string, executing that and then updating our control. 193 00:13:15,900 --> 00:13:20,040 Here I will give these scripts to you as well as the update db grid function. 194 00:13:20,040 --> 00:13:21,150 And the next assignment. 195 00:13:21,150 --> 00:13:25,860 You'll be able to kind of play around with this, but you should be able to look at this and understand 196 00:13:25,860 --> 00:13:32,910 it and hopefully write this on your own using the code we've written previously as an example. 197 00:13:33,210 --> 00:13:35,670 And there's one other change I want to make. 198 00:13:35,670 --> 00:13:37,380 I'm going to give you a quick tip. 199 00:13:37,380 --> 00:13:41,310 Notice how when I click into this field, it blows everything away. 200 00:13:41,310 --> 00:13:42,840 So I have to retype everything. 201 00:13:42,840 --> 00:13:45,270 What if I just want to change a couple of letters? 202 00:13:45,270 --> 00:13:48,330 Well, that's just a property on I o fields. 203 00:13:48,330 --> 00:13:56,700 So if I select this I o field and look at output input, there is a property called clear on new input. 204 00:13:56,700 --> 00:13:58,080 And I've showed you this before. 205 00:13:58,080 --> 00:13:59,160 But if you do right click. 206 00:13:59,160 --> 00:14:00,240 And what's this? 207 00:14:00,690 --> 00:14:04,500 This HTML help tells you exactly what it does. 208 00:14:04,500 --> 00:14:08,610 And this one if you have it set to yes, it blows away the contents. 209 00:14:08,610 --> 00:14:10,110 If you say no, it doesn't. 210 00:14:10,110 --> 00:14:15,360 What I will do is set this to no and set this one also to no. 211 00:14:15,360 --> 00:14:16,530 Save this. 212 00:14:16,530 --> 00:14:24,810 And then when we go to runtime, it does not blow away everything and we can just change only the things 213 00:14:24,810 --> 00:14:25,830 that we want. 214 00:14:25,830 --> 00:14:28,290 So there's an extra tip for you. 215 00:14:29,190 --> 00:14:34,260 So now you have several ways to visualize database data on your work screens. 216 00:14:34,290 --> 00:14:42,030 Next is going to be an assignment where you will use the work web browser control, as well as the MSH 217 00:14:42,030 --> 00:14:43,770 flex DB grid control. 218 00:14:43,770 --> 00:14:45,540 See you in the next assignment.