Skip to main content

Posts

Showing posts from June, 2012

Example of join table with subquery result in sql

Following is example that how to join table in sql using select query to retrieve rows from both tables. select * from  First_table as T1 ,Second_table as T2  where T1.wono=T2.wono and T1.id = (select top(1) id from   First_table as T1) In above query T1 is alias name of first table and T2 is alias name for second table. Wono is primary column which used to join both table. It also example of sub query.

How to Set Default Limit in Store Procedure in SQL

This is stored procedure which show you rows return as per mention in stored procedure. CREATE PROCEDURE get_categories( IN p_action_flag VARCHAR(50), IN p_firstrow int, IN p_lastrow int) BEGIN IF p_action_flag = 'all' THEN SELECT top 50 u.*, d.name, d.image FROM user u, member gu, detail d WHERE u.user_id = gu.user_id AND u.user_id = d.user_id AND u.status = '1' AND gu.group_id IN (1, 7) GROUP BY u.user_id ORDER BY d.name LIMIT p_firstrow,p_lastrow; ELSEIF p_action_flag = 'Most Viewed' THEN SELECT top 50 u.*, d.name, d.image FROM user u, member gu, detail d WHERE u.user_id = gu.user_id AND u.user_id = d.user_id AND u.status = '1' AND gu.group_id IN (1, 7) GROUP BY u.user_id ORDER BY u.views DESC, d.name ASC LIMIT p_firstrow,p_lastrow ; END IF END

simple macro that display assigned macros for each Button

 run the following simple macro that display assigned macros for each Button:  Sub ButtonOnAction() Dim shp As Shape Dim i As Integer For Each shp In ActiveSheet.Shapes ' Type 8=Button, 13=Picture, 4=Comment, ... '-- i = 1 If shp.Type = 8 Then ' 8 = Button Application.Goto Reference:=shp.TopLeftCell, Scroll:=True shp.Select MsgBox i & ". Button '" & shp.Name & "' - Macro: '" & shp.OnAction & "'" i = i End If Next shp End Sub

Data from Excel Sheet to Display In C#.Net Datagrid View

select data from an excel sheet and display it in a datagridview public void ImportExcel2007(string path) { FileInfo filePath = new FileInfo(path); DataTable workSheetsTable = new DataTable(); DataTable columnsTable = new DataTable(); string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;IMEX=1; HDR=YES'"; OleDbConnection excelConnection = new OleDbConnection(connString); excelConnection.Open(); //This bit assumes the first worksheet is the one to import, //change this as required. workSheetsTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGui d.Tables, null); string tableName = workSheetsTable.Rows[0]["TABLE_NAME"].To String(); //This section uses the tableName declared above to get a //list of columns for that worksheet only. columnsTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGu

Convert to proper case using dotnet code

Convert to proper case using dotnet code string sentence= "santro is my favorite car"; Response.Write(ConverTopropercase(sentence)); private static string (ConverTopropercase(string senstr) { return Regex.Replace(s, @"\b[a-z]\w+", delegate(Match yourMatch) { string Tempstr = yourMatch.ToString(); return char.ToUpper(Temp[0]) + Tempstr.Substring(1); }); } output: Santro Is My Favorite Car.

Dotnet Code to get size of Folder including subfolders

Dotnet Code to get size of Folder including subfolders Public Shared Function GetSize(ByVal folderFullPath As String, Optional ByVal includesubFolderss As Boolean = True) As Long Dim sizeoffile As Long = 0 Dim directory As directoryInfo = New directoryInfo(folderFullPath) For Each fileInfo As System.IO.FileInfo In directory.GetFiles() sizeoffile += fileInfo.Length Next If includesubFolderss Then For Each subFolders As System.IO.directoryInfo In directory.Getdirectoryctories() sizeoffile += GetSize(subFolders.FullName) Next End If

Clear temparary files from temp folder of internet explorer using .net code

To Clear temporary files from temp folder of internet explorer by .net code you can try below code in any event to check the effect. "using System.IO;" void clearTempfolder(DirectoryInfo MyDirPath) { foreach (FileInfo yourFile in MyDirPath.GetFiles()) { yourFile.Delete(); } foreach (DirectoryInfo youSubFolder in MyDirPath.GetDirectories()) { clearTempfolder(youSubFolder ); } } public static void Main() { clearTempfolder(new DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.InternetCache))); }

