Arun Kakkar - Software Engineer

Math Basics

2026-03-09T16:33:00.000-07:00

Topic

Sub-Topic / Details

Tables till 30

Reference list for rapid calculation

Squares till 30

Essential for Algebra/Geometry

Square roots of 1, 2, 3, 4, 9

Common irrational & rational roots

Cubes till 11

Volume & Algebra basics

Types of Numbers & Systems

Real Numbers Includes all possible numbers on a continuous number line (rational + irrational).

Rational Numbers Fractions with terminating or repeating decimals (Integers > Whole > Natural).

Irrational Numbers Cannot be expressed as fractions; non-repeating infinite decimals (e.g., π, √2, √10).

Roman Numerals, Even, Odd, Prime, & Composite

Classification and properties

Fractions (p/q)

Proper (Numerator < Denominator)
Improper (Numerator > Denominator)
Mixed Numbers

Decimals, Powers & Roots

Decimal
Indices (powers/exponents) (a raise to power b)
Surds (irrational roots or underroot)

Number Systems & Notation

Place value & expanded form
Number system (India & International) (reading & writing)
Scientific notation / Standard form

Order of Operations & Rules

Order of operations (BODMAS / BIDMAS / PEMDAS)
Divisibility Rules
BODMAS for Natural Numbers, Whole, Integer, Fractions, decimal, Indices, Surds

Arithmetic Foundations

Unitary method
LCM, HCF, Factors, Multiples
Average (mean)

Ratios & Applied Math

Ratio & Proportions
Ages problems
Time & work / Pipes & cisterns problems

Commercial Mathematics

% & taxes
Interest (simple, compound)
Profit, loss, discount

Motion & Speed

Time, speed, distance
Boats & Trains problems
Streams (Upstream/Downstream)

Numbers & Number Systems

Arithmetic

Geometry

Coordinate Geometry

Mensuration (2D, 3D)

Trigonometry

Statistics

Probability

Algebra

Numbers & Number Systems

Basic Number Concepts

Counting, reading & writing numbers
Place value & expanded form
Even & odd numbers
Factors, multiples, prime & composite numbers
HCF & LCM
Divisibility rules

Number Classification

Fractions — types, operations, comparison
Decimals — operations & conversion
Integers & negative numbers
Rational numbers — properties & operations
Irrational numbers & real numbers

Exponents & Theorems

Square roots & cube roots
Surds, indices & laws of exponents
Scientific notation / Standard form
Euclid's Lemma & Fundamental Theorem of Arithmetic

Arithmetic

Basic Operations & Rules

Basic operations — addition, subtraction, multiplication, division
Order of operations (BODMAS / BIDMAS / PEMDAS)
Roman numerals
Unitary method

Commercial Concepts

Ratio & proportion
Percentage
Profit, loss & discount
Simple interest & compound interest
Average (Mean)

Applied Word Problems

Time & work / Pipes & cisterns
Time, speed & distance
Trains, boats & streams (applied speed problems)

Business & Real-world Math

Partnership
Mixture & alligation
Ages problems
Financial literacy (budgeting, tax, GST, bills)

Geometry

Geometric Foundations

Basic 2D & 3D shape recognition
Lines, line segments, rays & angle types
Parallel lines & transversals
Symmetry & reflection

Polygons & Triangles

Triangles — types, properties, congruence
Quadrilaterals — types & properties
Polygons — interior & exterior angles
Similarity of triangles & Basic Proportionality Theorem (BPT)
Pythagoras theorem & applications

Circles & Drafting

Circles — chord, arc, tangent, angles, secant
Circle theorems
Geometric constructions with compass & straightedge

Transformations & Proofs

Congruence, similarity & transformations (rotation, reflection, translation)
Vectors — introduction
Geometric proof (Euclidean)

Coordinate Geometry

Points & Planes

Plotting points on a number line
Cartesian plane — axes, quadrants, plotting points

Key Formulas

Distance formula
Section / midpoint formula
Area of triangle using coordinates

Lines & Graphing

Slope & equation of a straight line
Parallel & perpendicular lines (coordinate)
Graphing linear & quadratic functions

Mensuration (2D, 3D)

2D Shapes (Area & Perimeter)

Perimeter of basic shapes
Area of square, rectangle, triangle
Area of parallelogram, rhombus, trapezium
Circumference & area of circle
Area of sector & segment

3D Solids & Volumes

Surface area & volume — cube & cuboid
Surface area & volume — cylinder, cone, sphere, hemisphere
Combinations of solids & frustum of cone
Composite / compound figures

Trigonometry

Trig Foundations

Trigonometric ratios — sin, cos, tan (right triangles)
Trigonometric identities
Complementary angle relationships

Applications & Rules

Heights & distances — elevation & depression
Sine & cosine rules (non-right triangles)
Bearings & directions

Statistics

Data Representation

Data collection & tally marks
Pictographs & bar graphs
Pie charts & line graphs

Central Tendency & Spread

Mean, median, mode for ungrouped data
Mean, median, mode for grouped data
Range, IQR & measures of spread

Statistical Analysis

Histograms, box plots & cumulative frequency
Scatter plots, correlation & line of best fit

Applied Statistics

Data Interpretation (tables, charts — applied)
Two-way tables & categorical data

Probability

Probability Foundations

Basic concept — likely, unlikely, equally likely
Probability scale 0 to 1
Classical definition of probability

Probability Types & Events

Experimental vs theoretical probability
Simple & compound events, complementary events

Tools & Advanced Theory

Tree diagrams & Venn diagrams
Conditional probability (basic)
Combinatorics / counting principles (intro)

Algebra

Algebraic Basics

Introduction to variables & algebraic expressions
Exponents & powers
Algebraic identities
Factorisation of algebraic expressions

Linear Equations

Linear equations — one variable
Linear equations — two variables & graphing
Pair of linear equations — graphical & algebraic methods

Higher Degree Equations

Polynomials — zeros, remainder & factor theorem
Quadratic equations — factorising, formula, discriminant

Advanced Topics

Inequalities — linear
Functions — definition, notation, linear functions
Sequences — arithmetic & geometric
Algebraic fractions

Multiplication Tables (2 to 30)

* Swipe left/right to view all columns (x1 to x10)

Table	x1	x2	x3	x4	x5	x6	x7	x8	x9	x10
2	2	4	6	8	10	12	14	16	18	20
3	3	6	9	12	15	18	21	24	27	30
4	4	8	12	16	20	24	28	32	36	40
5	5	10	15	20	25	30	35	40	45	50
6	6	12	18	24	30	36	42	48	54	60
7	7	14	21	28	35	42	49	56	63	70
8	8	16	24	32	40	48	56	64	72	80
9	9	18	27	36	45	54	63	72	81	90
10	10	20	30	40	50	60	70	80	90	100
11	11	22	33	44	55	66	77	88	99	110
12	12	24	36	48	60	72	84	96	108	120
13	13	26	39	52	65	78	91	104	117	130
14	14	28	42	56	70	84	98	112	126	140
15	15	30	45	60	75	90	105	120	135	150
16	16	32	48	64	80	96	112	128	144	160
17	17	34	51	68	85	102	119	136	153	170
18	18	36	54	72	90	108	126	144	162	180
19	19	38	57	76	95	114	133	152	171	190
20	20	40	60	80	100	120	140	160	180	200
21	21	42	63	84	105	126	147	168	189	210
22	22	44	66	88	110	132	154	176	198	220
23	23	46	69	92	115	138	161	184	207	230
24	24	48	72	96	120	144	168	192	216	240
25	25	50	75	100	125	150	175	200	225	250
26	26	52	78	104	130	156	182	208	234	260
27	27	54	81	108	135	162	189	216	243	270
28	28	56	84	112	140	168	196	224	252	280
29	29	58	87	116	145	174	203	232	261	290
30	30	60	90	120	150	180	210	240	270	300

Powers & Roots Reference

* Squares (1-30), Cubes (1-11), & Key Square Roots

No.	Squares	Cubes	Square Root
1	1	1	1
2	4	8	1.4142
3	9	27	1.732
4	16	64	2
5	25	125
6	36	216
7	49	343
8	64	512
9	81	729	3
10	100	1000
11	121	1331
12	144
13	169
14	196
15	225
16	256
17	289
18	324
19	361
20	400
21	441
22	484
23	529
24	576
25	625
26	676
27	729
28	784
29	841
30	900

How to read XML files using vb6.0

2012-10-11T11:18:00.001-07:00

RAM RAM Friends

After a very very long time, I got very few moments to share my knowledge.

Here is how we can read, write etc from/to XML files using vb6.0

Step 1 >> Set Reference to "Microsoft XML, v2.6" as below

Project Menu>References>select "Microsoft XML, v2.6">OK

Step 2 >> Have any one XML file to read data from it.

Below is the sample file I Used in this sample program

test.xml

<check>
<header>
<outlet> AK </outlet>
<profilename> ARUN KAKKAR </profilename>
</header>
<item>
<code> C1 </code>
<name> ITEM 1 </name>
<qty> 2 </qty>
</item>
<item>
<code> C2 </code>
<name> ITEM 2 </name>
<qty> 5 </qty>
</item>

</check>

Step 3 > Make procedure to read the file

Private sub ParseXMLDoc_ak()

Dim doc as new MSXML2.DOMDocument
Dim bsuccess as boolean, s as string , s2 as string
bsuccess=doc.Load(App.Path & "\ test.xml")
if bsuccess=false then

msgbox doc.parseError.reason
exit sub

end if
Dim n1 as MSXML2.IXMLDOMNode
Dim n2 as MSXML2.IXMLDOMNode
Dim n3 as MSXML2.IXMLDOMNode
Dim i as Integer
s="SrNo Code Name Qty"
For Each n1 in doc.childNodes

'n1=RootNode eg. CHECK
For Each n2 In n1.childNodes

'n2=1st Level child node i.e. child of n1 , e.g. ITEM, HEADER

If UCase(n2.nodeName)="ITEM" or UCase(n2.nodeName)="HEADER" Then

For Each n3 in n2.childNodes

'n3=2nd Level child Node i.e. child of n2 e.g. OUTLET,
' PROFILENAME,CODE, QTY, NAME
Select Case UCase(n3.nodeName)

Case "OUTLET"

s2=s2 & vbcrlf & "Outlet=" & n3.Text

Case "PROFILENAME"

s2=s2 & " " & "Profile Name=" & n3.Text

Case "CODE"

i=i+1
s=s & vbcrlf & i & " " & n3.Text

Case "NAME"

s=s & " " & n3.Text

Case "QTY"

s=s & " " & val(n3.Text)

End Select

Next n3

End if

Next n2

Next n1
if s <>; "" then MsgBox s2 & vbcrlf & s

End Sub

Step 4 > Below would be the output of above code

Outlet= AK Profile Name=ARUN KAKKAR
SrNo Code Name Qty
1 C1 ITEM 1 2
2 C2 ITEM 2 5

Note:
1. The above code is just very minimal use of the various features provided by "MSXML2 OBJECT"
2. You can use other methods & properties of this object to make it very easier to use xml in vb6.0

I hope, it would help you in dealing with XML files in much more better way,

Quickly Revise PL/SQL used in SQL Server 2000/2005/2008

2011-08-04T23:30:00.000-07:00

RAM RAM Friends

Quickly Revise PL/SQL used in SQL Server 2000/2005/2008

Following are the some of the important categories out of many for PL/SQL of SQL Server 2000/2005/2008

some important(frequently used) Built-In Functions
some important(frequently used) Built-in Stored Procedures
Stored procedures
Functions (UDF) (User Defined Functions) & their types
Triggers & their types
Views

Note:>>>> Below I have just mentioned their use & very less description as this is only for quickly revise

create proc InsertTable1
@name nvarchar(50)=null,
@image image=null,
@myimage2 image=null
as
begin
INSERT INTO Table1(name,image,myimage2) VALUES(@name,@image,@myimage2)
end

drop proc updatetable1

create proc UpdateTable1
@name nvarchar(50),
@image image,
@myimage2 image,
@id int
as
begin
if(@name is not null and @image is not null)
begin
UPDATE Table1
SET name=@name,
image=@image
end
end

USE arundb1
--==================
CREATE TABLE Countries
(
CountryId INT IDENTITY PRIMARY KEY,
CountryName NVARCHAR(50) UNIQUE
)

CREATE TABLE States
(
StateId INT IDENTITY PRIMARY KEY,
StateName NVARCHAR(50) NOT NULL,
CountryId INT FOREIGN KEY REFERENCES Countries(CountryId)
)

CREATE TABLE Cities
(
CityId INT IDENTITY PRIMARY KEY,
CityName NVARCHAR(50) NOT NULL,
StateId INT FOREIGN KEY REFERENCES States(StateId)
)

INSERT INTO Countries(CountryName)
SELECT 'Country1'
UNION ALL
SELECT 'Country2'

CREATE PROC FillCountries
as
begin
DECLARE @CountryNo INT
DECLARE @cmd NVARCHAR(200)

SELECT @CountryNo=1
WHILE(@CountryNo<5)
begin
SELECT @cmd='INSERT INTO Countries(CountryName) Values(''Cnt' + CONVERT(VARCHAR,@CountryNo) + ''')'
EXEC(@cmd)
SELECT @CountryNo=@CountryNo + 1
end
end

CREATE PROC FillStates
as
begin
DECLARE @StateNo INT
DECLARE @cmd NVARCHAR(200)
DECLARE @CountryId INT

DECLARE crsCountries CURSOR
FOR
SELECT CountryId FROM Countries

OPEN crsCountries
FETCH NEXT FROM crsCountries INTO @CountryId

WHILE(@@FETCH_STATUS=0) --CountryId
begin
SELECT @StateNo=1
WHILE(@StateNo<5) --insert 4 States
begin
SELECT @cmd='INSERT INTO States(CountryId,StateName) VALUES('+ CONVERT(VARCHAR,@CountryId) + ',''Cnt' + CONVERT(VARCHAR,@CountryId) + '_Stat' + CONVERT(VARCHAR,@StateNo) + ''')'
EXEC(@cmd)
SELECT @StateNo = @StateNo + 1
end

FETCH NEXT FROM crsCountries INTO @CountryId
end
CLOSE crsCountries
DEALLOCATE crsCountries
end

Create PROC FillCities
as
Begin
DECLARE @CountryId INT
DECLARE @StateId2 INT
DECLARE @CityNo INT
DECLARE @StateNo INT
DECLARE @cmd nvarchar(200)

DECLARE crsCountries CURSOR
FOR
SELECT CountryId FROM Countries
OPEN crsCountries
FETCH NEXT FROM crsCountries INTO @CountryId

WHILE(@@FETCH_STATUS=0) --Countryid
begin
DECLARE crsStates CURSOR
FOR
SELECT StateId FROM States
WHERE CountryId=@CountryId
OPEN crsStates
FETCH NEXT FROM crsStates INTO @StateId2

SELECT @StateNo=1
WHILE(@@FETCH_STATUS=0) -- StateId
begin
SELECT @CityNo=1

