VBScript Database and SQL – tutorial 8

VBScript Database and SQL – tutorial 8


Hello, and welcome to the next VBScript video. In this video, we look at how to work with the databases using VB script. Now, as the diagram shows, we have the script written in VBScript and basically, the script can connect to any kind of data source. So this data source can be a database or it can be another data source like an Excel file, an XML file, a CSV (comma separated values) file or any other data source. Now, there are COM objects available and in VBScript, we can create instances of those COM objects. And work with the data source or the database. The important COM objects that we can use in VBScript are the ADO objects. It stands for Activex Data Objects and two of the powerful ADO objects are the Connection and the RecordSet. Now, what is the Connection? A Connection is basically, an object that connects the script to the database. Now if the Connection is open, that time we can execute commands on the database and also, if the connection is open we can fetch data from the database. Now Connection is the fundamental object and in addition to the Connection, we can have the RecordSet object. Basically, the RecordSet object is a copy of the result set, that we can get from the database. So let us say, that we execute an SQL query on the database, which is something like select * from tablename. So that produces a result set, consisting of many columns and many rows. A Record Setis a copy of that result set and we can look at it in VBScript. And we can use it to show values on the screen in the GUI or the command line. And also, there are other operations that we can perform with it. So, let us see an example of how we can use VB script to connect to the database and execute commands on the database. Now, here is a VBScript that we have. But, before we see the VBScript, I am going to work with SQL Server SQL Express. And here, I have a database. The name of the database is TestCompany, and it contains a number of sample tables. One of the tables is Customers, and if we execute the SQL query, Select * from Customers, it shows us that there are three records in this table. And the names of the customers are Cust1, Cust2 and Cust3. Now, let us see how we can get that information using VBScript. So, here is the VBScript and we have a couple of variables. objConnection (to store the reference to the Connection) and objRecordSet to contain the reference to the Record Set. Now, the first sub procedure that will call is OpenADOobjects and this is the sub procedure. Now here, what we do is we use a CreateObject built-in VBScript function to create ADODB.Connection. Then, we call the Open method on the Connection and pass it a connection string. The connection string is basically a string, which specifies that what is the kind of the database (whether it is SQL server or any other kind of DBMS), what is the name of the server and other details like username and password. Then, we set a reference to the objRecordSet and create ADODB.RecordSet object. Now, this is the important line. We call the Open method on the objRecordSet object. And here, we have to specify the SQL query. So here our SQL query is Select * from Customers, which means return all the records in the customers table and this statement is executed on objConnection, and we have to give a couple of more arguments. The first argument is basically defining the type of the record set. So, this means adOpenKeySet. Basically, we can perform several operations when the record set is the open key set type and the argument of 3 means AddLockOptimistic. Basically, what kind of lock we want to place. So right now, let us not worry about the value of these two arguments. But understand that once the record set is open, it will contain all the rows from the Customers table, whether the Customers table contains 1 row or 10 rows or 100 rows. Whatever is the number of rows, a copy of the entire Customers table will be available in objRecordSet object. Now, the next sub procedure that we call is ShowCustomers. Now, in the ShowCustomers sub procedure, we use a with statement so we do not have to repeat objRecordSet again and again. Now, here is a Do While loop. So, when the record set is open, at that time the record set has a pointer. The pointer points to any particular record within the record set. So at the beginning, the pointer is going to point to the first record in the record set. That means the first customer. Now, here is the entire record set. So at the beginning, the pointer is going to point to the CustomerId of 1. Now what we do is, we say WScript.Echo and we get the name value from that record and “has their office address at” and then we get the BillingAddress value. And within the Do While loop, we write a MoveNext method. Now, MoveNext simply moves the record pointer to the next record. So basically, what is going to happen is it will start at the first record, display the value, then go to the next record. Again, display the value and keep on doing that until it is at end of file. And when it is at end of file, Not EOF condition becomes False. And it comes out of the loop. So let us execute this and see how it works. So, we are going to use the WScript command. WScript DBScript.vbs and here, we can see that it says CustId has their office address at Cust1 address, Cust2 has their office address at Cust2 Bill Address, Cust3 has their office address at Cust3 Billing Address and this is exactly the same data that we see over here. Cust1, Cust2 and Cust3. So, that is about how we can get the data from database and display it in our VB Script or use it in our VB Script. Now, let us see how we can add new data to the database. So for that, we have the AddTempCustomer sub procedure. Now, what AddTempCustomer sub procedure does is, it basically adds a dummy record. So right now, we can see that there are three records in the Customers table, basically for 3 customers. So, what we do is we call the AddNew method on the objRecordSet. So basically, it adds one more record to the record set and we can set each of the fields. So here, we can see the fields are CustomerId, Name, Bill Address and ShipAddress. So here, we have given some value of CustomerID, Name, Bill Address, Ship Address. So, let us execute this script and see how this works. So the script is now executed. Now, if we execute this SQL statement again (basically, look at all the rows in the Customers table), what we find is a new record has been inserted into the table. So, whatever values we gave of CustomerId, Name, Bill Address and Ship Address, those values have come over here. Now, what we can also do is we can update the existing data. Now so far, what we have seen is that we have been manipulating the record set. We used a copy of the result set of the SQL statement in our record set and we were able to display it in our VBScript. And also, we were able to add new records to the record set. Now let us say, that we have to update some existing data. Now, one way to update existing data is to find out that particular record in the record set and update it. There is another way of doing it and that is using a SQL statement to directly execute on the Connection object. So here, we have the Connection object and we say .execute and we write the Update statement. So here the statement is Update Customers SET Name=’Customer1′ WHERE NAME=’Cust1 So right now, if we execute this SQL statement, we see that the names of the 4 customers in the 4 rows are Cust1, Cust2, Cust3 and Cust4. But what we want to do now is we want to find out which row has the name as Cust1. So basically, it is the first record, which has the name as Cust1 so there we want to put the name as Customer1. Right now, it is Cust1. So let us execute this script now and go to our database and look at the contents of the Customers table. So here, we can see that instead of Cust1, we got Customer1. So here, the difference is that instead of working on the record set object, we directly worked with the Connection object. So that is how we can update the data. So let us say, we don’t want to update the data but we just want to delete the temporary record that we created in the Customers table. So basically, where we put the CustomerId as 4 and Name as Cust4. So here also, we can directly execute a command on the Connection object. So basically, the command is DELETE Customers WHERE CustomerId=4 So whenever you are issuing the delete command, it is very important to put the exact where clause. Because if you do not put the where clause, delete command will delete all the records in that table. So, be extra careful with the delete command. So, what we want to do is we want to find out all the rows in the Customers table where CustomerId is 4 and basically there is one record where CustomerId is 4 and delete that particular record from the Customers table. So here, we see that the CustomerId is 4 in just this record and this is what we want to delete. So let us execute this script. Once again, the script is successfully executed. Let us go back to the database and execute the Select * from Customers again, and we see that where CustomerId was 4, that record has been deleted now. So, this is how we can work with databases as VBScript is capable of connecting to many different kinds of data sources. It can be an Excel file or a CSV file or Microsoft Access database or it can be a SQL server, Oracle (any kind of relational database) and we can work with ADO COM objects. So basically, we can work with the Connection object or the RecordSet object. When we want to display some data to the user or perform some manipulations on the data of a result set, we can use the RecordSet object. If you just have to execute some simple commands, then we can use the Connection object. So, that is all for this video and see you in the next videos. Thank you.

Danny Hutson

13 thoughts on “VBScript Database and SQL – tutorial 8

  1. Whenever we make any change  in Recordset through our VB script, does it always get reflected in Actual database? Is there any difference in result if we execute Add, Delete or Update command on Recordset or directly on Connection?

  2. Outstanding tutorial!!!!  Is there anywhere I can obtain a copy of of the code displayed in this video?…so I can practice.  Save me the trouble of writing it out myself.  Thanks.

  3. Hi, can you please show how to do connection with sql server i.e where to mention database name, sql server credentials

Leave a Reply

Your email address will not be published. Required fields are marked *