1 00:00:05,270 --> 00:00:11,330 Now that we know how to create VB scripts that interact with the database, we can begin creating Wehncke 2 00:00:11,330 --> 00:00:11,750 screens. 3 00:00:11,750 --> 00:00:13,850 For our defect tracking application. 4 00:00:13,850 --> 00:00:19,580 We're going to need interfaces for operators to create defect records, and for repairmen and supervisors 5 00:00:19,580 --> 00:00:22,130 to update defect records as needed. 6 00:00:22,130 --> 00:00:24,860 We will also need some interfaces to update. 7 00:00:24,860 --> 00:00:30,260 Supporting tables such as staff will require an overall view of the production defect tracking table. 8 00:00:30,260 --> 00:00:36,770 But first, we need to create some generic VBS functions that will allow us to efficiently execute SQL 9 00:00:36,770 --> 00:00:41,240 commands, visualize table data, and log script errors to a file. 10 00:00:41,240 --> 00:00:47,330 We can also streamline our VB scripts using global definitions for commonly used data, such as database 11 00:00:47,330 --> 00:00:48,290 connection strings. 12 00:00:48,290 --> 00:00:54,170 So we'll use the VBS editor to write functions that can be called from buttons and other graphic objects 13 00:00:54,170 --> 00:00:54,860 on screens. 14 00:00:54,860 --> 00:00:57,860 These are the functions we're going to cover in this lecture. 15 00:00:57,860 --> 00:01:04,670 We have a log error function that logs error information to a CSV file that can be used for troubleshooting. 16 00:01:04,670 --> 00:01:07,970 That way you don't have to have a diagnostic window up all the time. 17 00:01:07,970 --> 00:01:15,650 Then we have exec SQL, which takes in a SQL command as an argument, and it returns a success or fail 18 00:01:15,650 --> 00:01:17,900 and logs any errors to a log file. 19 00:01:17,900 --> 00:01:22,640 We're going to be using a browser component to show our database table data. 20 00:01:22,640 --> 00:01:29,240 So we have a build HTML function that we can send it a SQL query in a file name. 21 00:01:29,240 --> 00:01:36,320 And it will convert the table contents into an HTML file, which can be shown in a browser on the screen. 22 00:01:36,320 --> 00:01:44,060 And finally, we have load global definitions, and VBS allows you to create global constants and variables 23 00:01:44,060 --> 00:01:46,970 that you can use from any VB script in c, c. 24 00:01:46,970 --> 00:01:53,060 So here we have globally defined our connection string which can be used in all Edo scripts. 25 00:01:53,060 --> 00:01:58,550 This has the added convenience of being able to make any changes in one place, rather than in every 26 00:01:58,580 --> 00:02:00,920 Edo script that you have in your project. 27 00:02:00,920 --> 00:02:06,170 Before we dive into how these functions work, let's take a look at how they can be used to create a 28 00:02:06,170 --> 00:02:07,820 database driven screen. 29 00:02:07,820 --> 00:02:15,440 I'll go to runtime on our first screen here, and I can open up our Manage Staff table in a pop up. 30 00:02:15,440 --> 00:02:21,830 This object here is a browser and it is showing the HTML contents of our staff table. 31 00:02:21,830 --> 00:02:26,900 So from this interface we are able to add new operators. 32 00:02:26,900 --> 00:02:34,520 So I can just add John Smith and define his role and hit insert. 33 00:02:34,520 --> 00:02:37,190 And he's automatically added to our table. 34 00:02:37,190 --> 00:02:44,390 We can also enter a staff ID and we can either add information to update the record, or we can just 35 00:02:44,390 --> 00:02:45,350 delete the record. 36 00:02:45,350 --> 00:02:51,710 There is a verification message box that we can assure that this is the action that we want to take. 37 00:02:51,710 --> 00:02:53,300 And I'll just click yes here. 38 00:02:53,300 --> 00:02:56,120 And we've deleted the John Smith entry. 39 00:02:56,120 --> 00:02:59,150 We can also use this for updating information. 40 00:02:59,150 --> 00:03:07,220 So if I want to update the jolly Roger I can just make a change here and click update. 41 00:03:07,220 --> 00:03:09,920 We'll get the same verification message. 42 00:03:09,920 --> 00:03:10,910 I'll click yes. 43 00:03:10,910 --> 00:03:13,280 And you can see that the record is updated. 44 00:03:13,730 --> 00:03:16,910 So let's take a look at how this page is put together. 45 00:03:16,910 --> 00:03:24,020 I'll go back to Graphics Designer and open our Manage Staff web page on the picture object itself. 46 00:03:24,020 --> 00:03:25,400 On the events. 47 00:03:25,400 --> 00:03:30,080 I've created a script which is going to fill out our web browser. 48 00:03:30,080 --> 00:03:36,650 Now this web browser comes from our controls palette and it is our wink web browser control. 49 00:03:36,650 --> 00:03:38,900 I actually use the chromium version. 50 00:03:38,900 --> 00:03:42,920 The name of this web browser object is Web browser. 51 00:03:42,920 --> 00:03:48,620 When the page opens, we want to show the initial content of our staff table. 52 00:03:48,620 --> 00:03:51,470 So I've put a VB script on open picture. 53 00:03:51,470 --> 00:04:00,440 If we open this, we just create our SQL string and create a file name that we would like to put this 54 00:04:00,440 --> 00:04:01,940 information into. 55 00:04:01,940 --> 00:04:11,000 And the build HTML VB script will create this file and return the path to that file in my f name. 56 00:04:11,000 --> 00:04:18,200 And then we just take that file and put it into our web browser's address property, which will show 57 00:04:18,200 --> 00:04:19,760 the contents of the table. 58 00:04:19,760 --> 00:04:26,870 So that is basically how when we go to runtime, it automatically puts in the information in the table. 59 00:04:26,870 --> 00:04:34,520 Next we have created tags for the information that we need to either insert, update or delete. 60 00:04:34,520 --> 00:04:39,890 So we have staff first name, staff last name. 61 00:04:40,280 --> 00:04:46,400 We have our role ID and notice that I've got a dropdown list hooked up here. 62 00:04:46,880 --> 00:04:49,970 And then we also have the selected ID. 63 00:04:49,970 --> 00:04:53,990 And this is what we're going to use to update or delete information. 64 00:04:53,990 --> 00:04:55,730 That is the primary key. 65 00:04:55,730 --> 00:04:59,480 Going back to Work Explorer we can open tag. 66 00:04:59,640 --> 00:05:03,780 Management and you can see where these tags are. 67 00:05:03,810 --> 00:05:10,500 Notice I have the value column that is enabled and it shows the current value in these tags. 68 00:05:11,180 --> 00:05:15,590 Going back to graphics designer, let's take a look at the rest of the controls. 69 00:05:15,590 --> 00:05:21,530 This is a combo box object, so that's under our standard palette combo box. 70 00:05:21,530 --> 00:05:26,060 The way you configure this is under your geometry. 71 00:05:26,060 --> 00:05:28,490 You set up for the number of rows that you want. 72 00:05:28,490 --> 00:05:32,630 So we want three rows, one for each of our staff roles. 73 00:05:32,630 --> 00:05:35,390 And then under font you have an index and a text. 74 00:05:35,390 --> 00:05:39,170 So index one which is the first index is operator. 75 00:05:39,170 --> 00:05:45,470 And if I change this to index two then I can enter in the text that I want to show which is repairman 76 00:05:45,470 --> 00:05:47,660 which is our role ID two. 77 00:05:47,660 --> 00:05:51,530 And then I can change this to three and its supervisor. 78 00:05:51,530 --> 00:05:56,180 And we've tied this to the staff role ID tag. 79 00:05:56,180 --> 00:05:58,190 This is going to be our selected box. 80 00:05:58,190 --> 00:06:00,140 So it's going to be one, 2 or 3. 81 00:06:00,140 --> 00:06:08,660 And on the events tab under miscellaneous when our selected box changes when somebody makes a different 82 00:06:08,660 --> 00:06:17,420 selection here, it just takes that selected box and it puts it right into our staff role ID tag, which 83 00:06:17,420 --> 00:06:19,250 is shown by this I o field. 84 00:06:19,250 --> 00:06:25,820 And so when we're in runtime, if we choose a different role it automatically updates this. 85 00:06:25,820 --> 00:06:30,830 Notice that I have control turned off for this because we want them to use this. 86 00:06:30,830 --> 00:06:33,770 That way they can't put something incorrect in here. 87 00:06:33,770 --> 00:06:39,140 So we have the first name, last name and role ID in these tags. 88 00:06:39,440 --> 00:06:44,030 So if we go look at our insert button I'm on the events tab. 89 00:06:44,030 --> 00:06:47,450 We'll look at mouse and we have a pretty simple script. 90 00:06:47,450 --> 00:06:54,530 And so we are using our exec SQL function that we wrote which is right here. 91 00:06:55,100 --> 00:07:01,820 And so we are pulling in our tag information from the screen first name, last name and role ID. 92 00:07:01,820 --> 00:07:05,060 And we have created a SQL string. 93 00:07:05,060 --> 00:07:07,160 This is an insert statement. 94 00:07:07,160 --> 00:07:14,330 These are our columns last name first name role ID and these are the data that are in the tags. 95 00:07:14,330 --> 00:07:19,490 Notice that I am putting a single quote because these are string values. 96 00:07:19,490 --> 00:07:22,340 So you have to have that single quote in there. 97 00:07:22,790 --> 00:07:25,340 And then we can just call exec SQL. 98 00:07:25,340 --> 00:07:29,840 And we should get a success or fail coming back from that. 99 00:07:29,840 --> 00:07:32,390 And so we can tell whether or not it failed. 100 00:07:32,660 --> 00:07:37,190 The exec SQL is going to handle any errors which will go to our log file. 101 00:07:37,190 --> 00:07:41,180 Once we have updated our table we have to refresh our browser. 102 00:07:41,180 --> 00:07:44,180 I am just taking a SQL string. 103 00:07:44,180 --> 00:07:47,090 Select everything from staff ordered by ID. 104 00:07:47,660 --> 00:07:55,940 I'm calling my build HTML, which is going to build a new HTML document based on the updated staff table 105 00:07:55,940 --> 00:07:56,810 contents. 106 00:07:56,810 --> 00:08:01,070 And then we're just going to reset our tags back to blank. 107 00:08:01,070 --> 00:08:07,460 And then finally we'll refresh our browser, which will take our new file name and put it back into 108 00:08:07,460 --> 00:08:12,920 the address property of our web browser, which is showing the updated table. 109 00:08:12,920 --> 00:08:21,440 Going back, if we want to add in a new staff member, we just put in the information. 110 00:08:21,770 --> 00:08:26,750 We choose the role that we want them to have hit insert. 111 00:08:26,750 --> 00:08:32,840 It puts in the information using exec SQL and then refreshes the web browser. 112 00:08:32,840 --> 00:08:35,180 And we see our new table. 113 00:08:35,180 --> 00:08:40,280 Now if we want to make a change we have to pick our ID and put it in here. 114 00:08:40,280 --> 00:08:44,660 And so let's say that we want to update Greg Brady. 115 00:08:44,660 --> 00:08:47,090 We could either update the name or delete it. 116 00:08:47,090 --> 00:08:48,590 I'll just go in and delete it. 117 00:08:48,590 --> 00:08:50,930 And I'll just say yes to this. 118 00:08:50,930 --> 00:08:53,570 And you can see that it is removed. 119 00:08:54,020 --> 00:08:58,340 So now let's take a look at how our update and delete buttons are working. 120 00:08:58,340 --> 00:09:01,550 We'll go back to Graphics Designer on our update button. 121 00:09:01,550 --> 00:09:03,140 I'm on the events tab. 122 00:09:03,140 --> 00:09:06,080 I have a VB script on the release left event. 123 00:09:06,080 --> 00:09:07,640 I'll double click to open it. 124 00:09:07,640 --> 00:09:14,990 We're using a traditional Edo script here, and so we have our connection and command objects and a 125 00:09:14,990 --> 00:09:17,210 SQL string using parameters. 126 00:09:17,210 --> 00:09:23,630 That's why I decided to use a standard Edo script with a command object rather than exec SQL. 127 00:09:23,630 --> 00:09:29,810 Notice that I have four parameters here, and then I've created a couple of other variables that we'll 128 00:09:29,810 --> 00:09:30,560 use. 129 00:09:30,890 --> 00:09:36,860 So the first thing we're going to do is pull in whatever the user entered from the tags into variables. 130 00:09:36,860 --> 00:09:40,670 So first name last name role ID and then our selected ID. 131 00:09:40,670 --> 00:09:45,800 And I've disabled our error reporting so we can handle our own errors. 132 00:09:45,800 --> 00:09:47,930 Here's where we create our message box. 133 00:09:47,930 --> 00:09:54,050 Here is a message box function in Visual Basic scripting that allows you to present a question to an 134 00:09:54,050 --> 00:09:56,960 operator and define the icons and the buttons. 135 00:09:56,960 --> 00:10:01,730 So this variable string update info we have built here. 136 00:10:01,730 --> 00:10:07,040 Are you sure you want to update staff member and we put in the first name, last name and staff ID just 137 00:10:07,040 --> 00:10:08,210 for verification. 138 00:10:08,210 --> 00:10:11,900 And we're giving them three buttons yes, no and cancel. 139 00:10:11,900 --> 00:10:15,470 And we're showing the critical icon which is a red X. 140 00:10:15,470 --> 00:10:22,700 And finally we are putting this in the title of our message box, verify update of staff ID. 141 00:10:22,700 --> 00:10:24,470 And then we're showing the staff ID. 142 00:10:24,500 --> 00:10:30,440 If they press anything other than yes, then we just cancel out and clear our selected ID. 143 00:10:30,440 --> 00:10:37,730 If not, we're going to call our load Global deaths, which basically just makes sure our global connection 144 00:10:37,730 --> 00:10:44,720 string is initiated and we are setting our connection object connection string to that global definition. 145 00:10:44,720 --> 00:10:45,980 And we're opening it. 146 00:10:45,980 --> 00:10:48,410 And then we're just going to check for errors. 147 00:10:48,410 --> 00:10:54,530 As long as there are no errors we are going to set up our command object with our connection. 148 00:10:54,530 --> 00:10:56,780 And we're going to give it our SQL string. 149 00:10:56,780 --> 00:10:59,180 And then we have to give it the four parameters. 150 00:10:59,180 --> 00:11:02,540 So last name, first name role id and staff ID. 151 00:11:02,540 --> 00:11:04,100 And then we'll just execute. 152 00:11:04,100 --> 00:11:05,450 We'll check for errors. 153 00:11:05,450 --> 00:11:11,000 And if there are no errors we'll just print out how many rows were affected and just print out a trace 154 00:11:11,000 --> 00:11:12,890 for us to know that it was successful. 155 00:11:12,890 --> 00:11:19,310 And we'll reset our selected ID and then if there are any errors, we will print out the errors and 156 00:11:19,310 --> 00:11:20,060 clear them. 157 00:11:20,060 --> 00:11:25,460 And whether or not we have an error, we're just going to go ahead and reload our table to our browser. 158 00:11:25,460 --> 00:11:28,400 So we just put in our SQL string. 159 00:11:28,400 --> 00:11:35,990 We call our build HTML function, get our file name back, and then set our web browser address property 160 00:11:35,990 --> 00:11:38,570 back to that file name and do housekeeping. 161 00:11:38,570 --> 00:11:41,720 So this is a pretty simple script. 162 00:11:41,720 --> 00:11:47,720 And looking at our delete function it's a pretty simple SQL command. 163 00:11:47,720 --> 00:11:54,440 We are just reading in our staff ID, building a SQL string and showing a message box just like. 164 00:11:54,580 --> 00:11:58,870 We do on our update button, except this is a delete button. 165 00:11:58,870 --> 00:12:05,800 As long as they press yes, then we will use exec SQL to execute our string. 166 00:12:06,250 --> 00:12:13,180 Print out the success message and then just reload our web browser with the updated tables in the same 167 00:12:13,180 --> 00:12:14,410 fashion that we did. 168 00:12:15,010 --> 00:12:22,690 Now let's go back to our VB Script editor and take a closer look at the SQL functions module that we're 169 00:12:22,690 --> 00:12:23,920 going to create. 170 00:12:23,920 --> 00:12:31,540 Starting at the very top of this file, VBS allows you to make what we call global constants or global 171 00:12:31,540 --> 00:12:37,240 variables, and Visual Basic in CC runtime compiles as one big script. 172 00:12:37,240 --> 00:12:44,080 If we declare something in the space outside of a sub or a function, this becomes part of the header 173 00:12:44,080 --> 00:12:46,480 and then all scripts will recognize it. 174 00:12:46,480 --> 00:12:52,450 You just have to call one of the functions in this module so that it is loaded in memory. 175 00:12:52,600 --> 00:12:57,280 And so you can see here we have created a connection string. 176 00:12:57,280 --> 00:13:00,370 And that way we can have one connection string. 177 00:13:00,370 --> 00:13:03,340 Every aido script can reference this. 178 00:13:03,340 --> 00:13:08,980 And then if we move this to another computer or we decide to use a different driver, we have one place 179 00:13:08,980 --> 00:13:13,180 that we can change this and it will affect all of the scripts. 180 00:13:13,180 --> 00:13:14,290 And that is a good thing. 181 00:13:14,290 --> 00:13:17,890 And the load global deaths is just an empty function. 182 00:13:17,890 --> 00:13:25,930 But just by virtue of calling this, it ensures that our header space has been loaded into runtime memory. 183 00:13:25,930 --> 00:13:29,440 Now for log error, let's cover this first. 184 00:13:29,440 --> 00:13:35,170 And log error basically just creates a CSV file with any script errors. 185 00:13:35,170 --> 00:13:39,940 And if you think about it, you're not always going to be around to be looking at a diagnostic window 186 00:13:39,940 --> 00:13:41,680 to see if every script runs. 187 00:13:41,680 --> 00:13:47,200 And so if you come into the plant maybe the next morning and they complain that something's happened, 188 00:13:47,200 --> 00:13:54,100 you can go check this log file and tell exactly what happened and what caused the error so that you 189 00:13:54,100 --> 00:13:55,450 can go back and troubleshoot it. 190 00:13:55,450 --> 00:13:57,730 This is a VBS function called log error. 191 00:13:57,730 --> 00:14:01,840 There is a description here and we're sending in first the source. 192 00:14:01,840 --> 00:14:06,400 This is going to be the screen and object that threw this error. 193 00:14:06,400 --> 00:14:12,280 So maybe it is on your staff table and button number one or your update button. 194 00:14:12,280 --> 00:14:15,970 This tells you where to go to start looking for the error. 195 00:14:15,970 --> 00:14:19,270 And then of course all the error object information. 196 00:14:19,270 --> 00:14:23,320 So your error number, air source and error description. 197 00:14:23,890 --> 00:14:28,630 First we are using the active project path. 198 00:14:28,630 --> 00:14:32,320 And basically this is your Wink project. 199 00:14:32,320 --> 00:14:37,660 And so this is going to go to your project on the file system. 200 00:14:37,660 --> 00:14:40,750 So this is your root project directory. 201 00:14:40,750 --> 00:14:47,230 Now we have created a folder called script errors where our log file will be handled. 202 00:14:47,230 --> 00:14:52,990 Doing it this way ensures if you move the Wink project you're still going to get that file because it's 203 00:14:52,990 --> 00:14:55,390 going to move with the project. 204 00:14:55,390 --> 00:15:01,750 So using the active project path always ensures that you know exactly where this path is. 205 00:15:01,750 --> 00:15:05,470 And then our log file name I've just hard coded it. 206 00:15:05,470 --> 00:15:08,560 I've just called it script error log dot CSV. 207 00:15:08,560 --> 00:15:14,830 And we're building a full path and file name and storing it in the variable log file name. 208 00:15:14,830 --> 00:15:21,550 Now in order to read and write files in VBS, we create an instance of the scripting file system object. 209 00:15:21,550 --> 00:15:24,130 Just like we make an Ado object. 210 00:15:24,130 --> 00:15:25,420 It's basically the same thing. 211 00:15:25,420 --> 00:15:30,610 We're creating an object and we're building a string to print to our log file. 212 00:15:30,610 --> 00:15:40,300 Now is a VBS function that just returns the date and time our string source is going to be whatever 213 00:15:40,300 --> 00:15:44,020 was passed in, and we're going to see what we're sending that. 214 00:15:44,020 --> 00:15:47,410 And then of course our error number error source and error description. 215 00:15:47,410 --> 00:15:50,710 So it's just building a string to write into that file. 216 00:15:50,890 --> 00:15:54,820 We disable our error reporting so we can handle it locally. 217 00:15:54,820 --> 00:16:00,370 And we are here checking to see if the project directory script errors exist. 218 00:16:00,370 --> 00:16:02,320 And if it doesn't we create it that way. 219 00:16:02,320 --> 00:16:04,270 You don't have to make sure it's created. 220 00:16:04,690 --> 00:16:10,150 And then as long as there's no error, then it checks to see if the log file itself exists. 221 00:16:10,150 --> 00:16:15,010 Then we call the open text file method of our file system object. 222 00:16:15,010 --> 00:16:21,400 To open our log file basically means we're going to append to the end of it, and true means create 223 00:16:21,400 --> 00:16:23,710 the file if it does not exist. 224 00:16:23,710 --> 00:16:26,950 And in this case it does not exist because we checked it. 225 00:16:26,950 --> 00:16:30,250 And then it's just going to put the first line as our headers. 226 00:16:30,250 --> 00:16:35,800 If the file already exists, then of course we're just going to open the text file. 227 00:16:35,800 --> 00:16:41,500 As long as there's no error we're going to call the write line function for our file. 228 00:16:41,500 --> 00:16:44,410 And then we're just going to put our print string in it. 229 00:16:44,410 --> 00:16:48,220 And of course that is our information about the error. 230 00:16:48,220 --> 00:16:51,070 And then we're just going to make sure we close the file. 231 00:16:51,070 --> 00:16:52,330 This is really important. 232 00:16:52,330 --> 00:16:56,080 We're going to set both these objects to nothing that gets rid of the memory. 233 00:16:56,080 --> 00:16:59,290 And then of course we're checking to see if there are any errors. 234 00:16:59,290 --> 00:17:03,100 If there are errors, we're going to return to our calling function a negative one. 235 00:17:03,100 --> 00:17:07,270 That way the script can easily know there was a problem in the log file wasn't written. 236 00:17:07,270 --> 00:17:13,210 And we're going to print out the information that we found and the calling function is going to clear 237 00:17:13,210 --> 00:17:13,960 the air. 238 00:17:13,960 --> 00:17:14,380 Of course if. 239 00:17:14,550 --> 00:17:15,360 There is no error. 240 00:17:15,360 --> 00:17:18,120 We're just going to return a one for success. 241 00:17:18,120 --> 00:17:22,290 And then this is how you return a value from a function call. 242 00:17:22,290 --> 00:17:26,850 You just give it the name of the function equals and then what you want it to return. 243 00:17:26,850 --> 00:17:29,850 So let's take a look at how this is implemented. 244 00:17:29,850 --> 00:17:33,690 If we go back to my first screen we have created a button. 245 00:17:33,690 --> 00:17:35,460 And this is just a test. 246 00:17:35,460 --> 00:17:37,620 Here we are just dimming results. 247 00:17:37,620 --> 00:17:39,750 And then we're calling the log error. 248 00:17:39,750 --> 00:17:46,140 And the way we tell it where the error originated from, this item is a passed in object, and it is 249 00:17:46,140 --> 00:17:48,840 just a reference to whatever object you're on. 250 00:17:48,840 --> 00:17:54,030 So in this case item represents this button right here. 251 00:17:54,030 --> 00:17:56,820 And so the item dot parent. 252 00:17:56,820 --> 00:18:00,570 That means the screen that this object is on. 253 00:18:00,720 --> 00:18:04,320 So in this case it is going to be my first screen. 254 00:18:04,320 --> 00:18:06,660 So we know what screen we're on. 255 00:18:07,020 --> 00:18:10,890 And then the item dot object name of course is just going to be the name of the button. 256 00:18:10,890 --> 00:18:13,170 I like to put these in square brackets. 257 00:18:13,170 --> 00:18:14,310 Kind of easy to pick out. 258 00:18:14,310 --> 00:18:15,750 Makes the log file look nice. 259 00:18:15,750 --> 00:18:22,260 And then I've just put some dummy information in here for error number, air source and error description. 260 00:18:22,260 --> 00:18:25,320 We can use this script to test. 261 00:18:25,320 --> 00:18:28,320 So I will save this. 262 00:18:28,320 --> 00:18:30,300 And let's go back. 263 00:18:30,300 --> 00:18:32,850 And you can see I don't really have this file. 264 00:18:32,850 --> 00:18:36,540 But I'm just going to go ahead and even delete the script errors folder. 265 00:18:36,540 --> 00:18:39,120 And then we'll save and go to runtime. 266 00:18:39,120 --> 00:18:40,560 I'll hit log error. 267 00:18:40,590 --> 00:18:42,720 The log error function is printing out what was sent. 268 00:18:42,720 --> 00:18:44,730 This is the name of the screen. 269 00:18:44,730 --> 00:18:46,560 This is the name of the object. 270 00:18:46,560 --> 00:18:48,870 And then here's my dummy information. 271 00:18:48,870 --> 00:18:52,560 If we go back to our file system you'll see that we do have script error. 272 00:18:52,560 --> 00:18:54,630 So it did create that folder. 273 00:18:54,630 --> 00:18:58,860 And if I right click I'm just going to open this with notepad. 274 00:18:58,860 --> 00:19:02,160 You can see that it did put the header in. 275 00:19:02,160 --> 00:19:04,950 And then here is the results of the now function. 276 00:19:04,950 --> 00:19:06,150 This is the time. 277 00:19:06,150 --> 00:19:10,020 This is the source of the error and my error information. 278 00:19:10,020 --> 00:19:16,710 If I call it multiple times then of course we can go back to our notepad document. 279 00:19:16,710 --> 00:19:19,320 And you see that we get multiple lines here. 280 00:19:19,320 --> 00:19:21,000 Going back to VBS. 281 00:19:21,000 --> 00:19:23,220 Now let's take a look at exact SQL. 282 00:19:23,220 --> 00:19:27,900 So exact SQL I am just sending this a SQL command. 283 00:19:27,900 --> 00:19:33,900 And typically it's going to be inserts, updates or deletes stuff that changes the database. 284 00:19:33,900 --> 00:19:39,630 And it's either going to give you a success which is a one or a number of values affected or a negative 285 00:19:39,630 --> 00:19:40,770 one which is an error. 286 00:19:40,770 --> 00:19:47,880 And so we're just creating our Ado connection and command objects and results variable disable error 287 00:19:47,880 --> 00:19:55,260 reporting, opening our connection, checking for errors and executing our command object. 288 00:19:55,260 --> 00:20:00,030 And if there are no errors then we just print out a success message. 289 00:20:00,030 --> 00:20:06,690 If there are errors, then we put out a trace as well as call our log error function. 290 00:20:06,690 --> 00:20:11,070 Since this function is going to be the source of that, we just put execute SQL. 291 00:20:11,070 --> 00:20:13,590 Now notice that I don't have parentheses. 292 00:20:13,590 --> 00:20:20,430 And in VBS if you are not returning a value which I'm not checking the error on this, then you don't 293 00:20:20,430 --> 00:20:23,370 use parentheses when you are calling a function. 294 00:20:23,370 --> 00:20:26,040 And then we are clearing the error here. 295 00:20:26,040 --> 00:20:28,980 And then of course just doing our housekeeping. 296 00:20:28,980 --> 00:20:34,650 Now our results are going to be the number of rows affected. 297 00:20:34,650 --> 00:20:42,360 And if the results is zero meaning no rows were affected, we can probably count that as an error. 298 00:20:42,360 --> 00:20:47,880 Either our SQL script returned no rows or we ran into an error. 299 00:20:47,880 --> 00:20:51,900 In that case, we're going to have an entry in our log. 300 00:20:52,320 --> 00:21:00,300 So if we go back to Graphics Designer to our Manage Staff page, our insert button is using the execute 301 00:21:00,300 --> 00:21:01,320 SQL function. 302 00:21:01,320 --> 00:21:06,330 And so if we put in an error here we'll just click okay. 303 00:21:06,780 --> 00:21:09,150 And we'll just put a name here. 304 00:21:10,300 --> 00:21:13,570 Fair and just do an insert. 305 00:21:13,600 --> 00:21:17,230 Notice that we didn't get an additional thing here. 306 00:21:17,230 --> 00:21:25,660 So if we go to our log file, we can see that we have a new error from exec SQL, LDB provider invalid 307 00:21:25,660 --> 00:21:27,100 object name a staff. 308 00:21:27,100 --> 00:21:29,320 So that tells us about the error. 309 00:21:29,350 --> 00:21:35,320 Now in an upcoming assignment, I'm going to have you change the exact SQL function to take in a source 310 00:21:35,320 --> 00:21:36,220 parameter. 311 00:21:36,220 --> 00:21:41,110 So we can put in the page and the button that called it, which will make troubleshooting easier. 312 00:21:41,110 --> 00:21:46,510 So now let's go back to our last function which is our build HTML function. 313 00:21:46,510 --> 00:21:53,320 And the idea here is we are sending into this function a name for the table, which ends up just being 314 00:21:53,320 --> 00:21:55,060 a label at the top of the table. 315 00:21:55,060 --> 00:21:56,320 I haven't actually used it. 316 00:21:56,320 --> 00:22:03,460 I've been sending in a blank string the name of the file that we want to store this HTML code in, and 317 00:22:03,460 --> 00:22:10,150 then the SQL command, which defines the contents of the HTML table that we're building. 318 00:22:10,270 --> 00:22:19,210 Now, before I go through this script, let's go take a look at what the HTML table actually looks like. 319 00:22:19,210 --> 00:22:22,210 This file here is what is being built by this script. 320 00:22:22,210 --> 00:22:26,350 When we send it the SQL command select star from staff. 321 00:22:26,350 --> 00:22:33,010 And we can just open this in edge and you can see exactly what the HTML looks like. 322 00:22:33,010 --> 00:22:34,150 We got this header. 323 00:22:34,150 --> 00:22:35,200 It's a dark blue. 324 00:22:35,200 --> 00:22:37,210 And then our even rows are white. 325 00:22:37,210 --> 00:22:39,490 Our odd rows are blue. 326 00:22:40,210 --> 00:22:42,520 And I'll just open this with notepad. 327 00:22:42,640 --> 00:22:45,190 Now, a lot of you may be familiar with HTML. 328 00:22:45,190 --> 00:22:46,120 It's pretty simple. 329 00:22:46,120 --> 00:22:48,340 I'm going to put some links in the resources. 330 00:22:48,340 --> 00:22:56,530 We are just creating a table, and we're defining the font and how big the font is and our padding, 331 00:22:56,530 --> 00:23:00,400 what our background color and border color are going to be. 332 00:23:00,400 --> 00:23:05,110 And then for our header we're using the th tag. 333 00:23:05,110 --> 00:23:10,660 And we're just putting in our last name first name and our role ID. 334 00:23:10,660 --> 00:23:14,260 And this is being created by the Recordset object in a loop. 335 00:23:14,440 --> 00:23:15,670 And then the TD. 336 00:23:15,700 --> 00:23:17,560 This is a cell definition. 337 00:23:17,560 --> 00:23:20,050 Basically this is going to be the contents. 338 00:23:20,050 --> 00:23:25,120 So here the header is printed out and TR is row. 339 00:23:25,120 --> 00:23:27,160 And so our header is printed out. 340 00:23:27,160 --> 00:23:30,670 And then for each record we're creating a cell. 341 00:23:30,670 --> 00:23:32,290 So this is going to be your last name. 342 00:23:32,290 --> 00:23:33,610 This is going to be your first name. 343 00:23:33,610 --> 00:23:35,680 This is going to be your staff ID. 344 00:23:35,680 --> 00:23:41,920 And we are just creating an alternating color that we're putting in, which translates to kind of a 345 00:23:41,920 --> 00:23:45,070 lighter blue color and a white color. 346 00:23:45,070 --> 00:23:49,750 So let's go back to the script and take a look at how this file is being generated. 347 00:23:49,750 --> 00:23:52,300 So we create our Ado objects. 348 00:23:52,300 --> 00:23:54,940 And we're building a file just like we did in log file. 349 00:23:54,940 --> 00:23:58,090 But here we are putting it in a folder. 350 00:23:58,090 --> 00:24:01,030 If the folder doesn't exist we are creating it. 351 00:24:01,030 --> 00:24:04,030 And then this is just building HTML. 352 00:24:04,030 --> 00:24:07,120 And so here's where we're putting in our font. 353 00:24:07,120 --> 00:24:10,180 And of course you can go through and change this. 354 00:24:10,180 --> 00:24:13,780 And the table name that is passed in is put in the caption. 355 00:24:13,780 --> 00:24:16,090 And again I've just been sending it blanks. 356 00:24:16,090 --> 00:24:19,960 And we're creating a file system object so we can create a file. 357 00:24:19,960 --> 00:24:25,780 So we use our global connection string, connect to the database disable error reporting so that we 358 00:24:25,780 --> 00:24:26,770 can handle it. 359 00:24:26,770 --> 00:24:32,380 Open our connection then open our record set with the passed in string. 360 00:24:32,380 --> 00:24:34,000 As long as there's no error. 361 00:24:34,000 --> 00:24:42,310 And as long as there are records, we just go to the first record and we loop through our our fields 362 00:24:42,310 --> 00:24:44,440 and print out the information. 363 00:24:44,440 --> 00:24:47,080 So here's the name of the fields. 364 00:24:47,080 --> 00:24:52,510 So we're doing this for f indexes zero to fields count minus one. 365 00:24:52,510 --> 00:25:00,760 And we're just adding to our string HTML string the table header and what color we're going to be. 366 00:25:00,760 --> 00:25:03,130 And then the field name. 367 00:25:03,130 --> 00:25:05,530 And then we're closing out that table header. 368 00:25:05,530 --> 00:25:07,540 And we're just looping through our headers. 369 00:25:07,540 --> 00:25:10,690 And then we are closing out that row. 370 00:25:10,690 --> 00:25:13,930 And then here we are going through our records. 371 00:25:13,930 --> 00:25:16,300 We're doing something a little fun here. 372 00:25:16,300 --> 00:25:18,100 We're alternating colors. 373 00:25:18,100 --> 00:25:22,390 So if you go back and look at runtime you can see we're alternating colors here. 374 00:25:22,390 --> 00:25:24,790 That makes it a lot easier to read. 375 00:25:25,240 --> 00:25:29,380 And so the way we're doing that is for our row index. 376 00:25:29,380 --> 00:25:32,380 We're just checking it and doing modulus of two. 377 00:25:32,380 --> 00:25:37,930 And if it comes back zero that means it's even if it comes back one it means it's odd. 378 00:25:37,930 --> 00:25:43,330 So our odd rows are this hex color here which is kind of that light blue. 379 00:25:43,330 --> 00:25:48,520 If it's even then that's going to be our white color which is all colors together. 380 00:25:48,790 --> 00:25:55,810 And so for each record we are deciding which color to put in there, which is going to be our RGB color. 381 00:25:55,810 --> 00:26:01,360 And then we're putting in each of our field values. 382 00:26:01,360 --> 00:26:07,720 So we're using our loop counter f index zero to our fields count. 383 00:26:07,720 --> 00:26:10,210 We're putting the fields value this time. 384 00:26:10,210 --> 00:26:12,700 And this is printing out the contents of the table. 385 00:26:12,700 --> 00:26:16,930 And we're continually adding this to our string object. 386 00:26:16,930 --> 00:26:23,140 And we're going to do this until we are basically done with all of our records. 387 00:26:23,140 --> 00:26:30,760 And then the last thing is we close out our table and we create our file, just like we did in our log 388 00:26:30,760 --> 00:26:31,360 file. 389 00:26:31,360 --> 00:26:32,560 We throw an error. 390 00:26:32,560 --> 00:26:38,770 If there's a problem, we open our text file, we write our HTML string. 391 00:26:38,770 --> 00:26:43,120 And we're we're looking for errors on open file as well as writing to the file. 392 00:26:43,120 --> 00:26:44,380 And then we just close it. 393 00:26:44,380 --> 00:26:47,680 And then if there was an error we print that out. 394 00:26:47,680 --> 00:26:50,410 And we need to put the log file function. 395 00:26:50,410 --> 00:26:54,010 So that's going to be part of your assignment to put our log file function in here. 396 00:26:54,010 --> 00:26:58,600 And then we're just doing housekeeping on our record set and connection objects. 397 00:26:58,600 --> 00:27:03,490 And then our HTML file name which is going to be the full path. 398 00:27:03,490 --> 00:27:04,780 And the file name. 399 00:27:04,780 --> 00:27:09,520 We're returning that as a string back to whoever called this function. 400 00:27:09,520 --> 00:27:16,780 Going back to graphics designer, if we look at our buttons when we call build HTML, we're catching 401 00:27:16,780 --> 00:27:19,870 the file name so we know exactly where the file is. 402 00:27:19,870 --> 00:27:27,040 And when we take that file and assign it to the address property of our web browser, that causes the 403 00:27:27,040 --> 00:27:32,200 web browser to show that new file and update the content. 404 00:27:33,490 --> 00:27:38,800 In the next assignment, you'll create the VB script functions we covered in this video, as well as 405 00:27:38,800 --> 00:27:41,440 build and configure the staff management screen. 406 00:27:41,440 --> 00:27:46,300 If you're new to Winc, the assignment video will lead you through these steps. 407 00:27:46,300 --> 00:27:47,440 I'll see you there.