select the non empty cell and clear the content to allow another selection in Excel

select the non empty cell and clear the content to allow another selection. Sub ReferenceNonEmptyCells() Dim rngAll As Range Dim rngConstants As Range Dim rngFormulas As Range ' 1. reference complete range Set rngAll = Worksheets("Data").Range("A1:K30&qu ot;) ' or use named range... Set rngAll = Worksheets("Data").Range("MyRange&q uot;) ' Reference Constants (23=Numbers+Text+Logical+Errors) On Error Resume Next Set rngConstants = rngAll.SpecialCells(xlCellTypeConstants, 23) ' Reference Formulas Set rngFormulas = rngAll.SpecialCells(xlCellTypeFormulas, 23) '-- ' Clear (or ClearContents) Non Empty Cells If Not rngConstants Is Nothing Then rngConstants.ClearContents End If If Not rngFormulas Is Nothing Then rngFormulas.ClearContents End If End Sub

Convert xml file to excel using vb.net code

Private Function XmlToExcelfile(ByVal XMLSourcefileAs String, ByVal ExcelfileAs String)         Dim Exapp As New Excel.Application Dim fileInf As New System.IO.FileInfo(XLSFile) If fileInf.Exists Then SetAttr(XLSXFile, vbNormal) fileInf.Delete() End If Dim fxml As New System.IO.FileInfo(XMLFile) If fxml.Exists Then exapp.Workbooks.Open(XMLFile) exapp.Workbooks.Item(1).CheckCompatibility = True exapp.DisplayAlerts = False exapp.Workbooks.Item(1).SaveAs(XLSXFile,exappcel.XlFileFormat.xlWorkbookDefault) exapp.DisplayAlerts = False exapp.Workbooks.Close() SetAttr(XMLFile, vbNormal) fxml.Delete() Else MessageBox.Show("XML File does not exists") End If Dim file2 As New System.IO.FileInfo(XLSXFile) If file2.Exists Then SetAttr(XLSFile, FileAttribute.Normal) End If exapp.Quit() Exapp= Nothin

Saving .xlsx file as .xls file by macro code in excel

Sub SaveAs(Filename As String, Format As String) 'XLS Format If UCase(Format) = "XLS" Then ActiveWorkbook.SaveAs Filename:= _ Filename, FileFormat:= _ xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Else 'default 'XLSX Format ActiveWorkbook.SaveAs Filename:= _ Filename, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If End Sub Sub Test() SaveAs "C:\tmp\Filename.xls", "xlsx" SaveAs "C:\tmp\Filename.xlsx", "xls" End Sub

Insert Values Into Table Using Stored Procedure With Identity Column

--First step is create Table empmaster with column empid,eno,ename etc create table empmaster ( empid int primary key identity, eno int, ename varchar ) -- Now create Table empdetails with column empid,sal,dept, hiredate etc create table empdetails ( empid int , sal money, dept varchar, hiredate datetime ) -- Now create Stored Procedure empvalues to insert records in tables create proc empvalues @eno int, @ename varchar(20), @sal money, @dept varchar(20), @hiredate datetime as begin insert into empmaster . INSERT INTO empdetails. end

Code to export crystal report to pdf directly

Code to export crystal report to pdf directly //Declare Reportdocument Object ReportDocument rpt=new ReportDocument(); //set a ReportPath and assign the dataset to reportdocument object rpt.Load(Server.MapPath("Report1.rpt")); rpt.SetDataSource(ds); //assign the values to crystal report viewer CrystalReportViewer1.ReportSource = rpt; CrystalReportViewer1.DataBind(); //Exporting PDF MemoryStream oStream; // using System.IO oStream = (MemoryStream) rpt.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat); Response.Clear(); Response.Buffer = true; Response.ContentType = "application/pdf"; Response.BinaryWrite(oStream.ToArray()); Response.End();

Visual Studio Team Foundation Server 2010 Administration

Exam : Microsoft 70-512 Title : TS: Visual Studio Team Foundation Server 2010,Administration Version : DEM0 1. You plan to install a dual-tier Visual Studio Team Foundation Server 2010 environment using two servers named Server1 and Server2. You install and configure Microsoft SQL Server 2008 and SQL Server 2008 Analysis Services on Server1. You install and configure IIS 7.5, SQL Server Reporting Services, and Windows SharePoint Services 3.0 on Server2. You need to install and configure Team Foundation Server using the two servers. What should you do? A. Install Team Foundation Server on Server2 and run the configuration wizard on Server2 using the Basic option. B. Install Team Foundation Server on Server2 and run the configuration wizard on Server2 using the Advanced option. C. Install Team Foundation Server on Server1 and Server2. Run the configuration wizard on Server1 using the Basic option. Run the configuration wizard on Server2 using the Application-Tier Only

Training centers lists of India

Training centers lists of India Arsh spoken english institute IIFCA Institute of Finance & Computer Accounting K11 Sangli Fitness Academy K11 Bangalore Fitness Academy K11 Indore Fitness Academy K11 Nashik Fitness Academy K11 Pune Fitness Academy Computer Application Centre(CAC),Cuttack Dcube Tech Campus Debest computer systems Chandigarh computer centre Technosys - Technically Ahead Mob com international Glance Tutorial Dynamic bharati Tutorials Aryan Academy ARS Tutorial Institute of Nephro Urology Triumphent Institute of Management Education - TIME Automation & Control System, Pune Ias study point ConceptZ Training Institute, Bangalore Prolific Systems & Technologies Pvt. Ltd. IIHT, Surat Broadline Technologies - SAP Authorised Training Centre A.K.Jindal Accountancy Centre Balaji Institute of Information Technology, Malleswaram, Bangalore Balaji Institute of Information Technology, Indiranagar, Bangalore Balaji Institute of Information Tech

Top NGOs in India

Top NGOs in India Abhiyan Patna - Bihar Action for Development of Demos(ADD) Patna - Bihar Adarsh Charitable Trust Ernakulam District - Kerala Ahmedabad Women's Action Group Ahmedabad - Gujarat Akhanda Seva for International Shanti (Operation Shanti) Mysore - Karnataka Akshara Foundation Bengaluru - Karnataka Alternative For Rural Movement(ARM) Balasore District - Orissa Amar Seva Sangam Ayikudy - Tamil Nadu Ananya Trust Bangalore - Karnataka Andhjan Kalyan Trust Rajkot - Gujarat Anga Karunya Kendra Bangalore - Karnataka Apnalaya Mumbai - Maharashtra Arokiya Charity Pudukottai - Tamil Nadu Asha Deepa School for the Blind Bidar - Karnataka Ashadeep Guwahati - Assam Ashish Foundation for the Differently Abled (AFDA) Charitable Trust New Delhi - Delhi Ashray Akruti Hyderabad - Andhra Pradesh ASHWINI Gudalur - Tamil Nadu ASSIST Guntur - Andhra Pradesh Association for Rural Development and Action Research (ARDAR) Visakhapatnam - Andhra Pradesh Association for Sust

Command to reindiex,update statistics, database tables in sql through query analyzer

Write this command in query editor then run the command to reindex database use yourdatabase dbcc dbreindex('DBNM') Then use this command to update statistics of database update statistics 'DBNM' Command to reindex table DBCC DBREINDEX(YourTableName, '', 80) or DBCC DBREINDEX(YourTableName, '', 70)

NGOs in Mumbai

List of the names of the NGOs in Mumbai Helpage India is a NGO working for the betterment of the elderly. http://www.helpageindia.org/ Childline India is a NGO working for child protection and rights. http://www.childlineindia.org.in/ Dignity Foundation is a NGO which works for the betterment of the elderly. http://www.dignityfoundation.com/ National Society for Equal Opportunities for the Handicapped (NASEOH) is a NGO working for the betterment of handicapped people. http://www.naseoh.org/ Humsafar Trust is a NGO working for spreading AIDS awareness. http://www.humsafar.org/ Bombay Leprosy Project is a NGO working for leprosy eradication. http://www.bombayleprosy.org Youth for Equality http://www.youthforequality.com Construct Life's Building Blocks For Poor Children. Contribute Now! India based NGO dedicated to Cancer Patients Care, Cancer Patients & Cancer ... King George V Memorial, Dr. E. Moses Road, Mahalakshmi, Mumbai - 400 011 w

Stored procedure to Insert, Update and delete records

You can write Stored procedure following  to Insert, Update and delete records from tables in sql. 1. Stored procedure to Insert records in table. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SP_ITEM_Insert] @CD char(7), @ITEM_DESC CHAR(60) AS INSERT [dbo].[ITEM] ( [CD], [ITEM_DESC] ) VALUES ( @CD, @ITEM_DESC ) 2. Example of Stored procedure to Update data in to table . set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SP_ITEM_Update] @CD char(7), @ITEM_DESC char(60) AS UPDATE [dbo].[ITEM] SET [ITEM_DESC] = @ITEM_DESC WHERE [CD] = @ CD 3. Example of Stored procedure to Update data in to table set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SP_ITEM_ Delete] @ CD char(7) AS DELETE FROM [dbo].[ITEM] WHERE [CD] = @CD You can set more conditions and columns as per requirement .

