1
00:00:05,090 --> 00:00:10,280
Now let's take a look at the Ado command object and what it can be used for.

2
00:00:10,310 --> 00:00:16,130
I have a button here with a script, so I'm just going to right click and open this VBS script.

3
00:00:16,490 --> 00:00:22,250
And we'll take a quick run through to look at the script and then go into runtime, watch it run, and

4
00:00:22,250 --> 00:00:24,890
then we'll come back for a more detailed look.

5
00:00:24,890 --> 00:00:28,220
So in this area we're creating our Ado objects.

6
00:00:28,220 --> 00:00:34,460
We've created a connection and a command, also a SQL query.

7
00:00:34,460 --> 00:00:38,780
But if we take a look at this query this is an Insert query.

8
00:00:38,780 --> 00:00:41,870
And this is our column's last name, first name and role ID.

9
00:00:41,870 --> 00:00:43,580
But take a look at our values.

10
00:00:43,580 --> 00:00:45,710
These are all question marks.

11
00:00:45,710 --> 00:00:47,960
These are called parameters.

12
00:00:47,960 --> 00:00:53,660
And we can fill these values in on the fly using VB script variables.

13
00:00:53,660 --> 00:00:57,050
Or if this were a function we could just pass them in.

14
00:00:57,050 --> 00:01:05,120
This makes it very easy to make generic functions to insert, delete or update items in the database.

15
00:01:05,600 --> 00:01:12,230
We have a results variable which will tell us how many records were affected and just a connection string.

16
00:01:12,230 --> 00:01:16,220
And then I've created some variables to temporarily hold the record.

17
00:01:16,220 --> 00:01:17,540
We're going to be inserted.

18
00:01:17,540 --> 00:01:20,030
So first name, last name and role ID.

19
00:01:20,030 --> 00:01:23,750
And we have put in some error detection.

20
00:01:23,750 --> 00:01:29,570
And so we open the connection and we assign our SQL string the command text.

21
00:01:29,570 --> 00:01:32,360
And then here we are adding the parameters.

22
00:01:32,360 --> 00:01:37,850
And these parameters are going to be filling in these areas here in order.

23
00:01:37,850 --> 00:01:42,140
So the first one we are going to call the parameter last name.

24
00:01:42,140 --> 00:01:47,360
And you can see here these are the arguments for this call.

25
00:01:47,360 --> 00:01:49,610
And we're going to be going over these in depth.

26
00:01:49,610 --> 00:01:56,600
But it is name data type and then direction either in or out the size and then the value.

27
00:01:56,600 --> 00:01:59,780
So the name of this parameter is last name.

28
00:01:59,780 --> 00:02:03,920
It is data type 200 which ends up being a varchar.

29
00:02:03,920 --> 00:02:06,680
And I'll show you later on where we get that.

30
00:02:06,680 --> 00:02:08,960
And then our direction.

31
00:02:08,960 --> 00:02:12,980
This is an input parameter and then our size.

32
00:02:12,980 --> 00:02:19,700
Since this is the last name, if we go back and look at our last name field as well as our first name

33
00:02:19,700 --> 00:02:21,980
field, it's an Varchar 50.

34
00:02:21,980 --> 00:02:25,220
So it can take up to 50 characters.

35
00:02:25,220 --> 00:02:30,230
And then our El name comes from our El name variable.

36
00:02:30,230 --> 00:02:33,860
So this is going to get Richards here with our first name.

37
00:02:33,860 --> 00:02:36,440
It's going to get the first name which is Keith.

38
00:02:36,440 --> 00:02:40,550
And then with our role ID this is data type integer.

39
00:02:40,550 --> 00:02:42,290
It is an input parameter.

40
00:02:42,290 --> 00:02:47,090
It is two bytes and it is the role id.

41
00:02:47,090 --> 00:02:50,000
And so three is going to be a supervisor.

42
00:02:50,000 --> 00:02:54,410
And so we call the execute function of our command object.

43
00:02:54,410 --> 00:02:56,630
It fills in all the parameters.

44
00:02:56,630 --> 00:03:03,470
And then it gets the number of rows that were affected by the command and puts it into our variable

45
00:03:03,470 --> 00:03:03,980
res.

46
00:03:03,980 --> 00:03:05,720
And then we can use this.

47
00:03:05,720 --> 00:03:13,370
And then of course if there are no errors we print out the number of rows affected and a success statement.

