Changing Crystalreport database connection at runtime

CrxReport.Database.Tables(1).ConnectionProperties.Item("Data source") = strserver
CrxReport.Database.Tables(1).ConnectionProperties.Item("User ID") = struser
CrxReport.Database.Tables(1).ConnectionProperties.Item("Initial Catalog") = strdatabase
CrxReport.Database.Tables(1).ConnectionProperties.Item("password") = strpassword

insert word file into the current document

m_WordApp.Selection.InsertFile filename:=(App.Path & "\premium info link.doc"), _
Range:="", ConfirmConversions:=False, Link:=False, Attachment:=False

Word macro to find and replace text with a image file

With m_WordApp.Selection.Find
.ClearFormatting
.Text = fvalue
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
Do While .Execute
m_WordApp.Selection.InlineShapes.AddPicture _
filename:=(App.Path & "\ABC HMO_CA.jpg"), _
LinkToFile:=False, SaveWithDocument:=True
m_WordApp.Selection.Collapse wdCollapseEnd
Loop
End With

How to use CASE in ORDER BY?

The following script sorts upper case addresses alphabetically followed by address lines starting with digits and finally lower case or non-alpha characters:

Use AdventureWorks;

Go

Select

AddressLine1,

AddressLine2=isnull(AddressLine2,''),

City,

[State] = sp.StateProvinceCode,

PostalCode,

Country = cr.Name

From Person.[Address] a

Join Person.StateProvince sp

On a.StateProvinceID = sp.StateProvinceID

Join Person.CountryRegion cr

On sp.CountryRegionCode = cr.CountryRegionCode

Order by

(Case

When Ascii([AddressLine1]) between 65 and 90 then 0 -- Upper case alpha

When Ascii([AddressLine1]) between 48 and 57 then 1 -- Digits

Else 2

End), AddressLine1, City

Go

Partial results:

AddressLine1	AddressLine2	City	State	PostalCode	Country
Adirondack Factory Outlet		Lake George	NY	12845	United States
Alderstr 1849		Braunschweig	NW	38001	Germany
Alderstr 2577		Poing	SL	66041	Germany
Alderstr 2646		Saarlouis	SL	66740	Germany
Alderstr 27		Offenbach	SL	63009	Germany

How to use ROW_NUMBER in a CTE (Common Table Expression)?

Execute the following script in Query Editor to demonstrate the use of ROW_NUMBER function with PARTITION BY. The TOP 10 selects the largest orders for each customer.

USE AdventureWorks

GO

WITH cteTotalDueSorted

AS

(

SELECT

Customer = s.Name,

s.CustomerID,

SalesOrderID,

OrderDate,

TotalDue='$'+convert(varchar,TotalDue,1),

SeqNo = ROW_NUMBER() OVER (

PARTITION BY soh.CustomerID

ORDER BY TotalDue DESC)

FROM Sales.SalesOrderHeader AS soh

JOIN Sales.Store s

ON soh.CustomerID = s.CustomerID

)

SELECT

Customer,

ItemNo=cte.SeqNo,

SalesOrderID,

OrderDate = convert(char(10), OrderDate,111),

TotalDue

FROM cteTotalDueSorted cte

WHERE cte.SeqNo <= 10

ORDER BY CustomerID, ItemNo

Partial Results:

Customer	ItemNo	SalesOrderID	OrderDate	TotalDue
A Bike Store	1	45283	2002/2/1	$31,972.17
A Bike Store	2	46042	2002/5/1	$29,418.53
A Bike Store	3	44501	2001/11/1	$22,152.24
A Bike Store	4	43860	2001/8/1	$12,381.08
Progressive Sports	1	46976	2002/8/1	$8,727.11
Progressive Sports	2	47997	2002/11/1	$4,682.69

Macro to extract the highlighted Text in the document

Sub ScratchMacro()
Dim list
list = ""
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Highlight = True
While .Execute
'MsgBox (oRng.Text)
list = ""
list = list & oRng.Text & "~"
Wend
End With
MsgBox (list)
End Sub

Disable Addressbars in Popups

Add the site viewing to the trusted sites tab in security option