Example of group by, having,min, max,convert method,get top 5 record etc in sql select query

Example of group by, having,min, max in sql select query select id, min(no) , max(no) id from mytable group by id having min(no) <> 12 order by id; I hope you will get Idea to use group by, having and aggregate function. Select first five record from table in sql To get first 5 records from table you need to use rownum column in select query. Here testtable is table and rownum is column which is physically not visible to it remain in all sql table. select *from testtable where rownum<=5 Select Query to Get Total Experience of All Employee in Sql  This is a Select Query to Get Total Experience of All Employee in Sql . Here Employee is a table name where all master data of Employee exists. SELECT E_ID, E_Name , E_Date_of_Joining, DateDiff('Year', E_Date_of_Joining, GetDate()) As Experience, E_salary FROM Employee  Select query that separate particular part that is separated by special character in sql select query that separate particular part th

Function to check key validation using asp.net

Function to check key validation using asp. net ublic Function KeyValid(ByVal keyChar As Char, ByVal strType As KeyType) As Boolean Dim blnRetVal As Boolean Dim strNumStr As String = "0123456789" Dim strDecStr As String = "0123456789." Dim strAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" Dim strDate As String = "0123456789/" Dim strPhone As String = "0123456789-()," Dim strTime As String = "01234567890:" Dim strEqn As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789./*+-()|" Dim strAlphaNum As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789" Try blnRetVal = False If Asc(keyChar) < 32 Then KeyValid = False Exit Function End If Select Case strType Case KeyType.KeyAlpha If strAlpha.IndexOf(keyChar) < 0 Then blnRetVal = Not blnRetVal Case KeyType.KeyAlphaNum If strAlphaNum.IndexOf(keyChar) &

How to get Long running queries from sql database

How to get Long running queries from sql database Just open query analyzer and then use current database then run the following query to get desired result. SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where total_elapsed_time >= 300000000 --5 min ORDER BY total_elapsed_time / execution_count DESC;

ITIL Foundation v.3 Certification

Exam : EXIN EX0-101 Title : ITIL Foundation v.3 Certification Version : V3.88 1. What are the three types of metrics that an organization should collect to support Continual Service Improvement (CSI)? A. Return On Investment (ROI), Value On Investment (VOI), quality B. Strategic, tactical and operational C. Critical Success Factors (CSFs), Key Performance Indicators (KPIs), activities D. Technology, process and service Answer: D 2. Event Management, Problem Management, Access Management and Request Fulfilment are part of which stage of the Service Lifecycle? A. Service Strategy B. Service Transition C. Service Operation D. Continual Service Improvement Answer: C 3. Reliability is a measure of: A. The availability of a service or component B. The level of risk that could impact a service or process C. How long a service or component can perform its function without failing D. A measure of how quickly a service or component can be restored to normal working Answer