48
00:03:13,370 --> 00:03:20,390
Finally, after the loop we check for any errors and if the error exists, we print out information

49
00:03:20,390 --> 00:03:24,890
on the error and just call the clear method and do housekeeping.

50
00:03:24,890 --> 00:03:26,750
So let's see if it runs.

51
00:03:26,750 --> 00:03:30,440
We'll click okay, save and go to runtime.

52
00:03:30,440 --> 00:03:33,710
And we'll just hit the button and we get a good printout.

53
00:03:33,710 --> 00:03:37,130
One row affected Keith Richards added to staff table.

54
00:03:37,400 --> 00:03:40,670
If we go back and look at our staff table.

55
00:03:41,380 --> 00:03:42,820
And I just execute.

56
00:03:42,820 --> 00:03:48,130
You can see that we did in fact add Keith Richards to our table.

57
00:03:49,160 --> 00:03:53,420
Now let's go back and take a closer look at our parameters.

58
00:03:53,780 --> 00:03:56,000
So I'll open the script back up.

59
00:03:57,010 --> 00:04:02,320
And let's look at these passed in for our parameter.

60
00:04:02,350 --> 00:04:08,470
Now in your class resources we have a command object parameter cheat sheet.

61
00:04:08,470 --> 00:04:11,350
This comes from various Microsoft sources.

62
00:04:11,350 --> 00:04:16,510
The syntax is you have a command object dot parameters dot append.

63
00:04:16,510 --> 00:04:18,610
So you're appending a new parameter.

64
00:04:18,610 --> 00:04:22,510
And then you're using the command object to create a parameter.

65
00:04:22,510 --> 00:04:26,500
And you're giving it the name data type, direction size and value.

66
00:04:26,770 --> 00:04:31,300
And so we give you an example which is just like what we have in our script.

67
00:04:31,300 --> 00:04:37,840
So for the parameters here this table tells you what you need.

68
00:04:37,840 --> 00:04:40,900
So you can name the parameter or not.

69
00:04:40,900 --> 00:04:41,860
We don't really use it.

70
00:04:41,860 --> 00:04:44,590
So we might get away with not using it here.

71
00:04:44,590 --> 00:04:46,240
The data type.

72
00:04:46,270 --> 00:04:50,830
Now this data type is where you're telling the parameter what data type it is.

73
00:04:50,830 --> 00:04:57,220
And that is coming from the data type enum list of values I haven't listed every single one here.

74
00:04:57,220 --> 00:05:03,280
A lot of these we won't talk about, but I did give you a link to the full definition of these.

75
00:05:03,280 --> 00:05:07,120
And so we did use an integer that is three.

76
00:05:07,150 --> 00:05:12,340
We also used a varchar that is called add varchar.

77
00:05:12,340 --> 00:05:14,950
And this is your variable term string.

78
00:05:14,950 --> 00:05:19,570
If we wanted to use a date we could use 133 and so forth and so on.

79
00:05:19,570 --> 00:05:23,320
And then of course our direction.

80
00:05:24,270 --> 00:05:33,030
This argument comes from the parameter direction enum values, and so it's zero through four.

81
00:05:33,030 --> 00:05:36,600
And the first three I've never used unknown.

82
00:05:36,600 --> 00:05:37,920
That's zero.

83
00:05:37,920 --> 00:05:43,920
But if you are inputting data into a function, into a SQL or a stored procedure, it's going to be

84
00:05:43,920 --> 00:05:44,250
one.

85
00:05:44,250 --> 00:05:46,680
That's usually what we're going to be using.

86
00:05:46,680 --> 00:05:53,880
If you are calling a function or expecting a return value, then you could define it as two.

87
00:05:54,150 --> 00:05:58,710
And then you could also have a parameter be both an input output value.

88
00:05:58,710 --> 00:06:03,780
And this is really only for stored procedures and a return value.

89
00:06:03,780 --> 00:06:05,790
Again this is for stored procedures.

90
00:06:05,790 --> 00:06:11,610
So primarily we're going to be using one for our values.

91
00:06:11,610 --> 00:06:15,180
And so that is where these arguments come from.

92
00:06:15,800 --> 00:06:21,140
And then, of course, we are pulling these directly from variables that we create.

93
00:06:21,170 --> 00:06:25,220
We can also use command objects with a record set.

94
00:06:25,220 --> 00:06:27,320
And we do it a little bit differently.

95
00:06:27,320 --> 00:06:30,080
So we set up everything here.

