Sendkeys error 70 permission denied in vb 6.0  on Windows 7 and above Versions

Solution 1 :

your UAC worked for SENDKEY problem in VB on windows 2007 platform. if your turn off your UAC you should be able to use that function.

if your UAC is off , then Turn it on again , Then , Turn it off again .... and RESTART your PC .... 
and make sure of using "Run as administrator " .... 

Solution 2 :

Write this code in the Module. The Problem is solved.

Code:

Public Sub Sendkeys(text$, Optional wait As Boolean = False)
   Dim WshShell As Object
   Set WshShell = CreateObject("wscript.shell")
   WshShell.Sendkeys text, wait
   Set WshShell = Nothing
End Sub

How to use the PrinterSetup Dialog Box From VB6 works with Crystal Reports

Printing the report through the Report Viewer and displaying the 'Print' dialog box====
Dim Report As New CrystalReport1

Private Sub Form_Load()
    'Set the report o the Report Viewer and display the report
    'with a zoom level of 100%
    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport
    CRViewer1.Zoom 100
End Sub

'This event fires when the ''Print'' button in the Report Viewer is clicked
Private Sub CRViewer1_PrintButtonClicked(UseDefault As Boolean)
     'Create an instance of the Printer Setup dialog box
     'Set the handle of the Printer Setup dialog box to the form
     Report.PrinterSetup Form1.hWnd
     'Select Paper Size
     Report.PaperSize = crPaperA5

    'The ''Print'' dialog box will appear by default
    The ''Print'' dialog box needs to be displayed if you want the user
    'to set start page and stop page, the number of copies, and collate
End Sub

SQL SERVER – Fix Error 7202 Could not find server in sys.servers. Verify that the correct server name was specified

The other day I received following error when I tried to restore a database from one server to another server.

Msg 7202, Level 11, State 2, Line 1
Could not find server ‘MyLinkedServer’ in sys.servers. Verify that the correct server name was specified.

Well, this is quite a popular error one receive when they attempt to restore database containing references of the linked server. The solution is to create a link server and restore database. Here is the quick script which can fix your error.

EXEC sp_addlinkedserver @server='MyLinkedServer'
EXEC sp_addlinkedsrvlogin 'MyLinkedServer', 'false', NULL, 'MyUserName', 'MyPassword'

Update/Insert NULL into SQL DataBase

How to Update/Insert  NULL into SQL DataBase

Update Syntax: 

Update TableName set ColumnName = NULL

Insert Syntax: 

INSERT INTO TableName (x,y,z) VALUES ( NULL,NULL,NULL)

Select Data Field using Sub Queries

Example 1:
SELECT     Epf_No,EMP_CALLING_NAME, Proc_Date,DeductAmt
FROM         TempDteSumIncentDeduct
 PS
inner join
(
Select EMP_NUMBER,EMP_CALLING_NAME
from    HS_HR_EMPLOYEE
) PS2
On PS.Epf_No = PS2.EMP_NUMBER

Note:
PS & PS2 is Temp DB


Example 2:
Select *
from
(Select EMP_NUMBER,EMP_CALLING_NAME,Epf_No,Proc_Date,DeductAmt
from    HS_HR_EMPLOYEE
PS
inner join
(
SELECT     Epf_No,Proc_Date,DeductAmt
FROM         TempDteSumIncentDeduct
where    Proc_Date = '07/11/2013'
) PS2
On PS.EMP_NUMBER = PS2.Epf_No
) as w

Note:
PS , PS2,W   is Temp DB

Crosstab queries with inner join using PIVOT in SQL Server

TABLE1 : EmpMaster 

EpfNo           Name              BathDay

100     Jone     07/01/1980  

101     Deyon    08/20/1985

102     Nimal    10/01/2000

103     Deyon    11/25/1975

TABLE1 : EmpSalary  

EpfNo        Month         PaySalary
100      Jan      25000

100      Jan        500

101      Jan      13000

101      Jan        500 

102      Jan      25000

102      Jan        500 

103      Jan      20000

100      Fab      20000

101      Fab      26800
102      Fab      13000