WHILE(@CityNo<5)--insert 4 Cities
begin
SELECT @cmd='INSERT INTO Cities(StateId,CityName) VALUES(' + CONVERT(VARCHAR,@StateId2) + ',''Cont' + CONVERT(VARCHAR,@CountryId) + '_St' + CONVERT(VARCHAR,@StateNo) + '_City_' + CONVERT(VARCHAR,@CityNo) + ''')'
print @cmd
exec(@cmd)
SELECT @CityNo=@CityNo +1
end

FETCH NEXT FROM crsStates INTO @StateId2
SELECT @StateNo=@StateNo+1
if(@StateNo>4)
SELECT @StateNo=1

end
close crsStates
DEALLOCATE crsStates
FETCH NEXT FROM crsCountries INTO @CountryId
end
CLOSE crsCountries
DEALLOCATE crsCountries
End

SELECT * FROM Countries
SELECT * FROM States
SELECT * FROM Cities
exec fillCountries
EXEC FillStates
EXEC FillCities
DROP PROC FillCountries
DROP PROC FillStates
DROP PROC FillCities
CLOSE crsCountries
DEALLOCATE crsCountries
DEALLOCATE @StateId
DELETE Cities
INSERT INTO Cities(CityName,StateId) VALUES('ArunCity',17)

sp_helptext usp_AddUpdateTaxes
'

' Alter PROCEDURE [DBO].[USP_ADDUPDATETAXES]
'(
' @TAXID INT
' ,@TAXTYPE NVARCHAR(15)
' ,@TAXNAME NVARCHAR(MAX)
' ,@TAXVALUE NUMERIC
' ,@CUSTID INT
' ,@MINVAL NUMERIC
' ,@DESCR NVARCHAR(300)
' ,@UNIT NVARCHAR(20)
' ,@CURRENCY NVARCHAR(50)
' ,@TaxTypeHandling nvarchar(300)
')
'AS
'BEGIN
' SET NOCOUNT ON;
'
' BEGIN TRAN
' IF @TAXID=0
' INSERT INTO TBL_TAXCHARGES(TAXTYPE,TAXNAME,TAXVALUE,CUSTID,MINVAL,DESCR,UNIT,CURRENCY,TaxTypeHandling) VALUES (@TAXTYPE,@TAXNAME,@TAXVALUE,@CUSTID,@MINVAL,@DESCR,@UNIT,@CURRENCY,@TaxTypeHandling)
' Else
' UPDATE TBL_TAXCHARGES SET TAXTYPE=@TAXTYPE,TAXNAME=@TAXNAME,TAXVALUE=@TAXVALUE,CUSTID=@CUSTID,MINVAL=@MINVAL,DESCR=@DESCR,UNIT=@UNIT,CURRENCY=@CURRENCY,TaxTypeHandling=@TaxTypeHandling WHERE TAXID=@TAXID
' -- SET @p_CompanyID_out = SCOPE_IDENTITY()
'
' IF @@ERROR<>0
' ROLLBACK TRAN
' Else
' COMMIT TRAN
'
'End

CREATE TABLE AddressBook
(
AddressId INT IDENTITY PRIMARY KEY,
MyName nvarchar(250) null,
ContactNo nvarchar(50) null,
Address nvarchar(300) null,
Photo image null
)

select * from AddressBook

CREATE PROCEDURE spInsertAddressBook
@Id int output,
@name nvarchar(250)=null,
@ContactNo nvarchar(50) =null,
@Address nvarchar(300)=null,
@Photo image=null
AS
BEGIN
INSERT INTO AddressBook(MyName,ContactNo,Address,Photo) VALUES(@name,@ContactNo, @Address,@Photo);
SELECT @Id=SCOPE_IDENTITY()
END

ALTER PROC spInsertAddressBook
@Id int Output,
@MyName nvarchar(250)=null,
@ContactNo nvarchar(50)=null,
@Address nvarchar(300)=null,
@Photo image=null
AS
BEGIN
INSERT INTO AddressBook(MyName,ContactNo,Address,Photo) VALUES(@MyName,@ContactNo,@Address,@Photo);
SELECT @Id=SCOPE_IDENTITY();
END

declare @myid int
EXEC spInsertaddressbook @myid output,'abc', 'a', 'a'
select @myid

--below is named paramerised sp calling
declare @myid int
exec spinsertaddressbook @id=@myid output,@myname='a',@contactno='aa'
select @myid
select * from AddressBook

-- Attaching a DB
use master ; EXEC sp_attach_db @dbname = 'ArunDB1',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\ArunDB1.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\ArunDB1_log.ldf'

create table myTable1
(
myId int Identity Primary Key,
FName nvarchar(200) null,
LName nvarchar(200) null,
Age numeric(3) null
)
insert into myTable1
select 'F_Ram1', 'L_Ram1',20
union all
select 'F_Ram2', 'L_Ram2', 21
union all
select 'F_Ram3', 'L_Ram3', 22
union all
select 'F_Ram4','L_Ram4',23

select * from mytable1

Select * from SysDataBases
select * from SysObjects
--P = SP
--FN = Scalar-valued UDF
--IF = Inline-Table Valued UDF
--TF = Multi-Statement Table-Valued UDF
--TR = Trigger
--V = View
--U == User Defined Tables

GO
if object_id('mysp1','P') is not null --mostly used now a days also working in SQL 2K but not at all places
--if exists(select name from sysobjects where name='mysp1' and type='P') --mostly used in SQL 2k but still working in SQL 2K5
drop proc mysp1
GO --needed since CREATE/ALTER must be first stmnt in batch query
create proc mysp1
@myFName nvarchar(200)='F%',
@myLName nvarchar(200)='L%'
as
begin
select * from mytable1 where FName like @myFName and LName like @myLName
end

exec mysp1
exec mysp1 '%','%'
exec mysp1 'F','L'
exec mysp1 'F%2'

--UDF User Defined functions---------------------
--1. Scalar valued UDF---------
Go
If object_id('GetMyAgeInYrs','FN') is not null
drop function GetMyAgeInYrs
Go
Create Function GetMyAgeInYrs (@myDOB datetime='07Apr1981', @CurrDt datetime='02May2011')
Returns int
as
begin
declare @myAge int
set @myAge=datediff(year,@myDob,@currdt)
return(@myage)
end

select GetMyAgeInYrs(default,default) as myAge --It shows error
--Scalar function name must be prefixed with either DBName.DBO.myFuntion1 or DBO.myFunction1
-- DBO is the owner name of db
select ArunDB1.DBO.GetMyAgeInYrs('07Apr1981',getdate()) as myAge
select dbo.getmyageinyrs(default,default) as myAgeInYrs

create function myNameWithAge (@myAge int=20)
returns nvarchar(200)
as
begin
declare @myName nvarchar(200)
set @myName=N'Arun Kakkar''s age is ' + convert(nvarchar(200),@myage) --If we not use N we got error
return(@myname)
end

select DBO.myNameWithAge(default) as Msg
select ArunDB1.DBO.myNameWithAge(30) as Msg

Alter Function myNameWithAge(@myAge int=30)
returns nvarchar(200)
as
Begin
declare @myName nvarchar(200)
set @myName=N'Arun Kakkar''s Age is=' + convert(nvarchar(200),@myAge)
return (@myName)
end

select DBO.myNameWithAge(29) as Msg

--2. Inline Table valued UDF--------------
Go
if exists(select name from sysobjects where name='myGetFName' and type='IF')
--if object_id('myGetFName','IF') is not null --working fine
drop function myGetFName
Go
create function myGetFName(@myFName nvarchar(200)='F%')
returns table
as
return(select * from myTable1 where FName like @myFName)

--in Select Query, Inline function acts as any table
select * from dbo.myGetFName(default)
select * from dbo.myGetFName('F%1')
select * from mytable1 where fname like 'F%1'

--3. Mulit-Statement Table-Valued UDF ------------------
Go
--if exists(select name from sysobjects where name='myGetFNameWithAge' and type='TF')
if object_Id('myGetFNameWithAge','TF') is not null
drop function myGetFNameWithAge
Go
Create Function myGetFNameWithAge(@myAgeAfterYrs int=20)
returns @myTable table
(
mySNo int Identity primary key ,
FName nvarchar(200) null,
NewAge numeric(4) null
)
as
Begin
insert into @myTable
select Fname,age from myTable1

update @myTable set NewAge= case
when NewAge>=20 and newAge<22 then
@myAgeAFterYrs
else
newAge
end
--return (@myTable)--It gives error
return
End

select * from dbo.myGetFNameWithAge(default)
select * from dbo.myGetFNameWithAge(29)

--***** Begin ****** Triggers **************************
Triggers
1. INSTEAD OF>> INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.
2. AFTER >>AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified only on tables.

Function AFTER trigger INSTEAD OF trigger
Applicability Tables Tables and views
Quantity per table or view Multiple per triggering action (UPDATE, DELETE, and INSERT) One per triggering action (UPDATE, DELETE, and INSERT)
Cascading references No restrictions apply Are not allowed on tables that are targets of cascaded referential integrity constraints.
Execution After:
• Constraint processing
• Declarative referential actions
• inserted and deleted tables creation
• The triggering action Before:
• Constraint processing
In place of:
• The triggering action
After:
• inserted and deleted tables creation
Order of execution First and last execution may be specified Not applicable
text, ntext, and image column references in inserted and deleted tables Not allowed Allowed

1. inserted and deleted Tables>> Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
a. The inserted and deleted tables are used primarily in triggers to:
i. Extend referential integrity between tables.
ii. Insert or update data in base tables underlying a view.
iii. Check for errors and take action based on the error.
iv. Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.
b. The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
c. The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
d. An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
e. Each column in the inserted and deleted tables maps directly to a column in the base table.
f. The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. For example:
i. CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
ii. AS
iii. SELECT EmployeeID, LastName, FirstName
iv. FROM Northwind.dbo.Employees
3. Syntax of Trigger creation
a. CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
--------------------
4. E.g 1 Use a trigger with a reminder message
a. USE pubs
b. --IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL –USED IN SQL Server 2005 onward
c. IF EXISTS (SELECT name FROM sysobjects
d. WHERE name = 'reminder' AND type = 'TR')
e. DROP TRIGGER reminder
f. GO
g. CREATE TRIGGER reminder
h. ON titles
i. FOR INSERT, UPDATE
j. AS RAISERROR (‘you are trying to do some unauthorized work’, 16, 10)
k. GO
5. E.g 2 Use a trigger with a reminder e-mail message
a. CREATE TRIGGER reminder
b. ON titles
c. FOR INSERT, UPDATE, DELETE
d. AS
e. EXEC master..xp_sendmail 'MaryM',
f. 'Don''t forget to print a report for the distributors.'
g. GO
6. E.g 3 Instead of trigger with view
a. CREATE TABLE BaseTable
b. (PrimaryKey int IDENTITY(1,1)
c. Color nvarchar(10) NOT NULL,
d. Material nvarchar(10) NOT NULL,
e. ComputedCol AS (Color + Material)
f. )
g. GO

h. --Create a view that contains all columns from the base table.
i. CREATE VIEW InsteadView
j. AS SELECT PrimaryKey, Color, Material, ComputedCol
k. FROM BaseTable
l. GO

m. --Create an INSTEAD OF INSERT trigger on the view.
n. CREATE TRIGGER InsteadTrigger on InsteadView
o. INSTEAD OF INSERT
p. AS
q. BEGIN
r. --Build an INSERT statement ignoring inserted.PrimaryKey and
s. --inserted.ComputedCol.
t. INSERT INTO BaseTable
u. SELECT Color, Material
v. FROM inserted
w. END
x. GO

y. --A correct INSERT statement supplying dummy values for the
z. --PrimaryKey and ComputedCol columns.
aa. INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
bb. VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
cc. --View the results of the INSERT statement.
dd. SELECT PrimaryKey, Color, Material, ComputedCol
ee. FROM InsteadView
7. E.g 4 used in SQL Server 2005
a. USE AdventureWorks2008R2;
b. GO
c. IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
d. DROP TRIGGER Purchasing.LowCredit;
e. GO
f. This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
g. when the credit rating of the specified vendor is set to 5 (below average).

h. CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
i. AFTER INSERT
j. AS
k. DECLARE @creditrating tinyint, @vendorid int;
l. IF EXISTS (SELECT *
m. FROM Purchasing.PurchaseOrderHeader p
n. JOIN inserted AS i
o. ON p.PurchaseOrderID = i.PurchaseOrderID
p. JOIN Purchasing.Vendor AS v
q. ON v.BusinessEntityID = p.VendorID
r. WHERE v.CreditRating = 5
s. )
t. BEGIN
u. RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
v. ROLLBACK TRANSACTION;
w. RETURN
x. END;
y. GO
8. E.g 5 Using a database-scoped DDL trigger
a. USE AdventureWorks2008R2;
b. GO
c. IF EXISTS (SELECT * FROM sys.triggers
d. WHERE parent_class = 0 AND name = 'safety')
e. DROP TRIGGER safety
f. ON DATABASE;
g. GO
h. CREATE TRIGGER safety
i. ON DATABASE
j. FOR DROP_SYNONYM
k. AS
l. RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
m. ROLLBACK
n. GO
o. DROP TRIGGER safety
p. ON DATABASE;
q. GO
9. e.g. 6. example of a single SQL Server trigger that handles all (3) three conditions of the trigger actions INSERT, UPDATE, and DELETE. This type of interface is sometimes needed when there is both a common configuration and a specific action to occur on an INSERT vs. UPDATE trigger. In these cases, to create a separate trigger for the UPDATE and the INSERT would become maintenance problem.
a. CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable]
ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with caller queries SELECT statements.
-- If an update/insert/delete occurs on the main table, the number of records affected
-- should only be based on that table and not what records the triggers may/may not
-- select.
SET NOCOUNT ON;

--
-- Variables Needed for this Trigger
--
DECLARE @PACKLIST_ID varchar(15)
DECLARE @LINE_NO smallint
DECLARE @SHIPPED_QTY decimal(14,4)
DECLARE @CUST_ORDER_ID varchar(15)
--
-- Determine if this is an INSERT,UPDATE, or DELETE Action
--
DECLARE @Action as char(1)
DECLARE @Count as int
SET @Action = 'I' -- Set Action to 'I'nsert by default.
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
BEGIN
SET @Action = 'D' -- Set Action to 'D'eleted.
SELECT @Count = COUNT(*) FROM INSERTED
IF @Count > 0
SET @Action = 'U' -- Set Action to 'U'pdated.
END

if @Action = 'D'
-- This is a DELETE Record Action
--
BEGIN
SELECT @PACKLIST_ID =[PACKLIST_ID]
,@LINE_NO = [LINE_NO]
FROM DELETED

DELETE [dbo].[MyDataTable]
WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
END
ELSE
BEGIN
--
-- Table INSERTED is common to both the INSERT, UPDATE trigger
--
SELECT @PACKLIST_ID =[PACKLIST_ID]
,@LINE_NO = [LINE_NO]
,@SHIPPED_QTY =[SHIPPED_QTY]
,@CUST_ORDER_ID = [CUST_ORDER_ID]
FROM INSERTED

if @Action = 'I'
-- This is an Insert Record Action
--
BEGIN
INSERT INTO [MyChildTable]
(([PACKLIST_ID]
,[LINE_NO]
,[STATUS]
VALUES
(@PACKLIST_ID
,@LINE_NO
,'New Record'
)
END
else
-- This is an Update Record Action
--
BEGIN
UPDATE [MyChildTable]
SET [PACKLIST_ID] = @PACKLIST_ID
,[LINE_NO] = @LINE_NO
,[STATUS]='Update Record'
WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
END
END
--***** End ****** Triggers **************************

--*****Begin******** VIEW ********************************************************
--VIEW-->>Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:
-----------------------------------------------------------------------------
--To focus, simplify, and customize the perception each user has of the database.
--As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
--To provide a backward compatible interface to emulate a table whose schema has changed.
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ]
}
--------------
--When a view is created, the name of the view is stored in the sysobjects table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the CREATE VIEW statement is added to the syscomments table. This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.
----------------------------------------
--E.G 1 CREATING A SIMPLE view
USE AdventureWorks2008R2 ;
GO
--IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS --used in SQL Server 2000
-- WHERE TABLE_NAME = 'categories')
IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL
DROP VIEW hiredate_view ;
GO
CREATE VIEW hiredate_view
AS
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate
FROM HumanResources.Employee e
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;
GO
------------------------------
--E.G 2 Using built-in functions within a view
USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('Sales.SalesPersonPerform', 'V') IS NOT NULL
DROP VIEW Sales.SalesPersonPerform ;
GO
CREATE VIEW Sales.SalesPersonPerform
AS
SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)
GROUP BY SalesPersonID;
GO
---------------------------
--E.G. 3 Use @@ROWCOUNT function in a view
--This example uses the @@ROWCOUNT function as part of the view definition.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'myview')
DROP VIEW myview
GO
CREATE VIEW myview
AS
SELECT au_lname, au_fname, @@ROWCOUNT AS bar
FROM authors
WHERE state = 'UT'
GO
SELECT * FROM myview
---------------------------------------------
--***** END ******** VIEW ********************************************************

--**************** Introduction to Transaction Logs ***********************************
--Every SQL Server 2005 database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.
--The transaction log supports the following operations:
-------Recovery of individual transactions.
-------Recovery of all incomplete transactions when SQL Server is started.
-------Rolling a restored database, file, filegroup, or page forward to the point of failure.
-------Supporting transactional replication.
-------Supporting standby-server solutions.
--------------------------------------------------------------------

select * from myTable1
--Inserted>>Insert, Update
--Deleted>> Delete, Update
Go
--If exists(select Name from sysobjects where name='myTable1_AfterInsertTrig' and type='TR')
if object_Id('myTable1_AfterInsertTrig','TR') is not null
drop trigger myTable1_AfterInsertTrig
Go
Create Trigger myTable1_AfterInsertTrig
on myTable1
After Insert
as
Begin
Declare @FName nvarchar(200)
Declare @LName nvarchar(200)
Select @FName=FName, @LName=LName from Inserted
Set @FName=@FName + '_InsertTrigger'
insert into myTable1(Fname,Lname) values(@FName,@Lname)
End

insert into myTable1(FName,LName,age) values('Ram','Ram',30)
select * from myTable1

Go
if object_id('myTable1_InsteadOfDelTrig','TR') is not null
--if exists(select name from sysobjects where name='myTable1_InsteadOfDelTrig' and type='TR')
drop trigger myTable1_InsteadOfDelTrig
Go
Create Trigger myTable1_InsteadOfDelTrig
on myTable1
Instead of Delete
as
Begin
Declare @myFName nvarchar(200)
Declare @myLName nvarchar(200)
select @myFName=Fname,@myLName=LName from Deleted
set @myFName=@myFName + '_InsteadOfDelTrigger'
Insert into myTable1(FName,LName) values (@myFName,@myLName)
end

delete from myTable1 where myId=2
select * from myTable1

-- View ------------------
Go
If exists(select name from sysobjects where name='myTable1_View' and type='V')
--if object_id('myTable1_View','V') is not null
drop view myTable1_View
Go
create view myTable1_View
As
select Fname,age from myTable1
---------------
select * from myTable1_View
insert into myTable1_View values('Ram_View',30)
select * from myTable1
--triggeres set for base table also work on views
--insert stmnt apply on view also apply on base table
-------** Begin ************* Try Catch ****************************
-- Note: Try catch mentioned below work in SQL Server 2005
Go
if exists(select name from sysobjects where name='mySP_ErrorHandler' and type='P')
--if object_Id('mySP_ErrorHandler','P') is not null
Drop proc mySP_ErrorHandler
Go
create proc mySP_ErrorHandler
@myNumerator int=1, @myDenominator int=0
As
Begin
Begin try
--print @myNumerator/@myDenominator
select @myNumerator/@myDenominator as myDivisionResult
End try
Begin Catch
select Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_Procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
End Catch
End

exec mySP_ErrorHandler
exec mySP_ErrorHandler 5,2
exec mySP_ErrorHandler 5,0

Due to lack of time & of course some laziness, I would not able to complete this post in proper manner.
But as soon as I got opportunity, I would update this post as I think it would be very necessary to update this post to make it more understandable, readable etc.

Wish you a very Happy Programming.

How to Revise Basic OOPs concepts in C Sharp.Net (Summary only)

2011-07-12T01:47:00.000-07:00

RAM RAM Friends

How to Revise Basic OOPs concepts in C Sharp.Net (only syntax and concepts but very less or no description) ) like (types of Constructors,Inheritance,Polymorphism,Abstract Class,Interface,Delegate,Events,Sealed Classes,Overriding, etc)

Here I try to explain different OOPs concepts in C#.NET using examples only with very less description as it is only for revision purpose (both syntax & concept).

Below I have shown my sample console program to use different OOPs concepts

*********ArunKakkarConsoleApp1************************

using System;
using System.Collections.Generic;
using System.Text;

using System.Text.RegularExpressions;

//Using Events Method 1 (a) -->>by Inheriting ArrayList
//below namespace is used form Event learning in c#.Net
namespace myListNamespace
{
using System.Collections;

//step 1. delegate type for hooking up change (event) notification
public delegate void myDelChangedEventHandler(object sender,EventArgs e);

public class myListClass : ArrayList
{
//step 2. declare event that clients can be used to notified
//--------whenever the event raised (here elements of list change)
public event myDelChangedEventHandler myEventChanged;

//step 3. Raise Event (here whenever elements of list changes)
protected virtual void myEventRaisedOnChanged(EventArgs e)
{
if (myEventChanged != null)
myEventChanged(this, e);
}

//step 4. create such methods etc which may raise the event
//below are methods(add,clear,this) which r override as they may change the list
//& in each method event is invoked
public override int Add(object value)
{
int i= base.Add(value);
myEventRaisedOnChanged(EventArgs.Empty);
return i;
}
public override void Clear()
{
base.Clear();
myEventRaisedOnChanged(EventArgs.Empty);
}
public override object this[int index]
{
get
{
return base[index];
}
set
{
base[index] = value;
myEventRaisedOnChanged(EventArgs.Empty);
}
}
}
}
namespace ArunKakkarConsoleApp1
{
//below namespace is used to learn Events in C#.Net
using myListNamespace;

//Different Types of Constructor
public class InstanceConstructor
{
private int x, y;
public InstanceConstructor()
{
x = 0;
y = 0;
Console.WriteLine("in Parameterless contructor,x={0}, y={1}", x, y);
}
public InstanceConstructor(int i)
{
x = i;
y = 0;
Console.WriteLine("In single parameter constructor, x={0}, y={1}", x, y);
}
public InstanceConstructor(int i, int j)
{
this.x = i;
this.y = j;
Console.WriteLine("In double parameter constructor, x={0}, y={1}", x, y);
}
~InstanceConstructor()
{
Console.WriteLine("Destructor of class InstanceConstructor is called");
Console.ReadLine();
}
}
public class myDefaultConstructorClass
{
private int _x;
private string _s;
public int x
{
get
{
return _x;
}
set
{
_x = value;
}
}
public string s
{
get
{
return _s;
}
set
{
_s = value;
}
}
public void myShow()
{
//if (_x==null )
// Console.WriteLine("X is null");
//if(_x != null )
Console.WriteLine("x is not null ,x={0}",_x);
}
}
public class myPrivateConstructor
{
public const double pi = 3.14;
private myPrivateConstructor()
{
}
}
public class myStaticConstructorClass
{
//Static constructor :
//Static Constructors are used to instantiate static data fields in the class. Static Constructors are called only once no matter how many instances you create for that class.
//Static Constructors are executed before any other type of constructor. Static Constructors cannot be called explicitly. They are automatically invoked when
//An instance of the class is created
//Any of the static members of the class are referenced
//It is a compile time error to have access modifiers for static constructors.

public int j;
public static int i;

public myStaticConstructorClass()
{
j = 25;
Console.WriteLine("\n Normal Instance constructor of myStaticConstructorClass is invoked");
}

static myStaticConstructorClass()
{
i = 20;
Console.WriteLine("\n static constructor of myStaticConstructorClass is invoked");
}
}

//Static Class
public static class myStaticClass1
{
public static int myVal;
//we can declare only static constructor in a static class
//public myStaticClass1()

//we can not use public etc modifire on the static class constructor
//public static myStaticClass1()

//not mandatory to declare constructor for a static class i.e. we can use class without any constructor
static myStaticClass1()
{
myVal = 30;
}
public static void myShow()
{
Console.WriteLine("\n Hello it is myShow() method of myStaticClass1");
}
//we can declare only static members in a static class
//public void myShow2()
//{ Console.WriteLine("\nHello it is myShow2() method of myStaticClass1"); }
}

//Polymorphism & Inheritance (Virtual->Override, Virtual->Override->Sealed, on creating instance of child class,sequence of constructor invoked 1. GrandParent 2.Parent 3.Child)
public class myParent
{
private string s;
//protected myParent()
public myParent()
{
s=@"This is a string from parent class have both \ and & signs";
Console.WriteLine("This is myParent class constructor");
}

public string ParseBackSlash()
{
string s1;
s1=s;
s1=s1.Replace("\\",string.Empty);
return s1;
}
public virtual void ShowMsg()
{
Console.WriteLine("This is showmsg from myParent class");
}
public virtual void myShowMsg5()
{
Console.WriteLine("this is myShowMsg5 method of myParent class");
}
}
public class myChild1 : myParent
{
public string ParseAmpersand()
{
string s2;
s2 = base.ParseBackSlash();
s2 = s2.Replace("&", string.Empty);
return s2;
}
public override void ShowMsg()
{
Console.WriteLine("This is showmsg from myChild1 class");
}
public sealed override void myShowMsg5()
{
//base.myShowMsg5();
Console.WriteLine("This is override sealed method myShowMsg5 which is originally of myParent class");

}
}
public class myChild2 : myParent
{
public myChild2()
{
Console.WriteLine("This is myChild2 class constructor");
}

public override void ShowMsg()
{
//base.ShowMsg();
Console.WriteLine("this is ShowMsg method of myChild2 class");
}

public void myShowAns()
{
Console.WriteLine("This is myShowAns method of myChild2 class");
}
}
public class myGrandChild1 : myChild2
{
public myGrandChild1()
{
Console.WriteLine("This is constructor of myGrandChild1 class, its parent class is myChild2, its grandParentClass is myParent");
}
}

//Interface handling
public interface myInterface_1
{
//i/f can't contain fields
//protected int x;
string id
{
get;
set;
}

void myShowResult();

//if we declare this here & do not implement it & use i/f then show compile time error
//void myWish();
}
interface myInterface_2
{
void myShowResult();
}
public class myClassUsingInterface1 :myInterface_1
{
protected string x;
public string id
{
get
{
return x;
}
set
{
x = value;
}
}
public myClassUsingInterface1()
{
Console.WriteLine("this is constructor of myClassUsingInterface1");
}
public void myShowResult()
{
Console.WriteLine("This is myshowResult of myInterface_1 implement by myClassUsingInterface1");
}
}
public class myClassUsingInterface2 : myInterface_1, myInterface_2
{
string x;
public string id
{
get
{
return x;
}
set
{
x = value;
}
}
public void myShowResult()
{
Console.WriteLine("This is myShowResult method which is common in both myInterface_1 & myInterface_2 implement by myClassUsingInterface2");
}
}
//Explicitly members of interfaces
public class myClassUsingInterface3 : myInterface_1, myInterface_2
{
string y;
public string id
{
get
{
return y;
}
set
{
y = value;
}
}

//Explicitly members of interfaces>>>> we can't use public etc keywords
void myInterface_1.myShowResult()
{
Console.WriteLine("This is myInterface_1.myShowResult() method called");
}
void myInterface_2.myShowResult()
{
Console.WriteLine("This is myInterface_2.myShowResult() method called");
}
}

//Abstract Class
public abstract class myAbstractClass
{
public void myShowMessage()
{
Console.WriteLine("my showmsg method of myAbstractClass class");
}
public abstract void myShowMessage2();
}
public class myChild3 : myAbstractClass
{
public void myShowMessage3()
{
base.myShowMessage();
Console.WriteLine("This is myShowMessage3 method of myChild3 class");
}
public override void myShowMessage2()
{
//base.myShowMessage2();
Console.WriteLine("This is from myShowMessage2 override by myChild3 class");
}
}

//Sealed Classes
public sealed class myChild4 : myChild3
{
public int myAddNumbers(int x, int y)
{
return x + y;
}
public void myShowMsg4()
{
Console.WriteLine("this is myShowMsg4 from sealed class myChild4");
}

public void myShowMsg5(int i)
{
Console.WriteLine("myShowMsg5 called with i=" + i);
}
public void myShowMsg6(int j)
{
Console.WriteLine("myShowMsg6 called with j={0}", j);
}
}

//5. Delegates & events
//(publisher [who declare/create event & told all subscriber that a particular event is raised]
//& subsriber(who write code for event handler))

//Using Events Method 1 (b) -->>by Inheriting ArrayList
public class myEventListner
{
//step 5. class that listen the events raised by event class
private myListClass myList;

//step 6. create method that acts as event handler
//called whenever list changes
private void myListChanged(object sender, EventArgs e)
{
Console.WriteLine("myListChanged method is called when event(change in list) fired");
}
public myEventListner(myListClass myL1)
{
myList = myL1;
//step 7. add events
myList.myEventChanged += new myDelChangedEventHandler(myListChanged);
}
public void myDetach()
{
//step 8. Detach the event & delete the list
myList.myEventChanged -= new myDelChangedEventHandler(myListChanged);
myList = null;
}
}

//Using Events Method 2 -->> Events as Publisher & Subscriber
public class myPublisher
{
//using System.Windows.Forms
public delegate void myDelegate2(object from, EventArgs args);
public event myDelegate2 myEvent;
public void myRaiseMyEvent(EventArgs args)
{
myEvent(this, args);
}

public void SendTheEvent()
{
Console.WriteLine("The event is fired here in SendTheEvent method of publisher class");
this.myRaiseMyEvent(new EventArgs());
}
}
public class mySubscriber
{
private void myHandleMyEvent(object sender, EventArgs args)
{
Console.WriteLine("myEvent is handled in the myHandleMyEvent of the mySubscriber class");
Console.WriteLine("who is the sender ? " + sender.GetType());
}
public mySubscriber()
{
myPublisher p1 = new myPublisher();
p1.myEvent += new myPublisher.myDelegate2(myHandleMyEvent);
p1.SendTheEvent();
}
}

//Using Events Method 3 --->> Event using built-in event delegate "EventHandler"
public class myEventPublisherWithDefaultDelegate
{
//For events that do not use any additional information, the .NET Framework has already defined an
//appropriate delegate type: EventHandler.

//If we not use EventHandler keyword, we need to define a Delegate(say myEvent2Delegate) eqvivalent to EventHanler
private string _myVal;
public event EventHandler myEvent2;
public void myEvent2Raiser(EventArgs e)
{
myEvent2(this, e);
}
public string myVal
{
get
{
return _myVal;
}
set
{
_myVal = value;
this.myEvent2Raiser(new EventArgs());
}
}
}
public class myEventSubscriberWithDefaultDelegate
{
public void myEvent2Handler(object sender, EventArgs e)
{
Console.WriteLine("\nThis is myEvent2Handler called since Event myEvent2 is raised");
Console.WriteLine("\n It is called by = " + this.GetType());
}
public myEventSubscriberWithDefaultDelegate()
{
myEventPublisherWithDefaultDelegate p1 = new myEventPublisherWithDefaultDelegate();
p1.myEvent2 +=new EventHandler(this.myEvent2Handler);
p1.myVal ="ram Ram";
p1.myVal +=" Hare Ram";
}
}

class Program

{

//Delegate Handling ( Single Cast, MultiCast )

public delegate int myDel(int a, int b);

public delegate void myDel2();

public delegate void myMultiCastDel(int m);

static void Main(string[] args)

{

//------------------------ Topics ---------------------------------------------------------------

//OOPS CONCEPTS

//1. Types of Constructor (a. Default, b. Instance, c. Private, d. Static) & Destructor

//2. Types of Classes

//a. Static,

//b. Abstract {(abstract & override) & abstract methods},

//c. Sealed (can not be inherited) & sealed override methods

//complete syntax of a class with all options explained

//3. Inheritance (Virtual & override), properties, static variables, private,protected,public variables etc

//4. Polymorphism, Early/Static/CompileTime Binding & Late/Dynamic/Runtime Binding

//5. Interface & Explicitly declaration

//6. Delegate (Signle Cast & MultiCast)

//7. Events

//-------------------------------

//8. Regular Expression

//9. Number To Words

//10.Webservice

//-------------------------------------------------------------------------------------

//1(a). Type of Constructors-->Instance Constructor

InstanceConstructor ic1 = new InstanceConstructor();

InstanceConstructor ic2 = new InstanceConstructor(3);

InstanceConstructor ic3 = new InstanceConstructor(4,5);

//Destructor

ic1 = null;

GC.Collect(); //Call Garbage collector

//1(b). Type of Constructors-->Default Constructor

myDefaultConstructorClass dc1 = new myDefaultConstructorClass();

Console.WriteLine(" before giving values, x={0},s={0}", dc1.x, dc1.s);

dc1.myShow();

dc1.x = 25;

dc1.s = "Arun Kakkar";

Console.WriteLine("After giving values, x={0},s={1}", dc1.x, dc1.s);

dc1.myShow();

//1(c). Type of Constructors-->Private constructor

Console.WriteLine("private class value, x=" + myPrivateConstructor.pi);

Console.ReadLine();

//1(d). Type of Constructors-->Static Constructor

Console.WriteLine("mystaticConstructorClass class value, x={0}", myStaticConstructorClass.i);

Console.ReadLine();

myStaticConstructorClass scs1=new myStaticConstructorClass();

Console.WriteLine("\n j={0} and i={1} in myStaticConstructorClass ",scs1.j ,myStaticConstructorClass.i);

Console.ReadLine();

//2(a). Types of Classes---> Static Class

myStaticClass1.myShow();

Console.WriteLine("\n MyVal of myStaticClass1 is=" + myStaticClass1.myVal);

myStaticClass1.myVal = 23;

Console.WriteLine("\n MyVal after updation of myStaticClass1 is=" + myStaticClass1.myVal);

//2(b). Types of Classes---> Abstract class

myChild3 ch3 = new myChild3();

//string s1 = "below";

//s1 = "ch3.myShowMessage=" + ch3.myShowMessage() + "\n";

//s1 += "ch3.myShowMessage2=" + ch3.myShowMessage2() + "\n";

//s1 += "ch3.myShowMessage3=" + ch3.myShowMessage3();

Console.WriteLine("various functions called by myChild3 class of parent abstract class are as below \n");

ch3.myShowMessage();

ch3.myShowMessage2();

ch3.myShowMessage3();

//2(c). Types of Classes---> Sealed classes

//(can not be inherited)

//A sealed class can not be used as "base class"

//A sealed class can not be an abstract class.

//sealing a class means one(class) can not be driven(drived) from it

// In C# structs are implicitly sealed.

//In C# methods can't be declared as sealed but when we override a method(in derived class),

//we can declared the overriden method as sealed thus we cannot override this method further

//public virtual void m1(); in any parent class

//public override sealed void m1() in the child class

myChild4 ch4 = new myChild4();

ch4.myShowMsg4();

//override Sealed method

myChild1 ch1 = new myChild1();

ch1.myShowMsg5();

//3. Inheritance (Virtual & override), properties, static variables, private,protected,public variables etc ***************

//1. when we create child class instance, then first parent class constructor is called then child class constructor is called

// & they (Constructors) are called in order from top to down sequentially.

//2. "this" keyword show all methods,etc of both current class & all its parent class >>>this.myShowMsg()

// but "base" keyowrd show methods of only parent class >>>base.myShowMsg()

//3. if u don't give any modifiers to constructor,variables they are "private" by default

//4. if both current class & parent class have same name funtion without using "virtual" keyword in the parent, we get compile time error

myChild1 c1 = new myChild1();

c1.ShowMsg();

Console.WriteLine("the parsed string is=" + c1.ParseAmpersand());

myChild2 c2 = new myChild2();

c2.myShowAns();

myGrandChild1 mgc1 = new myGrandChild1();

mgc1.myShowAns();

mgc1.ShowMsg();

//4. Polymorphism, Early/Static/CompileTime Binding & Late/Dynamic/Runtime Binding

//Polymorphism=>Through inheritance, a class can be used as more than one type;

// it can be used as its own type, any base type, or any interface type if it

//implements interface. This is called Polymorphism.

myParent myP1 = new myParent();

myChild1 myCh1 = new myChild1();

myChild2 myCh2 = new myChild2();

//Early/Static/CompileTime binding

Console.WriteLine("\n Below are Early Binding ShowMsg calls from myparent,child1,child2 classes");

myP1.ShowMsg();

myCh1.ShowMsg();

myCh2.ShowMsg();

//Late/Dynamic/Runtime binding

myParent[] myP2 = new myParent[3];

myP2[0] = new myParent();

myP2[1] = new myChild1();

myP2[2] = new myChild2();

Console.WriteLine("\n Below are Late Binding ShowMsg calls from myparent,child1,child2 classes");

for (int i = 0; i <= 2; i++)

{

myP2[i].ShowMsg();

}

//below can be used instead of above loop

//myP2[0].ShowMsg();

//myP2[1].ShowMsg();

//myP2[2].ShowMsg();

//5. Interface & Explicitly declaration

//1. all methods of i/f must be implement by class implement i/f else get compile time error

//2. inside i/f>> void myShowMsg(); is enough i.e. no need to be public etc

myClassUsingInterface1 cui1 = new myClassUsingInterface1();

cui1.myShowResult();

cui1.id = "ram ram";

Console.WriteLine("Value of cui1.id=" + cui1.id);

myClassUsingInterface2 cui2 = new myClassUsingInterface2();

cui2.myShowResult();

//Explicitly members of interfaces

myClassUsingInterface3 myif3 = new myClassUsingInterface3();

myif3.id = "ram ram ji from myIf3";

Console.WriteLine("calling myif3.id=" + myif3.id);

myInterface_1 myinterface1 = (myInterface_1)myif3;

myInterface_2 myinterface2 = (myInterface_2)myif3;

myinterface1.myShowResult();

myinterface2.myShowResult();

Console.WriteLine("calling myinterface1.id={0}", myinterface1.id);

//6. Delegate (Signle Cast & MultiCast)

//delegate are derived from System.Delegate

//below line declared above the void main method

//public delegate int myDel(int a, int b);

myDel d1=new myDel(ch4.myAddNumbers);

Console.WriteLine("addition of 20 & 30 using delegates is=" + d1(20, 30));

//SingleCast delegate

myDel2 d2 = new myDel2(ch4.myShowMessage);

Console.WriteLine("void method using delegate is=\n");

d2();

//MultiCast Delegate

//call more than one kind of method in sequence but problem is only same value of parameter is passed in all methods

Console.WriteLine("Below line show multicast delegate result\n");

myMultiCastDel d3 = new myMultiCastDel(ch4.myShowMsg5);

d3 += new myMultiCastDel(ch4.myShowMsg6);

d3(3);

//7(a). Events --->Method 1

//Event handling using UserDefined EventDelegate

Console.WriteLine("\nBelow line show delegate and events combination result");

mySubscriber sub1 = new mySubscriber();

//7(b). Events --->Method 2

//Event Handling using Default (DotNet framework provided EventDelegate named 'EventHandler')

Console.WriteLine("\nBelow line shows event handling with default in-built EventDelegate named 'EventHandler'");

myEventSubscriberWithDefaultDelegate mySub1 = new myEventSubscriberWithDefaultDelegate();

//7(c). Events --->Method 3

//create new list to test Events in C#.Net

myListClass myL = new myListClass();

//create class that listen event (whenver list item changed) of list

myEventListner myE = new myEventListner(myL);

//Add & remove items from the list

Console.WriteLine("\nThis line before any event is fired in list class");

myL.Add("item 1");

Console.WriteLine("\nThis line after adding item 1 in list class");

myL.Add("item 2");

Console.WriteLine("\nThis line after adding item 2 in list class");

myL.Clear();

Console.WriteLine("This line after clearing all items in list class");

myE.myDetach();

Console.WriteLine("This line after detaching event from the myEventLister class");

//8. Regular Expression

Console.WriteLine("Please enter any alphanumeric word only it can include underscore(_) also=");

string s = Console.ReadLine();

string r = @"^[\w]+$";

if (Regex.IsMatch(s, r))

{

Console.WriteLine("You have entered an alphanumeric word=" + s);

}

else

{

Console.WriteLine("Its a wrong word please enter only Alphanumeric word");

}

Console.ReadLine(); // just like getch() in C++

//10.Webservice calling --Webservice1

localhostAK.ArunKakkarwsCustomers myWS = new localhostAK.ArunKakkarwsCustomers();

Console.WriteLine("customer name from WebService localhostAK.ArunKakkarwsCustomers is {0}", myWS.GetCustomerName(2));

//webservice 2

AKWebService_1.ArunKakkarWebServices akws1 = new AKWebService_1.ArunKakkarWebServices();

Console.WriteLine("Addition of {0} and {1} from WebService AKWebService_1.ArunKakkarWebServices is {2}",5,9, akws1.Add2IntNo(5,9));

//globalweather_NetWS webservice 3 download from net with link http://www.webservicex.com/globalweather.asmx?WSDL

string myCountry;

GlobalWeather_NetWS.GlobalWeather gw = new GlobalWeather_NetWS.GlobalWeather();

Console.WriteLine("\nEnter country name to get list of its all cities");

myCountry = Console.ReadLine();

//it return a dataset in xml format

Console.WriteLine("\n Various cities in {0} are {1}", myCountry, gw.GetCitiesByCountry(myCountry));

Console.ReadLine();

}

I hope, it would help those who want to just quickly revise OOPs concepts (both syntax & use).

Due to lack of time, I did not provide, description of various terms.

Wish you best of luck for good programming.

How to convert number into words in C Sharp.Net

2011-04-25T05:08:00.000-07:00

Ram Ram Friends

Well there are many different great algos (easy & small) to achieve this.
Below is the one of them.

Following is the sample code in C#.Net to convert number into words
Since, I am Indian, so as per Indian requirement, I made this program to suit our basic needs,
But as this algo is very clear & easy to understand, you can convert it very easily by making very few changes to suit your culture/needs.
This algo basically more focus on using strings (very less Mathematical formulas/tricks) to achieve the goal, this make it more simpler & easy to convert it into any programming language.

using System;
namespace ArunKakkarConsoleApp1
{
public class AK_NumberToWords
{
public string GetDigitInWords(int i)
{
if (i < 1)
return "";//"zero";
string s="";
if (i.ToString().Length > 2)
{
i = Convert.ToInt32(i.ToString().Substring(i.ToString().Length - 2, 2));
}
string[] s1 = new string[] { "", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen" };
string[] s2 = new string[] { "","", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety" };
string s3, s4;
if (i < 20)
s = s1[i];
else
{
s3 = i.ToString().Substring(0, 1);
s = s2[Convert.ToInt32(s3)];
s4 = i.ToString().Substring(1, 1);
s = s + " " + s1[Convert.ToInt32(s4)];
}
/*switch (i) //you can use this switch statement if u don't like to use array
{
case 1:
s = "one";
break;
case 2:
s = "two";
break;
case 3:
s = "three";
break;
case 4:
s = "four";
break;
case 5:
s = "five";
break;
case 6:
s = "six";
break;
case 7:
s = "seven";
break;
case 8:
s = "eight";
break;
case 9:
s = "nine";
break;
case 10:
s = "ten";
break;
case 11:
s = "eleven";
break;
case 12:
s = "twelve";
break;
case 13:
s = "thirteen";
break;
case 14:
s = "fourteen";
break;
case 15:
s = "fifteen";
break;
case 16:
s = "sixteen";
break;
case 17:
s = "seventeen";
break;
case 18:
s = "eighteen";
break;
case 19:
s = "nineteen";
break;
default:
string s2;
s2 = i.ToString().Substring(0, 1);
switch (Convert.ToInt32(s2))
{
case 2:
s = "twenty";
break;
case 3:
s = "thirty";
break;
case 4:
s = "fourty";
break;
case 5:
s = "fifty";
break;
case 6:
s = "sixty";
break;
case 7:
s = "seventy";
break;
case 8:
s = "eighty";
break;
case 9:
s = "ninety";
break;
}
s2 = i.ToString().Substring(1, 1);
s = s + " " + this.GetDigitInWords(Convert.ToInt32(s2));
break;
}*/
return s;
}
public string GetNoInWords(double d)
{
string s="";
//19,23,45,67,89,12,34,56,789.87
//Rs ....., 67 Kharab,89 Arab,12 crore, 34 lacs, 56 thousands,7 hundred, 89 and 87 paise only
//67,89,12,34,56,789.87
//Rs 67 Kharab,89 Arab,12 crore, 34 lacsh, 56 thousands,7 hundred, 89 and 87 paise only
string sPaise,sTens, sHundred, sThousand, sLacs, sCrore, sArab, sKharab, sLong;
int i;
sLong = ((long)d).ToString();
if (sLong.Length > 13)
{
//s = "Out of range value and maximum value is= ";
sLong = sLong.Substring(sLong.Length - 13, 13);
}
if(sLong.Length <13)
sLong =sLong.PadLeft(13,'0');
//Console.WriteLine("\nslong=" + sLong);
sKharab = sLong.Substring(0, 2); //Kharab
sArab = sLong.Substring(2, 2); //arab
sCrore = sLong.Substring(4, 2); //Crore
sLacs = sLong.Substring(6, 2); //Lacs
sThousand = sLong.Substring(8, 2); //thousands
sHundred = sLong.Substring(10, 1); //Hundred
sTens = sLong.Substring(11, 2); //Tens
if (d.ToString().Contains("."))
sPaise = d.ToString().Substring(d.ToString().IndexOf('.')+1);
else
sPaise = "0";
if (sPaise.Length>2)
sPaise = sPaise.Substring(0, 2);
i = Convert.ToInt16(sKharab);
if (i > 0)
s = this.GetDigitInWords(i) + ((i>1)? " Kharabs " : " Kharab ");
i = Convert.ToInt16(sArab);
if (i > 0)
s += this.GetDigitInWords(i) + ((i>1)?" Arabs " : " Arab ");
i = Convert.ToInt16(sCrore);
if (i > 0)
s += this.GetDigitInWords(i) + ((i>1)?" Crores " : " Crore ");
i = Convert.ToInt16(sLacs);
if (i > 0)
s += this.GetDigitInWords(i) + ((i>1)? " Lacs ": " Lac ");
i = Convert.ToInt16(sThousand);
if (i > 0)
s += this.GetDigitInWords(i) + ((i>1)? " Thousands " : " Thousand ");
i = Convert.ToInt16(sHundred);
if (i > 0)
s += this.GetDigitInWords(i) +((i>1)? " Hundreds ": " Hundred ");
i=Convert.ToInt16(sTens);
if (i > 0)
s += this.GetDigitInWords(i) + " ";
i = Convert.ToInt16(sPaise);
if (i > 0)
s += " and " + this.GetDigitInWords(i) + ((i>0)? " Paises ": " Paise ");
s += "only";
return s;
}//public string GetNoInWords(double d)

//Below is using Math logic

//I tried to give example at almost each step to understand logic

public String GetDigitInWordsByMath(int i)
{
string s="";
int j, k;
string[] s1 = new string[] { "", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "tweleve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen" };
string[] s2 = new string[] { "", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety" };
if (i < 20)
s = s1[i];
else
{
j = i / 10; //tens digit
k = i % 10; //unit digit
s = s2[j] + " " + s1[k];
}
return s;
}
public string GetNumInWordsByMath(double d)
{
//19,23,45,67,89,12,34,56,789.87
//Rs ....., 67 Kharab,89 Arab,12 crore, 34 lacs, 56 thousands,7 hundred, 89 and 87 paise only
//67,89,12,34,56,789.87
//Rs 67 Kharab,89 Arab,12 crore, 34 lacs, 56 thousands,7 hundred, 89 and 87 paise only
string s="";
long myNum;
int iPaise, iDigit, iHundred, iThousand, iLac, iCrore, iArab, iKharab;
//let d=1234567898765.43
// 25%10=5 (return Remainder) applicable in Integer only
// 25/10=2 (return Quotient) applicable in Integer only
myNum = (long)(d*100); //myNum=1234567898765.43*100 =123456789876543
iPaise = (int)(myNum % 100); //iPaise=123456789876543%100 =43
myNum = myNum / 100; //myNum=123456789876543/100 =1234567898765
iDigit = (int)(myNum % 100); //iDigit=1234567898765 %100 =65
myNum = myNum / 100; //myNum =1234567898765 /100 =12345678987
iHundred =(int)( myNum % 10); //iHundred=12345678987 %10 =7
myNum = myNum / 10; //myNum =12345678987/10 =1234567898
iThousand = (int)(myNum % 100); //iThousand=1234567898%100 =98
myNum = myNum / 100; //myNum=1234567898/100 =12345678
iLac = (int)(myNum % 100); //iLac=12345678%100 =78
myNum = myNum / 100; //myNum=12345678/100 =123456
iCrore = (int)(myNum % 100); //iCrore=123456%100 =56
myNum = myNum / 100; //myNum=123456/100 =1234
iArab = (int)(myNum % 100); //iArab=1234%100 =34
myNum = myNum / 100; //myNum=1234/100 =12
iKharab = (int)(myNum % 100); //iKharab=12%100 =12
myNum = myNum / 100; //myNum=12/100 =0
if (iKharab > 0)
s = this.GetDigitInWordsByMath(iKharab) + ((iKharab > 1) ? " Kharabs " : " Kharab ");
if (iArab > 0)
s += this.GetDigitInWordsByMath(iArab) + ((iArab > 1) ? " Arabs " : " Arab ");
if (iCrore > 0)
s += this.GetDigitInWordsByMath(iCrore) + ((iCrore > 1) ? " Crores " : " Crore ");
if (iLac > 0)
s += this.GetDigitInWordsByMath(iLac) + ((iLac > 1) ? " Lacs " : " Lac ");
if (iThousand > 0)
s += this.GetDigitInWordsByMath(iThousand) + ((iThousand > 1) ? " Thousands " : " Thousand ");
if (iHundred > 0)
s += this.GetDigitInWordsByMath(iHundred) + ((iHundred > 1) ? " Hundreds " : " Hundred ");
if (iDigit > 0)
s += this.GetDigitInWordsByMath(iDigit) + " ";
if (iPaise > 0)
s += "and " + this.GetDigitInWordsByMath(iPaise) + ((iPaise > 1) ? " Paises " : " Paise ");
s= "Rs " + s + "only";
return s;
}

} //class
class Program
{
static void Main(string[] args)
{
AK_NumberToWords nw1 = new AK_NumberToWords();
string myAns,myVal;
do
{
Console.WriteLine("Please enter 2 digit no to get its value in words");
myVal = Console.ReadLine();
Console.WriteLine("{0} in words is={1}", myVal, nw1.GetDigitInWords(Convert.ToInt32(myVal)));
Console.WriteLine("\nPlease enter any number to get its value in words:");
myVal = Console.ReadLine();
Console.WriteLine("{0} in words is=\n {1}", myVal, nw1.GetNoInWords(Convert.ToDouble(myVal)));
Console.WriteLine("\nPlease enter any number to get its value in words using math logic:");
myVal = Console.ReadLine();
Console.WriteLine("{0} in words is=\n {1}", myVal, nw1.GetNumInWordsByMath(Convert.ToDouble(myVal)));
Console.WriteLine("Do u want to try more?(Y/N)");
myAns = Console.ReadLine();
} while (myAns == "Y" || myAns == "y");
}//class program
}//namespace

Friends, as I already told, it is just one of many different algo used to convert number into words.

Of course, there are lot of ways to minimize/modify it to get more accuracy, speed, & less memory consumption etc.

I hope it would help you.

Wish you a very happy programming.

How to make TRIAL version( Date based) section in Window Application Software

2011-04-21T03:32:00.000-07:00

RAM RAM Friends

How to make TRIAL version (Date based) section in Window Application Software

Hi Friends, when I used to learn how to make complete software during my MCA, I face lot of problem to find a way to provide Trial version option in my projects so that they would run in trial mode for some time/days & after that they need registration to continue.

Now I am providing here a way to resolve such problem by one way out of many possible ways & this is

Trial Version (Date based).

First I had mentioned the algo to explain its working then
I gave code to implement it in VB6.0 &
Then code in VB.NET

Here it goes

1. ALGO

({[EVALUATION/DEMO/TRIAL version Management Plan]})

Return “AK_Expired” key from registry
If AK_Expired=false or 1 (or any other value indicating evaluation expired)

Disable evaluation button
Show message “Evatuation period expired etc”

Else //if AK_Expired=true or 0 (or any other value indicating evaluation not expired)

Search AK_StartDt.akk (or any other name you like) present in computer “Windows” Directory (or any other place in computer you like)
If AK_StartDt.akk file not present in computer “Windows” directory

Create new file AK_StartDt.akk in computer “Windows” Directory & write current date in this file.
Create another file AK_EndDt.akk in computer “Window” directory & write new date which is equal to (current date + 31 days).
Enter current date in “AK_LastUsedDt” key in registry
Display label “Demo Version........ Just for 31 Days.”
Enable evaluation button.

Else if AK_StartDt.akk file present in computer “Windows” Directory

Return FirstDate from AK_StartDt.akk file
Return LastDate from AK_EndDt.akk file
Return LastUseDate from “AK_LastUsedDt” key in registry
//first level of security for evaluation
If LastUseDate=” ”

enter current date in “AK_LastUsedDt” key in registry

Else if LastUseDate is a date

if LastUseDate>current date

display message “Evaluation Expired …. Contact ABC”
disable “Evaluation” button
enter False or 1 in “AK_Expired” key in registry

else //if LastUseDate<=current date

enter current date in “AK_LastUsedDt” key in registry

end if

Else // if LastUseDate is other than date or “ ”

display message “Evaluation Expired …. Contact ABC”
disable “Evaluation” button
enter False or 1 in “AK_Expired” key in registry

End if // If LastUseDate=” ”
//Second Level of security for evaluation
If currend date < FirstDate or current date >LastDate

display message “Evaluation Expired …. Contact ABC”
disable “Evaluation” button
enter False or 1 in “AK_Expired” key in registry

Else

Show message “only (31-(current date-FirstDate)) days are remaining”

End if // If current date < FirstDate or current date >LastDate

End if // If AK_StartDt.akk file not present in computer “Windows” directory

End if // If AK_Expired=false or 1

Note:

1. I have used following keys to store information in Registry

1. AK_Expired 2. AK_LastUsedDt

2. I have used following files to store information in Windows directory

1. AK_StartDt.akk 2. AK_EndDt.akk

3 To provide more security, you can use any other location or any other keyname/file name & write encrypted information there.

DRAWBACKS

As almost all things in universe have few or more drawbacks, this algo also has one drawbacks

If after installing & using your software at least one time, user changes his/her system date & then open your software then your software shows

"Trial version expired..............."

SOLUTION ---------

Since there is no proper solutions, but you can make a small exe which would erase all/few of the evaluation securities or correct them to activate your software in evaluation mode.

since one can use the same exe more than one time, so to protect it, you can use either pwd (hard coded or dynamic(based on current date,time,etc), or do some coding so that this file execute only once in its life cycle etc.

since sometime after using your evaluation version, user can ask you after some time(days/months/yrs) that he want to use it again or there may be some other porblem, the above solution would be useful in such cases also.

VB6.0 Code to implement the above algo

‘ Below is the api function must write in module1 or somewhere else

Public Declare Function GetWindowsDirectory Lib "kernel32.dll" _
Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Integer) As Long

Note:-----Below procedure must be put in either startup procedure of project or in load event of evaluation form
‘get the Window directory path
strTemp = String$(255, Chr(0))
lngRetVal = GetWindowsDirectory(strTemp, Len(strTemp))
strTemp = Replace(strTemp, Chr(0), "")
‘Get Hard drive serial no to act as Project password. You can encrypt it to make software more secure
'--Begin-------how to get Hard drive serial no via vb6------------------
Dim FS As FileSystemObject
Set FS = New FileSystemObject
Dim d As Drive
Set d = FS.GetDrive("C:")
GValidNo = Abs(d.SerialNumber)
'--End-------how to get Hard drive serial no via vb6------------------
‘Get Project password (here Hard drive Serial No) from registry
mactno = GetSetting("ArunKakkarCompany ", " AK_Proj1", "AK_MacPwd")
‘Check if software is registered or not
If mactno = GValidNo Then

‘below are the various controls on the evaluation form
Me.Frame1.Visible = False
Me. lblMsg.Visible = True

Else

If GetSetting("ArunKakkarCompany ", " AK_Proj1", " AK_Expired ") <> "1" Then

If FS.FileExists(strTemp & "\ AK_StartDt.akk ") Then

Fl = FreeFile
‘here reading date from file which(date) is stored as day in 1^st line, ‘month in 2^nd line & year in 3^rd line of file to make a small kind of encryption & converting it into d/m/yyyy format
‘open file in read only mode
Open strTemp & "\ AK_StartDt.akk " For Input As #Fl

S1 = ""
Do While Not EOF(Fl)

‘read one line from file & store value in s
Input #Fl, S
If S1 = "" Then

S1 = S

Else

S1 = S1 & "/" & S

End If

Loop

Close #Fl
S1 = Format(S1, "D/M/YYYY")
Fl = FreeFile
Open strTemp & "\ AK_EndDt.akk " For Input As #Fl

S2 = ""
Do While Not EOF(Fl)

Input #Fl, S
If S2 = "" Then

S2 = S

Else

S2 = S2 & "/" & S

End If

Loop

Close #Fl
S2 = Format(S2, "D/M/YYYY")
LastUsedDate = GetSetting("ArunKakkarCompany ", "AK_Proj1", "AK_LastUsedDt")
If LastUsedDate = "" Then

SaveSetting " ArunKakkarCompany ", "AK_Proj1", "AK_LastUsedDt", Date

ElseIf CDate(LastUsedDate) Then

If (Date - CDate(LastUsedDate)) < 0 Then

Me. lblMsg.Visible = True
Me. lblMsg.Caption = "EVALUATION EXPIRED...., contact ArunKakkarCompany "
Me.Frame1.Visible = True
Me.lblUseEavl.Enabled = False
Timer1.Enabled = False
SaveSetting " ArunKakkarCompany ", "AK_Proj1", "AK_Expired", "1"
Me.lblUseEavl.Enabled = False
Exit Sub

End If
SaveSetting " ArunKakkarCompany ", "AK_Proj1", "AK_LastUsedDt", Date

Else //If LastUsedDate = "" Then

Me. lblMsg.Visible = True
Me. lblMsg.Caption = "Evaluation Expired...., contact ArunKakkarCompany "
Me.Frame1.Visible = True
Me.lblUseEavl.Enabled = False
Timer1.Enabled = False
SaveSetting " ArunKakkarCompany ", "AK_Proj1", "AK_Expired", "1"
Me.lblUseEavl.Enabled = False

End If //If LastUsedDate = "" Then
If DateDiff("d", Date, S1) > 0 Or DateDiff("d", Date, S2) < 0 Then

Me. lblMsg.Visible = True
Me. lblMsg.Caption = "Evaluation Expired...., contact ArunKakkarCompany "
Me.Frame1.Visible = True
Me.lblUseEavl.Enabled = False
Timer1.Enabled = False
SaveSetting " ArunKakkarCompany ", "AK_Proj1", "AK_Expired", "1"
Me.lblUseEavl.Enabled = False

Else

Me. lblMsg.Visible = True 'Change
Me. lblMsg.Caption = "Only " & (31 - Val(DateDiff("D", CDate(S1), Date))) & " Days are Remaining"
'Me. lblMsg.Caption = "Only " & 31 & " Days are Remaining"
'Me.lblUseEavl.Enabled = True

End If

Else //If FS.FileExists(strTemp & "\ AK_StartDt.akk ") Then

Fl = FreeFile
Open strTemp & "\AK_StartDt.akk" For Output As #Fl

Print #Fl, Format(Day(Date), "0#")
Print #Fl, Format(Month(Date), "#")
Print #Fl, Year(Date)

Close #Fl
EndDt = CDate(DateAdd("D", 31, Date))
Fl = FreeFile
Open strTemp & "\AK_EndDt.akk" For Output As #Fl

Print #Fl, Format(Day(EndDt), "0#")
Print #Fl, Format(Month(EndDt), "#")
Print #Fl, Year(EndDt)

Close #Fl
Me. lblMsg.Visible = True
SaveSetting "ArunKakkarCompany ", "AK_Proj1", "AK_LastUsedDt", Date
Me. lblMsg.Caption = "Demo Version........ Just for 31 Days."
Me.lblUseEavl.Enabled = True

End If //If FS.FileExists(strTemp & "\ AK_StartDt.akk ") Then

Else //If GetSetting("ArunKakkarCompany ", " AK_Proj1", " AK_Expired ") <> "1" Then

Me. lblMsg.Visible = True
Me. lblMsg.Caption = "Evaluation Expired...., Contact ArunKakkarCompany "
Me.Frame1.Visible = True
Me.lblUseEavl.Enabled = False
Timer1.Enabled = False

End If //If GetSetting("ArunKakkarCompany ", " AK_Proj1", " AK_Expired ") <> "1" Then

End If //If mactno = GValidNo Then

Below is the VB.NET Code

Here along with date/time based trial version procedure, I also put a small/low quality startup sequence of starting any application software.
of course, there are lot of great ways to make software startup/security much more powerful & secure.
Below code is the small portion of my small project "Cure"

========================================================

Private Sub frmCure_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

lblContactUs.Text = GDvlprCompanyName & vbNewLine & GDvlprCompanyAddress & vbNewLine & GDvlprCompanyPhoneNo & vbNewLine & GDvlprCompanyEmailId & vbNewLine & GDvlprCompanyWebsite
lblActivate.Visible = False
txtActivate.Visible = False
btnActivate.Visible = False
Main(Me)
CheckEvaluationPeriod(Me)

End Sub
----------------
'Begin---Start point of Cure Project------------------------
Public Sub Main(ByVal frm As Form)

'Planning+++++++++++++++++++++++++++++++++++++++++++++

'1 Change computer/system date format to dd/mm/yyyy
'2 return registration key (rk) from registry
'3 if rk =” “or rk not found/exists

' Show “Start up” form

' Else

' Return activation key (ak) by calling appropriate function(say createAK) using os drive serial no
' If ak != rk

' Show “Start Up” form

' Else //if ak=rk

' Call function to start s/w (say StartSoftware)

' End If

' End If

'+++++++++++++++++++++++++++++++++++++++++++++++++
'ChangeDateFormat()
Try

CureSecurity(enCureSecurity.ChangeDateFormat)
Dim rk As String
rk = My.Computer.Registry.GetValue(GKeyPath, GRegistrationKey, "")
If rk = "" Then

frm.Show()

Else

Dim ak As String
'ak = retUsrNo(OS_Drive_SNo)
ak = CureSecurity(enCureSecurity.SoftwareActivationKey)
If ak = rk Then

'frm.Close()
OpenRegisteredCure(frm)

Else

frm.Show()

End If

End If

Catch ex As Exception

CureErrorMsg(ex, "frmModuleFunction-Main")

End Try

End Sub
---------------
Public Function CureSecurity(ByVal Work As enCureSecurity, Optional ByVal UserMachineCode As String = "A") As String

Dim s As String = ""
Try

Dim temp As String = ""
Dim ak As New ArunKakkarSoft.ArunKakkarSWReg
temp = My.Computer.FileSystem.SpecialDirectories.Desktop
'retrieve the os drive name & then os drive Windows directory path
temp = temp.Substring(0, 1)
Select Case Work

Case enCureSecurity.ChangeDateFormat

ak.ChangeDateFormat()
s = ""

Case enCureSecurity.SoftwareActivationKey

s = ak.GetActivateCodeForSoftware(ak.GetDriveSerialNo(temp))

Case enCureSecurity.UserActivationKey

s = ak.GetActivateCodeForUser(UserMachineCode)

Case enCureSecurity.UserMachineCode

s = ak.GetUserMachineCode(ak.GetDriveSerialNo(temp))

End Select
ak = Nothing

Catch ex As Exception

CureErrorMsg(ex, "frmModuleFunction-CureSecurity")

End Try
Return s

End Function
--------------
Public Sub CheckEvaluationPeriod(ByVal frm As frmCureEvaluation)

'Return “AK_Expired” key from registry
'If AK_Expired = False Or 1 Then

'Disable evaluation button
'Show message “Evatuation period expired etc”

'Else //if AK_Expired=true or 0

'Search AK_StartDt.akk or another name (like AK_StartDt.akk) present in computer “Windows” Directory()
'If AK_StartDt.akk file not present in computer “Windows” directory

'1Create new file AK_StartDt.akk in computer “Windows” Directory & write current date in this file
'2 Create another file AK_EndDt.akk in computer “Window” directory & write new date which is equal to (current date + 31 days)
'3 enter current date in “AK_LastUsedDt” key in registry
'4 display label Demo Version........ Just for 31 Days.
'5 enable evaluation button

'Else if AK_StartDt.akk file present in computer “Windows” Directory

'Return FirstDate from AK_StartDt.akk file
'Return LastDate from AK_EndDt.akk file
'Return LastUseDate from “AK_LastUsedDt” key in registry
//first level of security for evaluation
'If LastUseDate = " " Then

'enter current date in “AK_LastUsedDt” key in registry

'Else if LastUseDate is a date

'if LastUseDate>current date

'display message “Evaluation Expired …. Contact ABC”
'disable “Evaluation” button
'enter False or 1 in “AK_Expired” key in registry

'else //if LastUseDate<=current date

'enter current date in “AK_LastUsedDt” key in registry

'End If

'Else // if LastUseDate is other than date or “ ”

'display message “Evaluation Expired …. Contact ABC”
'disable “Evaluation” button
'enter False or 1 in “AK_Expired” key in registry

'End if // If LastUseDate=” ”
'//Second Level of security for evaluation
'If currend date < FirstDate or current date >LastDate

'display message “Evaluation Expired …. Contact ABC”
'disable “Evaluation” button
'enter False or 1 in “AK_Expired” key in registry

'Else

'Show message “only (31-(current date-FirstDate)) days are remaining”

'End if // If currend date < FirstDate or current date >LastDate

'End if // If AK_StartDt.akk file not present in computer “Windows” directory

'End if // If AK_Expired=false or 1
'Dim f1 As New frmCureEvaluation
Try

Dim ActiveKey As String
Dim FirstDate, LastDate, LastUseDate, temp2 As String
Dim i As Integer
'if activekey=1 then evaluation expired
ActiveKey = My.Computer.Registry.GetValue(GKeyPath, GIsActive, "0")
If ActiveKey = "1" Then

ExpireEvaluation(frm, False)

Else 'activekey=0 'active key<>1

If Not My.Computer.FileSystem.FileExists(GetCureFilePath(CureFiles.Cure_dll)) Then 'if Cure.dll file not found

'write to Cure.dll
WriteToFile(CureFiles.Cure_dll, Format(Now.Day, "0#"), False)
WriteToFile(CureFiles.Cure_dll, Format(Now.Month, "#"), True)
WriteToFile(CureFiles.Cure_dll, Now.Year, True)
'write to Cure1.dll
Dim LastDt As Date
LastDt = DateAdd(DateInterval.Day, 31, Now.Date)
WriteToFile(CureFiles.Cure1_dll, Format(LastDt.Day, "0#"), False)
WriteToFile(CureFiles.Cure1_dll, Format(LastDt.Month, "#"), True)
WriteToFile(CureFiles.Cure1_dll, LastDt.Year, True)
'write to registry
My.Computer.Registry.SetValue(GKeyPath, GLastUsed, Now.Date.ToShortDateString)
frm.lblEvaluationMsg.Text = "Demo Version........ Just for 31 Days."
frm.btnEvaluation.Enabled = True

Else 'if Cure.dll file found

'read from Cure.dll
FirstDate = ""
For i = 1 To 3

temp2 = ReadFromFile(CureFiles.Cure_dll, i)
If FirstDate = "" Then

FirstDate = temp2

Else

FirstDate = FirstDate & "/" & temp2

End If

Next
If IsDate(FirstDate) Then

FirstDate = Format(CDate(FirstDate), "d/M/yyyy")

Else 'if someone make changes in file data

ExpireEvaluation(frm, True)
Exit Sub

End If
'read from Cure1.dll
LastDate = ""
If My.Computer.FileSystem.FileExists(GetCureFilePath(CureFiles.Cure1_dll)) Then

For i = 1 To 3

temp2 = ReadFromFile(CureFiles.Cure1_dll, i)
If LastDate = "" Then

LastDate = temp2

Else

LastDate = LastDate & "/" & temp2

End If

Next
If IsDate(LastDate) Then

LastDate = Format(CDate(LastDate), "d/M/yyyy")

Else 'if someone make changes in file data

ExpireEvaluation(frm, True)
Exit Sub

End If

Else 'if Cure1.dll file not found

ExpireEvaluation(frm, True)
Exit Sub

End If
'verify again that FirstDate & LastDate are valid date else evaluation expire
If Not (IsDate(FirstDate) And IsDate(LastDate)) Then

ExpireEvaluation(frm, True)
Exit Sub

End If
'get LastUseDate from registry
LastUseDate = My.Computer.Registry.GetValue(GKeyPath, GLastUsed, "")
'first level of security for evaluation
If LastUseDate = "" Then

My.Computer.Registry.SetValue(GKeyPath, GLastUsed, Now.Date.ToShortDateString)

ElseIf IsDate(LastUseDate) Then

If (CDate(LastUseDate) > Now.Date) Then

ExpireEvaluation(frm, True)

Else 'if LastUseDate<=CurrentDate

My.Computer.Registry.SetValue(GKeyPath, GLastUsed, Now.Date.ToShortDateString)

End If

Else 'if LastUseDate is not a date & not empty

ExpireEvaluation(frm, True)

End If
'Second Level of Security for evaluation
If (Now.Date < CDate(FirstDate)) Or (Now.Date > CDate(LastDate)) Then

ExpireEvaluation(frm, True)

Else

frm.lblEvaluationMsg.Text = "EVALUATION VERSION------->>>Only " & (31 - (Now.Date - CDate(FirstDate)).Days) & " days are remaining!!!!!"

End If

End If 'If Not My.Computer.FileSystem.FileExists(filePath) Then 'if Cure.dll file not found

End If 'If ActiveKey <> "0" Then

Catch ex As Exception

CureErrorMsg(ex, "frmModuleFunction-CheckEvaluationPeriod")

End Try

End Sub
--------------
Public Sub ExpireEvaluation(ByVal frm As frmCureEvaluation, Optional ByVal ModifyActiveKey As Boolean = True)

Dim ExpireMsg As String
ExpireMsg = "EVALUATION EXPIRED...., contact ARUNKAKKARCOMPANY "
If ModifyActiveKey Then

My.Computer.Registry.SetValue(GKeyPath, GIsActive, "1")

End If
frm.lblEvaluationMsg.Text = ExpireMsg
frm.btnEvaluation.Enabled = False

Catch ex As Exception

CureErrorMsg(ex, "frmModuleFunction-ExpireEvaluation")

End Try

End Sub
---------------------
'Begin-----File Handling in VB.NET ---------------------------------------------------------
Public Function GetCureFilePath(ByVal file As CureFiles) As String

Dim path As String = ""
Try

Dim temp As String
temp = My.Computer.FileSystem.SpecialDirectories.Desktop
'retrieve the os drive name & then os drive Windows directory path
temp = temp.Substring(0, 1) & ":\Windows\" 'Cure.dll"
path = ""
Select Case file

Case CureFiles.Cure_dll

path = temp & "Cure.dll"

Case CureFiles.Cure1_dll

path = temp & "Cure1.dll"

Case CureFiles.CurePurchaseOrder_txt

path = My.Computer.FileSystem.SpecialDirectories.Desktop & "\CurePurchaseOrder.txt"

Case CureFiles.CureServer_ini

path = My.Application.Info.DirectoryPath & "\CureServer.ini"

Case CureFiles.CureDataBaseFile

path = My.Application.Info.DirectoryPath & "\CureData\Cure.mdf"

Case CureFiles.CureDataBaseLogFile

path = My.Application.Info.DirectoryPath & "\CureData\Cure_log.ldf"

End Select

Catch ex As Exception

CureErrorMsg(ex, "frmModuleFunction-GetCureFilePath")

End Try
Return path

End Function
Public Sub WriteToFile(ByVal file As CureFiles, ByVal StringData As String, Optional ByVal AppendMode As Boolean = True)

Dim path As String
path = GetCureFilePath(file)
Dim fileWrt As System.IO.StreamWriter
fileWrt = My.Computer.FileSystem.OpenTextFileWriter(path, AppendMode)
fileWrt.WriteLine(StringData)
fileWrt.Close()

Catch ex As Exception

CureErrorMsg(ex, "frmModuleFunction-WriteToFile")

End Try

End Sub
Public Function ReadFromFile(ByVal file As CureFiles, ByVal lineno As Integer, Optional ByVal IsFullFile As Boolean = False) As String

Dim line As String = ""
Try

Dim i As Integer
Dim path As String
path = GetCureFilePath(file)
Using sr As StreamReader = New StreamReader(path)

'Read and display the lines from the file until the end of the file is reached.
If IsFullFile Then

Dim s As String = ""
i = 0
Do

i = i + 1
s = sr.ReadLine()
If Not (s Is Nothing) Then

line = line & vbNewLine & i & " " & s

End If

Loop Until s Is Nothing
line = Trim(line)

Else

For i = 1 To lineno - 1

line = sr.ReadLine()

Next
line = Trim(sr.ReadLine())

End If
sr.Close()

End Using

Catch ex As Exception

CureErrorMsg(ex, "frmModuleFunction-ReadFromFile")

End Try
Return line 'ReadFromFile = line

End Function
'End-----File Handling in VB.NET ---------------------------------------------------------

I hope this would be helpful.

Best of luck for good programming.

Basic Chemistry Syllabus upto 10th Standard Students

2011-01-13T23:48:00.000-08:00

RAM RAM Friends

Basic Chemistry Syllabus upto 10th Standard Students

When I got chance to teach chemistry to students upto 10th standards, I found that they found chemistry very strange & non-understandable subject. So after spending some days with them, I observed that they don't know much about basics of Chemistry. So before getting started with their books, I teach them some basic topics of Chemistry which would be necessary & very helpful to understand & even love to study Chemistry.
So below is the very basic topics I used to cover before starting teaching Chemistry to make proper background in Chemistry so that they can understand Chemistry whenever & wherever they study it.
Due to lack of time, I had posted the pictures of syllabus as below

Note :-->>Request from all the teachers of entire universe

Please teach students by providing them real life examples in their local language so that they can understand much better & use the knowledge in their daily life (helping their friends, relatives, neighbors etc).
Please make classroom atmosphere friendly & fearless so that students can express their feelings, understand topic much better, & most important ask any Questions without any kind of hesitation.
Always remember, what you do with student, they may follow you & make future world the way you are as everyone know students are the future of any world.

I hope above information would be helpful.

How to make SQL Query to get Birthday & Anniversary Alert

2011-01-11T01:40:00.000-08:00

RAM RAM Friends

How to get list of clients from Database whose Birthday or Anniversary would come within the next few days

When I got this problem, first I thought it would be normal & not tough.
But when I start working on it, I found it very very difficult & like anyone else I tried to find its solution on the Internet using Google etc, but after searching a lot for few days, I feel very upset as I didn't get the correct solution neither the hint to solve it.
But after getting tired from net, I never gave up & decided to solve this problem by my own way,
so after a lot of hard work I found the way & moreover when writing this blog, I also found other ways to make the whole procedure very simple.

Below is the summary of the whole procedure which I implemented in the project

Note: Please note that whole procedure is written in VB6.0 & MS SQL Server 2000

   Dim S As String, IsShow As Boolean
   Dim UserDays As Integer
   Dim AlertDt As Date, CurrentDt As Date, CurrentNextYrDt As Date

CurrentDt = Format$(Now, "DD/MMM/YYYY")

UserDays = Val(IIf(IsNull(GDREC!settingvalue) = False, GDREC!settingvalue, "0"))

AlertDt = CurrentDt + UserDays

CurrentNextYrDt = DateAdd("yyyy", 1, CurrentDt)

Note: In below SQL Query,

All capital words are Keywords of SQL Server
CurrentDt="26Oct2010"
AlertDt="01Mar2011" , (UserDays ="01Mar2011" - "26Oct2010")
CurrentNextYrDt="26Oct2011"

   SELECT Bday,ClientId,ClientName,Phone,Mobile,AlterBDay
   FROM (SELECT Bday,ClientId,ClientName,Phone,Mobile,
   CASE WHEN MONTH(BDay) < MONTH(GETDATE()) THEN
   DATEADD(YEAR,DATEDIFF(YEAR,BDay,'26Oct2011'),BDay)
   Else
   DATEADD(YEAR,DATEDIFF(YEAR,BDay,'26Oct2010'),BDay)
   END As AlterBDay
   FROM ClientMaster WHERE BDay IS NOT NULL AND NOT(BDay='01Jan1900')
   ) AS NewClientMaster
   WHERE AlterBDay BETWEEN '26Oct2010' AND '01Mar2011'
   ORDER BY AlterBDay

Explanation of above Query:

Here I have used one kind of Nested query with Case, When Else End As clause
Here I used nested query to return a table (view) from which main query extract desired data
In the nested query, I created one new column "AlterBDay" which use value of BDay (Birthday) column & have modified date by making year part to either current year or next year of BDay column according to AlertDt ("01Mar2011") value.
After studying & understanding the above query & you would certainly get lot of ideas to make this query more short & also easy to understand, as I think, this query would be more easy if you use/create Views instead of nested query.

Below is the use of above query in VB6 project

by passing the whole query in the string variable named "S"

   S = " SELECT Bday,ClientId,ClientName,Addr1,Phone,Mobile,AlterBDay "
   S = S & " FROM (SELECT Bday,ClientId,ClientName,Addr1,Phone,Mobile, "
   S = S & " CASE WHEN MONTH(BDay) < MONTH(GETDATE()) THEN "
   S = S & " DATEADD(YEAR,DATEDIFF(YEAR,BDay,'" & Format$(CurrentNextYrDt, "DD/MMM/YYYY") & "'),BDay) "
   S = S & " Else "
   S = S & " DATEADD(YEAR,DATEDIFF(YEAR,BDay,'" & Format$(CurrentDt, "DD/MMM/YYYY") & "'),BDay) "
   S = S & " END As AlterBDay "
   S = S & " FROM ClientMaster WHERE BDay IS NOT NULL AND NOT(BDay='01Jan1900') "
   S = S & " ) AS NewClientMaster WHERE AlterBDay BETWEEN '" & Format$(CurrentDt, "DD/MMM/YYYY") & "' AND '" & Format$(AlertDt, "DD/MMM/YYYY") & "' "
   S = S & " ORDER BY AlterBDay "

I hope this query would help you a lot in case you get this kind of problem.

Happy Programming.

I uses programming as a way to help people to make their life more comfortable.

Basic English Course Syllabus to read, write & understand English

2011-01-07T23:42:00.000-08:00

RAM RAM Friends

Friends during my struggle days, I used to be a private tutor. As I live in town area, where most of the students of poor or middle class families who mostly study in Government schools, face lots of difficulty while studying English. When I got chance to teach them English, I have to teach them very basic things in English to make their base in the language. For this I have to teach the same topics to various students in different batches.

I had to remember all the topics & write them again & again. To overcome this problem I made Basic English Course Syllabus & then start teaching them according to that, this make me much comfortable & it would be very good for them to make quick revision & also help their friends & relative to learn it.

So keeping in mind these benefits, I decide to provide it online so that everyone in the world who want to learn English Language, would be benefited from it.

Please note that it is just a Syllabus.

Following are the various topics

Basic Formula for making Simple Sentence

Sub+HV+MV+Ob+Ad+.

where

Sub-->Subject (Who)

HV -->Helping Verb

Ob--->Object

a. Living (Whom)

b. Non Living (What)

Ad---->Adverb/Adjective

Where

When

How

Why

Following are very basic terms

Subject Adv Object

I My Me

We Our Us

You Your You

He His Him

She Her Her

They Their Them

It Its It

Subject	Adv	Object
I	My	Me
We	Our	Us

You	Your	You

He	His	Him
She	Her	Her
They	Their	Them
It	Its	It

Below In More details

I	My	Mine	Me	Myself
We	Our	Ours	Us	Ourselves

You	Your	Yours	You	Yourself/Yourselves

He	His	His	Him	Himself
She	Her	Hers	Her	Herself
They	Their	Theirs	Them	Themselves
It	Its	Its	It	Itself

Some more Important Daily use Subjects etc

This
That
These
Those

Difference between Singular & Plural
Difference between A & An
Various Important Helping verbs (Most frequently used)

Is
Am
Are
Was
Were
Will be
Shall be
Will
Shall
Has
Have
Had
Will have
Shall have
Is to
Am to
Are to
Was to
Were to
Has to
Have to
Had to
Will have to
Shall have to

Modals (Special Helping Verbs)

Can
Could
May
Might
Should
Ought to
Need
Dare
Will
Shall
Would
Must

Some Important Sentence building words

It
There
Let
Would that ____ were____

Types of Sentences

Simple/Affirmative Sentences
Negative Sentences
Interrogative Sentences

Interrogative Sentences without wh-family
Interrogative Sentences with wh-family

Without Object
With Object

Interrogative Negative Sentences

Without wh-family
With wh-family

without object
with object

Imperative Sentences

Wh-Family words (Most common are below)

Who
What
How
Where
When
Why
Whose
Whom
Which
How many
How much
How old
How far
How long
From where
To whom
In which

Various useful words (whose meaning one should know)

So	This is because	Thus
Hence	Therefore	That is why
But	However	Since
As	When	Moreover
Whatever	Thereafter	Whatsoever
Because	While	Whereas
Which	Who	That
Already	After	Before
Just	For	Of
In	Into	On
Onto	From	With
Without	Once	Ago
According to	Opinion	Latest
At least	At last	Eventually
Fortunately	Unfortunately	Till
Until	Etc.	E.g.
i.e.	So on	In case of
Like	Between	Among
Each other	One another	as well as
if --- else	otherwise	instead of
rather	either--or	neither--nor
although--yet	although----,	on the other hand
is called	is known as	only
due to	one of the most---	if ---- then
if ---- ,	in order to	as a result
if

Now do following after learning all above

Tense chart
Tenses
Voices (Active voice & Passive voice)
Narration ( Direct & Indirect sentences)
Non-Finite
Complete any translation book

I hope it would be helpful for the persons who want to learn English language

How to send E-Mail from VB 6.0 via Internet

2011-01-03T16:01:00.000-08:00

RAM RAM Friends

How to send E-Mail from VB 6.0 via Internet

When I got this work to implement in our project, first I got very excited but when I tried to search this topic on Internet via Google etc, I had to struggle a lot to find the correct method.

Here I would try to explain HOW TO SEND EMAIL FROM VB via INTERNET

Below is a very simple program to explain this

Private Sub cmdSend_Click()

On Error GoTo ArunKakkarErr

Dim iMsg As Object

Dim iConf As Object

Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")

Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults

Set Flds = iConf.Fields

Dim Schemas As String

'Schemas = "http://schemas.Microsoft.Com/cdo/configuration/" 'causing error as it is very case sensitive

Schemas = "http://schemas.microsoft.com/cdo/configuration/"

With Flds

.Item(Schemas & "smtpusessl") = 1 'True

.Item(Schemas & "smtpauthenticate") = 1

.Item(Schemas & "sendusername") = Trim(Me.txtMailId.Text) '"yourmail@gmail.Com"

.Item(Schemas & "sendpassword") = Trim(Me.txtPwd.Text) '"yourpassword"

.Item(Schemas & "smtpserver") = Trim(Me.txtSMPT.Text) '"smtp.Gmail.Com" it causes error as it is case sensitive, use "smtp.gmail.com"

.Item(Schemas & "sendusing") = 2

.Item(Schemas & "smtpserverport") = 25 '465

.Update

End With

strBody = Trim(Me.txtBody.Text) & vbCrLf & Now '"Sample message " & Time

With iMsg

Set .Configuration = iConf

.To = Trim(Me.txtTo.Text) 'sendto

.CC = Trim(Me.txtCC.Text) '""

.BCC = Trim(Me.txtBCC.Text) '""

' Note: The reply address is not working if you use this Gmail example

' It will use your Gmail address automatic. But you can add this line

' to change the reply address .ReplyTo = "Reply@something.Com"

.From = Trim(Me.txtFrom.Text) '""

.Subject = Trim(Me.txtSubject.Text) '"subject"

.TextBody = strBody

.AddAttachment Trim(Me.txtAttachment.Text)

.AddAttachment Trim(Me.txtAttachment2.Text)

.Send

End With

MsgBox ("Mail has been sent successfully, Please check the mail if you have any doubt.")

Exit Sub

ArunKakkarErr:

MsgBox "There is an error while sending mail. And details are =" & Err.Description

End Sub

Important Notes:

Please Allow this program to access Internet in your Firewall setting or Antivirus program if they ask for it else you can't send the email.
Your program may take time while sending email so make proper coding in your program so that user may know that your program is busy. ( e.g by changing your mouse pointer etc)
Please take the commented notes given in above code very seriously to get proper result.

Good luck for programming.

How to send SMS from VB 6.0 via Internet

2011-01-03T01:52:00.000-08:00

RAM RAM Friends

At last I got time to write one of important topic here.

How to send SMS from VB6.0 via Internet

When I got this work to implement in our project, first I got very excited but when I tried to search this topic on Internet via Google etc, I had to struggle a lot to find the correct method.

Well We can send SMS from VB6.0 by two ways

By using Internet (this method is explained Here )
By Using Mobile Phone (Not explained here)

After long time, I finally got chance to provide download link to demo project which is below
The downloaded file is rar compressed. In case you don't have software to extract it, just download wrar320 or higher version free of cost from the internet via Google etc.

Please note: You need some valid Developer API.

Please either contact company or create an demo account to get developer API from the site, I had mentioned Below.

Click here to Download SMS Demo project

click here to Download SMS Demo project Setup without code 2.67mb

Here I would try to explain HOW TO SEND SMS FROM VB via INTERNET

The very first thing is that U need to contact to any SMS Providers (e.g. in India some are

http://www.mysmsmantra.com/
http://www.smscountry.com/ (click on Register now button at top)
http://www.freesmsapi.com/ (click on Sign Up button at top)
http://mirchsms.com/bulk_sms.html (please contact at no. provided at top or email at bottom to request to get trial sms api ). I also tested it very well, you may also refer my name to them.)

Now get the Developer API from these SMS Providers either by purchasing account or if you want to test before purchasing then you can ask these SMS Providers to provide you a free trial account so that you can test their service)

Below is one of the example of Developer API
'http://bulksms.mysmsmantra.com:8080/WebSMS/SMSAPI.jsp?username=demouser&password=399501089&sendername=DM&mobileno=919999999999&message=Hello"

Now just use the following code in your VB program to send SMS

Dim WinHttpReq As Object
Dim sResult As String, strURL As String
Set WinHttpReq = CreateObject("Msxml2.XMLHTTP")
strURL="http://bulksms.mysmsmantra.com:8080/WebSMS/SMSAPI.jsp?username=demouser&password=399501089&sendername=DM&mobileno=919999999999&message=Hello"
'Replace the Message & MobileNo with the no to whom you want to send SMS
With WinHttpReq
.Open "GET", strURL, False
.Send
sResult = .responseText
End With
MsgBox "Status of Sent SMS is=" & sResult

Some Important notes We should keep in mind while Sending SMS

Check the Maximum Message Length that you can send as allowed by your SMS Service Providers
Check whether you have to make your Message encode according to WWW standards, confirm it from your SMS Providers
While sending data over internet/network, some letters can not be sent as it is, they need to be converted to send them to internet. e.g " &" can not be send as it, it needs to be converted to send it over internet.

URL encoding converts characters into a format that can be transmitted over the Internet.
For more information see following link

http://www.w3schools.com/html/html_urlencode.asp

Always Allow the access of your program to get connected with Internet which would be asked by the Firewall of your OS or Antivirus program else you could not send SMS
As sending SMS may take time, you should do appropriate changes in your program to show user that your program is busy in sending SMS like change Mouse Pointer Shape from normal to HourGlass & after sending make it normal.

Please note: You need some valid Developer API.

Please either contact company or create an demo account to get developer API from the site, I had mentioned above.

click here to Download SMS Demo project

Below is a sample which I used in my project

Private Sub cmdSMSTest_Click()
   'validate information
  'Message(max 160 Chars)

   Me.MousePointer = 11 'Hourglass
   Call SendMySMS(myMob, myMsg, True, Trim(Me.txtSMSDeveloperAPI), Trim(Me.txtSMSMobileText), Trim(Me.txtSMSMsgText))
   Me.MousePointer = 0 'Default

End Sub

Public Function SendMySMS(ByVal myMob As String, ByVal myMsg As String, Optional ByVal IsTest As Boolean = False, Optional ByVal UserDeveloperAPI As String = "", Optional ByVal MobText As String = "", Optional ByVal MsgText As String = "", Optional ByVal AskToSend As Boolean = True, Optional ByVal ShowStatus As Boolean = True) As Boolean

'Here you write your code

Following is the sample code to test it--- but of course you need Developer API to use the code

Private Sub cmdSMSTest_Click()
'validate information
'Message(max 160 Chars)
If Me.chkSMSActivateService.Value = 0 Or Len(Me.txtSMSDeveloperAPI) < 1 Or Len(Me.txtSMSMobileText) < 1 Or Len(Me.txtSMSMessageText) < 1 Then
MsgBox "Please enter all setting values"
Me.txtSMSDeveloperAPI.SetFocus
Exit Sub
End If

Dim s As String
Dim myMob As String
Dim myMsg As String

s = "Enter Mobile nos separated by , then put @ now write your msg not more than 160 chars"
s = s & vbCrLf & "e.g 9971745868,9811994797@Hi this is test sms by ABM Infotech"
s = InputBox(s, , "9971745868,9811360935@Hi this is test sms by ABM Infotech")
myMsg = Mid(s, InStr(1, s, "@", vbTextCompare) + 1)
If Len(myMsg) > 155 Then
myMsg = Left$(myMsg, 155)
End If
myMob = Mid(s, 1, InStr(1, s, "@", vbTextCompare) - 1)

Me.MousePointer = 11 'Hourglass
Call SendMySMS(myMob, myMsg, True, Trim(Me.txtSMSDeveloperAPI), Trim(Me.txtSMSMobileText), Trim(Me.txtSMSMessageText))
Me.MousePointer = 0 'Default

End Sub

Public Sub SendMySMS(ByVal myMob As String, ByVal myMsg As String, Optional ByVal IsTest As Boolean = False, Optional ByVal UserDeveloperAPI As String = "", Optional ByVal MobText As String = "", Optional ByVal MsgText As String = "", Optional ByVal AskToSend As Boolean = True, Optional ByVal ShowStatus As Boolean = True)
'myMob,myMsg, Optional IsTest,UserDeveloperAPI , MobText, MsgText,AskToSend ,ShowStatus As Boolean = True
On Error GoTo ArunKakkarErr

Dim sMobText1 As String
Dim sMobText2() As String
Dim sMobText3() As String

Dim sResult As String, strURL As String
strURL = ""

If IsTest Then

Else 'retrieve settings from db
'''SMS_IsActivate 1,0 SMS_DeveloperAPI SMS_MobileText SMS_MessageText
If UserPreferencesGetValue("SMS_IsActivate", "0") = "0" Then Exit Sub
UserDeveloperAPI = UserPreferencesGetValue("SMS_DeveloperAPI")
MobText = UserPreferencesGetValue("SMS_MobileText")
MsgText = UserPreferencesGetValue("SMS_MessageText")
End If