96
00:06:30,080 --> 00:06:37,100
And again on our SQL command we use one or more question marks to define incoming parameters.

97
00:06:37,100 --> 00:06:41,690
So here we're selecting all fields from customers table.

98
00:06:41,690 --> 00:06:45,890
We're in the battery code database where the state ID equals.

99
00:06:45,890 --> 00:06:47,900
And then whatever we put in.

100
00:06:48,230 --> 00:06:51,560
And so we've created our command object.

101
00:06:51,560 --> 00:06:56,990
And here we're filling in CA as our state ID.

102
00:06:57,110 --> 00:06:59,090
So we only have one parameter.

103
00:06:59,090 --> 00:07:03,200
And we're printing out the columns like we did in our earlier script.

104
00:07:03,200 --> 00:07:08,510
But instead of looking at record set we are doing a while loop.

105
00:07:08,510 --> 00:07:12,860
While not object record set EOF.

106
00:07:12,890 --> 00:07:14,330
EOF is a constant.

107
00:07:14,330 --> 00:07:15,620
It stands for end of file.

108
00:07:15,620 --> 00:07:22,310
So we're basically saying while you're not at the end, keep looping, and then we're looping through

109
00:07:22,310 --> 00:07:29,390
each of the fields and printing out the value of the field, and then going to the next column or field.

110
00:07:29,390 --> 00:07:31,430
And then we're moving to the next record.

111
00:07:31,430 --> 00:07:35,960
And so this loop will run until we hit EOF and it'll pop out.

112
00:07:35,960 --> 00:07:40,430
And of course we will check for any errors and print those out.

113
00:07:40,430 --> 00:07:45,740
And so we have hard coded our state to CA which is California.

114
00:07:45,740 --> 00:07:49,280
And let's take a look at how that works.

115
00:07:49,700 --> 00:07:50,990
We'll go to runtime.

116
00:07:51,470 --> 00:07:52,760
We'll hit select.

117
00:07:53,730 --> 00:07:56,820
And here is our list of column names.

118
00:07:56,820 --> 00:08:02,100
And you can see all of our records are in the state of California.

119
00:08:02,930 --> 00:08:11,150
Now, we could make these a little more interactive by taking in our parameters from internal tags.

120
00:08:11,150 --> 00:08:15,200
And so let's do a quick example where we would do that.

121
00:08:15,200 --> 00:08:17,750
First I'll go back to graphics designer.

122
00:08:18,420 --> 00:08:22,980
And let's take a look here where we're inserting into our staff table.

123
00:08:22,980 --> 00:08:25,290
So we have last name first name role ID.

124
00:08:25,290 --> 00:08:33,450
And so we need a varchar to hold first name a varchar or string to hold last name and an integer to

125
00:08:33,450 --> 00:08:34,800
hold our role ID.

126
00:08:34,800 --> 00:08:38,310
So I am going to Link Explorer.

127
00:08:38,310 --> 00:08:43,350
And in tag management I have got a group here called Script Tags.

128
00:08:43,350 --> 00:08:46,710
This is where I did my divisor tag in the error module.

129
00:08:46,710 --> 00:08:50,610
And let's just add some tags for our staff table.

130
00:08:50,610 --> 00:08:55,320
So let's say this is called staff last name.

131
00:08:55,320 --> 00:08:59,670
And our data type here is going to be a string.

132
00:08:59,670 --> 00:09:02,160
So we'll do text 16.

133
00:09:02,830 --> 00:09:10,690
And then I can highlight that and do a pull down and just change that to first name, get rid of this

134
00:09:10,690 --> 00:09:17,800
and then we want staff role ID and this is going to be an integer.

135
00:09:17,800 --> 00:09:19,690
So I'll just make it unsigned eight bit.

136
00:09:20,080 --> 00:09:25,120
So now I have some internal tags that we can use to get user input.

137
00:09:25,120 --> 00:09:27,430
Next we'll go back to Graphics Designer.

138
00:09:27,460 --> 00:09:33,310
Go to our tag browser and under our script tags we have these tags here.

139
00:09:33,310 --> 00:09:35,440
So I can just drag these up.

140
00:09:35,440 --> 00:09:36,850
There's last name.

141
00:09:37,210 --> 00:09:40,570
There's first name and there's role ID.

142
00:09:40,810 --> 00:09:43,660
And so let's just format these.

143
00:09:43,660 --> 00:09:47,140
So I'm going to make these big enough so that we can see them.

