Saturday, May 30, 2009

Adding a list item to Document library through c# in SharePoint 2007

As we all know the required filed for document library is not Title. We should be upload a file for the document library list item. This is the required field by default. You can add columns and make them required as well. So, when through program we want to add a list item to the document library then we need to collect the uploaded file and the meta data i.e. the list of columns data. So on the custom form of the document library we need to add ASP.NET controls for file upload and for all the other columns. Here I thought of giving C# code we need to use to create a list item in SharePoint document library. I think this is the only efficient way of adding a list item to document library. If not please post your ideas.

string fileName = fileUpload.PostedFile.FileName;
        using (SPSite site = new SPSite("http://sharepointserver"))
            using (SPWeb web = site.OpenWeb("/"))
                    web.AllowUnsafeUpdates = true;
                    using (FileStream fs = File.Open(fileName, FileMode.Open))
                        SPList list = web.Lists["Documents"];
                        Hashtable metaData = new Hashtable();
                        for (int i = 0; i < keys.Count; i++)
                            metaData.Add(keys[i], values[i]);
                        SPFile destfile = list.RootFolder.Files.Add(fileName.Substring(fileName.LastIndexOf("\\") + 1),
                            fs, metaData, true);
                        if (destfile == null)
                            lit.Text = "Error in adding file";
                { }
                    web.AllowUnsafeUpdates = false;

NOTE: fileUpload is the control I am using in my code to get the file from the custom form. And the key and value fields are the column label and it’s value respectively. If you know the list column names then remove for loop and hard code the label, value while adding them to the metadata. Example like below code,

metaData.Add("Title", "DemoDocument"); metaData.Add("Version", "1.0"); metaData.Add("Author", "Praveen"); metaData.Add("ContentType", "Document");

NOTE: There is a problem in adding the list item through the code if your list columns are not inheriting from the data types string, int and datetime. You will get an exception. So follow this post to fix it.

Please post your ideas on it.

Show data from list by using web services through SharePoint designer

When working on big SharePoint projects we will get really good requirements and most of them are the challenging. In some cases we need to show the data by pulling from one list in a site to another site. For example, on “Site A” home page we want to show the data from another site or sub site. Then we need to use the web services and call the list and get the items. This is what this blog post is completely about.

How is it useful?

It is useful because as far as i know we have two ways of doing this without coding.

1. By using IFrames on the web pages. Create a content editor web part on the page of the site where you want to show the list content and add a IFrame and point it to the page where list data is present in another web site. So it is just doing nothing, just loading that page as it is. But it’s not a right way of implementing it. Because if authentication is different then you need to login to the site 2 times. One for the main site and another for the IFrame. Another problem is we can’t use IFrames on the web pages for security and performance issues. So it’s not a good way of doing. Then is there any other way?

2. Yes, We have a solution for this and it’s the better way of implementing it. Call a web services by connecting to the web services of the site we want to get the data, pass credentials and then select the list or library you want. Add a web part to the page and give the list we pulled from web service to the web part. That’s it. It is the easy and efficient way. I am explaining this a little bit detail below.

  • Create a SharePoint web part page for showing content by using web services.
  • Open the page in SharePoint designer and detach it from page layout.
  • Now, we need to add a web part for showing the data. We have a very comfortable and most usable web part in SharePoint nothing but Data View web part. Add this to the page.
  • Now, web part is ready and need of data source. So we need to go for list where we have to pull the data. It can be in the current site, sub site or some other site.        SharePoint_XML_Web_Services
  • Go to Task pane menu item of SharePoint designer and select the Data Source Library. Now you are able to see the all the data sources exist in the current site only. For example, if you want to pull data from a list which is in subsite or some other site you can’t find the data source entry in the lists and libraries section. This is the time where we need to go for XML web services. See above figure for more details.
  • Now we need to add a new XML web service data source to our site. How difficult it will be? very simple, follow below.
  • Expand the node XML web services and you can find the link called “Connect to a web service”.
  • Now you are open up with a window where you can see the properties of the web service data source.XML_web_services_Data_Source_PorpsNow we need to set the properties for the data source. Move to the tag General. Fill the name, description and keywords. 
  • Move to tab source and fill the source description location. You can browse to the location of the site and add the which asmx file you want to add. Or if you know location you can manually enter the url. Usually the location is like this. http://sharepointserver/_vti_bin/lists.asmx?WSDL
  • Remember for example purpose, i given the lists.asmx file path, you can use any of available web service.
  • Before click on Connect now button, check the credentials. You have options for entering credentials, or use default/windows credentials or don’t ask for credentials etc. Set the credentials type and then say Connect now.


  • Now the command to configure. Our case, we want to show the data. So it should be the Select operation.
  • Select the port : ListsSoap [default one]
  • Select operation type : GetListItems
  • Now time to pass parameters to the data source. First thing is you need to select the listname parameter that from which list you need to get the data.You can pass parameters to the web service to filter the results too.  For example, if your list has the boolean column for IsUpdated. Then we want to show only the columns which are having IsUpdated=false, then you can set that here. DataSource_ParametersWe are done with configuring and setting the data source properties.
  • We have data source ready and we need to use it to link to the data view web part.
  • If you set everything correct, then you can see the entry under the XML web service section.
  • Click on the data source and choose Show data from the menu.
  • Now, it will open Data Source Details task pane that pull all the column information from the list that you connected to as shown below.