'To put 91 etc for more than one mobile nos
sMobText2 = Split(MobText, "=")
sMobText1 = ""
If UBound(sMobText2) > 0 Then
sMobText1 = sMobText2(1) '2nd element
End If
If Len(sMobText1) > 0 Then
sMobText3 = Split(myMob, ",")
If UBound(sMobText3) > 0 Then
myMob = ""
For i = LBound(sMobText3) To UBound(sMobText3)
If myMob = "" Then
myMob = sMobText3(i) '1st Mobile No
Else
myMob = myMob & "," & sMobText1 & sMobText3(i) '2nd, 3rd, 4th Mob No
End If
Next i
End If
End If

strURL = UserDeveloperAPI & "&" & MobText & myMob & "&" & MsgText & myMsg

If AskToSend Then
If MsgBox("Do you want to send SMS?", vbYesNo + vbDefaultButton1) = vbNo Then
Exit Sub

End If
End If
Dim WinHttpReq As Object

Set WinHttpReq = CreateObject("Msxml2.XMLHTTP")
'strURL = "http://bulksms.mysmsmantra.com:8080/WebSMS/SMSAPI.jsp?username=demouser&password=399501089&sendername=DM&mobileno=919999999999&message=Hello"
'Replace the Message & MobileNo with the no to whom you want to send SMS
With WinHttpReq
.Open "GET", strURL, False
.Send
sResult = .responseText
End With
If ShowStatus Then MsgBox "Status of Sent SMS is=" & sResult

