VB script working with database SQL

shahid-koka (32)in #technology • 6 years ago (edited)

VB script.Now, as the diagram shows, we have the script written in VBScript andbasically, 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 anExcel 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 objectsthat we can use in VBScript are theADO objects. It stands for Activex Data Objects andtwo ofthe 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 andalso, 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 theresult 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 thatproduces a result set, consisting of many columns and many rows. A Record Setis a copy of thatresult set and we canlook at it in VBScript. And we can use it to show values on the screen in theGUI 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 withSQL ServerSQL Express. And here, I have a database. The name of the database is TestCompany, and it contains a number ofsample 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 canget 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 tocontain the reference to the Record Set. Now, the first sub procedure that will call is OpenADOobjects andthis 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 specifiesthat what is the kind of the database (whether it is SQL server or any other kind ofDBMS),what is the name of the server and other details like username and password.Then, we set a reference to the objRecordSetand 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 andthisstatement is executed on objConnection, and we have to give a couple of more arguments. The first argument isbasically 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 andthe 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 isopen, 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 statementso we do not have to repeat objRecordSet again and again.Now, here is aDo While loop.So, when the record set is open, at that timethe 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 fromthat record and"has their office address at" and then we get the BillingAddress value. And within the Do While loop, we write a MoveNextmethod.Now, MoveNext simply moves the record pointer to the next record. So basically, what is going to happen isit will start at the first record,display the value, then go to the next record. Again, display the value and keep on doing that untilit is at end of file. And when it is at end of file, Not EOFcondition 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 andhere, we can see that it says CustId hastheir office address at Cust1 address,Cust2 has their office address at Cust2 Bill Address, Cust3 has their office address at Cust3 Billing Address andthis 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 ourVB Script.Now, let us see how we can addnew 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 thatthere are three records in the Customers table, basically for 3 customers.So, what we do iswe call the AddNew method on the objRecordSet. So basically, it adds one more record to the record set and wecan 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 thisworks.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 beeninserted into the table. So, whatever values we gave ofCustomerId, 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 thatwe have been manipulating the record set. We used acopy 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, thatwe have to update some existing data. Now, one way to update existing data is tofind out that particular record in the record set and update it.There is another way of doing it and that isusing aSQL statement to directly execute on the Connection object. So here, we have the Connection object and we say .execute andwe write the Update statement. So here the statement is Update Customers 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 iswe want tofind out which row hasthe name as Cust1. So basically, it is the first record, which has the name as Cust1 so there we want to putthe name as Customer1. Right now, it is Cust1.So let us execute this script now andgo 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 databut we just want to deletethe temporary record thatwe 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 isDELETE Customers WHERE CustomerId=4So 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 andbasically there is one record whereCustomerId is 4 and delete that particular record from the Customers table.So here, we see thatthe CustomerId is 4 in justthis record and this is what we want to delete.So let usexecute this script. Once again, the script is successfully executed. Let us go back to the database andexecute 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 asVBScript is capable of connecting tomany different kinds of data sources. It can be an Excel file or a CSV file or Microsoft Access databaseor it can be a SQL server, Oracle (any kind ofrelational database) and we can work withADO 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 somemanipulations 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.

source of image