DataSource_DetailsSelect the  list of columns that you want to show on the page. [You can select multiple columns by pressing the control key.]

  • Now click on the option “Insert selected fields as” Multiple Item view. It will add all the selected columns to the Dataview web part.
  • Save the page and view it in browser.

How nice it is, and how simple it is?

Really thanks to SharePoint designer and Data view web part that makes our work easier.

Please post comments if you have any questions.

Friday, May 29, 2009

Deleting the list items at a time from list using batch command in SharePoint 2007

When we are working with SharePoint, especially when programming we can discover plenty of things. Because SharePoint is a very big system and it is pretty much good for the developers and administrators that they can discover more and explore more. Its a lovely product and I really love it from Microsoft. From my experience in SharePoint i thought of placing all the SharePoint stuff in my blog. So now we are going to discuss about the performance and operations we need to perform in coding when dealing with SharePoint objects.
When we do SharePoint coding we need to think a minute about the efficiency and performance of the code . For example, if you take a person object, it has some properties around 10-15 and if you don’t dispose it from memory then we don’t get serious problems. But where as we dealing with SharePoint objects like SPSite and SPWeb etc, these are very big and occupies lot of memory than objects what we use every day in C#. So, don’t forget to use Using keyword for them so that they will be disposed and removed from memory when it’s scope is over automatically.
Another performance related is performing common operation to all list items in a list in SharePoint. Usually we will write a foreach statement to loop through all the list items in a list and perform operation. i.e. add, edit or delete. When we need to update all the list items and columns, data varies dynamically then  we need to write a update command inside the foreach statement. But when we know that we are updating the same columns with same data for all items then we shouldn’t follow the code by writing update statement in foreach statement. Because it will be very costly and time taking. It will use lot of resources multiple times. So for this, Microsoft SharePoint team already integrated beautiful feature in SharePoint system. i.e. called Batch programming for doing operations on list items. This is the most efficient way of deleting or updating in a single list.
Below is the code i use to delete all the list items in a SharePoint list.
private void DeleteAllItemsUsingBatch()
        using (SPSite site = new SPSite("http://mySharePointServer"))
            SPWeb web = site.OpenWeb("/");
            SPList list = web.Lists["Links"];
            StringBuilder sb = new StringBuilder();
            string batchCommand = "<method><setlist scope=\"Request\">" + list.ID + "</setlist><setvar name=\"ID\">{0}</setvar><setvar name=\"Cmd\">DELETE</setvar></method>";
            foreach (SPListItem item in list.Items)
                sb.Append(string.Format(batchCommand, item.ID.ToString()));
            web.AllowUnsafeUpdates = true;
            web.AllowUnsafeUpdates = false;
Below is the code for deleting all library items in a document library. Remember, the only difference is for document libraries we have files as the primary fields. So, we should specify the file name along with the other parameters. So, find below code.
private static void DeleteAllItemsUsingBatch()
            using (SPSite site = new SPSite("http://mysharepointserver"))
                SPWeb web = site.OpenWeb("/");
                SPList list = web.Lists["Documents"];
                StringBuilder sb = new StringBuilder();
                string batchCommand = "<method><setlist scope=\"Request\">" + list.ID + "</setlist><setvar name=\"ID\">{0}</setvar><setvar name=\"Cmd\">DELETE</setvar><setvar name=\"owsfileref\">{1}</setvar></method>";
                foreach (SPListItem item in list.Items)
                    sb.AppendFormat(batchCommand, item.ID.ToString(), item.File.ServerRelativeUrl);

                web.AllowUnsafeUpdates = true;
                web.AllowUnsafeUpdates = false;
