Now that we know how to create VB scripts that interact with the database, we can begin creating Wehncke
screens.
For our defect tracking application.
We're going to need interfaces for operators to create defect records, and for repairmen and supervisors
to update defect records as needed.
We will also need some interfaces to update.
Supporting tables such as staff will require an overall view of the production defect tracking table.
But first, we need to create some generic VBS functions that will allow us to efficiently execute SQL
commands, visualize table data, and log script errors to a file.
We can also streamline our VB scripts using global definitions for commonly used data, such as database
connection strings.
So we'll use the VBS editor to write functions that can be called from buttons and other graphic objects
on screens.
These are the functions we're going to cover in this lecture.
We have a log error function that logs error information to a CSV file that can be used for troubleshooting.
That way you don't have to have a diagnostic window up all the time.
Then we have exec SQL, which takes in a SQL command as an argument, and it returns a success or fail
and logs any errors to a log file.
We're going to be using a browser component to show our database table data.
So we have a build HTML function that we can send it a SQL query in a file name.
And it will convert the table contents into an HTML file, which can be shown in a browser on the screen.
And finally, we have load global definitions, and VBS allows you to create global constants and variables
that you can use from any VB script in c, c.
So here we have globally defined our connection string which can be used in all Edo scripts.
This has the added convenience of being able to make any changes in one place, rather than in every
Edo script that you have in your project.
Before we dive into how these functions work, let's take a look at how they can be used to create a
database driven screen.
I'll go to runtime on our first screen here, and I can open up our Manage Staff table in a pop up.
This object here is a browser and it is showing the HTML contents of our staff table.
So from this interface we are able to add new operators.
So I can just add John Smith and define his role and hit insert.
And he's automatically added to our table.
We can also enter a staff ID and we can either add information to update the record, or we can just
delete the record.
There is a verification message box that we can assure that this is the action that we want to take.
And I'll just click yes here.
And we've deleted the John Smith entry.
We can also use this for updating information.
So if I want to update the jolly Roger I can just make a change here and click update.
We'll get the same verification message.
I'll click yes.
And you can see that the record is updated.
So let's take a look at how this page is put together.
I'll go back to Graphics Designer and open our Manage Staff web page on the picture object itself.
On the events.
I've created a script which is going to fill out our web browser.
Now this web browser comes from our controls palette and it is our wink web browser control.
I actually use the chromium version.
The name of this web browser object is Web browser.
When the page opens, we want to show the initial content of our staff table.
So I've put a VB script on open picture.
If we open this, we just create our SQL string and create a file name that we would like to put this
information into.
And the build HTML VB script will create this file and return the path to that file in my f name.
And then we just take that file and put it into our web browser's address property, which will show
the contents of the table.
So that is basically how when we go to runtime, it automatically puts in the information in the table.
Next we have created tags for the information that we need to either insert, update or delete.
So we have staff first name, staff last name.
We have our role ID and notice that I've got a dropdown list hooked up here.
And then we also have the selected ID.
And this is what we're going to use to update or delete information.
That is the primary key.
Going back to Work Explorer we can open tag.
Management and you can see where these tags are.
Notice I have the value column that is enabled and it shows the current value in these tags.
Going back to graphics designer, let's take a look at the rest of the controls.
This is a combo box object, so that's under our standard palette combo box.
The way you configure this is under your geometry.
You set up for the number of rows that you want.
So we want three rows, one for each of our staff roles.
And then under font you have an index and a text.
So index one which is the first index is operator.
And if I change this to index two then I can enter in the text that I want to show which is repairman
which is our role ID two.
And then I can change this to three and its supervisor.
And we've tied this to the staff role ID tag.
This is going to be our selected box.
So it's going to be one, 2 or 3.
And on the events tab under miscellaneous when our selected box changes when somebody makes a different
selection here, it just takes that selected box and it puts it right into our staff role ID tag, which
is shown by this I o field.
And so when we're in runtime, if we choose a different role it automatically updates this.
Notice that I have control turned off for this because we want them to use this.
That way they can't put something incorrect in here.
So we have the first name, last name and role ID in these tags.
So if we go look at our insert button I'm on the events tab.
We'll look at mouse and we have a pretty simple script.
And so we are using our exec SQL function that we wrote which is right here.
And so we are pulling in our tag information from the screen first name, last name and role ID.
And we have created a SQL string.
This is an insert statement.
These are our columns last name first name role ID and these are the data that are in the tags.
Notice that I am putting a single quote because these are string values.
So you have to have that single quote in there.
And then we can just call exec SQL.
And we should get a success or fail coming back from that.
And so we can tell whether or not it failed.
The exec SQL is going to handle any errors which will go to our log file.
Once we have updated our table we have to refresh our browser.
I am just taking a SQL string.
Select everything from staff ordered by ID.
I'm calling my build HTML, which is going to build a new HTML document based on the updated staff table
contents.
And then we're just going to reset our tags back to blank.
And then finally we'll refresh our browser, which will take our new file name and put it back into
the address property of our web browser, which is showing the updated table.
Going back, if we want to add in a new staff member, we just put in the information.
We choose the role that we want them to have hit insert.
It puts in the information using exec SQL and then refreshes the web browser.
And we see our new table.
Now if we want to make a change we have to pick our ID and put it in here.
And so let's say that we want to update Greg Brady.
We could either update the name or delete it.
I'll just go in and delete it.
And I'll just say yes to this.
And you can see that it is removed.
So now let's take a look at how our update and delete buttons are working.
We'll go back to Graphics Designer on our update button.
I'm on the events tab.
I have a VB script on the release left event.
I'll double click to open it.
We're using a traditional Edo script here, and so we have our connection and command objects and a
SQL string using parameters.
That's why I decided to use a standard Edo script with a command object rather than exec SQL.
Notice that I have four parameters here, and then I've created a couple of other variables that we'll
use.
So the first thing we're going to do is pull in whatever the user entered from the tags into variables.
So first name last name role ID and then our selected ID.
And I've disabled our error reporting so we can handle our own errors.
Here's where we create our message box.
Here is a message box function in Visual Basic scripting that allows you to present a question to an
operator and define the icons and the buttons.
So this variable string update info we have built here.
Are you sure you want to update staff member and we put in the first name, last name and staff ID just
for verification.
And we're giving them three buttons yes, no and cancel.
And we're showing the critical icon which is a red X.
And finally we are putting this in the title of our message box, verify update of staff ID.
And then we're showing the staff ID.
If they press anything other than yes, then we just cancel out and clear our selected ID.
If not, we're going to call our load Global deaths, which basically just makes sure our global connection
string is initiated and we are setting our connection object connection string to that global definition.
And we're opening it.
And then we're just going to check for errors.
As long as there are no errors we are going to set up our command object with our connection.
And we're going to give it our SQL string.
And then we have to give it the four parameters.
So last name, first name role id and staff ID.
And then we'll just execute.
We'll check for errors.
And if there are no errors we'll just print out how many rows were affected and just print out a trace
for us to know that it was successful.
And we'll reset our selected ID and then if there are any errors, we will print out the errors and
clear them.
And whether or not we have an error, we're just going to go ahead and reload our table to our browser.
So we just put in our SQL string.
We call our build HTML function, get our file name back, and then set our web browser address property
back to that file name and do housekeeping.
So this is a pretty simple script.
And looking at our delete function it's a pretty simple SQL command.
We are just reading in our staff ID, building a SQL string and showing a message box just like.
We do on our update button, except this is a delete button.
As long as they press yes, then we will use exec SQL to execute our string.
Print out the success message and then just reload our web browser with the updated tables in the same
fashion that we did.
Now let's go back to our VB Script editor and take a closer look at the SQL functions module that we're
going to create.
Starting at the very top of this file, VBS allows you to make what we call global constants or global
variables, and Visual Basic in CC runtime compiles as one big script.
If we declare something in the space outside of a sub or a function, this becomes part of the header
and then all scripts will recognize it.
You just have to call one of the functions in this module so that it is loaded in memory.
And so you can see here we have created a connection string.
And that way we can have one connection string.
Every aido script can reference this.
And then if we move this to another computer or we decide to use a different driver, we have one place
that we can change this and it will affect all of the scripts.
And that is a good thing.
And the load global deaths is just an empty function.
But just by virtue of calling this, it ensures that our header space has been loaded into runtime memory.
Now for log error, let's cover this first.
And log error basically just creates a CSV file with any script errors.
And if you think about it, you're not always going to be around to be looking at a diagnostic window
to see if every script runs.
And so if you come into the plant maybe the next morning and they complain that something's happened,
you can go check this log file and tell exactly what happened and what caused the error so that you
can go back and troubleshoot it.
This is a VBS function called log error.
There is a description here and we're sending in first the source.
This is going to be the screen and object that threw this error.
So maybe it is on your staff table and button number one or your update button.
This tells you where to go to start looking for the error.
And then of course all the error object information.
So your error number, air source and error description.
First we are using the active project path.
And basically this is your Wink project.
And so this is going to go to your project on the file system.
So this is your root project directory.
Now we have created a folder called script errors where our log file will be handled.
Doing it this way ensures if you move the Wink project you're still going to get that file because it's
going to move with the project.
So using the active project path always ensures that you know exactly where this path is.
And then our log file name I've just hard coded it.
I've just called it script error log dot CSV.
And we're building a full path and file name and storing it in the variable log file name.
Now in order to read and write files in VBS, we create an instance of the scripting file system object.
Just like we make an Ado object.
It's basically the same thing.
We're creating an object and we're building a string to print to our log file.
Now is a VBS function that just returns the date and time our string source is going to be whatever
was passed in, and we're going to see what we're sending that.
And then of course our error number error source and error description.
So it's just building a string to write into that file.
We disable our error reporting so we can handle it locally.
And we are here checking to see if the project directory script errors exist.
And if it doesn't we create it that way.
You don't have to make sure it's created.
And then as long as there's no error, then it checks to see if the log file itself exists.
Then we call the open text file method of our file system object.
To open our log file basically means we're going to append to the end of it, and true means create
the file if it does not exist.
And in this case it does not exist because we checked it.
And then it's just going to put the first line as our headers.
If the file already exists, then of course we're just going to open the text file.
As long as there's no error we're going to call the write line function for our file.
And then we're just going to put our print string in it.
And of course that is our information about the error.
And then we're just going to make sure we close the file.
This is really important.
We're going to set both these objects to nothing that gets rid of the memory.
And then of course we're checking to see if there are any errors.
If there are errors, we're going to return to our calling function a negative one.
That way the script can easily know there was a problem in the log file wasn't written.
And we're going to print out the information that we found and the calling function is going to clear
the air.
Of course if.
There is no error.
We're just going to return a one for success.
And then this is how you return a value from a function call.
You just give it the name of the function equals and then what you want it to return.
So let's take a look at how this is implemented.
If we go back to my first screen we have created a button.
And this is just a test.
Here we are just dimming results.
And then we're calling the log error.
And the way we tell it where the error originated from, this item is a passed in object, and it is
just a reference to whatever object you're on.
So in this case item represents this button right here.
And so the item dot parent.
That means the screen that this object is on.
So in this case it is going to be my first screen.
So we know what screen we're on.
And then the item dot object name of course is just going to be the name of the button.
I like to put these in square brackets.
Kind of easy to pick out.
Makes the log file look nice.
And then I've just put some dummy information in here for error number, air source and error description.
We can use this script to test.
So I will save this.
And let's go back.
And you can see I don't really have this file.
But I'm just going to go ahead and even delete the script errors folder.
And then we'll save and go to runtime.
I'll hit log error.
The log error function is printing out what was sent.
This is the name of the screen.
This is the name of the object.
And then here's my dummy information.
If we go back to our file system you'll see that we do have script error.
So it did create that folder.
And if I right click I'm just going to open this with notepad.
You can see that it did put the header in.
And then here is the results of the now function.
This is the time.
This is the source of the error and my error information.
If I call it multiple times then of course we can go back to our notepad document.
And you see that we get multiple lines here.
Going back to VBS.
Now let's take a look at exact SQL.
So exact SQL I am just sending this a SQL command.
And typically it's going to be inserts, updates or deletes stuff that changes the database.
And it's either going to give you a success which is a one or a number of values affected or a negative
one which is an error.
And so we're just creating our Ado connection and command objects and results variable disable error
reporting, opening our connection, checking for errors and executing our command object.
And if there are no errors then we just print out a success message.
If there are errors, then we put out a trace as well as call our log error function.
Since this function is going to be the source of that, we just put execute SQL.
Now notice that I don't have parentheses.
And in VBS if you are not returning a value which I'm not checking the error on this, then you don't
use parentheses when you are calling a function.
And then we are clearing the error here.
And then of course just doing our housekeeping.
Now our results are going to be the number of rows affected.
And if the results is zero meaning no rows were affected, we can probably count that as an error.
Either our SQL script returned no rows or we ran into an error.
In that case, we're going to have an entry in our log.
So if we go back to Graphics Designer to our Manage Staff page, our insert button is using the execute
SQL function.
And so if we put in an error here we'll just click okay.
And we'll just put a name here.
Fair and just do an insert.
Notice that we didn't get an additional thing here.
So if we go to our log file, we can see that we have a new error from exec SQL, LDB provider invalid
object name a staff.
So that tells us about the error.
Now in an upcoming assignment, I'm going to have you change the exact SQL function to take in a source
parameter.
So we can put in the page and the button that called it, which will make troubleshooting easier.
So now let's go back to our last function which is our build HTML function.
And the idea here is we are sending into this function a name for the table, which ends up just being
a label at the top of the table.
I haven't actually used it.
I've been sending in a blank string the name of the file that we want to store this HTML code in, and
then the SQL command, which defines the contents of the HTML table that we're building.
Now, before I go through this script, let's go take a look at what the HTML table actually looks like.
This file here is what is being built by this script.
When we send it the SQL command select star from staff.
And we can just open this in edge and you can see exactly what the HTML looks like.
We got this header.
It's a dark blue.
And then our even rows are white.
Our odd rows are blue.
And I'll just open this with notepad.
Now, a lot of you may be familiar with HTML.
It's pretty simple.
I'm going to put some links in the resources.
We are just creating a table, and we're defining the font and how big the font is and our padding,
what our background color and border color are going to be.
And then for our header we're using the th tag.
And we're just putting in our last name first name and our role ID.
And this is being created by the Recordset object in a loop.
And then the TD.
This is a cell definition.
Basically this is going to be the contents.
So here the header is printed out and TR is row.
And so our header is printed out.
And then for each record we're creating a cell.
So this is going to be your last name.
This is going to be your first name.
This is going to be your staff ID.
And we are just creating an alternating color that we're putting in, which translates to kind of a
lighter blue color and a white color.
So let's go back to the script and take a look at how this file is being generated.
So we create our Ado objects.
And we're building a file just like we did in log file.
But here we are putting it in a folder.
If the folder doesn't exist we are creating it.
And then this is just building HTML.
And so here's where we're putting in our font.
And of course you can go through and change this.
And the table name that is passed in is put in the caption.
And again I've just been sending it blanks.
And we're creating a file system object so we can create a file.
So we use our global connection string, connect to the database disable error reporting so that we
can handle it.
Open our connection then open our record set with the passed in string.
As long as there's no error.
And as long as there are records, we just go to the first record and we loop through our our fields
and print out the information.
So here's the name of the fields.
So we're doing this for f indexes zero to fields count minus one.
And we're just adding to our string HTML string the table header and what color we're going to be.
And then the field name.
And then we're closing out that table header.
And we're just looping through our headers.
And then we are closing out that row.
And then here we are going through our records.
We're doing something a little fun here.
We're alternating colors.
So if you go back and look at runtime you can see we're alternating colors here.
That makes it a lot easier to read.
And so the way we're doing that is for our row index.
We're just checking it and doing modulus of two.
And if it comes back zero that means it's even if it comes back one it means it's odd.
So our odd rows are this hex color here which is kind of that light blue.
If it's even then that's going to be our white color which is all colors together.
And so for each record we are deciding which color to put in there, which is going to be our RGB color.
And then we're putting in each of our field values.
So we're using our loop counter f index zero to our fields count.
We're putting the fields value this time.
And this is printing out the contents of the table.
And we're continually adding this to our string object.
And we're going to do this until we are basically done with all of our records.
And then the last thing is we close out our table and we create our file, just like we did in our log
file.
We throw an error.
If there's a problem, we open our text file, we write our HTML string.
And we're we're looking for errors on open file as well as writing to the file.
And then we just close it.
And then if there was an error we print that out.
And we need to put the log file function.
So that's going to be part of your assignment to put our log file function in here.
And then we're just doing housekeeping on our record set and connection objects.
And then our HTML file name which is going to be the full path.
And the file name.
We're returning that as a string back to whoever called this function.
Going back to graphics designer, if we look at our buttons when we call build HTML, we're catching
the file name so we know exactly where the file is.
And when we take that file and assign it to the address property of our web browser, that causes the
web browser to show that new file and update the content.
In the next assignment, you'll create the VB script functions we covered in this video, as well as
build and configure the staff management screen.
If you're new to Winc, the assignment video will lead you through these steps.
I'll see you there.