obscure-t-sql-annoyance

We are moving toward the use of SQL Server 2005 at work, so I have been getting my head wrapped around things like stored procedures and triggers. I have been using SQL Server Management Studio to create my stored procedures on my test database.

I created a stored procedure tonight that would insert a new record in the database and return the new identity in an output parameter. My stored procedure looked like this...
Use testing
GO

CREATE PROCEDURE [dbo].AddName
@firstname varchar(20) = NULL,
@lastname varchar(30) = NULL,
@NameID int OUTPUT
AS
SET @NameID = 0 -- return zero if the insert fails

IF @firstname IS NOT NULL AND @lastname IS NOT NULL
BEGIN
SELECT @NameID = ID
FROM names
WHERE firstname = @firstname
and lastname = @lastname

IF @NameID IS NULL
BEGIN
INSERT INTO names (firstname, lastname)
VALUES (@firstname, @lastname)

SELECT @NameID = SCOPE_IDENTITY()
END
END
SELECT @NameID
GO


Everything that I did just would not work. I kept getting a NULL back for the output parameter value. My test went something like this....

DECLARE @NameID INT
EXEC AddName 'John', 'Smith', @NameID
SELECT @NameID


I used the Template Explorer and setup a new stored procedure on the test database using the template. It worked like a charm with the output parameter being properly populated when the stored procedure returned.

I finally figured it out a little while ago. That little light blue word called OUTPUT over on the right after the last parameter in the EXEC statement for the stored procedure. I completely missed that on the template code example. I did not realize that you had to include the direction declaration there since the procedure itself already designated the last parameter as an output parameter.

My EXEC statement should have looked like this to work properly...

EXEC AddName 'John', 'Smith', @NameID OUTPUT

Export Gridview to Excel asp.net 2.0

It is easy to make excel file of gridview just copy and paste the following code to your csharp code and change the gridview name. It will generate the excel sheet easily. please don't forget to give comments

protected void Button1_Click(object sender, EventArgs e)
{
HtmlForm form = new HtmlForm();
string attachment = "attachment; filename=Employee.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);
form.Controls.Add(gvContact);
this.Controls.Add(form);
form.RenderControl(htextw);
Response.Write(stw.ToString());
Response.End();
}

How to get duplicate records from the Database

We have to use the group by with having command to get the duplicate values. this query shall show the result of only the users have duplicate values in the employee table.

SELECT columnName FROM TableName
Group By columnName
Having Count(*)>1