1 00:00:04,880 --> 00:00:11,510 So now that we have all of our screens done, we have a fully realized defect tracking application. 2 00:00:11,540 --> 00:00:15,290 Operators can enter in production defects and repair. 3 00:00:15,290 --> 00:00:20,840 People can go in, put in their repair comments, put in the root cause, make updates to records. 4 00:00:20,840 --> 00:00:23,120 You have supervisors who can do the same thing. 5 00:00:23,120 --> 00:00:30,710 You can manage staff and you may want to build interfaces for your other tables like your defect reasons. 6 00:00:30,770 --> 00:00:33,380 Matter of fact, that's going to be a challenge a little later. 7 00:00:33,380 --> 00:00:38,600 The important thing is that you understand how this application works, understand how and why the scripts 8 00:00:38,600 --> 00:00:44,270 work, and then you can extend this application, or you can create your own database driven applications. 9 00:00:44,270 --> 00:00:46,790 Now here's one last thing that's very important. 10 00:00:46,790 --> 00:00:48,710 And it's about error handling. 11 00:00:48,710 --> 00:00:52,610 And we talked about the VBS error object back a few sections ago. 12 00:00:52,610 --> 00:00:58,160 And we found out that the error object just gets the last error thrown in the script. 13 00:00:58,160 --> 00:01:00,950 So with that being said, I'd like to show you something. 14 00:01:00,950 --> 00:01:06,080 So let's go to the operator screen and we are going to enter in a new production defect. 15 00:01:06,080 --> 00:01:12,860 I'll just leave all of my drop downs at default, and I'm going to add a comment and it's going to be 16 00:01:12,860 --> 00:01:20,090 with a and then I'm going to put a single quote, and I am going to pull up AP dag and I'm going to 17 00:01:20,090 --> 00:01:20,780 hit insert. 18 00:01:20,780 --> 00:01:22,700 And it looks like we have an error. 19 00:01:22,700 --> 00:01:32,420 And so a really good way to troubleshoot this is to do an HMI runtime trace on your SQL query and just 20 00:01:32,420 --> 00:01:34,040 copy it out of here. 21 00:01:34,040 --> 00:01:38,120 And then we can go paste it into SQL Server Management Studio and troubleshoot it. 22 00:01:38,120 --> 00:01:39,740 But let's take a look at what it says. 23 00:01:39,740 --> 00:01:41,030 The error it printed out. 24 00:01:41,030 --> 00:01:44,000 It says incorrect syntax near open parentheses. 25 00:01:44,000 --> 00:01:45,980 Well that doesn't really tell us a lot. 26 00:01:45,980 --> 00:01:49,310 So we'll go into SQL Server Management Studio. 27 00:01:49,310 --> 00:01:53,270 And I'm just going to go to our production database here. 28 00:01:53,270 --> 00:01:55,520 I'm going to open a new query window. 29 00:01:55,520 --> 00:01:57,800 Make sure the production database is in the dropdown. 30 00:01:57,800 --> 00:02:04,040 And I can just paste in our SQL query and hit execute and take a look at this. 31 00:02:04,040 --> 00:02:06,230 We get actually more information. 32 00:02:06,230 --> 00:02:09,530 We got incorrect syntax near open parentheses. 33 00:02:09,530 --> 00:02:15,830 But then there is another line and it says unclosed quotation mark after the character string. 34 00:02:15,830 --> 00:02:20,120 And if you look at this you can tell because all this stuff isn't read. 35 00:02:20,120 --> 00:02:24,170 So the problem is this single quote caused an issue. 36 00:02:24,170 --> 00:02:29,210 And then if I take the single quote out, I can execute it and it'll work. 37 00:02:29,210 --> 00:02:33,740 So number one, why did a single quote cause the issue. 38 00:02:33,740 --> 00:02:37,400 And it just broke up our string, so it made the value incorrect. 39 00:02:37,400 --> 00:02:43,160 But more importantly, why did we not get this information in our AP dag? 40 00:02:43,160 --> 00:02:44,780 It only gave us the first line. 41 00:02:44,780 --> 00:02:51,830 Well, the reason is that the VBS error objects only gives you the last error and it gives you the first 42 00:02:51,830 --> 00:02:52,970 line of the last error. 43 00:02:52,970 --> 00:02:59,690 But the Ayd0 errors collection gives you all of that information, and we can go back and use that in 44 00:02:59,690 --> 00:03:06,560 our script so that we have that information taken from the Ayd0 error collections object in our script. 45 00:03:06,560 --> 00:03:13,310 So if we go take a look at the Microsoft page on our Ado objects, there is an errors collection. 46 00:03:13,310 --> 00:03:16,520 And basically this is usually attached to your connection object. 47 00:03:16,520 --> 00:03:21,050 And we can loop through and print out all the errors if there's more than one. 48 00:03:21,050 --> 00:03:26,900 And that's what we're going to do in order to get all the information about our error into winrk and 49 00:03:26,900 --> 00:03:28,370 into our log file. 50 00:03:28,370 --> 00:03:32,930 So I'm going to go back to the Global Scripts editor to our SQL functions module. 51 00:03:32,930 --> 00:03:34,460 And I'm going to go to exec SQL. 52 00:03:34,460 --> 00:03:37,190 And this is where a lot of errors are being thrown. 53 00:03:37,190 --> 00:03:40,400 I'm going to go down and find where we're checking for errors. 54 00:03:40,400 --> 00:03:47,660 So if Ado gets an error then since it's in the VB script, the VBS error object will also see that error. 55 00:03:47,660 --> 00:03:49,850 So this line is going to stop it. 56 00:03:49,850 --> 00:03:55,490 And right now what we're doing is we're just printing out the VBS error, which is the last error that 57 00:03:55,490 --> 00:03:55,850 we have. 58 00:03:55,850 --> 00:03:59,090 But we've already seen it's not all the information that we want. 59 00:03:59,090 --> 00:04:03,350 And so I am going to replace this code with this code. 60 00:04:03,350 --> 00:04:05,480 And I have a debug statement up here. 61 00:04:05,480 --> 00:04:12,530 And basically what it does is it prints out the count of the errors collection for our obj connection. 62 00:04:12,530 --> 00:04:16,970 And obj connection is our Ado connection object. 63 00:04:16,970 --> 00:04:22,610 And so basically if VBS has an error at this point we're just going to assume it's an ado error because 64 00:04:22,610 --> 00:04:24,020 that's all we have going on. 65 00:04:24,020 --> 00:04:30,200 We create a variable and set it equal to a new Adodb error object. 66 00:04:30,200 --> 00:04:32,930 And then it's going to contain one or more errors. 67 00:04:32,930 --> 00:04:34,190 We want to print them all out. 68 00:04:34,190 --> 00:04:36,320 So we're just using a for each block. 69 00:04:36,320 --> 00:04:38,720 So we're calling our error E. 70 00:04:38,720 --> 00:04:40,190 So you have to do this first. 71 00:04:40,190 --> 00:04:48,740 And for each e error in object connection errors collection we're going to print out on the debug trace 72 00:04:48,740 --> 00:04:51,890 the number the source and the description. 73 00:04:51,890 --> 00:04:53,930 And then we're also going to log it. 74 00:04:53,930 --> 00:04:58,730 That way we have all the information about the error in Winneshiek in our error log. 75 00:04:58,730 --> 00:04:59,330 That way we don't have. 76 00:04:59,430 --> 00:05:06,120 Have to go over to SSMs and see if we're missing any information, and then the next is going to just 77 00:05:06,120 --> 00:05:08,760 going to cause it to loop through each error. 78 00:05:08,760 --> 00:05:12,150 Once it's done, it's going to clear the VBS error object. 79 00:05:12,150 --> 00:05:14,460 And we'll right click do a syntax check. 80 00:05:14,460 --> 00:05:17,100 Everything's okay I'm going to save that. 81 00:05:17,100 --> 00:05:23,520 Now let's go back to Graphics Designer I'm just going to restart the app and let's try this again. 82 00:05:23,520 --> 00:05:27,990 I'm going to put up AP diag and I am going to clear this. 83 00:05:27,990 --> 00:05:31,320 And we're just going to do the same thing as a. 84 00:05:31,320 --> 00:05:33,480 And I'll just put a single quote in there. 85 00:05:33,900 --> 00:05:36,510 Hit enter and then I'll do insert. 86 00:05:36,750 --> 00:05:39,180 And so it looks like we have some more information. 87 00:05:39,180 --> 00:05:43,620 And it does say unclosed quotation mark after the character string. 88 00:05:43,620 --> 00:05:49,710 That's good information because that tells us what's wrong with our SQL query. 89 00:05:49,710 --> 00:05:51,930 And we can look at our SQL query here. 90 00:05:51,930 --> 00:05:53,010 And it's got right there. 91 00:05:53,010 --> 00:05:55,200 So the next question is what do we do about it. 92 00:05:55,200 --> 00:06:01,400 Well, what we want to do is when we send a SQL string to a function, we want to make sure it doesn't 93 00:06:01,400 --> 00:06:02,810 have any single quotes in it. 94 00:06:02,810 --> 00:06:05,330 The next question is where do we fix this? 95 00:06:05,330 --> 00:06:07,190 Do we go to exec SQL? 96 00:06:07,190 --> 00:06:08,810 That seems like a good place. 97 00:06:08,810 --> 00:06:13,340 And you might think, well, we could just go back to exec SQL and do replace single quotes. 98 00:06:13,340 --> 00:06:15,320 That's kind of a bad idea. 99 00:06:15,320 --> 00:06:20,420 Let me show you why we go back to the operator screen and to the script on our insert button. 100 00:06:20,420 --> 00:06:25,970 And if you look at the SQL command we're sending, we do have valid single quotes. 101 00:06:25,970 --> 00:06:28,310 And that's what denotes a string. 102 00:06:28,310 --> 00:06:34,040 So if we just randomly replace all the single quotes it's going to get rid of these. 103 00:06:34,040 --> 00:06:35,900 And that's going to break it. 104 00:06:35,900 --> 00:06:37,310 So we don't want to do that. 105 00:06:37,310 --> 00:06:42,410 What we want to do is any string that's being sent in here, we want to clean it here. 106 00:06:42,410 --> 00:06:46,280 And so we'll go up to where we're getting the operator comments. 107 00:06:46,280 --> 00:06:56,210 And we are just going to say operator comments equals, and we're going to use the VBS replace function. 108 00:06:56,210 --> 00:07:01,940 And basically what this function does is it takes in a string variable, and it finds every instance 109 00:07:01,940 --> 00:07:07,970 of one or more characters that you specify, and it replaces it with a different instance. 110 00:07:07,970 --> 00:07:11,870 And so the string that we're looking for is going to be. 111 00:07:11,870 --> 00:07:12,440 Operator. 112 00:07:12,440 --> 00:07:13,010 Comments. 113 00:07:13,010 --> 00:07:19,190 So it's going to look in operator comments and we're going to look for the single quote character. 114 00:07:19,190 --> 00:07:21,620 And then we got to figure out what to replace it with. 115 00:07:21,650 --> 00:07:30,830 You can do double single quotes if you want that single quote in the actual field in SQL server, or 116 00:07:30,830 --> 00:07:36,590 I like to just replace it with an accent character, or you could just get rid of it and just put the 117 00:07:36,590 --> 00:07:37,700 empty string here. 118 00:07:37,700 --> 00:07:43,520 The end result here is we are cleaning this string of single quotes so it doesn't break. 119 00:07:43,520 --> 00:07:47,060 And so basically it's going to find the single quote. 120 00:07:47,060 --> 00:07:50,270 It's going to replace it with the accent character. 121 00:07:50,270 --> 00:07:52,700 It's going to set it back to the original thing. 122 00:07:52,700 --> 00:07:56,030 And so now we should have cleaned this. 123 00:07:56,030 --> 00:07:59,420 But we haven't messed with these single quotes here. 124 00:07:59,420 --> 00:08:02,750 So I am just going to click okay and hit save. 125 00:08:02,750 --> 00:08:05,360 I'll go back to my defect tracking main. 126 00:08:05,360 --> 00:08:07,010 We'll refresh runtime. 127 00:08:07,310 --> 00:08:14,330 I'm going to pull up AP diag again and then we'll just add a comment adding a and I'll put a single 128 00:08:14,330 --> 00:08:14,720 quote. 129 00:08:14,720 --> 00:08:19,760 I'll put it in parentheses just so we can see it to the comment. 130 00:08:19,760 --> 00:08:22,280 I'll hit enter and then we'll do insert. 131 00:08:22,280 --> 00:08:24,380 And it looks like it did insert it. 132 00:08:24,380 --> 00:08:27,710 And you can see that it did change it to an accent. 133 00:08:27,710 --> 00:08:29,810 So that's called cleaning a string. 134 00:08:29,810 --> 00:08:36,350 And it's a good idea to clean your strings before you send this out to a common function. 135 00:08:36,350 --> 00:08:43,100 So the big takeaway here is just remembering that the VBS error object only shows the last error. 136 00:08:43,100 --> 00:08:48,380 And if you're dealing with Ado, if you've got errors on your connection, it's a good idea to just 137 00:08:48,380 --> 00:08:52,580 loop through them and print this out so that you have all of the information. 138 00:08:52,820 --> 00:08:55,400 So hopefully you found that lesson useful. 139 00:08:55,400 --> 00:08:56,990 Coming up you'll have an assignment. 140 00:08:56,990 --> 00:09:04,610 We're going to use what you've learned to update our VB scripts with new Ado errors collection handling. 141 00:09:04,610 --> 00:09:06,440 I'll see you in the next assignment.