Explore more and know more. Happy Coding!!!

Thursday, May 28, 2009

How to change the server name which has SharePoint 2007 installed?


Everyone is using and liking SharePoint these days because it is matching a lot of requirements we have. This is a cool and nice product released from Microsoft and we need to know about it and complete administration. I am working from long time on SharePoint and few months back i was trying to change the system name of the server in which SharePoint was installed, i learnt lot of things from it. But before discussing about it, i want to put something about what are the situations for renaming the server?

  • We are developers and will work on ASP.NET, SharePoint, Silver light etc…. SharePoint itself is a very big system and if you install directly the product on the server means development environment then SharePoint installs some services. If you want to work on ASP.NET application tomorrow, then all SharePoint services will run by default. So whenever we don’t want SharePoint to be running on the server then we manually go to the services and stop them. Its not a good way of doing that. So, what i propose here is to create a VHD which has the SharePoint, Sql Server and related software needed for SharePoint. by providing some extra memory to your system i.e RAM around 2-3GB, it will run fast. When SharePoint is needed then only we will run the VHD otherwise not.  So, in our office we have plenty of SharePoint developers and everyone will do the same creating VHD and install SharePoint. But  it’s a very time consuming and long process. Installing will take plenty of time. So, we will create a single VHD and copy it on some share location. From there all devs, admin, team will get the VHD and work on it.

So, till now did you find any problems? no, it was a simple process. When will we get the problem? if we want to add the VHD to network. Now the problem starts…. We have a very big task that we need to add the server to domain and everyone will access the SharePoint server of each other. But we copied the VHD and installed on our system, so all VHD’s will have the same names. So we can’t add the servers to the domain because of having same names, network conflicts will come. So finally we need to perform operation to change the server name.


Here we go: by following the steps below we can complete this operation.

  •    Changing the Alternate Access Mappings:
  1. How to: Browse to SharePoint central administration, operations tab from the top navigation and under the section Global Configuration, select Alternate Access Mappings.


  1. Click on the Alternate access mappings and here select the mapping collection to select all as shown in below figure.


  1. Now, you can see all the mappings[urls] for the existing sites in SharePoint system. This is where we need to change the urls of the sites which points to the new urls. Click on the “Edit public urls” link on the top sub nav menu on the page. Here select the site from mapping collection on the right side one by one and map their urls to new server name as described below. For example, if your old server name is oldMOSSServer then the urls would be like this.
  2. http://oldMOSSServer/, http://oldMOSSServer:8080/, http://oldMOSSServer:4534 etc…..
  3. And if new server name is newMOSSServer then the url’s we need to changed to are as follows…
  4. http://newMOSSServer/, http://newMOSSServer:8080/, http://newMOSSServer:4534 etc…..

Note: Don’t change any port numbers in the above operation, just change the server name only.

  1. Repeat the above steps for all sites until everyone points to new server name.
  • Rename the SharePoint server by using STSADM tool
  1. Go to start –> run and type “cmd” to open the command prompt.
  2. Move to the path where STSADM.exe file is resides. Usually it is in 12hive\Bin folder.
  3. Command we need to run is renameserver. Syantax is:

     stsadm -o renameserver -newservername "newservername" -oldservername "oldservername"

  1. Change the strings highlighted in red to the new server name and old server name respectively and run the command. Remember by performing this operation means we changed the server name only within the SharePoint system but not on the server. This will only replaces the SharePoint system entries old server name with new name in SharePoint databases. Because in SharePoint everything will store in database.
  • Rename the original system name
  1. This is the simple operation generally all of us know. Go to Start button, right click on the Computer option and select Properties.
  2. Click on the change button type the new name for the server and [add it to domain if it’s not already in] and save the changes.
  3. RESTART your server.
  • Change the System and SharePoint credentials
  1. Change the credentials of the SharePoint server by running updatefarmcredentials command from STSADM tool.
  2. Check once all IIS application pools points to the old system name, if any change them and restart IIS.

That’s it!!!

Do you think we are completed with the process? NO, last and final step, TESTING: please open the central admin site of the SharePoint and check everything is working fine!!!