Exit Sub
ArunKakkarErr:
MsgBox ("Sorry there is some error while sending sms" & vbCrLf & Err.Description)

End Sub

Public Function UserPreferencesGetValue(ByVal SettingName As String, Optional ByVal DefaultValue As String = "", Optional ByVal myTable As String = "UserPreferences", Optional ByVal NameCol As String = "SettingName", Optional ByVal ValueCol As String = "SettingValue")
Dim s As String
s = ""
Dim rptRs As New ADODB.Recordset
If rptRs.State Then rptRs.Close
rptRs.Open "select * from " & myTable & " where " & NameCol & "='" & SettingName & "'", Scon, adOpenStatic, adLockReadOnly
If rptRs.EOF = False Then ' if already present then return
s = IIf(IsNull(rptRs!SettingValue) = False, rptRs!SettingValue, DefaultValue)
Else ' if not present then return default
s = DefaultValue
End If
UserPreferencesGetValue = s
End Function

Public Sub UserPreferencesSetValue(ByVal SettingName As String, ByVal SettingValue As String, Optional ByVal myTable As String = "UserPreferences", Optional ByVal NameCol As String = "SettingName", Optional ByVal ValueCol As String = "SettingValue")
Dim rptRs As New ADODB.Recordset
If rptRs.State Then rptRs.Close
rptRs.Open "select * from " & myTable & " where " & NameCol & "='" & SettingName & "'", Scon, adOpenStatic, adLockReadOnly
If rptRs.EOF = False Then ' if already present then update
Scon.Execute "Update " & myTable & " set " & ValueCol & " = '" & SettingValue & "' where " & NameCol & " = '" & SettingName & "'"
Else ' if not present then insert
Scon.Execute "Insert into " & myTable & " (" & NameCol & "," & ValueCol & ") values ('" & SettingName & "','" & SettingValue & "')"
End If
End Sub

