Now let's take a look at the Ado command object and what it can be used for.
I have a button here with a script, so I'm just going to right click and open this VBS script.
And we'll take a quick run through to look at the script and then go into runtime, watch it run, and
then we'll come back for a more detailed look.
So in this area we're creating our Ado objects.
We've created a connection and a command, also a SQL query.
But if we take a look at this query this is an Insert query.
And this is our column's last name, first name and role ID.
But take a look at our values.
These are all question marks.
These are called parameters.
And we can fill these values in on the fly using VB script variables.
Or if this were a function we could just pass them in.
This makes it very easy to make generic functions to insert, delete or update items in the database.
We have a results variable which will tell us how many records were affected and just a connection string.
And then I've created some variables to temporarily hold the record.
We're going to be inserted.
So first name, last name and role ID.
And we have put in some error detection.
And so we open the connection and we assign our SQL string the command text.
And then here we are adding the parameters.
And these parameters are going to be filling in these areas here in order.
So the first one we are going to call the parameter last name.
And you can see here these are the arguments for this call.
And we're going to be going over these in depth.
But it is name data type and then direction either in or out the size and then the value.
So the name of this parameter is last name.
It is data type 200 which ends up being a varchar.
And I'll show you later on where we get that.
And then our direction.
This is an input parameter and then our size.
Since this is the last name, if we go back and look at our last name field as well as our first name
field, it's an Varchar 50.
So it can take up to 50 characters.
And then our El name comes from our El name variable.
So this is going to get Richards here with our first name.
It's going to get the first name which is Keith.
And then with our role ID this is data type integer.
It is an input parameter.
It is two bytes and it is the role id.
And so three is going to be a supervisor.
And so we call the execute function of our command object.
It fills in all the parameters.
And then it gets the number of rows that were affected by the command and puts it into our variable
res.
And then we can use this.
And then of course if there are no errors we print out the number of rows affected and a success statement.
Finally, after the loop we check for any errors and if the error exists, we print out information
on the error and just call the clear method and do housekeeping.
So let's see if it runs.
We'll click okay, save and go to runtime.
And we'll just hit the button and we get a good printout.
One row affected Keith Richards added to staff table.
If we go back and look at our staff table.
And I just execute.
You can see that we did in fact add Keith Richards to our table.
Now let's go back and take a closer look at our parameters.
So I'll open the script back up.
And let's look at these passed in for our parameter.
Now in your class resources we have a command object parameter cheat sheet.
This comes from various Microsoft sources.
The syntax is you have a command object dot parameters dot append.
So you're appending a new parameter.
And then you're using the command object to create a parameter.
And you're giving it the name data type, direction size and value.
And so we give you an example which is just like what we have in our script.
So for the parameters here this table tells you what you need.
So you can name the parameter or not.
We don't really use it.
So we might get away with not using it here.
The data type.
Now this data type is where you're telling the parameter what data type it is.
And that is coming from the data type enum list of values I haven't listed every single one here.
A lot of these we won't talk about, but I did give you a link to the full definition of these.
And so we did use an integer that is three.
We also used a varchar that is called add varchar.
And this is your variable term string.
If we wanted to use a date we could use 133 and so forth and so on.
And then of course our direction.
This argument comes from the parameter direction enum values, and so it's zero through four.
And the first three I've never used unknown.
That's zero.
But if you are inputting data into a function, into a SQL or a stored procedure, it's going to be
one.
That's usually what we're going to be using.
If you are calling a function or expecting a return value, then you could define it as two.
And then you could also have a parameter be both an input output value.
And this is really only for stored procedures and a return value.
Again this is for stored procedures.
So primarily we're going to be using one for our values.
And so that is where these arguments come from.
And then, of course, we are pulling these directly from variables that we create.
We can also use command objects with a record set.
And we do it a little bit differently.
So we set up everything here.
And again on our SQL command we use one or more question marks to define incoming parameters.
So here we're selecting all fields from customers table.
We're in the battery code database where the state ID equals.
And then whatever we put in.
And so we've created our command object.
And here we're filling in CA as our state ID.
So we only have one parameter.
And we're printing out the columns like we did in our earlier script.
But instead of looking at record set we are doing a while loop.
While not object record set EOF.
EOF is a constant.
It stands for end of file.
So we're basically saying while you're not at the end, keep looping, and then we're looping through
each of the fields and printing out the value of the field, and then going to the next column or field.
And then we're moving to the next record.
And so this loop will run until we hit EOF and it'll pop out.
And of course we will check for any errors and print those out.
And so we have hard coded our state to CA which is California.
And let's take a look at how that works.
We'll go to runtime.
We'll hit select.
And here is our list of column names.
And you can see all of our records are in the state of California.
Now, we could make these a little more interactive by taking in our parameters from internal tags.
And so let's do a quick example where we would do that.
First I'll go back to graphics designer.
And let's take a look here where we're inserting into our staff table.
So we have last name first name role ID.
And so we need a varchar to hold first name a varchar or string to hold last name and an integer to
hold our role ID.
So I am going to Link Explorer.
And in tag management I have got a group here called Script Tags.
This is where I did my divisor tag in the error module.
And let's just add some tags for our staff table.
So let's say this is called staff last name.
And our data type here is going to be a string.
So we'll do text 16.
And then I can highlight that and do a pull down and just change that to first name, get rid of this
and then we want staff role ID and this is going to be an integer.
So I'll just make it unsigned eight bit.
So now I have some internal tags that we can use to get user input.
Next we'll go back to Graphics Designer.
Go to our tag browser and under our script tags we have these tags here.
So I can just drag these up.
There's last name.
There's first name and there's role ID.
And so let's just format these.
So I'm going to make these big enough so that we can see them.
And I will add some labels.
So that will just be last name.
And we'll.
Copy the properties.
Paste the properties in.
We'll duplicate this here is that to first name.
So these are just labels.
And then we will duplicate this again.
And we'll do role ID.
And just for aesthetics we'll make those blue.
We'll go to our properties.
We will turn off our global color scheme.
Make our font.
Now, these are empty strings, so they're not really showing anything in there right now.
But we'll save these.
And then if we go here, what we want to do is instead of hard coding these, we want to pull these
from tags.
So what we'll do is I'll just right click and do my runtime dot.
And this is a list of all tags.
And what I want to do is use my tag browser.
And this is the first name.
And then I can just do dot read.
And so this will read that tag and throw it into this variable.
Now I'm just going to copy this whole thing again.
And this is going to be our last name.
So there's my last name.
And then finally.
This is going to be our role ID so now this script will still work, but it's going to take input from
our tags.
That gives us a kind of crude user interface.
So let's test it out I'll click okay save go to runtime.
And our last name is Jenkins I'll hit enter and first name is Leroy.
And Leroy is a repairman and I can hit insert.
So it does print out that it did add to the table.
Let's go back and test our error handling.
So I'll just put in Smith Janet and let's put in role ID that doesn't exist.
So something like seven we hit insert.
And so it says basically there is no role.
That is role seven.
So that works.
If we go back and put in something valid then Janet Smith has been added to the table.
If we go back to SQL server and hit execute again there are our new staff members.
So the command object is set up to be able to take parameters very easily from user interfaces, which
is what we want to do.
It also returns the number of rows affected by your SQL query, which can be really useful in making
sure that the statement ran properly and for error checking.
So the Ado command object is great when you're inserting, updating, or deleting data from the database.
In the next assignment, you're going to get some practice using the Ado command objects with parameters,
and begin building some Wink interfaces to interact with the database.