Software Applications Programming Errors and solutions.

C sharp donet code to Get Single query Data . Use following code in common class . static public string getSingleQueryData(string _Querystr) { string Connstr= ""; OleDbConnection connection = null; OleDbDataAdapter adp = null; OleDbCommandBuilder oleDbCommandBuilder = null; DataTable dataTable = null; string _strReturn = ""; Connstr= ConfigurationSettings.AppSettings["strconnection"]; connection = new OleDbConnection(strconnection); connection.Open(); adp = new OleDbDataAdapter(_Querystr, connection); oleDbCommandBuilder = new OleDbCommandBuilder(adp); dataTable = new DataTable(); adp.Fill(dataTable); connection.Close(); if (dataTable.Rows.Count > 0) { _strReturn = dataTable.Rows[0][0].ToString(); } return _strReturn; }

System monitoring or tooling for SQL •SQL Server Pro-filer •System monitoring / Perform/ PAL tool •Activity Monitor (SQL Server Management Studio) •Event logs, SQL logs •Dynamic Management Views (DMVs) / standard reports •SQL trace (sp_traceTSQL) •System Stored Procedures (sp_who, sp_lock…) •DBCC statements •Built-in functions (@@CPU_BUSY, @@CONNECTIONS…) •Trace Flags •Database Engine Tuning Advisers

Examples of Sql complex select queries select count(MGR),count(sal) from salesmans; select ename,(sal+nvl(comm,0)) as totalsal from salesmans; select * from salesmans where sal> any(select sal from salesmans where sal<3000); select * from salesmans where sal> all(select sal from salesmans where sal<3000); select ename,deptno,sal from salesmans order by deptno,sal desc; Create table salesmans1 as select * from salesmans where 1=2; Select * from salesmans where sal>=1000 And sal<2000; select * from salesmans where rowid in (select decode(mod(rownum,2),0,rowid, null) from salesmans); select * from salesmans where rowid in (select decode(mod(rownum,2),0,null ,rowid) from salesmans); select * from dept where deptno not in (select deptno from salesmans); select * from dept a where not exists (select * from salesmans b where a.deptno = b.deptno); select salesmansno,ename,b.deptno,dname from salesmans a, dept b where a.deptno(+) = b.deptno

Query string is used to Pass the values or information form one page to another page. //First page of aspx Partial Class Test1 Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click 'bind the textbox values to querystring Response.Redirect("Test2.aspx?ValuefromTxtbox1=" & TxtBoxControl1.Text) End Sub End Class //Second page of aspx Partial Class Test2 Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Get the values from Query string TxtBoxControl1.Text = Request.QueryString("ValuefromTxtbox1") End Sub End Class

Get number of days in a month by passing date in sql function CREATE FUNCTION [dbo].[GetNoOfDaysInMonthfromdate] ( @DateParm DATETIME ) RETURNS INT AS BEGIN RETURN CASE WHEN MONTH(@DateParm) IN (1, 3, 5, 7, 8, 10, 12) THEN 31 WHEN MONTH(@upDate) IN (4, 6, 9, 11) THEN 30 ELSE CASE WHEN (YEAR(@DateParm) % 4 = 0 AND YEAR(@DateParm) % 100 != 0) OR (YEAR(@DateParm) % 400 = 0) THEN 29 ELSE 28 END END END GO

How to use insert into table by using select query in sql. You can insert recordset at a once by using select query this way. INSERT INTO @Tablename(Col1, Col2)     SELECT Col1, col2     FROM table2 This will select records from table2 and will insert in table1.

Macro That Automatica​lly Updates Data Itself In Excel If you need to have a database that automatically updates itself.  In fact, if you have a folder with different excel files, say folder Y (all the same form/template), and the first X cells of these files need to be extracted to another excel workbook (file Z). Every file represents 1 row in workbook  Z. But this should be done automatically. So if I copy a new excel file in folder Y, the first X of this new excel file should automatically (can be with a ‘button demand’)  be copied into workbook Y. Option Explicit Const cstFolder = "C:\Users\ATC0155\Documents\Toolbox\Excel\1208 20 selfupdate" Const cstCols = 5 Sub DoUpdate() Dim wks As Worksheet Dim strFile As String Dim appExcel As Excel.Application Dim lngRow As Long ' Prepare the sheet to receive the data Set wks = Sheet1 wks.Cells.ClearContents ' Run through each file in the folder strFile = Dir(cstFolder &