Public Sub AKakkar_Error(Optional ByVal Source As String = "")
Dim s As String
s = "Sorry for the inconvinence, There is some error"
s = s & vbCrLf & "ABM Source=" & Source
s = s & vbCrLf & "Error No=" & Err.Number
s = s & vbCrLf & "Error Description=" & Err.Description
s = s & vbCrLf & "Error Source=" & Err.Source
MsgBox s, vbCritical
End Sub

Note:::>>>> If someone need sample program, just mail me, I would like to help you.

Now after learning it you can explore it in details to get more powerful options for your programs.
Best of Luck for Programming.

Quickly Revise SQL used in many popular RDBMS Databases

2010-10-06T02:39:00.000-07:00

RAM RAM Friends,

I make following notes for my personal revision of SQL Queries & when I got the chance to share these notes, I am posting them as it is without making major changes due to lack of time.

Most of the content has been learnt from W3School website's SQL tutorial

I hope it will help those who wish to make quick revision of sql.

Please Note that I have build these notes to quickly revise SQL Query Syntax, commands etc Only.

Since I used to learn syntax by learning/memorizing examples, so I have not mentioned any Syntax rather I use examples with only most used options for syntax.
MS SQL SERVER NOTES

1 SQL

stands for Structured Query Language
is an ANSI (American National Standard Institute) standard
standard language for accessing & manipulating db
used in various db(RDBMS) like Access, (MS) SQL Server,Oracle, (IBM) DB2, Sybase,MYSQL, etc
it is not case sensitive
What can it do? it can create database,sp,tables,views,functions,triggers
it can retrieve,insert,update,delete records from/in a db
it can execute queries against a db
it can set permissions on tables,sp,view
Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
Some DB systems require a semicolon at the end of each SQL stmnt, usually it is required by them to allow more than one SQL stmnt to be executed in the same call to the server.
it is divided into 2 parts DML & DDL