103      Fab      20000

SQL Query:

SELECT EpfNo, Name, BathDay, Jan, Fab
FROM 
(

   SELECT EpfNo, Name, PaySalary
   FROM EmpMaster) DB1

inner join

(

   Select  EpfNo,  Sum(PaySalary) As  Total

   From  EmpSalary 

   Group By EpfNo

)  DB2

 PIVOT
)  PS
  SUM (PaySalary)
  FOR Month IN
  ( [JAN], [FAB])
) AS pvt

After Run CrossTab Query Using PIVOT 

EpfNo        Name         BirthDay             JAN           FAB          Total        

100     Jone     07/01/1980   25500    20000   45500   

101     Deyon    08/20/1985   13500    26800   40300 

102     Nimal    10/01/2000   25500    13000   38500 

103     Deyon    11/25/1975   20500    20000   40500

On Error found in running this query

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

Crosstab queries using PIVOT in SQL Server

TABLE1 : EmpMaster 

EpfNo           Name              BathDay

100     Jone     07/01/1980  

101     Deyon    08/20/1985

102     Nimal    10/01/2000

103     Deyon    11/25/1975

TABLE1 : EmpSalary  

EpfNo        Month         PaySalary
100      Jan      25000

100      Jan        500

101      Jan      13000

101      Jan        500 

102      Jan      25000

102      Jan        500 

103      Jan      20000

100      Fab      20000

101      Fab      26800
102      Fab      13000

103      Fab      20000

SQL Query:

SELECT EpfNo, Name, BathDay, Jan, Fab
FROM 
(SELECT EpfNo, Name, PaySalary
FROM EmpMaster) DB1
PIVOT
(
SUM (PaySalary)
FOR Month IN
( [JAN], [FAB])
) AS pvt

After Run CrossTab Query Using PIVOT 

EpfNo        Name         BirthDay             JAN           FAB             

100     Jone     07/01/1980   25500    20000    

101     Deyon    08/20/1985   13500    26800   

102     Nimal    10/01/2000   25500    13000    

103     Deyon    11/25/1975   20500    20000   

How does this work?
There are three pieces  in order to construct the query.

• (1) The SELECT statement

  • SELECT EpfNo, Name, BathDay, Jan, Fab

  • This portion of the query selects the three columns for the final result set (Epfno, Name, Birthday, Jan, Fab)
  
  
• (2) The query that pulls the raw data to be prepared
  • (SELECT EpfNo, Name, PaySalary FROM EmpMaster) ps
  • This query pulls all the rows of data that we need to create the cross-tab results.  The (DB1) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
  
  
• (3) The PIVOT expression

  • PIVOT(SUM (PaySalary)FOR Month IN( [JAN], [FAB])) AS pvt

  • This query does the actual summarization and puts the results into a temporary table called pvt

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3).  These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.

On Error found in running this query

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

IF...THEN in an SQL SELECT statement

IF...THEN in an SQL SELECT statement

 The simple case:

Syntax :

SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
                       WHEN <othervalue> THEN <returnthis>
                                         ELSE <returndefaultcase>
       END
FROM <table>

Example: 

SELECT  Working =
        CASE ActiveEmp
        WHEN 'N' THEN 'NO'
        ELSE 'YES'
    END

The extended case:

Syntax :

SELECT CASE WHEN <test>      THEN <returnvalue>
            WHEN <othertest> THEN <returnthis>
                             ELSE <returndefaultcase>
       END
FROM <table>

Example: 

SELECT  CASE WHEN   ActiveEmp = 'N' THEN 'NO'

             WHEN   ActiveEmp = 'Y' THEN 'YES'

                                    ELSE 'INVALID'

        END

FROM    EMPLOYEE_MASTER 

SQL Server 2012 you can use the IIF Function for this.

Syntax :

SELECT IIF(<test>, <returnthis>, <returndefaultcase>) AS <variable>, * 
FROM <table>

Example:   

SELECT IIF(Reorder = 'N' or InStock = 'Y', 1, 0) AS OrderOut, * FROM StockMaster 

In a where clause, use:

Syntax :

where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end