Software Applications Programming Errors and solutions.

Excel  macro to send mail automatically Summary : This is example of macro which helps to send mail automatically with attachment of entire Workbook and pasting one particular sheet in the mail body. Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2010 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True

Code to automatic page breaks in Excel Sub identify() 'establish v to represent VERTICAL Dim v As Integer: v = 0 ' start loop to identify vertical break Do v = v + 1 ' go through each column at step 1 Loop Until ActiveSheet.Columns(v).PageBreak = xlAutomatic 'continue looping until it identifies a automatic verical (v) page break MsgBox ("Page ends COLUMN : " & v - 1) 'Why "V - 1"? Well, the new page starts on column v... final page therefore is 'column v-1! End Sub

This is a Example of dynamic SQL. You can wrtie following code to check how dynamic sql works. column1 := 'custno'; column2 := 'custname'; t_table1 := 'cust_test'; t_table2 := 'cust'; testval1 cust.custno%type; t_valeu2 cust.custname%type; t_resultestval1 cust.custno%type; t_resultestval2 cust.custname%type; execsqlcodetest := 'select '||column1||','||column2||' from '||table2||' where ('||column1||','||column2||') in (select '||column1||','||column2||' from '||table1|| ' where '||column1||' like %:b1% and '||column2||' like %:b2%)'; execute immediate execsqlcodetest using testval1, testval2 into t_resultestval1, t_resultestval2;

This example will tell you How to use cursor in stored procedure in MS SQL. Cursor is complex terms in sql but when you start to use it its become simple. You can understand cursor well by using following example. DECLARE @ testcols1 AS varchar(30) DECLARE @ testcols2 AS varchar(30) DECLARE c_Cursor CURSOR FOR SELECT Col1, Col2 from Customer OPEN c_ Cursor FETCH NEXT FROM c_ Cursor INTO @ testcols1, @ testcols2 WHILE @@FETCH_STATUS=0 BEGIN Exec ('select ' + @ testcols1 + ' , ' + @vcol2 + ' from CustE) FETCH NEXT FROM c_ Cursor INTO @ testcols1, @ testcols2 END CLOSE c_ Cursor DEALLOCATE c_ Cursor

 convert date in 'MM/DD/YYYY HH:MI A.M.' format using MS sql select CONVERT(nvarchar(30), GETDATE(), 101) + ' ' + CONVERT(nvarchar(30), GETDATE(), 114) format sequence 101 = mm/dd/yyyy 108 = hh:mi:ss 114 = hh:mi:ss:mmm(24h)

This is nice example of Stored procedure . You can get Idea that how to Create stored procedure to get employee details as per filtered parameter range of sql. To become SQL Expert we should required to expert in Stored procedure and parameter terms. SET ANSI_NULLS ON Go SET QUOTED_IDENTIFIER ON Go CREATE PROCEDURE spx_GetEMploye @Filter VARCHAR(50) AS BEGIN SET NOCOUNT ON; IF @Filter = 'ALL' SELECT ContactName, City, Country, PostalCode FROM Employee ELSE IF @Filter = '10' SELECT TOP 10 ContactName, City, Country, PostalCode FROM Employee ELSE SELECT ContactName, City, Country, PostalCode FROM Employee WHERE mailto:Country=@Filter END GO

Validation for future date in c sharp dot net lanugage you can write this code in validation class which you may have in common class file. public static bool IsNotFuturedt(DateTimePicker datpic) { if (datpic.Value.Date > DateTime.Now.Date) { MessageBox.Show("Please enter date less than today "); datpic.Value = DateTime.Now; datpic.Focus(); return false; } else { return true; } }