Software Applications Programming Errors and solutions.

How to create sitemap runtime in asp.net // begin program abc // declare namespace using System.Xml; using System.Text; using System.Data.SqlClient; //declare class public partial class sitemap : System.Web.UI.Page { SqlConnection cn; protected void Page_Load(object sender, EventArgs e) { try { cn = new SqlConnection("Your connection string"); string mydomain = "your domain name"; string strSql = "select abc from test"; SqlDataAdapter dacontent = new SqlDataAdapter(strSql, cn); DataSet dscontent = new DataSet(); dacontent.Fill(dsd, "SiteMap"); //Now we are going to create XML file using XMLTextWriter XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.UTF8); writer.WriteStartDocument(); writer.WriteStartElement("urlset", "url"); writer.Writ...

Restore database through query in sql Just try to execute following query to restore database using query analyzer or from network location. RESTORE DATABASE testdb FROM DISK = 'C:\folder1\testdb.bak' WITH REPLACE, MOVE 'testdb_Data' TO 'C:\folder1\testdb.mdf', MOVE 'testdb_Log' TO 'C:\folder1\testdb.LDF' If you want to restore database by code then you can try following SQL Code to restore database . Use master go RESTORE DATABASE [SID] FROM disk = N'\\SERVERNAME\SFILE.bak' WITH FILE = 1, NORECOVERY, REPLACE, NOUNLOAD, STATS = 10 go RESTORE LOG [SID] FROM DISK = N'\\SERVERNAME\SFILE.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 GO sp_change_users_login @Action='Report' Attach mdf data base to new database in sql To Attach mdf data base to new database in sql You can follow following steps. 1 Go to enterprise manager, expand tree till Database 2 Right click on...

Query to delete duplicate records from table using sql select * from test where exists (select * from test as t where test.id=t.id having count(*)>1 and min(t.id) !=test.id.); How to Remove Duplicates from a Single Column SQL Table To Remove Duplicates from a Single Column Table in sql you can use temparary table concept and can try this way lock tables Table1 write; drop temporary table if exists TestTmp; begin work; create temporary table TestTmp select distinct * from Table1; delete from Table1; insert into Table1 select * from TestTmp; unlock tables; commit; delete all the duplicate rows in the table of SQL database If the table is not having an unique column you can use the below: DELETE FROM _Table1 WHERE _Table1.ID IN -- List 1 - all rows that have duplicates (SELECT F.ID FROM _Table1 AS F WHERE Exists (SELECT Field1, Field2, Count(ID) FROM _Table1 WHERE _Table1.Field1 = F.Field1 AND _Table1.Field2 = F.Fi...

This is a Sql stored procedure which can be used to change column name from lower case to upper in tables. Its simple to use and understand. DECLARE @count INT, @script NVARCHAR(1000), @column_name_old VARCHAR(100), @column_name_new VARCHAR(100) DECLARE @table TABLE (nid INT IDENTITY(1,1), column_name VARCHAR(1000)) INSERT INTO @table(column_name) SELECT name FROM syscolumns WHERE id = OBJECT_ID('test_column_change') SELECT @count= count(1) FROM @table WHILE(@count >=1) BEGIN SELECT @column_name_old = column_name FROM @table WHERE nid = @count SELECT @column_name_new = UPPER(@column_name_old) SELECT @Script ='sp_rename ''test_column_change.'+@column_name_old+''' , '''+@column_name_new+''' , ''COLUMN''' --select @script EXEC (@Script) SELECT @Count = @count-1 END

Set Auto Increment of column which is varchar type in sql If you want to Set Auto Increment of column which is varchar type in sql table then you can do that by using following way CREATE TABLE StudentTable ( Regid int IDENTITY(1,1) NOT NULL , StudentCol nvarchar(3) DEFAULT 'STD' , StudentId AS (StudentCol+ REPLICATE('0', 10-LEN(CONVERT(nvarchar(100),Regid )) ,OtherColumnsYouNeed nvarchar(MAX) )

Example of To_Char function in sql By following example we could know how to use to_char function in sql select query. To get number of student who registered on particular date. Select Stdname ,to_char(regdate ,'day') day from student where to_char(regdate ,'day')='saturday' or to_char(regdate ,'day')='monday';

Summary: Inserting picture in worksheet using macro. Details Sub Insert_ImagePic() Dim wks As Worksheet Dim Copies As Variant Dim strPath As String Dim strFileNm As String Dim Pic '-- ' Reference active sheet On Error Resume Next Set wks = ActiveSheet ' Better solution is to reference specified worksheet, e.g.: ' In this example worksheet name = 'ImagePic', customize name Set wks = ThisWorkbook.Worksheets("ImagePic") ' Check if sheet exists If wks Is Nothing Then MsgBox "Active sheet is not Worksheet...'" & vbLf & _ "Please select Worksheet.... and recall procedure.", _ vbExclamation, "Insert ImagePic" Exit Sub End If On Error GoTo 0 ' Initialize File name and Path (you use ComboBox) ' Customize File name and Path strPath = "C:\Users\Andro\Desktop\Articles\avXl...

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; } }