2 Website using SQL

We need following to build website using sql to access data

an RDBMS db program like MS Access,SQL Server, MYSQL etc
a server-side scripting language like PHP, ASP
SQL
HTML/CSS

3 RDBMS

Relational DataBase Management System
it is base for SQL & all modern db systems
it has data(cell) column,row(record),table,db,etc

4 DML

stands for Data Manipulation Language
it is formed by Query & Update Commands

SELECT --->extracts data from a db
UPDATE--->updates data in a db
DELETE---> deletes data from a db
INSERT INTO---->inserts new data into a db

5 DDL

stands for Data Definition Language
it permits db tables to be created,deleted
it also define indexes(keys),specify links b/w tables,& impose constraints b/w tables

DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL DDL

e.g.

CREATE DATABASE

USE db1
CREATE DATABASE --->creates a new db
CREATE DATABASE db1

ALTER DATABASE

ALTER DATABASE-------->modifies a db

DROP DATABASE

used to delete the db
DROP DATABASE db1
drop multiple dbs

DROP DATABASE db1,db2,db3,db4

CREATE TABLE

CREATE TABLE------------>creates a new table

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255)
)

sql Constraints

used to limit the type of data that can go into a table
it can be specified when a table is created/altered
NOT NULL constraint

it enforces a column to NOT accept NULL values
by default , a table column can hold NULL values
it enforces a field to always contain a value.so u can't insert a new record or update a record without adding a value to this field

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255)
)

UNIQUE constraint

it uniquely identifies each record in a db table
both UNIQUE & PRIMARY KEY constraints provide a guarantee for uniqueness for a col/set of cols
PRIMARY KEY constraints automatically has a UNIQUE constraint defined on it
we may have many UNIQUE constraints per table but only one PRIMARY KEY constraint per table

different Constraints on diff cols

In SQL Server/oracle/access

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255)
)

In MYSQL

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

one time on diff cols (multiple cols)(mysql/sqlserver/oracle/access)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

Constraints on ALTER TABLE stmnt

ALTER TABLE Persons ADD UNIQUE (P_Id)

for multiple cols

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

DROP CONSTRAINT

to DROP Unique
In SQLServer/Oracle/MS Access

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

In MySQL

ALTER TABLE Persons DROP INDEX uc_PersonID

PRIMARY KEY constraint

it uniquely identifies each record in a db table
Primary Key col must contain unique values, can't contain NULL values, ONLY one Primary Key of each table
each table should have a PK & each table can have only one PK
In sqlserver/oracle/access

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255)
)

for MySQL

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
PRIMARY KEY (P_Id)
)

use PK(Primary Key) on multiple cols

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

PK on ALTER TABLE

ALTER TABLE Persons ADD PRIMARY KEY (P_Id)

on multiple cols

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

if you use ALTER TABLE stmnt to add PK, PK col(s) must already have been declared to not contain NULL values

DROP CONSTRAINT

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
In MySQL

ALTER TABLE Persons DROP PRIMARY KEY

FOREIGN KEY constraint

FOREIGN KEY in one table points to a PK in another table
This constraint is used to prevent actions that would destroy link b/w tables.
it also prevents that invalid data is inserted into the foreign key column, since it has to be one of the values contained in the table it points to.
In SQL Server,Oracle,MS Access

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

In MYSQL

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

allow naming of FOREIGN KEY constraint & apply on multiple

CREATE TABLE Orders
(
O_Id int NOT NULL,
columns OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

using ALTER stmnt

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons (P_Id)

allow naming of FOREIGN KEY constraint & apply on multiple columns

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons (P_Id)

Drop FK constraint

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

In MySQL

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

CHECK constraint

CHECK constraint is used to limit the value range that can be placed in a column/table.
for col

if define on a single col it allow only certain values for this col.

for table

if define on a table, it can limit the values in certain cols based on values in other cols in the row.

E.g.

Below constraint specifies that col "P_Id" must only include intergers >0
For SQL Server, Oracle, MS Access

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
)

In MySQL

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
CHECK (P_Id>0)
)

allow naming of CHECK constraint & apply on multiple columns

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes' )
)

using ALTER stmnt

ALTER TABLE Persons
ADD CHECK (P_Id>0)

for naming & multiple cols

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

Drop CHECK constraint

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

DEFAULT constraint

used to insert a default value into a col & default value will be added to all new records, if no other value is specified
E.g.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Delhi'
)

it can also be used to insert system values, by using functions like GETDATE()

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

using ALTER stmnt

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Delhi'
in MySQL

ALTER TABLE Persons
ALTER City SET DEFAULT 'Delhi'

drop DEFAULT constraint

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
in MySQL

ALTER TABLE Persons
ALTER City DROP DEFAULT

AUTO INCREMENT field

Auto-increment allows a unique number to be generated when a new record is inserted into a table.
very often we would like the value of the PK field to be created automatically every time a new record is inserted.
In sql server

ms sql server uses IDENTITY keyword to perform an auto-increment feature.
by default, starting value for IDENTITY is 1 & it will increment by 1 for each new record
to sepecify starting value=10 & increment by 5, change the IDENTITY to IDENTITY(10,5)
To insert a new record , we don't need to specify a value for the AUTO-INCREMENT field, a unique value will be added automatically.
E.g.

CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Delhi'
)

E.g. with different start no

CREATE TABLE Persons

(
P_Id int PRIMARY KEY IDENTITY(10,5),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Delhi'
)

In ms access

CREATE TABLE Persons

