So now that we have all of our screens done, we have a fully realized defect tracking application.
Operators can enter in production defects and repair.
People can go in, put in their repair comments, put in the root cause, make updates to records.
You have supervisors who can do the same thing.
You can manage staff and you may want to build interfaces for your other tables like your defect reasons.
Matter of fact, that's going to be a challenge a little later.
The important thing is that you understand how this application works, understand how and why the scripts
work, and then you can extend this application, or you can create your own database driven applications.
Now here's one last thing that's very important.
And it's about error handling.
And we talked about the VBS error object back a few sections ago.
And we found out that the error object just gets the last error thrown in the script.
So with that being said, I'd like to show you something.
So let's go to the operator screen and we are going to enter in a new production defect.
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
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
hit insert.
And it looks like we have an error.
And so a really good way to troubleshoot this is to do an HMI runtime trace on your SQL query and just
copy it out of here.
And then we can go paste it into SQL Server Management Studio and troubleshoot it.
But let's take a look at what it says.
The error it printed out.
It says incorrect syntax near open parentheses.
Well that doesn't really tell us a lot.
So we'll go into SQL Server Management Studio.
And I'm just going to go to our production database here.
I'm going to open a new query window.
Make sure the production database is in the dropdown.
And I can just paste in our SQL query and hit execute and take a look at this.
We get actually more information.
We got incorrect syntax near open parentheses.
But then there is another line and it says unclosed quotation mark after the character string.
And if you look at this you can tell because all this stuff isn't read.
So the problem is this single quote caused an issue.
And then if I take the single quote out, I can execute it and it'll work.
So number one, why did a single quote cause the issue.
And it just broke up our string, so it made the value incorrect.
But more importantly, why did we not get this information in our AP dag?
It only gave us the first line.
Well, the reason is that the VBS error objects only gives you the last error and it gives you the first
line of the last error.
But the Ayd0 errors collection gives you all of that information, and we can go back and use that in
our script so that we have that information taken from the Ayd0 error collections object in our script.
So if we go take a look at the Microsoft page on our Ado objects, there is an errors collection.
And basically this is usually attached to your connection object.
And we can loop through and print out all the errors if there's more than one.
And that's what we're going to do in order to get all the information about our error into winrk and
into our log file.
So I'm going to go back to the Global Scripts editor to our SQL functions module.
And I'm going to go to exec SQL.
And this is where a lot of errors are being thrown.
I'm going to go down and find where we're checking for errors.
So if Ado gets an error then since it's in the VB script, the VBS error object will also see that error.
So this line is going to stop it.
And right now what we're doing is we're just printing out the VBS error, which is the last error that
we have.
But we've already seen it's not all the information that we want.
And so I am going to replace this code with this code.
And I have a debug statement up here.
And basically what it does is it prints out the count of the errors collection for our obj connection.
And obj connection is our Ado connection object.
And so basically if VBS has an error at this point we're just going to assume it's an ado error because
that's all we have going on.
We create a variable and set it equal to a new Adodb error object.
And then it's going to contain one or more errors.
We want to print them all out.
So we're just using a for each block.
So we're calling our error E.
So you have to do this first.
And for each e error in object connection errors collection we're going to print out on the debug trace
the number the source and the description.
And then we're also going to log it.
That way we have all the information about the error in Winneshiek in our error log.
That way we don't have.
Have to go over to SSMs and see if we're missing any information, and then the next is going to just
going to cause it to loop through each error.
Once it's done, it's going to clear the VBS error object.
And we'll right click do a syntax check.
Everything's okay I'm going to save that.
Now let's go back to Graphics Designer I'm just going to restart the app and let's try this again.
I'm going to put up AP diag and I am going to clear this.
And we're just going to do the same thing as a.
And I'll just put a single quote in there.
Hit enter and then I'll do insert.
And so it looks like we have some more information.
And it does say unclosed quotation mark after the character string.
That's good information because that tells us what's wrong with our SQL query.
And we can look at our SQL query here.
And it's got right there.
So the next question is what do we do about it.
Well, what we want to do is when we send a SQL string to a function, we want to make sure it doesn't
have any single quotes in it.
The next question is where do we fix this?
Do we go to exec SQL?
That seems like a good place.
And you might think, well, we could just go back to exec SQL and do replace single quotes.
That's kind of a bad idea.
Let me show you why we go back to the operator screen and to the script on our insert button.
And if you look at the SQL command we're sending, we do have valid single quotes.
And that's what denotes a string.
So if we just randomly replace all the single quotes it's going to get rid of these.
And that's going to break it.
So we don't want to do that.
What we want to do is any string that's being sent in here, we want to clean it here.
And so we'll go up to where we're getting the operator comments.
And we are just going to say operator comments equals, and we're going to use the VBS replace function.
And basically what this function does is it takes in a string variable, and it finds every instance
of one or more characters that you specify, and it replaces it with a different instance.
And so the string that we're looking for is going to be.
Operator.
Comments.
So it's going to look in operator comments and we're going to look for the single quote character.
And then we got to figure out what to replace it with.
You can do double single quotes if you want that single quote in the actual field in SQL server, or
I like to just replace it with an accent character, or you could just get rid of it and just put the
empty string here.
The end result here is we are cleaning this string of single quotes so it doesn't break.
And so basically it's going to find the single quote.
It's going to replace it with the accent character.
It's going to set it back to the original thing.
And so now we should have cleaned this.
But we haven't messed with these single quotes here.
So I am just going to click okay and hit save.
I'll go back to my defect tracking main.
We'll refresh runtime.
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
quote.
I'll put it in parentheses just so we can see it to the comment.
I'll hit enter and then we'll do insert.
And it looks like it did insert it.
And you can see that it did change it to an accent.
So that's called cleaning a string.
And it's a good idea to clean your strings before you send this out to a common function.
So the big takeaway here is just remembering that the VBS error object only shows the last error.
And if you're dealing with Ado, if you've got errors on your connection, it's a good idea to just
loop through them and print this out so that you have all of the information.
So hopefully you found that lesson useful.
Coming up you'll have an assignment.
We're going to use what you've learned to update our VB scripts with new Ado errors collection handling.
I'll see you in the next assignment.