Remember, if any thing is broken or not working as expected, then you are always welcome to revert back the changes you did. If central admin site is not opening then you can do the below steps to revert back to initial state.

1. Rename your server name and [remove from domain and add it to WorkGroup if it was not in the domain before].

2. Rename the SharePoint server by running STSADM command to old server name.

3. Change the Alternate access mappings to revert back to old server name.

4. Restart the server and check the changes.

5. Change the credentials, RESTART IIS.

6. You should be back with all settings as before.

Please post your comments here if you have any questions  or issues.

Note: I was successfully performed all the steps as i said few months back, but forgot to change the Alternate access mappings. So what was the result, i always frustrated and felt bad that server change operation failed. After a long research and study we got it working. Hope this help others….

Tuesday, May 26, 2009

Adding master page to a SharePoint web page

In SharePoint, when we add a new page from browser by going to Site Actions and select Create Page then there we will select the page layout and create the page. So we are selecting the page layout, its always binds to a master page. Whenever you change the default or custom master page, then the changes will be applied to all the page layouts by default. Because SharePoint won’t give direct master page url or name any where. it will be ~masterurl/custom.master for custom master page and ~masterurl/default.master for the default master page.

But when we add a new ASPX page from the SharePoint designer, there we are not having any option to select page layout and inherit it.  So, we will create a simple ASPX page where nothing is added by default to the page other than the head, body tags. When we try to add the master page to the current page then we need to follow some steps to apply master page to the current page. Below are the steps to follow to add master page to the aspx page we just added.

  • By default you will create the ASPX page in pages folder if your site is using publishing site template otherwise create page in the root of the web site.
  • You can create site by right click on the pages folder or on the root site and then select New –> ASPX page. The empty ASPX page when we add will look like as shown below.
  • Empty_ASPX_Page_No_Master
  • We need to perform below steps to add master page to the current page.

1. Add master page reference to the page.

2. Add Placeholders required to the page depends on the master page.

1. Adding master page reference to the page.

  • Now, we need to add the reference to the master page. We can do this by adding below tags.
  • For publishing site template pages, we need to add two entries.
  1. reference master page

    <%@ Reference VirtualPath="~masterurl/custom.master" %>

  2. Adding <%@ Page tag which will inherits from the publishing page.

    <%@ Page language="C#" Inherits="Microsoft.SharePoint.Publishing.PublishingLayoutPage,Microsoft.SharePoint.Publishing,Version=,Culture=neutral,PublicKeyToken=71e9bce111e9429c" meta:progid="SharePoint.WebPartPage.Document" %>

  • But for team web site we need to add page directive as follows.

    <%@ Page language="C#" MasterPageFile="~masterurl/default.master"    Inherits="Microsoft.SharePoint.WebPartPages.WebPartPage,Microsoft.SharePoint,Version=,Culture=neutral,PublicKeyToken=71e9bce111e9429c" meta:progid="SharePoint.WebPartPage.Document" %>

2. Add Placeholders required to the page depends on the master page.

  • This is the very crucial and important step we need to perform. Because placeholders are not same for all site templates. This again will depends on the master page as well. If you customized the master page then you need to add placeholders depends on the placeholders you define.
  • Here i will tell you the main or default placeholders we need to add to a page.

    <asp:Content ContentPlaceHolderId="PlaceHolderPageTitle" runat="server">
    <asp:Content ContentPlaceHolderId="PlaceHolderPageTitleInTitleArea" runat="server">
    <asp:Content ContentPlaceHolderId="PlaceHolderTitleAreaClass" runat="server">
    <asp:Content ContentPlaceHolderId="PlaceHolderPageDescription" runat="server">

    <asp:Content ContentPlaceHolderId="PlaceHolderBodyRightMargin" runat="server"> </asp:Content>

    <asp:Content runat="server" ContentPlaceHolderID="PlaceHolderMain"></asp:Content>

Placeholder “PlaceHolderPageTitle” is for setting the page title of the current page.

Placeholder “PlaceHolderMain” is the main and important placeholder where we keep all the content on the page. Place all the content of your web page in this placeholder.

Note: This blog post is only for letting you know about how to convert sample ASPX page to SharePoint web page by applying the master page to it. All the placeholders and the content is not 100% match your master page. If you have any problems in applying any step then please provide a comment, so that i will look into it.

JQuery integration in SharePoint

As we are well experienced with the JQuery in ASP.NET applications, JQuery is a client side script for executing really impressive logics, calling server-side methods, animations, smooth rendering etc.