144
00:09:47,260 --> 00:09:50,950
And I will add some labels.

145
00:09:50,950 --> 00:09:54,280
So that will just be last name.

146
00:09:54,400 --> 00:09:55,630
And we'll.

147
00:09:56,490 --> 00:09:58,200
Copy the properties.

148
00:09:58,200 --> 00:10:00,000
Paste the properties in.

149
00:10:00,210 --> 00:10:04,860
We'll duplicate this here is that to first name.

150
00:10:04,860 --> 00:10:06,510
So these are just labels.

151
00:10:06,990 --> 00:10:09,660
And then we will duplicate this again.

152
00:10:09,660 --> 00:10:12,210
And we'll do role ID.

153
00:10:12,210 --> 00:10:16,770
And just for aesthetics we'll make those blue.

154
00:10:17,160 --> 00:10:18,990
We'll go to our properties.

155
00:10:19,700 --> 00:10:23,420
We will turn off our global color scheme.

156
00:10:24,360 --> 00:10:25,560
Make our font.

157
00:10:25,590 --> 00:10:29,910
Now, these are empty strings, so they're not really showing anything in there right now.

158
00:10:30,060 --> 00:10:31,350
But we'll save these.

159
00:10:31,350 --> 00:10:38,280
And then if we go here, what we want to do is instead of hard coding these, we want to pull these

160
00:10:38,280 --> 00:10:39,480
from tags.

161
00:10:39,510 --> 00:10:46,290
So what we'll do is I'll just right click and do my runtime dot.

162
00:10:46,290 --> 00:10:48,810
And this is a list of all tags.

163
00:10:48,810 --> 00:10:53,850
And what I want to do is use my tag browser.

164
00:10:54,540 --> 00:10:56,670
And this is the first name.

165
00:10:57,240 --> 00:11:00,540
And then I can just do dot read.

166
00:11:01,170 --> 00:11:05,850
And so this will read that tag and throw it into this variable.

167
00:11:06,180 --> 00:11:09,420
Now I'm just going to copy this whole thing again.

168
00:11:09,420 --> 00:11:13,680
And this is going to be our last name.

169
00:11:13,680 --> 00:11:15,660
So there's my last name.

170
00:11:15,750 --> 00:11:17,790
And then finally.

171
00:11:18,830 --> 00:11:27,350
This is going to be our role ID so now this script will still work, but it's going to take input from

172
00:11:27,350 --> 00:11:28,220
our tags.

173
00:11:28,220 --> 00:11:31,700
That gives us a kind of crude user interface.

174
00:11:31,700 --> 00:11:36,950
So let's test it out I'll click okay save go to runtime.

175
00:11:36,950 --> 00:11:44,630
And our last name is Jenkins I'll hit enter and first name is Leroy.

176
00:11:45,560 --> 00:11:51,320
And Leroy is a repairman and I can hit insert.

177
00:11:51,320 --> 00:11:54,860
So it does print out that it did add to the table.

178
00:11:55,560 --> 00:11:58,710
Let's go back and test our error handling.

179
00:11:58,710 --> 00:12:04,650
So I'll just put in Smith Janet and let's put in role ID that doesn't exist.

180
00:12:04,650 --> 00:12:08,610
So something like seven we hit insert.

181
00:12:08,610 --> 00:12:13,080
And so it says basically there is no role.

182
00:12:13,080 --> 00:12:14,520
That is role seven.

183
00:12:14,520 --> 00:12:16,080
So that works.

184
00:12:16,080 --> 00:12:23,130
If we go back and put in something valid then Janet Smith has been added to the table.

185
00:12:23,130 --> 00:12:30,840
If we go back to SQL server and hit execute again there are our new staff members.

186
00:12:30,840 --> 00:12:38,670
So the command object is set up to be able to take parameters very easily from user interfaces, which

187
00:12:38,670 --> 00:12:40,230
is what we want to do.

188
00:12:40,590 --> 00:12:49,110
It also returns the number of rows affected by your SQL query, which can be really useful in making

189
00:12:49,110 --> 00:12:52,650
sure that the statement ran properly and for error checking.

190
00:12:52,650 --> 00:12:59,160
So the Ado command object is great when you're inserting, updating, or deleting data from the database.

191
00:12:59,970 --> 00:13:06,000
In the next assignment, you're going to get some practice using the Ado command objects with parameters,

192
00:13:06,000 --> 00:13:11,490
and begin building some Wink interfaces to interact with the database.