Sample Codes: November 2010

Read and Write Binary file: int, string

Imports System.IO

Module Module1

Sub Main()
  Dim Stream As FileStream

  'Writing
  Try
   Stream = New FileStream("test.dat", FileMode.Create)
   Catch E As Exception
    Console.WriteLine("Error creating test.Dat")
    Console.WriteLine("Error {0}", E.Message)
  End Try

  Dim BinaryStream As New BinaryWriter(Stream)

  Dim Age As Integer = 21
  Dim Salary As Double = 100000.0
  Dim Name As String = "Joe"

  Try
   BinaryStream.Write(Age)
   BinaryStream.Write(Salary)
    BinaryStream.Write(Name)
    BinaryStream.Close()

    Console.WriteLine("Data written to test.Dat")
  Catch E As Exception
    Console.WriteLine("Error writing to test.Dat")
    Console.WriteLine("Error {0}", E.Message)
  End Try

  'Reading

  Try
   Stream = New FileStream("test.dat", FileMode.Open)
  Catch E As Exception
   Console.WriteLine("Error opening test.Dat")
   Console.WriteLine("Error {0}", E.Message)
  End Try

  Dim BinaryStreamReader As New BinaryReader(Stream)

  Try
   Age = BinaryStreamReader.ReadInt32()
   Salary = BinaryStreamReader.ReadDouble()
    Name = BinaryStreamReader.ReadString()
    BinaryStreamReader.Close()

    Console.WriteLine("Age: {0}", Age)
    Console.WriteLine("Salary: {0}", Salary)
    Console.WriteLine("Name: {0}", Name)
  Catch E As Exception
   Console.WriteLine("Error reading to test.Dat")
   Console.WriteLine("Error {0}", E.Message)
  End Try

End Sub

End Module

EXISTS operator returns TRUE if a subquery contains any rows

EXISTS operator returns TRUE if a subquery contains any rows. In the follwing example we want to retrive list of employees from department number 4, only if people from this department produced sales in 1999 :

SELECT a.EmpNo, RTrim(a.FirstName) + ' ' + RTrim(a.LastName) AS EmpName, SUM(b.DocTotal) AS STotal FROM Employees AS a LEFT JOIN Invoice AS b ON a.EmpNo = b.SlpCode WHERE a.DeptNo = 4 AND EXISTS (SELECT * FROM Invoice AS t1 INNER JOIN Employees AS t2
ON t1.SlpCode = t2.EmpNo WHERE t2.DeptNo = 4 AND YEAR(t1.docdate)=1999) GROUP BY a.EmpNo, RTrim(a.FirstName) + ' ' + RTrim(a.LastName);

Update Join syntax in TSQL

For some reason we need to put CustomerName in "Invoices" table. This table allready have CustomerID indicator:

UPDATE a
SET a.CustomerName = b.CustomerName
FROM Invoice AS a INNER JOIN Customers AS b
ON a.CustomerID = b.CustomerID

Removing an Excel Workbook VBA Password

A VBA project password can be removed with a hex editor.

Close the workbook and open the workbook file in the hex editor.
Find the string "DPB" and change it to "DPx".
Save the file.
Open the workbook and click OK until the workbook is open (one or more dialogs are displayed describing various problems with the VBA project).
Press ALT+F11, choose the menu command Tools->VBAProject Properties, navigate to the Protection tab, and change the password but do not remove it (note the new password).
Save, close, and re-open the workbook.
Press ALT+F11 and enter the new password.
Choose Tools->VBAProject Properties, navigate to the Protection tab
and remove the password.
Save the workbook.

T-SQL code to find out the nth highest number in a column

CREATE PROC nthHighestNo 
(
 @table_name sysname,
 @column_name sysname,
 @nth int
)
AS
BEGIN

Regular Expressions in .NET

In this article, Darren explains a nice and simple way of how to use regular expression in .Net. For those who are confused about how regular expressions work, this article is for you.

The Four Steps of Programming

Introduction

This article will not tell you how to use what functions to solve that problem, but will take a look at the process a person should go through in order to write simple, yet elegant code. In a world where we are met with colorful examples of things not to do, there is a great need for people who know what they are doing.

Receive all updates via Facebook. Just Click the Like Button Below▼

Receive all updates via Facebook. Just Click the Like Button Below
▼