SharePoint is a platform and which is built upon ASP.NET, so we can do all the stuff in SharePoint which we implemented in ASP.NET applications. Here is a small walk through of how to integrate the JQuery in SharePoint applications. We usually write lot of logics by using JQuery to get data from server using Ajax implementation by calling Page Web methods and render the data by using JTemplates etc… But We can’t implement the same in SharePoint because we can’t wriite page web methods. Reason behind is SharePoint don’t support page web methods because it is build with ASP.NET 2.0 version. Other than that you can implement all the logics in SharePoint as well.

Follow the steps below to integrate JQuery into SharePoint.

  • Open your SharePoint site in SharePoint designer.
  • It’s always better to organize your data and files in good structure. So create a folder for placing all scripts named “Scripts” if it does not exist.
  • Now copy the Jquery script file to this folder. I am using the file jquery-1.3.1.js.
  • Create an ASPX page in your pages folder of the site if it is a published web site template otherwise create a page in the root of site. [However, any place it works.]
  • This page is not a web part page, we are just creating a simple ASPX page for JQuery integration.
  • Here, add a reference to the JQuery java script file to the head tag of the page.
  • Add the below code to test the Jquery functionality to body of the page.
  • <script type="text/javascript">
        $(document).ready(function() {
            $("#cb").live('click', function() {
            $("#lblMessage").text("you clicked on CheckBox, selected = " + $("#cb").attr('checked'));

    <input type="checkbox" id="cb" />
    <label id="lblMessage"></label>

  • We just wrote a very small piece of code snippet for testing the JQuery functionality. This post main goal is to integrate the JQuery plug-in for SharePoint. The same way you can add reference to the master page of the site to get the advantage of JQuery in all pages of the web site.

  • We can apply the master page to the current ASPX page by following this post. This will give you the same look and feel as other pages.

  • We have plenty of ways to do this. For example, for simple integration purpose i explained you to place the JQuery file in scripts folder of the root of the web site. But good way of doing is, placing the file in Layouts folder of 12 hive in SharePoint system. This way you can access the file in any site and on any page through out the SharePoint. Because Layouts is the common sub site exist for all the sites.

  • Adding script reference to all the pages in a site:

  1. Add the script reference to the <HEAD> tag of the master page of the site. So that all pages have the reference to the JQuery script and you can use it any where.
  2. Syntax: <script type=”text/javascript” src=”/_layouts/scripts/jquery-1.3.1.js”></script>
  • Add script reference to specific pages:
  1. For this we have a good and nice web part to add html/script/css. That is nothing  but Content Editor Web part. We can add a content editor web part on the page [most probably on the top of page] and we will add the script reference code to it. Now the JQuery is available only for the pages where you added the code.

Thursday, May 7, 2009

File System path for Adobe Connect now in Windows Vista

Hi guys,
Today i have a demo with one of my client to show the work i did. One of my colleague try to connect to Acrobat Connect now from his system [i was busy with some other work at that time]. And it was not opening for him, because of some version mismatch or files are missing in his system. He is using the WS 2003 OS, and connectnow files are storing in c:/.../username/application data/MacroMedia/FlashPlayer/ I am using Windows Vista and for me connectnow is working and my idea is to send him the files what i have, because it's working for me. I got the above path from him and when i search for the files in that location in Vista it's not there, because the file system path for Vista is different. After spent some time on it around 7 minutes, i found below location where all the files belongs to the Connectnow.
I sent him the files i have and it's working great. May it will help others....
This is the path for it in Vista....
C:\Users\UserName\AppData\Roaming\Macromedia\Flash Player\\bin\acaddin

Wednesday, May 6, 2009

Use of XML HTTP Request object to make server calls through javascript

We have a requirement that we need to show light box when you click on search results in search results page. When i was new to ASP.NET and know some what about ASP.NET AJAX i thought of using it, but it didn't work out well. Because search results returns large amount of data and if i put everything in UpdatePanel then it can't process the request because Ajax is meant for processing small amounts of data to and from server.

The main requirement is on search results page, we are showing a title, small description and read more link, when anyone clicks on the read more link, it will make a server call and get's the corresponding search results related data and show it up on the page in light box. It needs an ajax call. So, here ASP.NET Ajax won't work. After thought about 2 days i got a new idea and implemented that and working great. Everything was implemented with 15 lines of javascript code by using the XMLHttpRequest object.

How it works:
I have created an ASPX page, where it will return the HTML i need to show in the light box. From the XMLHttpRequest object, i will call that page, gets the response from it and show it in the light box. Very simple!!!
You can use handlers as well to do this. We can write some logic like depends on the querystring params, inside HTTPHandlers build HTML and return that html to browser. But for better styling and html formatting i used ASPX page instead of handlers. Finally, we will catch the response and bind it to the page.


  1. I have taken a data list on my page, where it binds all the search results which matches the given keywords.
  2. I am using data bound event to bind the data on the server side, you can check that in the ASPX.CS section.
  3. I am using a ASP panel, to bind the response from the server and to show the light box. (This is tha panel we are using to bind the response from the server and show the light box.)
protected void dlData_DataBound(object sender, DataListItemEventArgs e)
if (e.Item.ItemType == ListItemType.Header || e.Item.ItemType == ListItemType.Footer) return;

Literal litTitle = e.Item.FindControl("litTitle") as Literal;
string anchorText = "<a href="javascript:void();" onclick="\"javascript:loadurl('{0}','{2}');return false;\">{1}</a>"; 
litTitle.Text = String.Format(anchorText, "Path of the page", "Title", panelLightBox.ClientID);
Note: "Path of page" is the actual page we need to call, and "Title" is the anchor text.

In this event, you can get the server object and bind the data to the controls declared in the item template of the data list. Example purpose, i am binding data to only litTitle control.

Here, if you observe i am creating a html anchor tag and binding that to the literal control, it's not a good way, rather you can create a html anchor control with runat="server" in Item template and bind the data to it, any thing works. I am using onclick event to make a call to the server, in the onclick event of the HTML anchor control i am calling a javascript function called, "loadurl", which will make a server request through XMLHttpRequest obejct.
So, the process is,
  1. From our code, when you click on the title, it will call the loadurl javascript function.
  2. In loadurl function, we will create a xml http request object and sends the request to server.
  3. We will get response from the server and we will catch the responseText from it, and bind it to the light box control.
You can check the loadurl function below.

var xmlhttp;
function loadurl(dest, parentID) {
try {
xmlhttp = window.XMLHttpRequest?new XMLHttpRequest(): new ActiveXObject("Microsoft.XMLHTTP");
} catch (e) {
xmlhttp.onreadystatechange = function(){triggered( parentID)};
//xmlhttp.setContentType("text/xml");"GET", dest);
var mainLightBoxDiv = null;
function triggered( parentID) {mainLightBoxDiv = parentID;
if ((xmlhttp.readyState == 4) && (xmlhttp.status == 200))
var div = document.getElementById(parentID);
div.innerHTML = xmlhttp.responseText.toString();

if (self.pageYOffset) {
yScroll = self.pageYOffset;
} else if (document.documentElement && document.documentElement.scrollTop){ // Explorer 6 Strict
yScroll = document.documentElement.scrollTop;
} else if (document.body) {// all other Explorers
yScroll = document.body.scrollTop;
var centerY = (yScroll + 170); = centerY+'px';"block";

function CloseDiv()
document.getElementById(mainLightBoxDiv).style.display = 'none';
  1. We are using two variables. One for the destination url, and one for the parentID which holds the id of the control for light box.
  2. We are creating an XMLHttpRequest object.
  3. When ready state changed, we are trigerring one event to process our request.
  4. Making the GET request to get the data from server.
  5. Binding the reponseText from the response to the parentID innerHTML.
  6. And some sort of logic to detect the y axis unit where we need to show light box, for this we are detecting the scrollbar position and setting the position of the division. i am doing some operation by adding/subtracting 170 - which is the minimum height of the light box in my scenario.
  7. We are using another function to close the light box.
These days, there are lot of technologies are coming and the best way of implement the above case is using JQuery. We cn use JTemplates to bind the search results data and make an ajax call to the page web method to get what we want. This is very simple and best.

For seeing it live or to test, you can see the page i developed for one of our client.

Very simple!!! Happy coding.

Tuesday, May 5, 2009

How to generate random numbers in t-sql?

How to generate random numbers in t-sql? Is there any built-in logic or keyword for Random? Yes, the answer is below.
DECLARE @mx int, @mn int;
SELECT @mx = MAX(EmployeeID) FROM Employee;
SELECT @mn = MIN(EmployeeID) FROM Employee;
SELECT EmployeeID,EmployeeName FROM Employee WHERE EmployeeID = ROUND(@mn + (RAND() * (@mx-@mn)),0);
RAND() is the function which gives you the random number between 0 and 1. So, depends on the minimum and maximum numbers, it will give you the random number. Hope this is what you are looking for.

How to write inner join in update query T-SQL

When we are very new to t-sql, we generally face a problem like how to make a query to update a table by doing inner join. if you don't know this, your queries become very complex. Below is the example how to write that.

Where it can be useful is,
for example, some how db admin created a table called State, where he is using the StateName as the foreign key instead of the StateID to employee table. Now he wants to update the State column in employee table with the StateID from StateName, we need this inner join in update query.
Update Table1 set columnName = pt.columnName
from Table1 d
inner join Table2 pt on d.columnName = pt.ColumnName

Update Employee set State = s.StateID
from Employee e
inner join State s on e.State = s.StateName 

How to changes column size and column name through t-sql

Alter a column size in a table:

Syntax: Alter Table TableName Alter Column ColumnName DataType(Size)

Example: Alter Table dbo.[AddressType] Alter Column [Address] nvarchar(512);

Change ColumnName, DataType in a Table:
In t-sql, if we want to change the column name and data type as well, then it's some what complex than any other alter statements, below explain the process of how to change the column name as well as the data type change.

--Changing the column name.
Alter Table TableName Alter Column ColumnName DataType NULL/NOT NULL

EXEC sp_rename //Stored Procedure.
@objname = ' TableName. OldColumnName’,
@newname = 'New ColumnName',
@objtype = 'COLUMN'

Alter Table AddressType Alter Column Address VARCHAR(512) NULL

EXEC sp_rename
@objname = 'AddressType.Address',
@newname = 'Address1',
@objtype = 'COLUMN'

How good it is? Did you find any best way other than this?

How to Create/Drop an index in a table through t-sql

Create an Index in a Table:

Syntax: CREATE INDEX [IndexName]
ON [dbOwner].[TabelName] ([ColumnName])

Example: CREATE INDEX [UQ_DateCreated]
ON [dbo].[Contact] ([DateCreated])

Drop an Index in a Table:

Syntax: DROP INDEX [IndexName] on dbOwner.TableName

Example: DROP INDEX [IX_Content] on dbo.Content

How to add a column and set primary key in a table through t-sql

Add a Column to a Table:

Syntax: ALTER TABLE TableName add columnName DataType NULL/NOT NULL

Example: ALTER TABLE Employee add IsDeleted bit NULL

Add a Primary Key To aTable:

Syntax: ALTER TABLE TableName add PRIMARY KEY(ColumnName)

Example: ALTER TABLE AddressType add PRIMARY KEY(AddressTypeID)

Note: PrimaryKey:Don’t Allow duplicates.

How to set Foreign key through sql script

FOREIGN KEY (ColumnInFirstTable)
REFERENCES ReferenceTable(ColumnName)

ADD CONSTRAINT Fk_Employee_AddressType
REFERENCES AddressType (AddressTypeID)

Note: The column AddressTypeID should be a primary key in both tables.

Use of SET ANSI_NULLS ON in SQL SERVER stored procedures

As we discussed the use of NOCOUNT in previous post , we also use another statement with all the stored procedures in sql server. That is SET ANSI_NULLS ON/OFF

Which is useful when,
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE.

When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with non-null values in the column.


In every stored procedure we have define a common syntax is SET NOCOUNT ON/OFF. But most of us don't have any idea of why are we using it.
It is useful because of knowing the number of rows effected by some t-sql statement.

"Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results."

Syntax is: SET NOCOUNT {ON | OFF}

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

Monday, May 4, 2009

Debug SQL Server stored procedures in Visual Studio

We have a beautiful feature in Visual studio, where we can debug the stored procedures.
Follow steps below to debug SPROCS.

  1. Go to Server Explorer (Visual Studio -- View menu -- Server Explorer),
  2. Right click on Data Connections -- select Add Connection,
  3. Here type sql server name and select authentication type(windows or sqlserver), and then type your database. Click ok. Now you are connected to sql server.
  4. Select a SPROC and Right click on stored procedure -- select Step Into Stored Procedure.
  5. Give the default values to debug. Now it starts Debugging.
That's it!!! How simple it is...
Let me know your feedback on it,

MuteX could not be created error in ASP.NET

If you get error like MuteX could not be created then the problem is because of the so many versions of your site are in the temporary files. To fix it, do the following.
  1. If you have visual studio 2005 open, then close it
  2. Go to the ASP.NET temporary folder for v2.0 of the framework drive:\Windows\Microsoft.Net\Framework\v2.0\Temporary ASP.NET Files
  3. Remove the folder for your application. [If it is not deleting, then do IISRESET and delete it.]
  4. Reset IIS (on a command line window, >iisreset)
  5. Now, browse application(http://localhost/your app)
  6. Then reopen Visual studio, open your project in VS and build it.
  7. And it should work now.
All problems were resolved and no problems in running your applications. Hope this tip helped you out!!!

Register IIS with ASP.NEt framework

Some times we forget to install IIS before installing ASP.NET frame work. In these cases, we encounter different problems because of not registering IIS with ASP>NET frame work. Please follow below steps to register IIS.
  1. Open Command Prompt;
  2. Move to your (.net) version directory, like C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>
  3. type registerIIS exe file with option i and enable it. (For Windows2003 you must give enable option,For Windows2000/XP no need just option I is enough.)
Example: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis.exe -i –enable

Now just run IISRESET once to set everything correct.

Special behaviour of Aggregate or Set operation functions(SQL SERVER)

If you are calculating the average of employe salaries from employe table,
The aggregate functions (example AVG()) : Null values are ignored.

Note: 1. Null value is eliminated by an aggregate or other SET operations.
2. For Count,Count_Big(For number of items in a group -- returns type of bigint.), they don’t ignore NULL or duplicates. Remaining aggregate functions ignores NULL values.

Take care about these aggregation while analyzing the values....

Sql Server error message "don't allow remote connections"

If your Sqlserver gives error like "don't allow remote connections" etc….
Or error like
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

Main cause for this error is, you are trying to connect to remote sqlserver, but on that server, remote connections option was disabled.

Do the following to solve the problem.
Start -- All Programs -- MicrosoftSqlServer2005 -- Configuration Tools -- SqlServer Surface Area Configuration -- Surface Area Configuration for Services and Connections -- choose your server(MSSQLServer or SQLEXPRESS) -- Database Engine -- Remote Connections -- Using both TCP/IP and namedpipes, click Apply then Ok.

Note: You must restart your server after change this configuration. Go to Administration Tools -- Services -- your server (MSSQLServer or SQLEXPRESS) -- restart/start.

IE Hacking - Apply Styles only to IE

Some scenarios, we need to apply styles only specific to browser. So, here is a small way to hack apply styles only for IE - We need to add inline comments (//) at start of the line.
min-height:300px; /*For other browsers*/
//min-height:380px; /*For IE */

// comments won't read by Mozilla.

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.

Go to IIS and check your virtual directory is configured as an application. If it is not converted as an application then create an application.

Not possible to debug and Integrated Authentication mode not enabled on your server

While Debugging VS2005 if error comes like Not possible to debug and Integrated Authentication mode not enabled on your server then
Go to IIS -- default website -- properties -- Directory Security tab -- Authentication and Access Control -- edit -- Enable Integrated Authentication mode.

unrecognized configuration section 'connectionStrings'

If you get the error like unrecognized configuration section 'connectionStrings' in ASP.NET application then the reason behind is your application is not running under the ASP>NET version 2.0. It is running under the ASP.NET version 1.1.4322.
To change this, please follow the steps below.
  1. Go to IIS and select the folder Website
  2. Select Default Websites
  3. Right click on the default web sites
  4. Select properties and choose tab
  5. Here select the ASP.NET version to 2.0
Note: To allow ASP.NET version 2.0 you need to do this. In IIS, Web service Extensions allow v2.0.50727.

How to enable Sql Server Authentication in Sql server.

Usually, while installing the sql server, we select the option to use the Windows authentication mode to connect to sql server. But for some scenarios we need Sql server authentication mode to access the database. How to changes? Below are the steps to follow to enable sql server authentication for sql server.
  1. Login to sql server with windows authentication.
  2. Right click on the server (your sql server name), select properties.
  3. In the section, select a page, select security and in the server authentication section select the radio button SqlServer and windows Authentication mode, then click ok.
  4. After your changes saved, then go to Security -- users -- add a user for sql authentication.
  5. Needs restart of the Sql Service instance to reflect the changes.