Kamalraaja’s Weblog

April 15, 2009

How to get last date of a Month in Sql Server

Filed under: Sql Tip — kamalraaja @ 3:37 am

declare @date varchar(10)

set @date=convert(varchar,year(getdate()))+’-'+convert(varchar,(month(getdate())+1))+’-01′

select dateadd(day,-1,@date)

Logic is which month we want to find the last date take that month.

Suppose i want to find last day of   14-04-2009

Add 1 month to this and create a format of next month first day

the next month will be 05 ,2009

2009-05-01

With dateadd function add -1 day to that function . So it will come to the previous month last day.

dateadd(day,-1,’2009-05-01′)

it will display   ‘2009-04-31

March 27, 2009

Inserting using one insert statement

Filed under: Sql Tip — kamalraaja @ 10:08 am

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
GO

creating tables from other tables in sql 2005

Filed under: Sql Tip — kamalraaja @ 10:05 am

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
—-Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
—-INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
—-Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

February 3, 2009

Sending Request And Get Response with POST Method

Filed under: Asp.Net — kamalraaja @ 7:32 am
Tags:
Imports System.IO  
Imports System.Net  
Partial Class Default1  
    Inherits System.Web.UI.Page  
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
        Dim strURL As String = “”  
        Dim strPostData As String = “”  
        Dim strResult As String = “”  
        Dim wbrq As HttpWebRequest  
        Dim wbrs As HttpWebResponse  
        Dim sw As StreamWriter  
        Dim sr As StreamReader  
  
        ’ Set the URL to post to  
        strURL = “http://www.webcom.com/cgi-bin/form”  
        ’ Post some values to the page  
        strPostData = String.Format(”your_name={0}&userid={1}&form_name={2}”, “Mark Smith”, “webcom”, “tutortest”)  
  
        ’ Create the web request  
        wbrq = WebRequest.Create(strURL)  
        wbrq.Method = “POST”  
        ’ We don’t always need to set the Referer but in this case   
        ’ the page we are posting to will only issue a response if we do  
        wbrq.Referer = “http://www.webcom.com/cgi-bin/form”  
        wbrq.ContentLength = strPostData.Length  
        wbrq.ContentType = “application/x-www-form-urlencoded”  
  
        ’ Post the data  
        sw = New StreamWriter(wbrq.GetRequestStream)  
        sw.Write(strPostData)  
        sw.Close()  
  
        ’ Read the returned data   
        wbrs = wbrq.GetResponse  
        sr = New StreamReader(wbrs.GetResponseStream)  
        strResult = sr.ReadToEnd.Trim  
        sr.Close()  
  
        ’ Write the returned data out to the page  
        TextBox1.Text = strResult  
    End Sub  
End Class  

May 22, 2008

To get one form values in another Form

Filed under: c#.net — kamalraaja @ 9:01 am

Write this code in second Form

label1.Text = Application.OpenForms["FirstForm"].Controls["controlname"].Text;

Some SQL Tips

Filed under: Sql Tip — kamalraaja @ 8:56 am

How to replace spaces in text stored in sql server with in function or Store Proceduer?

declare @name as varchar(50)
declare @rep as varchar
set @rep=’ ‘
set @name=’kamal   raja       v’
select replace(@name,@rep,”)

It will replaces the characters what ever you define in @rep that character is replaced from @name.

It is help full when writing functions or stored procedures , bcoz sql does not provides directly Trim() function which replaces spaces between given text.

select getdate()                      —– > To get Current Date

select datepart(day,getdate())  —–> To get Day

select datepart(month,getdate()) —-> To get Current Month

select datepart(year,getdate()) —-> To get Current Year

Some examples of converting date format:

select convert(varchar,getdate(),111)  =====>  ‘2008/5/21′

select convert(varchar,getdate(),106)  =====> ‘21 May 2008′

April 1, 2008

How to use Sql Profiler?

Filed under: Sql Tip — kamalraaja @ 10:04 am

Take some example that some data base updations will be done through one of your screen in the project. To know what are the Sql server tables will be effected when doing some thing in that screen…………………….

Make sure that dabase will be on local machine….

Open Sql Profiler of Sql Server……………….

Go File –> New –> Trace

Then Trace Properties screen will appears and asks to give trace name. Give what ever name you like..

Click Run Button….

Now go for your project screen and click any button which will update or modify database. All the transactions will be loaded in the Trace of sql profiler.

means what are the queries executed when you clicked that button , which stored  procedures executed………etc….

Gives a sample view that what are the tables effected when click a button of a screen in the project.

How to Release Excel Com object From memory after using it?

Filed under: c#.net — kamalraaja @ 7:01 am

when working with excel work books please use some standard to read data from excel sheet….
1.Create object for Excel Application
2. Create object for Excel Workbooks
3. Create object for ExcelWorkbook
4.Create object for ExcelWorkSheet
when using Excel 2000 component
{
Excel.Application xla = new Excel.Application();
Excel.Workbooks xlwbs = xla.Workbooks;
Excle.Workbook xlwb; Excel.Worksheet xls;
xlwb=xlwbs.Open(”[ExcelFileName]“, 0, false, 5, “”, “”, true, Excel.XlPlatform.xlWindows, “\t”, false, false, 0, true); xls=(Excel.Worksheet)xlwb.Worksheets["ExcleSheetname"];
}
Read necessary data from excel work sheet and after completion of working with
excel then release objects in which manner they are created…
so
{
   ReleaseComObject(xls);
   xlwb.close(false,null,null);
   ReleaseComObject(xlwb);
   xlwbs.Close();
   ReleaseComObject(xlwbs);
   xla.Quit();
   ReleaseComObject(xla);
   Gc.Collect();
}
private void ReleaseComObject(object o)
{
   try
   {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
 }
 catch (Exception ex){  }
 finally {  o = null;}
}

March 31, 2008

To generate an single quoted string to execute in sql Server

Filed under: c#.net — kamalraaja @ 9:15 am

if you have more data to check in sql server

like Data1,Data2,Data3…….DataN

the query must be like this

select * from [tablename] where [columnname] in(’Data1′,’Data2′,’Data3′,…….’DataN’);

then write all the data in a single file each value in one row and save it.like

Data1

Data2

Data3

….

….

….

DataN

save this as one file name.

Then run this program and browse File and click ‘Display’ Button.

Rename the file extension with .jpeg with .execreating-a-file-to-exectue-in-sqlquery-analyzer.jpeg

To get values from one form to another form

Filed under: c#.net — kamalraaja @ 9:02 am

write this code in second form in which you want to retrieve data from Form1  and assign to required one.

Application.OpenForms["Form1 "].Controls["controlName"].Text

Next Page »

Blog at WordPress.com.