The most basic storage unit of any operating system is a file. Files can contain anything from textual data in this article, a bitmap image of your baby daughter, a MS Excel worksheet containing your finances or a Visual Basic program you are currently working on. Therefore it is very important to know how to manipulate files after they are created. Common actions that are performed on files are copy, rename, move and delete. Each file has properties such as creation date, last date modified, and attributes such as read…
Tag: free code
Output Clause
The OUTPUT clause was introduce with the release of SQL Server 2005. This clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be inserted into a table or variable to implement business requirements such as confirmation messages, archiving, and atomic transactions. Today, I would like to use the AdventureWorks Sample database as the basis of our discussion. The first example creates a new Country By Continent table to store all the countries in South America. The OUTPUT clause…
Deleting Data
The DELETE reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to remove existing data in one table in the database. Today, I would like to review the different ways to remove data using the AdventureWorks Sample database supplied by Microsoft. Each example has a SELECT statement to identify the number of rows affected before executing the DELETE. 1 – One way to purge data from a table is to use the DELETE statement without a where clause. Only use this syntax when…
Updating Data
The UPDATE reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to modify existing row data in one table in the database. Today, I would like to review the different ways to update data using the Adventure Works Sample database supplied by Microsoft. Each example has a SELECT statement to identify the number of rows affected before executing the UPDATE. 1 – The simplest form of the UPDATE statement modifies one column in a given table filtered by one where clause. The following…
Inserting Data
The INSERT reservered word is part of the Data Manipulation Language (DML) defined by Codd in 1970 and is used to add data to a table in the database. Today, I would like to review the different ways to insert data using the AdventureWorks Sample database supplied by Microsoft. 1 – Many times a table is defined with a surrogate key, an autoincrement column. You can specify values for this column by setting the INDENTITY INSERT property of the table. The following code adds a row to the Human Resources…
Fixed Formatted Text Files
The invention of a fixed formatted text (flat) file layout started with IBM in the 1960’s. Data sets on the mainframe could be stored in both fixed and variable length records. A flat file is defined as having one record of data per row that is ended with a new line. Each field inside the record is represented by a fixed number of characters. While flat files can have either ASCII or EBCDIC encoding, I will be exploring the former. Another generalization of this format is to store numbers in…
Running the SMTP relay
Today, I want to talk about getting in shape to run the Microsoft Exchange, SMTP Relay. It takes years of practice to get in condition for such a grueling race. I am just kidding! However, the topic of a SMTP relay is an important one that you will come across in your career. A open mail relay allows a Visual Basic script to send mail messages from a mail box that you can name and does not have to exist on the mail server. In todays information world, it is…
Recursive Programs
I would like to chat about one of my favorite programming topics, implementing recursive algorithms. Recursion in computer science is a method where the solution to a problem depends on solutions to smaller instances of the same problem. A recursive program is a program that calls itself to work on a smaller sub problem. When the stop condition (last problem) is reached, all results are put together to reach the desired result. The tricky part is to make sure that the program does not execute in a infinite call loop.…
Working with databases
Most Commercial off-the-self (COTS) systems read and write data to a relational database such as SQL Server, Oracle or DB2. Canned reports and data interfaces included with the COTS system sometimes have limited features that do not fully meet the business requirements. That is where you, the software developer, can come to the rescue by providing a custom work-around solution. Actions against the database can be expressed in the data definition language (DDL), data control language (DCL) or data manipulation language (DML). I have encapsulating the logic for working with…
Excel Files
Business Users like to have data in a spreadsheet format. Since Microsoft Office is installed world-wide, it will be a common request for data in a XLS format. Let’s make believe that we have the business problem to write information about the first five presidents to an XLS format. The Cliché, there are many ways to skin a cat, does apply to technical solutions. I have abstracted the details behind reading and writing files by encapsulating the logic into two classes for your use. These classes can be implemented in…