Feeds:
Posts
Comments

Archive for October, 2007

Till the era of SQL Server 2000, there was no standard and structured way of exception handling in procedures return in SQL. Developers have to use the standard error variable “@@ERROR” to check if any error has occurred and take the necessary action.

A sample code in SQL Server 2000 will look like this:-

BEGIN TRANSACTION

–Write your statements here like “insert into some table”

set @Error = @@ERROR

–if there is some error

if @Error <> 0
Begin
 ROLLBACK TRANSACTION
Else
 COMMIT TRANSACTION
End

SQL Server 2005 have come with Try-Catch blocks, very similar to those used in frontline programming languages like C# and Java. Hence the same code above can be written in a much structured and efficient way in SQL Server 2005 as follows :-

BeginTry
       BEGIN TRANSACTION
       — Do some operations here like “insert into some table”
       COMMIT TRANSACTION
EndTry
BeginCatch
      ROLLBACK TRANSACTION
EndCatch

Notice the important keywords here – “BeginTry”, “EndTry”, “BeginCatch” and “EndCatch”. These are equivalent of “Try-Catch” blocks used in C# or Java.

~Mayank

Advertisements

Read Full Post »

In a hyperlink column of gridview, suppose  the value of the property DataNavigateUrlFormatString is “ProductsForSupplierDetails.aspx?SupplierID={0}”. Many people think that the argument {0} is replaced by the value of the property DataKeyNames of the Gridview. But in actual case this is replaced by the value of the property DataNavigateUrlFields of the hyperlink column.

Now suppose you want to pass two arguments instead of one, something like this “ProductsForSupplierDetails.aspx?SupplierID={0}&CompanyName={1}”.

Now as these two arguments come from property DataNavigateUrlFields of the hyperlink column. So value of this property has to be “DataNavigateUrlFields=”SupplierID,CompanyName”.

At run time the .NET replaces {0} with “SupplierID” and {1} with “CompanyName” of that row.

A complete sample code will look like this:-

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” DataKeyNames=”SupplierID” DataSourceID=”ObjectDataSource1″>
<Columns>
<asp:BoundField DataField=”SupplierID” HeaderText=”SupplierID” InsertVisible=”False” ReadOnly=”True” SortExpression=”SupplierID” />
<asp:BoundField DataField=”CompanyName” HeaderText=”CompanyName” SortExpression=”CompanyName” />
<asp:BoundField DataField=”Address” HeaderText=”Address” SortExpression=”Address” />
<asp:BoundField DataField=”City” HeaderText=”City” SortExpression=”City” />
<asp:BoundField DataField=”Country” HeaderText=”Country” SortExpression=”Country” />
<asp:BoundField DataField=”Phone” HeaderText=”Phone” SortExpression=”Phone” />
<asp:HyperLinkField DataNavigateUrlFields=”SupplierID,CompanyName” DataNavigateUrlFormatString=”ProductsForSupplierDetails.aspx?SupplierID={0}&CompanyName={1}” Text=”View Products” />
</Columns>
</asp:GridView>

Pretty simple and straightforward it seems but I have seen many people struggling with this simple concept. Also I found wrong answers for this problem in some .NET forums. Hence thought to post it here for benefit of masses.

~Mayank

 

Read Full Post »

Well, that’s a pretty simple requirement if you are a database developer. Surprisingly I was only aware of how to do this using SQL Server Enterprise Manager. But one of my friends asked me how to do it using SQL Script. The requirement was that we cannot drop the existing table and recreate it again as the table has a lot of data. A little googling helped to figure out that it’s pretty simple.

There is an inbuilt system stored procedure “sp_rename” which can be used to accomplish this task. Infact this system stored procedure can be used to rename a user created object like table, column or user-defined data-type.

The syntax is pretty simple:-

sp_rename [ @objname = ] ‘object_name’,[ @newname = ] ‘new_name’ [ , [ @objtype = ] ‘object_type’ ]

Eg. – To rename a column of table – “MyTable” from “OldColumnName” to “NewColumnName”, use following syntax:-

EXEC sp_rename ‘MyTable.[OldColumnName]’, ‘NewColumnName’, ‘COLUMN’

And yes, that’s it. Pretty simple, but then you need to know it that something like this exists. 🙂

~Mayank

Read Full Post »