(
P_Id int PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

NOTE: use AUTOINCREMENT(10,5) for same purpose as in ms sql server

In MySQL

CREATE TABLE Persons

(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

In MySQL to start with other value than 1

ALTER TABLE Persons AUTO_INCREMENT=100

In Oracle

in oracle, to use autoincrement field, we will have to create an auto-increment field with the sequence object (this object generates a number sequence)
E.g.

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

above code creates a sequence object that starts with 1 & will increment by 1. It will also cache up to 10 values for performance.
The cache option specifies how many sequence values will be stored in memory for faster access.
E.g.

INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES(seq_person.nextval,'Ram', 'Mohan')

To insert a new record, we will have to use the nextval function of seq_person object to retrieve the next value

ALTER TABLE

used to add,delete or modify cols in an existing table.
add col

ALTER TABLE Persons
ADD DateOfBirth date

modify(change data type)

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

delete a col

NOTE: some db not allow deleting the column so check your db
E.g.

ALTER TABLE Persons
DROP COLUMN DateOfBirth

DROP TABLE

DROP TABLE----------------> deletes a table
E.g

DROP TABLE Persons

CREATE INDEX

CREATE INDEX------------->creates an index(search key)
used to create indexes in tables either on single col or on multiple cols to find data more quickly & efficiently.
it allow db application to find data fast; without reading the whole table
user/developer can't see the indexes, they are just used to speed up searches/queries.
NOTE: updating a table with indexes takes more time that updating a table without (since indexes also need an update). So you should only create indexes on cols (& tables) that will be frequently searched against.
NOTE:systax for creating indexes varies amongst diff db. So check its syntax in your db
syntax (allow duplicate)

In below syntax duplicate values are allowed.

CREATE INDEX a
ON table1 (col1)

syntax (duplicate not allowed)

e.g.

CREATE UNIQUE INDEX a
ON table1 (col1)

e,g,

CREATE INDEX PIndex
ON Persons (LastName)

on multiple cols

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

DROP INDEX

DROP INDEX----------------> deletes an index
In MS SQL SERVER

DROP INDEX Persons.Pindex

In MS Access

DROP INDEX Pindex ON Persons

In DB2/Oracle

DROP INDEX Pindex

In MySQL

ALTER TABLE Persons DROP INDEX Pindex

CREATE VIEW stmnt

a view is a virtual table based on the result-set of an SQL stmnt either from one or more tables
We can add SQL functions,WHERE,JOIN etc stmnt to a view & present the data as if the data were coming from one single table.
a view always shows up-to-date data! The db engine recreates the data, using the view's SQL stmnt, every time a user queries a view.
If you have NORTHWIND db , u can see that it has several views installed by default. E.g.
e.g.1

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

we can query the above view as follows:

SELECT * FROM [Products Above Average Price]

e.g.2

below view calculates the total sale for each category in 1997.NOTE: it selects its data from another view

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

accessing the view

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

modify a view

we can update a view by using "CREATE OR REPLACE VIEW" syntax
below code add a new col "Category" in view "Current Product List"

CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued='No'

DROP VIEW stmnt

delete a view

DROP VIEW [Current Product List]

DML DML DML DML DML DML DML DML DML DML DML DML DML DML DML

6 SELECT stmnt

select stmnt is used to select data from a db & result is stored in a result table called "result-set"
* the asterisk (*)

the asterisk (*) is used to select all columns

SELECT * FROM customers

selected column

it return only those column which are mentioned in SQL stmnt

SELECT firstname,city FROM customers

Distinct

this keyword is used to return only distinct(different) values

SELECT DISTINCT city FROM customers

WHERE clause

used to filter records ie to extract only those records that fulfill a specified criterion
SQL uses single quotes arount text,date & time values(most db systems will also accept double quotes)
Numeric values should not be enclosed in quotes
Operators allowed in it

(= , <> , > , < , >= , <= )
note:- in some versions of SQL <> operator may be written as != )

SELECT Company,Country FROM Customers WHERE country <> 'USA'
SELECT Company,Country FROM Customers WHERE sal >= 250250

BETWEEN

-> between an inclusive range ( same as >= and <=)
used to select a range of data b/w two values (both inclusive).values may be no,text,dates

SELECT * FROM Persons WHERE LastName BETWEEN 'Arun' AND 'Sachin'

in different db, either 1st value or 2nd value or both or neither is inclusive , so check your db

NOT BETWEEN

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Arun' AND 'Sachin'

-> If you know the exact value you want to return for at least one of the columns
allow to specify multiple values in a WHERE clause

SELECT * FROM Persons WHERE LastName IN ( 'Kakkar' , 'Yadav')

NOT IN

SELECT * FROM Persons WHERE LastName NOT IN ( 'Kakkar' , 'Yadav')

LIKE

->Search for a pattern, can use wild cards
used to search a specified pattern in a column

SELECT * FROM customers WHERE companyname LIKE 'a%'
SELECT * FROM customers WHERE companyname LIKE '%tav%'

NOT LIKE

SELECT * FROM customers WHERE companyname NOT LIKE '%tav%'
SQL Wildcards

used to search data in db
%

used to search data in db

substitute for exactly one character
SELECT * FROM customers WHERE companyname LIKE '_av'
SELECT * FROM customers WHERE companyname LIKE 'A_u_Kakkar'

[charlist]

any single character in charlist
SELECT * FROM customers WHERE companyname LIKE '[avt]%'
above will display all records where companyname begins with either a or v or t

[^charlist] or [!charlist]

any single character not in charlist
SELECT * FROM customers WHERE companyname LIKE '[!avt]%'
above will display all records where companyname not begins with either a or v or t

sql AND operator

used to filter records based on more than one conditions
display a record only if both the first condition & the 2nd condition is TRUE
SELECT * FROM persons WHERE FirstName='Tove' AND LastName='Svendson'

sql OR operator

used to filter records based on more than one conditions
display a record if either the first condition or the 2nd condition is TRUE
SELECT * FROM persons WHERE FirstName='Tove' OR LastName='Svendson'

combining AND & OR

can be combined by using parenthesis to form complex expressions
SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')

ORDER BY keyword

sort result-set by a specified column, by default it sort in ascending order (ASC)
to sort in descending order use DESC keyword
SELECT * FROM Persons ORDER BY LastName
SELECT * FROM Persons ORDER BY LastName DESC

IS NULL operator

NOTE:

Null

is an absence of a value.
NULL isn't allocated any memory
the string with NUll value is just a pointer which is pointing to nowhere in memory.
Null has no bounds, it can be used for string, integer, date, etc. fields in a database.
Null is the database's determination of an absense of a value logically, so to speak
By default, a table column can hold NULL values.
it is not possible to compare NULL & 0 . They are not equivalent.
SELECT * FROM Persons WHERE Address IS NULL

An empty string

is a value, but is just empty.
Empty IS allocated to a memory location, although the value stored in the memory is "".
Empty string is just regarding a string; it's a string like 'asdfasdf' is, but is just has no length.
If you have no value for a field, use null, not an empty string.

IS NOT NULL operator

SELECT * FROM Persons WHERE Address IS NOT NULL

INSERT INTO stmnt

used to insert a new row/record in a table
one way (Method)

doesn't specify column names where the data will be inserted, only their values
INSERT INTO Persons VALUES (4,'Nilsen' , 'Johan', 'Bakken2' , 'Stavanger' )

2nd way (Method)

it specifies both the column names & values to be inserted
INSERT INTO Persons (P_Id, LastName,FirstName) VALUES (5,'Tom', 'Ram')

3rd Way (Method)

INSERT INTO TempTbl(ClientName,Item) SELECT ClientName,Item FROM TempTbl WHERE Srno=948

in older version , it represent left outer join

in older version , it represent right outer join

use Statement

USE Master
USE MyDataBase1

E.g of some important/Usefull queries showing use of various operators/keywords

e.g1

select productmaster.SHOPNO,productmaster.MODELNO,

isnull(sum(isnull(ProductMaster.Qty,0)),0) as PQTY,
case productmaster.MODELNO

when productmaster.MODELNO

then (Select isnull(sum(isnull(ComponentMaster.Qty,0)),0) from ComponentMaster

where ComponentMaster.MODELNO=ProductMaster.MODELNO and ComponentMaster.SHOPNO=ProductMaster.SHOPNO)

else 0
end as SQTY,

case productmaster.MODELNO

when productmaster.MODELNO

then (isnull(sum(isnull(ProductMaster.Qty,0)),0)-(Select isnull(sum(isnull(ComponentMaster.Qty,0)),0) from ComponentMaster

where ComponentMaster.MODELNO=ProductMaster.MODELNO and ComponentMaster.SHOPNO=ProductMaster.SHOPNO))

else 0
end as BALQTY

From PRDMODELSTKDTL
left outer JOIN PRODUCTMASTER ON PRDMODELSTKDTL.modelno=productmaster.MODELNO
group by PRODUCTMASTER.SHOPNO,PRODUCTMASTER.MODELNO

UPDATE stmnt

used to update existing records in a table
in it WHERE clause specifies which record or records should be updated.
if WHERE clause is omitted , all records will be updated
UPDATE Persons SET Address='Abc' , City='pqr' WHERE LastName='m' AND FirstName='mm'

DELETE stmnt

used to delete records/rows in a table
in it WHERE clause specifies which record or records should be deleted.
if WHERE clause is omitted , all records will be deleted
DELETE FROM Persons WHERE LastName='abc' AND FirstName='p'
delete all rows

one way

DELETE FROM Persons

2nd way

DELETE * FROM Persons

3rd way

using TRUNCATE stmnt

delete all rows from the table
TRUNCATE TABLE Persons

TOP Clause

used to specify the no of records to return
Note:->not all db systems support the TOP clause
SELECT TOP no|percent col1 FROM T1
SELECT TOP 2 * FROM Persons
SELECT TOP 50 PERCENT * FROM Persons
Top in MySQL

SELECT * FROM Persons LIMIT 5

Top in Oracle

SELECT * FROM Persons WHERE ROWNUM <= 5

AS (SQL Alias)

an alias name can be given to a table or to a column with alias
E.g with Alias

SELECT t1.OrderID,t2.LastName,t2.FirstName
FROM Persons AS t2 , Product_Orders AS t1
WHERE t2.LastName='Ram' AND t2.FirstName='Mohan'

E.g without alias

SELECT Product_Orders.OrderID,Persons.LastName,Persons.FirstName

FROM Persons , Product_Orders
WHERE Persons.LastName='Ram' AND Persons.FirstName='Mohan'

sample tables ->

Persons

P_Id LastName FirstName Address City
1 Kakkar Arun C-13 Delhi
2 Gupta Vikas H.No. 323 VikasPuri

Orders

O_Id OrderNo P_Id OrderDate OrderPrice Customer
1 77895 3 07/05/2010 25000 Ram
2 44678 3 05/05/2010 800 Mohan
3 22456 1 07/04/2010 4100 Ganesh
4 24562 1 07/05/2009 8500 Mohan
5 34764 15 25/03/2010 98500 Ram

Employees_Norway

E_Id E_Name
01 Ram
02 Krishan
03 Mohan
04 Jesus

Employees_USA

E_Id E_Name
01 Vishnu
02 Krishan
03 Ganesh
04 Brahma

SQL JOINS

JOIN keyword is used to query data from 2 or more tables, based on a relationship b/w certain cols in these tables

Types of JOINS

INNER JOIN

return rows when there is atleast one match in both tables
Join

SELECT p.LastName,p.FirstName, or.OrderNo
FROM Persons as p INNER JOIN Orders as or ON p.p_Id=or.P_Id
ORDER BY p.LastName

above query return all the persons with any orders.

Self-Join
Natural-Join

OUTER JOIN

Left Join (Left Outer Join)

return all rows from left table, even if there is no matches in right table
in some db, Left Join is called Left Outer Join
below query show all persons & their orders if any (if for a persons orderno not present then orderno will be blank

SELECT P.LastName, Or.OrderNo
FROM Persons as P LEFT JOIN Orders as Or ON P.P_Id=Or.P_Id
ORDER BY P.LastName

it returns all rows from persons even if there are no matches in orders table

Right Join (Right Outer Join)

return all rows from right table, even if there is no matches in left table
below query return all orders with containing persons if any

SELECT P.LastName, Or.OrderNo
FROM Persons as P RIGHT JOIN Orders as Or ON P.P_Id=Or.P_Id
ORDER BY P.LastName

it returns all rows from Orders even if there are no matches in Persons table

Full Join

return rows when there is a match in one of the tables
below returns all persons & their Orders & all orders with their persons

SELECT P.LastName, Or.OrderNo
FROM Persons as P FULL JOIN Orders as Or ON P.P_Id=Or.P_Id
ORDER BY P.LastName

it returns all rows from persons & all rows from orders table

CROSS JOIN

sql UNION Operator

it combines the result-set of two or more SELECT stmnts
each SELECT stmnt must have same no of cols,similar data types, & all cols must be in same order
it selects only distinct values by default
cols names in result-set of a UNION are always equal to the col names in the first SELECT stmnt in UNION
Below query list all DIFFERENT employees in Norway & USA

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

above query will return krishan only once

UNION ALL

it is used to allow duplicate values, which is not possible in UNION operator
Below query list all employees in Norway & USA

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

above query will return krishan Twice

sql SELECT INTO stmnt

used to create backup copies of tables
it select data from one table & inserts it into a different table
copy in same db

SELECT * INTO Persons_BackUp FROM Persons

copy in diff db

SELECT * INTO Persons_BackUp IN 'Backup.mdb' FROM Persons

copy only some cols

SELECT LastName,FirstName INTO Persons_Backup FROM Persons
WHERE city='Delhi'

copy from 2 tables

SELECT P.FirstName, Or.OrderNo INTO Persons_Orders_Backup
FROM Persons as P INNER JOIN Orders as Or ON P.P_Id=Or.P_Id

SQL built-in functions->

SQL DATES functions

The most difficult part when working with the dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the db.
As long as your data contains only the date portion, your queries will work as expected.However, if a time portion is involved, it gets complicated.
In SQL Server built in Date functions

GETDATE()

Returns the current date and time

DATEPART()

Returns a single part of a date/time

DATEADD()

Adds or subtracts a specified time interval from a date

DATEDIFF()

Returns the time between two dates

CONVERT()

Displays date/time data in different formats

data types for date in sql server

sql server has following data types for storing a date or a date/time value in the database;

DATE -->

format YYYY-MM-DD

DateTime --->

format: YYYY-MM-DD HH:MM:SS

SmallDateTime -->

format: YYYY-MM-DD HH:MM:SS

TIMESTAMP -->

format : a unique number

In MySQL built-in date functions

NOW()

Returns the current date and time

CURDATE()

Returns the current date

CURTIME()

Returns the current time

DATE()

Extracts the date part of a date or date/time expression

EXTRACT()

Returns a single part of a date/time

DATE_ADD()

Adds a specified time interval to a date

DATE_SUB()

Subtracts a specified time interval from a date

DATEDIFF()

Returns the number of days between two dates

DATE_FORMAT()

Displays date/time data in different formats

data types for date in MySQL

DATE -->

format YYYY-MM-DD

DATETIME --->

format: YYYY-MM-DD HH:MM:SS

TIMESTAMP -->

format: YYYY-MM-DD HH:MM:SS

YEAR -->

format YYYY or YY

SELECT * FROM Orders WHERE OrderDate='2008-11-11'
if orderdate don't have time component, we will get proper result.
if orderdate have time component, we will not get any resultant row.
if you want to keep your queries simple & easy to maintain, do not allow time components in your date field.

sql NULL functions

ISNULL() ( In SQL Server & MS Access)

SELECT Name,Price*(Units +ISNULL(extraUnits,0)) FROM OrdersBill

NVL() (In Oracle)

SELECT Name,Price*(Units +NVL(extraUnits,0)) FROM OrdersBill

IFNULL() (mysql)

SELECT Name,Price*(Units +IFNULL(extraUnits,0)) FROM OrdersBill

COALESCE() (mysql)

SELECT Name,Price*(Units +COALESCE(extraUnits,0)) FROM OrdersBill

SQL Agregate functions

they return a single value, calculated from values in a column.
Aggregate functions often need an added GROUP BY stmnt
AVG()

Returns the average value of a numeric col
SELECT AVG(sal) AS AverageSalary FROM Persons
below query show all persons whom salary is above the average salary

SELECT * FROM Persons
WHERE sal>(SELECT AVG(sal) FROM Persons)

COUNT()

returns the number of rows/records
null values wil not be counted of the specified column
DISTINCT

will count the no of distinct values ot the specified col

SELECT COUNT(Ename) AS EmployeeName FROM Persons

WHERE group='Operator'

SELECT COUNT(*) FROM Persons
SELECT COUNT(DISTINCT customer) AS TotalCustomer FROM SaleBillOrders
SELECT COUNT(Customer) AS Ram FROM Orders WHERE Customer='Ram'

MAX()

Returns the largest value of selected col
SELECT MAX(Age) AS OldestEmplyeeAge FROM Persons

MIN()

returns the smallest value of the selected col
SELECT MIN(Age) AS YoungestEmplyeeAge FROM Persons

SUM()

returns the total sum of a numeric col
SELECT SUM(Sal) As TotalSal FROM Persons

FIRST()

return the first value of selected col
SELECT FIRST(Name) AS PersonName FROM Persons
if first not work in db

SELECT Name FROM Persons ORDER BY O_Id LIMIT 1

LAST()

return the last value of selected col
SELECT LAST(Name) AS PersonName FROM Persons
if Last() not work in db

SELECT Name FROM Persons ORDER BY O_Id DESC LIMIT 1

GROUP BY stmnt

It is often needed by Aggregate functions to group the result-set by one or more cols
SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders

GROUP BY Customer,OrderDate

HAVING clause

mostly used with GROUP BY stmnt to provide conditions as WHERE keyword
can't be used with aggregate functions.
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000
SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Ram' OR Customer='Mohan' GROUP BY Customer HAVING SUM(OrderPrice)<2000

SQL SCALAR functions

return a single value based on the input value
UCASE() or UPPER() for SQL server

converts a value to upper case
SELECT UCASE(LastName) AS LastName,FirstName FROM Persons
UPPER() for SQL server

SELECT UPPER(LastName) AS LastName,FirstName FROM Persons

LCASE() or LOWER() in SQLserver

converts a value to lower case

SELECT LCASE(LastName) AS LastName,FirstName FROM Persons
LOWER() in SQLserver

SELECT LOWER(LastName) AS LastName,FirstName FROM Persons

MID()

extract characters from a text field syntax
SELECT MID(col_Name,Start [,length]) FROM table_name

start----> starting position (starts as 1)

SELECT MID(City,1,4) AS SmallCity FROM Persons

LEN()

return the length of a text field
SELECT LEN(Address) AS LengthOfAddress FROM Persons

ROUND()

rounds a numeric field to the number of decimals specified
SELECT ProductName,ROUND(UnitPrice,0) AS UnitPrice FROM Products

NOW()

return the current system date & time
SELECT ProductName,UnitPrice,NOW() AS PerDate FROM Products

FORMAT()

formats how a field to be displayed
SELECT ProductName,UnitPrice,FROMAT(NOW(),'YYYY-MM-DD') as PerDate FROM Products

SQL HOSTING

If Your web site store & display data from a db, your web server should have access to a db system that uses SQL Language.
If your web server will be hosted by an ISP (Internet Service Provider) ie you purchase web hosting space (not uses your own pc as webserver & db), you will have to look for SQL Hosting Plans.
SQL hosting dbs

most common are MySQL, MS SQL Server,MS Access
SQLServer,MS Access

Runs only on Windows OS

MySQL

Runs on both Windows and Linux/UNIX OS (very cheap)

SQL COMMENTS

SINGLE LINE (--)

--this is SINGLE LINE comment

MULTIPLE LINES (/* */)

/* this is multiple line comment method this second line is also commented */

ConnectionString by UDL file (in VB6)

open udl file & set connection & to see whole connection string
open udl file in notepad

Miscellaneous

sysobjects (In MS SQL Server 2000)

return list of all tables,procedure,functions etc diffenciated by type
type=p for sp (stored Procedures) , U for User Defined table in db etc
E.g

select * from sysobjects order by type
Select name from sysobjects where type='U'
use logmaindata ;select name from sysobjects where type='u' order by name

above query give list of all tables in db "logmaindata"

sysdevices

return all the devices used/created while taking database backup/restore
Use Master ; select * from sysdevices

SysDatabases (In MS SQL Server 2000)

list of all database. To use it, we must use master db first of all
E.g

use master ; select * from sysdatabases order by name
SELECT COUNT (*) AS CNT FROM SYSDATABASES WHERE NAME='logmaindata'

Backup a db (In MS SQL Server 2000) using VB6

Below is a procedure in VB6 to take Backup of DB
Public Sub Manual_Backup()

On Error GoTo xer
Dim Path As String, MyFile As String, FullPath As String
Dim num As Integer
Set create = New FileSystemObject
With create

If FrmTool.Check1.Value = 1 Then 'user select default setting

Path = "C:\Back Up"
MyFile = "LogDryClnBakUp.bak"
FullPath = Path & "\" & MyFile

ElseIf FrmTool.Check1.Value = 0 Then 'if user provide path & file name

'MyFile = Trim(FrmTool.TxtName.Text)
FullPath = Trim(FrmTool.TxtPath.Text)
If FullPath = "" Then

MsgBox "BackUp has not been taken., Please Give the Path and File Name", vbInformation + vbOKOnly
Exit Sub

End If
If InStr(1, FullPath, ".bak") < 1 Then FullPath = FullPath & ".bak"
MyFile = FullPath
num = InStr(1, MyFile, "\")
If num < 1 Then MsgBox "BackUp has not been taken., Please Give the correct Path and File Name", vbInformation + vbOKOnly: Exit Sub
Do While num > 0

MyFile = Mid(MyFile, num + 1, Len(MyFile) - num)
num = InStr(1, MyFile, "\")

Loop
'MyFile = MyFile & ".bak"
Path = Mid$(FullPath, 1, InStr(1, FullPath, MyFile, vbTextCompare) - 2)

End If
If .FolderExists(Path) = False Then

CreateFolder (Path)

End If
If .FileExists(FullPath) = True Then

Kill FullPath

End If
Connect GSVRNAME, "sa", "", "master"
If GREC.State Then GREC.Close
GREC.Open "select * from sysdevices where name='" & MyFile & "'", GCON, adOpenStatic, adLockReadOnly
If GREC.EOF = False Then

strg = "exec sp_dropdevice '" & MyFile & "'"
GCON.Execute strg

End If
strg = "EXEC sp_addumpdevice 'disk', '" & MyFile & "','" & FullPath & "'"
GCON.Execute strg
strg = "BACKUP DATABASE logmaindata TO disk='" & FullPath & "'"
GCON.Execute strg
strg = "exec sp_dropdevice '" & MyFile & "'"
GCON.Execute strg

End With
MsgBox "Backup Database Successfully Saved", vbInformation + vbOKOnly
Connect GSVRNAME, "sa", "", "logmaindata"
strg = "UPDATE Confrigation SET SettingValue='N' WHERE SettingName='AutoBackup'"
GCON.Execute strg
Exit Sub

xer:

MsgBox "There is an error -> Unable to take BackUp. Please Select Path to Click Brows Button", vbInformation + vbOKOnly
Connect GSVRNAME, "sa", "", "logmaindata"

End Sub

Restore a DB (In MS SQL Server 2000) using VB6

Below is a procedure in VB6 to take Restore DB from above created backup file
Private Sub CmdRestore_Click()
On Error GoTo xer

If Me.Txtpth <> "" Then

If MsgBox("The Restore Facility Required to quit from software," & Chr(13) & " wish to continue", vbQuestion + vbYesNo) = vbYes Then

Me.CmdRestore.Enabled = False: Me.CmdReset.Enabled = False
Connect GSVRNAME, "sa", GPWD, "master"
strg = "RESTORE DATABASE logmaindata FROM disk='" & Me.Txtpth.Text & "'"
GCON.Execute strg
MsgBox "Database Has Been Successfully Restored", vbInformation + vbOKOnly
End

End If

Else

MsgBox "Please select the file", vbInformation + vbOKOnly
Exit Sub

End If
Exit Sub
xer:

CritError
'MsgBox "Database is Currently in Use Can't Restore", vbInformation + vbOKOnly

End Sub

Backup DB compressd (In MS SQL Server 2000) using VB6

Below is a procedure in VB6 to take Backup of DB in compressed format

Private Sub cmdBackup_Click()
On Error GoTo xer

If Not FS.FolderExists("c:\backup") Then

FS.CreateFolder "c:\backup"

End If
If Dir("c:\backup\Tmp", vbDirectory) <> "" Then FS.DeleteFolder "c:\backup\Tmp"
MkDir "c:\backup\tmp"
cur_data = Me.ListView1.SelectedItem.SubItems(2)
Me.ProgressBar1.Value = 10
cstr1 = GServer.GCON.ConnectionString
If GServer.GCON.STATE = 1 Then GServer.GCON.Close
cstr2 = GServer.GXCON.ConnectionString
If GServer.GXCON.STATE = 1 Then GServer.GXCON.Close
GServer.ConnectXDB gsvrname, guname, gpwd, "master"
If Cn.STATE = 1 Then Cn.Close
Cn.ConnectionString = GServer.GXCON.ConnectionString
Cn.Open
Cn.Execute "backup database " & cur_data & " to disk ='C:\Backup\Tmp\" & cur_data & "_Backup" & ".bak'"
Me.ecCompress1.FileName = "C:\Backup\" & cur_data & "_Backup" & ".cab"
Me.ecCompress1.Files.Add "C:\Backup\tmp\" & cur_data & "_Backup" & ".bak"
CheckSpace
Me.ecCompress1.Compress
If MsgBox("Backup Completed Successfuly", vbOKOnly) = vbOK Then

FS.DeleteFolder "c:\backup\tmp"
GServer.GCON.ConnectionString = cstr1
GServer.GCON.Open
GServer.GXCON.ConnectionString = cstr2
GServer.GXCON.Open
If Cn.STATE = 1 Then Cn.Close
Unload Me

Else

Unload Me

End If
If Cn.STATE = 1 Then Cn.Close
Exit Sub

xer:

If Err.Number = -2147217900 Or Err.Number <> 0 Then

Resume Next

Else

MsgBoxEx "Problem Exist Please Contact RNT COMPUTER SOLUTIONS (P) LTD.", 0, 0, BottomRight, vbCritical + vbOKOnly
Resume Next

End If

End Sub

Restore DB compressd (In MS SQL Server 2000) using VB6

Below is a procedure in VB6 to Restore Backup of DB taken above in compressed format
Private Sub RestoreBackup(DRV As Variant)
On Error GoTo er

If Dir("c:\backup\Tmp", vbDirectory) <> "" Then FS.DeleteFolder "c:\backup\Tmp"
MkDir "c:\backup\tmp"
Me.ProgressBar2.Visible = True
If Dir("C:\backup\" & Me.ListView2.SelectedItem.SubItems(2) & "_Backup.cab", vbNormal) <> "" Then

Me.ecExtract1.FileName = "c:\backup\" & Trim(Me.ListView2.SelectedItem.SubItems(2) & "_backup.cab")
Me.ecExtract1.Files.SelectAll
Me.ecExtract1.ExtractAll "c:\backup\Tmp\"
cstr1 = GServer.GCON.ConnectionString
If GServer.GCON.STATE = 1 Then GServer.GCON.Close
cstr2 = GServer.GXCON.ConnectionString
If GServer.GXCON.STATE = 1 Then GServer.GXCON.Close
GServer.ConnectXDB gsvrname, guname, gpwd, "master"
GServer.Connect gsvrname, guname, gpwd, "master"
If Cn.STATE = 1 Then Cn.Close
Cn.ConnectionString = GServer.GXCON.ConnectionString
Cn.Open
If GServer.GCON.STATE = 1 Then GServer.GCON.Close
If GServer.GXCON.STATE = 1 Then GServer.GXCON.Close
cur_data = Me.ListView2.SelectedItem.SubItems(2)
Cn.Execute ("Use Master")
Cn.Execute "restore database " & cur_data & " From disk ='C:\Backup\tmp\" & cur_data & "_Backup" & ".bak'"
If MsgBox("Restore Completed Successfuly", vbOKOnly) = vbOK Then

FS.DeleteFolder "c:\backup\tmp"
GServer.GCON.ConnectionString = cstr1
GServer.GCON.Open
GServer.GXCON.ConnectionString = cstr2
GServer.GXCON.Open

Else

Unload Me

End If

Else

GoTo ext

End If
If Dir("c:\backup\Tmp", vbDirectory) <> "" Then FS.DeleteFolder "c:\backup\Tmp"
If Cn.STATE = 1 Then Cn.Close
Unload Me
Exit Sub

ext:

MsgBox "No Backup Information For '" & Me.ListView2.SelectedItem.Text & "' Company Please Insert Another Disk", vbInformation + vbOKOnly, "Eh!"
Exit Sub

If Err.Number <> 0 Then

MsgBox Err.Description, vbCritical + vbOKOnly, "Eh!"

End If

End Sub

List of databases (In MS SQL Server 2000)

Use Master ; SELECT * FROM SYSDATABASES

Attach a database with user defined location but within same computer i.e. location must not be in network (In MS SQL Server 2000)

use master ; EXEC sp_attach_db @dbname = 'TEMPCure',

@filename1 = 'c:\Temp2\TEMPCure.mdf',
@filename2 = 'c:\Temp2\TEMPCure_log.ldf'

Deattach a database from the server (In MS SQL Server 2000)

EXEC sp_detach_db 'TEMPCure', 'true'

Convert a Column Value from "Male" to "Female" & "Female" to "Male" & leave rest as it is in single query

Its very simple If one know USE of "CASE" stmnt in SQL Query (Its very important as I am unable to clear one of my interview due to lack of its knowledge)
E.g

Ist of all I create a temporary table for testing & below are some useful Queries along with main query

use ArunDatabase1
create table temp1(sno int Primary Key Identity, sex nvarchar(10) null)
select * from temp1
Add new column in the existing table

alter table temp1 add Age int null

Insert Multiple rows in one query->1st method use ; 2nd method-> use below UNION ALL and INSERT INTO … SELECT… clauses.

INSERT INTO Temp1(Sex,age)
SELECT 'Male',20
UNION ALL
SELECT 'Female',25
UNION ALL
SELECT 'Female',30
UNION ALL
SELECT 'Male',40

UPDATE Temp1 SET
Sex=
CASE
WHEN sex='Male'
THEN 'Female'
WHEN sex='Female'
THEN 'Male'
ELSE Sex
END,
Age=
CASE
WHEN Age>35 THEN Age+3
WHEN Age<25 THEN Age+2
WHEN Age IS NULL THEN 20
ELSE AGE
END

Below are some more Useful examples to make the use of Case statement more clearer
E.g 1

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

below query give same result as above one

SELECT title, price, 'Expensive'

FROM titles
WHERE price > 20.00

UNION ALL

SELECT title, price, 'Moderate'

FROM titles
WHERE price BETWEEN 10.00 AND 19.99

UNION ALL

SELECT title, price, 'Inexpensive'

FROM titles
WHERE price < 10.00

UNION ALL

SELECT title, price, 'Unknown'

FROM titles
WHERE price IS NULL

E.g 2

Before using below query I create a table to execute below queries

CREATE TABLE prodsales
(product char(3),
mnth smallint,
sales money)

Now queries using above created tables are as below

SELECT product,
SUM(CASE mnth WHEN 1 THEN sales ELSE NULL END) AS jan,
SUM(CASE mnth WHEN 2 THEN sales ELSE NULL END) AS feb,
SUM(CASE mnth WHEN 3 THEN sales ELSE NULL END) AS mar,
SUM(CASE mnth WHEN 4 THEN sales ELSE NULL END) AS apr,
SUM(CASE mnth WHEN 5 THEN sales ELSE NULL END) AS may,
SUM(CASE mnth WHEN 6 THEN sales ELSE NULL END) AS jun,
SUM(CASE mnth WHEN 7 THEN sales ELSE NULL END) AS jul,
SUM(CASE mnth WHEN 8 THEN sales ELSE NULL END) AS aug,
SUM(CASE mnth WHEN 9 THEN sales ELSE NULL END) AS sep,
SUM(CASE mnth WHEN 10 THEN sales ELSE NULL END) AS oct,
SUM(CASE mnth WHEN 11 THEN sales ELSE NULL END) AS nov,
SUM(CASE mnth WHEN 12 THEN sales ELSE NULL END) AS dec
FROM prodsales
GROUP BY product

E.g 3

SELECT product,
SUM(CASE WHEN mth BETWEEN 1 AND 3 THEN sales
ELSE NULL END) AS q1,
SUM(CASE WHEN mth BETWEEN 4 AND 6 THEN sales
ELSE NULL END) AS q2,
SUM(CASE WHEN mth BETWEEN 7 AND 9 THEN sales
ELSE NULL END) AS q3,
SUM(CASE WHEN mth BETWEEN 10 AND 12 THEN sales
ELSE NULL END) AS q4
FROM prodsales
GROUP BY product

E.g 4

SELECT au_lname, au_fname, title, Category =
CASE
WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
WHERE t.title_id = ta.title_id) > 65
THEN 'Very High'
WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
WHERE t.title_id = ta.title_id)
BETWEEN 55 and 64
THEN 'High'
WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
WHERE t.title_id = ta.title_id)
BETWEEN 41 and 54
THEN 'Moderate'
ELSE 'Low'
END
FROM authors a,
titles t,
titleauthor ta
WHERE a.au_id = ta.au_id
AND ta.title_id = t.title_id
ORDER BY Category, au_lname, au_fname

Below query find 2nd Lowest age but if there is duplicacy then it gives wrong result

SELECT TOP 1 Age FROM
(SELECT TOP 2 Age FROM Temp1 ORDER BY Age ASC) AS SeconLowestAge
ORDER BY Age DESC

Below query find 2nd Lowest age but if there is duplicacy then also it gives correct result

SELECT TOP 1 Age FROM
(SELECT TOP 2 Age FROM
(SELECT DISTINCT Age FROM Temp1) AS T1
ORDER BY Age ASC) AS SeconLowestAge
ORDER BY Age DESC

Above queries run well in SQL Server 2000, but in SQL Server2005, you can't use Order by clause in the subquery, so these would not work there.
Below query find nth highest age using Joins

Note: here let n=5 to find 5th highest age for table
CREATE TABLE temp1
(
sno INT PRIMARY KEY IDENTITY,
sex NVARCHAR(10) NULL,
age INT NULL
)
select * from temp1 t1

where 5-1=(select count(distinct age) from temp1 t2

where t2.age>t1.age)

Below query find nth Lowest age using Joins
fill table from another table
create table temp2
(
id int identity not null,
myName nvarchar(20)
)
insert into temp2(myName)
select sex from temp1
Make SQL Query to get columns from diff small queries as diff column names
select (select ReceiptId from FeeReceiptMain where ReceiptNo='REC-1') as FromFeeId, (select ReceiptId from FeeReceiptMain where ReceiptNo='REC-10') as ToFeeId

I hope it will help those who wish to make quick revision of sql. Please forgive me if I made any mistake. I made these notes keeping my habit of revising a topic & due to lack of time & length of this blog, I don't add many comments to explain it. Its just notes for quick revision not for any fresher. Of course, there are lots of topic left in it but its just the overview of some of the main topics which are used most frequently.

I keeps on updating it as I get time & would like & appreciate your comments to improve it.

Basic Computer Course to be good in Microsoft Computers

2010-09-30T22:35:00.000-07:00

Ram Ram Friends

I made following notes while teaching Computer Basics at a computer institute as I have no syllabus offered to teach, but based on my capability at that time, I made these notes.

Computer Basics

Contents:-

1 Computer Fundamentals

2 Windows

3 Paint

4 Notepad

5 Word Pad

6 M S Word

7 M S Excel

8 M S Power Point

9 Internet

1 Computer Fundamentals

Contents:-

1 Computer
2 Strength of Computer
3 Weakness of Computer
4 Functioning of Computer (I-P-O Cycle)
5 Input Devices
6 Output Devices
7 CPU
8 Memory &its type
9 Hierarchy of memory storage media
10 Storing Method in different storage media
11 Unit of Memory
12 Storing Capacity of various memory devices
13 H/W & S/W
14 Types of S/W

2 Windows

Contents:-

1 Hard disk & its Partition
2 drive, folder, & file
3 File Address & its hierarchy
4 Creating Folders, Deleting files
5 Cut, Copy & Paste files, folders among folders, drives.
6 GUI & CUI

1 Computer Fundamentals

1 Computer ->

A computer is an electronic device that can perform a variety of operations in accordance with a set of instructions called programs.

Computer takes some order (input) then perform (process) the task to complete the order & give result (output).

2 Strength of Computer ->

A. Speed ->

B. High storage capacity ->

C. Accuracy ->

D. Reliability ->

E. Versatility ->

1. Repetitive jobs

2. Hazardous jobs

3 Weakness of Computer ->

Zero IQ
Lack of Decision making power

5 Input Devices ->
These are the machines, which are used to give input (order) to the computer.

Following are the various kinds of input devices: -
1. Keyboard
2. Mouse
3. Mike
4. Web Camera
5. Joystick
6. Scanner
7. Light-pen
8. Track ball
9. MICR (Magnetic Ink Character Recognition)
10. OMR (Optical Mark Recognition)
11. OCR (Optical Character Recognition)
12. Touch screen

6 Output Devices ->
These are the machines, which are used to display output (result) from the computer.

Following are the various kinds of output devices: -
1. VDU (Visual Display Unit) / Monitor
2. Speakers
3. Printers
4. Plotters

7. C P U (Central Processing Unit ) ->

CPU has two parts
1. ALU (Arithmetic & Logical Unit)
2. CU (Control Unit)

1. ALU (Arithmetic & Logical Unit) ->
It has two parts

A. Arithmetic Unit -> It is used to perform the arithmetic operations like

Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
It also perform comparison operations like
Equal (=)
Less than (<) Less than or equal to (<=) Greater than (>)
Greater than or equal to (>=)

B. Logical Unit -> It is used to perform the logical operations like

AND
OR
NOT

2. CU (Control Unit) ->

It controls the flow of operations and instructions . i.e. it controls which instruction will be executed first & which instruction will follow it etc.

8. Memory &its type ->

Memory -> It is used for storing both instructions and data.

There are two kinds of memory

a. Primary memory/Main memory/Memory/volatile memory ->
It can store data & instructions in it till the electricity is on or till the system (computer) is on.

Once the computer is turned off (shut down) , then all the data stored in it , will be deleted automatically & we can't get that data again, because it is permanently removed from the memory.

b. Secondary memory/non-volatile memory/ Storage media/ Permanent memory ->
It can store data in it in both situations like when the computer is on & when the computer is off.

9 Hierarchy of memory storage media

Memory

Primary Memory

RAM (Ramdom Access Memory)
ROM (Read Only Memory)

Secondary Memory

Magnetic Media

Floppy
Hard Disk

Optical Media

CD (Compact Disk)
DVD (Digital Versatile/Video Disk)

IC (Chip) Media

Pen Drive
Flash Drive

10 Storing Method in different storage media
11 Unit of Memory
12 Storing Capacity of various memory devices

Unit of memory:-

1Bit= 1 or 0
1 Byte = 8 bits= 1 character
1 KB (Kio Byte) = 2^10 Bytes= 1024 Bytes= 1000 Bytes nearly
1MB (Mega Byte) = 2^10 KB= 1024 KB
1 GB (Giga Byte) = 2^10 MB
1 TB (Tera Byte) = 2^10 GB
Note: 2^10 means 2 raise to the power 10

1 floppy = 1.44 MB
1 RAM = 32 MB, 64 MB, 128 MB, 256 MB, 512 MB
1 CD = 700 MB
1 DVD = 4 GB to 17 GB
1 Hard Disk (HD) = 5 GB,10 GB, 20 GB, 40 GB, 80 GB,160 GB, 250 GB, 500 GB,1TB etc

Internet-> Network of network

Network -> a collection of more than one computer so that they can communicate with each other i.e. they can exchange information or files & folders with each other

Modem =>It is a machine which do modulation & demodulation

Mo=Modulation->Conversion of analog form of data into digital form of data

Dem=Demodulation-> Conversion of digital form of data into analog form of data

Analog form of data -> Data present in telephone line

Digital form of data -> data present in computer etc.

Functioning of internet -> Internet is working with the help of hypertext.

Hypertext -> It is a text/word/small line , written in blue color,& underline, & when mouse pointer is come over it then it gain the shape of a hand which point something with index finger.
Hypertext work by HYPERLINK
i.e. when we click on hypertext then it open a new page

ISP ->Internet Service Provider
e.g. VSNL, Satyam

websites

Home page – first page of any website

public website -> Provide free ‘e- mail’ facility

http => Hyper Text Transfer Protocol (rules)

www =>World Wide Web
e.g.
http://www.yahoo.com
http://www.google.com
http://www.rediff.com
http://www.rediffmail.com
http://www.indiatimes.com
http://www.jagran.com

private website :->

http://www.nokia.com
http://www.cbse.co.in
http://www.mtnl.co.in

e-mail (Electronic Mail) ->

To send or receive messages across the world.
For this You must have an address known as e-mail address.
e.g.
arunkakkar1@gmail.com
arunkakkar1@yahoo.com
arunkakkar1@hotmail.com

Difference between - (hyphen or minus or dash) & _ (underscore)
a-k (hyphen) read as "a hyphen k"
a_k (underscore) read as "a underscore k"

Its just the basic computer course, After learning it, you are open & of course ready to face the world of computing with lots of confidence.

My TimePass

2010-08-04T01:57:00.000-07:00

Its very difficult to pass time if you don't have atleast one kind of passion.

Dot Net as whole vs by many institutes

2010-04-08T22:48:00.000-07:00

Hi Friends
Most of us hear about dot net from various institutes.
Almost all computer institutes claim to teach complete dot net, but I found most of them are fraud since they teach only very small portion of dot net.

Whole dot net have many tools
But I am paying more focus only on programming tools.
As per my knowledge, dot net has following programming tools
1 vb.net
2 C#.net
3 V C++

And for web development both vb.net & c#.net are used with asp.net as server site scripting language & java script as client site script language
Sql server as database

Most of institutes teaches only c#.net but that also to a limited level

So my suggestion is that just beware of such institutes or set your mind before joining them.

Wish a best of luck for new software/web developers