Thursday, December 31, 2009

Display Images in GridView from Sql Server Database Table Using Asp.net C#




Please visit my new Web Site WWW.Codedisplay.com



In my previous post i showed you "How one can upload images into Sql Server using Asp.net C# FileUpload control". In this post i will show you how one can display images into a GridView from Sql Server table. As you know in most of the web applications requires to handle different type of images like large,thumbnail etc. If those web applications are e-commerce site then you must be carefull when handling images. In previous post i showed how you can store images & in this post i will show you how one can display images from Sql server table. The table structure is given below:




















Fig: Table structure

Displaying picture or image in a GridView is a different way then just using a image tag. In ASP.NET we can define a Handler to access the image from data base. So now we need to create a Handler to read binary data from database. To do that Right click on solution explorer and Add new item, click on Generic Handler and name it ImageHandler.ashx. Write this code in ProcessRequest method:
using System;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public class ImageHandler : IHttpHandler 
{
    
    public void ProcessRequest (HttpContext context) 
    {
        string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "Select [Content] from Images where ID =@ID";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;

        SqlParameter ImageID = new SqlParameter("@ID", SqlDbType.BigInt);
        ImageID.Value = context.Request.QueryString["ID"];
        cmd.Parameters.Add(ImageID);
        conn.Open();
        SqlDataReader dReader = cmd.ExecuteReader();
        dReader.Read();
        context.Response.BinaryWrite((byte[])dReader["Content"]);
        dReader.Close();
        conn.Close();
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }
}
Ok now add an aspx page in your project. Add a GridView control with a template field. Within the template field define image URL like below:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Display_images.aspx.cs" Inherits="Display_images" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Display Images in GridView from SQL Server</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        
        <asp:GridView ID="GVImages" runat="server" AutoGenerateColumns="false" HeaderStyle-BackColor="red" HeaderStyle-ForeColor="white">
        <Columns>
    
        <asp:BoundField DataField="ID" HeaderText="ID" />
        <asp:BoundField DataField="Name" HeaderText="Description" />
        <asp:BoundField DataField="Type" HeaderText="Type" />
    
        <asp:TemplateField HeaderText="Image">
        <ItemTemplate>
        <asp:Image ID="Image1" runat="server" 
                   ImageUrl='<%# "ImageHandler.ashx?ID=" + Eval("ID")%>'/>
        </ItemTemplate>
        </asp:TemplateField>
    
        </Columns>        
        </asp:GridView>
    
    </div>
    </form>
</body>
</html>
Now everything is set except binding sql server data into the GridView. To do that write the below code in Page_Load event:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.UI;
using System.Data.SqlClient;

public partial class Display_images : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connectionString);
            using (conn)
            {
                SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Images", conn);
                ad.Fill(dt);
            }
            GVImages.DataSource = dt;
            GVImages.DataBind();
        }
    }
}
Now run the project & hope you wil get a webpage like below:















Fig: Sample Output

So i think now you can display images from sql server table into a GridView. Happy programming.

Wednesday, December 30, 2009

Save Images into Sql Server Database Table using asp.net FileUpload Control




Please visit my new Web Site WWW.Codedisplay.com



This article will explain how one can insert or save images into a sql server database table using asp.net FileUpload control. You may ask why we will save or store images into sql server database table instead of a server folder? The answer is its easy to use, easy to manage, easy to backup as well as easy programming. But one thing you have to keep in mind that you need to extend the size of your database rather than your regular size. Always deleting images from server folder is a hectic job where security will play a great role but if you store images into sql server database you can remove images or related images by issuing a simple delete sql command.

If you want to upload images into server folder then click here.

If you want to display images from server folder into datalist then click here.

To start to explain how to store images into sql server database  or Upload images into sql server first create a below like table:




















Where ID is the identity field which you will use as a foreign key when entering details data like products_images. In products_images you can easily use imageid as a thumbnail image or large image. The table columns for products_images may like ProductID, ImageType & ImageID.

Now add a webpage in your project and named it Save_Images.aspx then copy the HTML Markup code from below:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Save_Images.aspx.cs" Inherits="Save_Images" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>How to save images into sql server database</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        Name:        
        <asp:TextBox runat="server" ID="txt_Image_Name"></asp:TextBox><br />    
        Image Path:
        <asp:FileUpload runat="server" ID="FileUpload1" /><br /><br />
        <asp:Button runat="server" ID="cmd_Upload" Text="Upload Image" OnClick="cmd_Upload_Click" />

    </div>
    </form>
</body>
</html>

Your page will look like below:













Now under cmd_Upload button write the below server side code. For your better understanding i have put full code here:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;

public partial class Save_Images : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void cmd_Upload_Click(object sender, EventArgs e)
    {
        string s_Image_Name = txt_Image_Name.Text.ToString();
        if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
        {
            byte[] n_Image_Size = new byte [FileUpload1.PostedFile.ContentLength];
            HttpPostedFile Posted_Image = FileUpload1.PostedFile;
            Posted_Image.InputStream.Read(n_Image_Size, 0, (int)FileUpload1.PostedFile.ContentLength);

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString);

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "INSERT INTO Images(Name,[Content],Size,Type)" +
                              " VALUES (@Image_Name,@Image_Content,@Image_Size,@Image_Type)";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            SqlParameter Image_Name = new SqlParameter("@Image_Name", SqlDbType.VarChar, 500);
            Image_Name.Value = txt_Image_Name.Text;
            cmd.Parameters.Add(Image_Name);

            SqlParameter Image_Content = new SqlParameter("@Image_Content", SqlDbType.Image, n_Image_Size.Length);
            Image_Content.Value = n_Image_Size;
            cmd.Parameters.Add(Image_Content);

            SqlParameter Image_Size = new SqlParameter("@Image_Size", SqlDbType.BigInt, 99999);
            Image_Size.Value = FileUpload1.PostedFile.ContentLength;
            cmd.Parameters.Add(Image_Size);

            SqlParameter Image_Type = new SqlParameter("@Image_Type", SqlDbType.VarChar, 500);
            Image_Type.Value = FileUpload1.PostedFile.ContentType;
            cmd.Parameters.Add(Image_Type);


            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}
Now run your page give a name for your image. Then select an image to upload. For multiple upload you may read my another post CLICK HERE. Click on upload. Hope your image will be successfully uploaded. Go to the SqlServer andopen the images table. You will get a scenario like below:






So now i hope you can upload images into your sql server using FileUpload Asp.net control. In my next article i will show you how one can retrieve images from Sql Server into a GridView/DataList/Repeater control.

Wednesday, December 16, 2009

Javascript confirm message before delete from Asp.net LinkButton or Button Control




Please visit my new Web Site WWW.Codedisplay.com



In our Asp.Net web application a common task is to insert, update or delete data from Database. It will be a good practice if you prompt user before deleting a row or data from Database since after deletion you can not retrieve data from database. Think if user click to delete a row & you don't prompt any message then the data will be deleted. If the user click on delete Button mistakenly then what happend? So its a good idea to prompt user by using javascript confirm before deleting a row.


Fig: Output

As shown in above figure when a user clicks on a LinkButton or on a Button control system will prompt "Are you sure to delete?" javascript confirm message box with two button OK & Cancel. If user click on OK then corresponding server side code will be executed. If cancel then nothing happened.

One can prompt user by using three ways. You can use any one in LinkButton, Button Or Image Button control even within GridView, DataList or Repeater control. These are:
Way 1: Using Properties.
Way 2: From HTML Markup.
Way 3: Runtime or Programmatically.

Using Properties:
This is the most simple, easy way to prompt user confirmation message from LinkButton / Button / Image Button control property. Look at the below image how to do it.


Fig: Property window

Here i explain how you can do it. First right click on any one of your control such as LinkButton / Button / Image Button. Then go to the properties. From properties find OnClientClick attribute and write the following code:
return confirm('Are you sure to delete?');

From HTML Markup:
Its also easy. One can easily add the confirmation message from HTML Markup language of the control in the following way:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Delete_Confirmation.aspx.cs" Inherits="Delete_Confirmation" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Javascript confirmation delete example</title>
</head>


<body>
<form id="form1" runat="server">
<div>


<asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" OnClientClick="return confirm('Are you sure to delete?'); "></asp:LinkButton>
<asp:Button ID="cmdDelete" runat="server" Text="Delete" OnClientClick="return confirm('Are you sure to delete?'); " />


</div>
</form>

</body>

</html>

Runtime or Programmatically:
Its a bit advance option. In many cases you need to generate controls dynamically like if you generate a GridView in runtime & also add a LinkButton in GridView rows to give options user to delete data then you don't have any choice except it. Look at the below code how one can add dynamically javascript confirmation message on a dynamically generated LinkButton:


using System;
using System.Web.UI.WebControls;

public partial class Delete_Confirmation : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
lnkDelete.Attributes.Add("onclick", "return confirm('Are you sure to delete?');");
cmdDelete.Attributes.Add("onclick", "return confirm('Are you sure to delete?');");


Button btnDelete = new Button();
btnDelete.Text = "Dynamic Control to Delete";
btnDelete.Attributes.Add("onclick", "return confirm('Are you sure to delete?');");
this.Form.Controls.Add(btnDelete);
}
}
}

Hope now one easily add javascript confirmation message for LinkButton, Button & Image Button even though these were within a GridView or DataList or a Repeater control.

Read my another post How to add confirmation message before deletion in a GridView LinkButton.

Tuesday, December 15, 2009

Change Windows startup and shutdown sounds or music




Please visit my new Web Site WWW.Codedisplay.com



You may does not like the default Windows startup and shutdown sounds, those are just simple music & after hearing in most the times you may borred. Thats why you may think to change your Windows startup & shutdown music that i will explain in this post step by step.

One another nice thing is one can also change other music or sounds in windows such as error sound, new hardware insert sound, empty recycle bin sound and more! To do that you can take one of your favourite mp3 which you like to hear as default windows sound.

You have to do two extra job like firts cut a portion of your selected mp3 then convert it into wav format. To cut the portion of your mp3 you can use freeware like Audacity to chop up the music. You can use a free software like JetAudio, Media-Convert to convert your audio files into WAV format. But keep in mind that the size must be less than or eqal to 1MB. It will better if you keep the size within 500KB.

Step 1: Choose your favourite mp3 song.
Step 2: Cut two portion that you like for startup & shutdown music.
Step 3: Convert mp3 portions to WAV format.
Step 4: Rename these files to Windows XP Startup.wav and Windows XP Shutdown.wav respectively.
Step 5: Now click on My Computer and go to C:\Windows\Media. Here you will found the files Windows XP Startup.wav and Windows XP Shutdown.wav along with a lot of other windows sounds like error, empty reccyle bin etc.
Fig: Location of files

Step 6: Now copy the two files into another location so if your settings does not work then you can revert back.
Step 7: Now copy your two files that you have made & paste into the C:\Windows\Media folder and restart your computer to take place the changes.Now you should hear the new music or songs or sounds rather than the default sound.

If you don't like other Windows default sounds then you can alos change those by using exactly the ways that i have described.

It's really simple to change the Windows login and logoff sounds. So hope now you can do it.

Cross Page Posting or Postback in Asp.net 2.0 / 3.5




Please visit my new Web Site WWW.Codedisplay.com



In Asp.net 1.1 we can postback a form itself but can't posting back to other pages. This is a limitation of Asp.net 1.0. We can simulate cross page postback by using Response.Redirect or Server. Transfer that i have already describedin one of my previous post named "Passing data/parameters/values from one aspx page to another aspx page".

In Asp.net 2.0 or Asp.net 3.5 microsift gives us a solution to easily do the cross page postback that i will cover in this article. The feature known as "Cross Page PostBack" from a web form or aspx page to post back to a different or another web formor aspx page.

Asp.net 2.0 or 3.5 offers us to post back to another form by using PostBackURL property of a control that implements IButtonControl. Here note that Button, ImageButton, LinkButton has the PostBackURL property to submit one page/form to different page/form. Also provides us a FindControl Method to catch the controls of previous page or source page. By using PreviousPage property we will catch the page that has been submitted. So using PreviousPage property to catch the previous page & FindControl Method to catch the controls of this previous page.

There are two ways we can post back a form into another form. These are:
1. Direct access controls from target page
2. Direct access public properties from target page

Direct access controls from target page:
To describe this example first create a project. Then rename the default.aspx page to page1.aspx. Add one another page & rename it by page2.aspx. Here we will generate cross page postback from page1.aspx to pag2.aspx page. Now add two TextBox in source page means page1.aspx and a Button control. Set the PostBackUrl="page2.aspx" of theButton control. No server side code is required for the page1.aspx since after clicking on the page1, Button Control will submit itsself into the page2.aspx that i have specified in PostBackUrl property. Have a look at source page page1.aspx markup:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="page1.aspx.cs" Inherits="page1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Cross page postback example 1</title>
</head>
<body>
<form id="form1" runat="server">

<div>
<asp:Label ID="lblFirstNum" runat="server">Enter First Number:</asp:Label>
<asp:TextBox ID="txtFirstNum" runat="server"></asp:TextBox>

<br />

<asp:Label ID="lbl2ndNum" runat="server">Enter 2nd Number: </asp:Label>
<asp:TextBox ID="txt2ndNum" runat="server"></asp:TextBox>

<br /><br />

<asp:Button ID="cmdCross" runat="server" Text="Click to Trigger Postback" PostBackUrl="page2.aspx" />
</div>

</form>
</body>
</html>

Now we have to do some work on page2.aspx to collect data from page1.aspx. By using PreviousPage property & FindControl Method we can easily catch data from source page. Have a look at the page2.aspx page_load event where we catch source page first number & Second number & show the cumulative result in page2.aspx page.
Code behind of page2.aspx:


using System;

using System.Web.UI.WebControls;

public partial class page2 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

if (PreviousPage != null && PreviousPage.IsCrossPagePostBack)

{

decimal nFirstNum = Convert.ToDecimal(((TextBox)PreviousPage.FindControl("txtFirstNum")).Text);

decimal n2ndNum = Convert.ToDecimal(((TextBox)PreviousPage.FindControl("txt2ndNum")).Text);

Response.Write("Sum of previous page two number is: " + (nFirstNum + n2ndNum));

}

else

{

// This is not cross page postback.

// Implement Other logic here

}

}

}

}

If you use master page then you need to give diefferent treatment to get data from cross page postback. Keep page1.aspx as it is. Only modify the page2.aspx page_load event in the following way:


using System;

using System.Web.UI.WebControls;



public partial class mPage2 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

if (PreviousPage != null && PreviousPage.IsCrossPagePostBack)

{

ContentPlaceHolder content = (ContentPlaceHolder)Page.PreviousPage.Form.FindControl("ContentPlaceHolder1");

decimal nFirstNum =Convert.ToDecimal(((TextBox)content.FindControl("txtFirstNum")).Text);

decimal n2ndNum = Convert.ToDecimal(((TextBox)content.FindControl("txt2ndNum")).Text);

Response.Write("Sum of previous page two number is: " + (nFirstNum + n2ndNum));

}

else

{

// This is not cross page postback.

// Implement Other logic here

}

}

}

}



Direct access public properties from target page:
To direct access public properties you have to create some public properties in source page which we will access from target page. To do that modify page1.aspx page server side code in the following way & keep HTML mark up as it is:


using System;

public partial class page1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

public decimal nFirstNum
{
get { return Convert.ToDecimal(txtFirstNum.Text); }
}

public decimal n2ndNum
{
get { return Convert.ToDecimal(txt2ndNum.Text); }
}
}

Don't forget to add the below page directives into the page2.aspx. Otherwise the next code segment will give you error since in page_load event you will acess page1 public properties. The directive is:

<%@ PreviousPageType VirtualPath="~/page1.aspx" %>

Now also modify page2.aspx page_load event in the following way:


using System;

using System.Web.UI.WebControls;


public partial class page2 : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

if (PreviousPage != null && PreviousPage.IsCrossPagePostBack)

{

decimal nFirstNum = PreviousPage.nFirstNum;

decimal n2ndNum = PreviousPage.n2ndNum;

Response.Write("Sum of previous page two number is: " + (nFirstNum + n2ndNum));

}

else

{

// This is not cross page postback.

// Implement Other logic here

}

}

}

}


The Output will be like this:

Fig: Output

Note:
If your source page has more than one control that implements IButtonControl than you can define different target page fordifferent controls.

In this example i showed you how to make Cross Page Posting or postback in ASP.NET 2.0 / 3.5 using C#. Hope it will help.

Monday, December 14, 2009

Bind images from folder to DataList




Please visit my new Web Site WWW.Codedisplay.com



If you are looking for how to bind images from database to DataList or GridView i hope you will get a lot of example for it. But in this article here i will show you "How one can bind images from server folder to a DataList". Its a bit easy in Asp.Net since Asp.Net provides us a really efficient namespace "System.IO". In this post i will first read the folder where images stored then read all file names if file has a valid extension for images then stored into a list. After that i will bind those list into the DataList. Within DataList i will use an Image control and HyperLink control to display images & name respectively. Here you can also filter image file extension like bmp, jpeg, jpg, png, gif whatever you want. In this example i only consider Jpeg, Jpg, Gif format files or images.

OUTPUT:

Fig: Images within DataList

Complete HTML Markup Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Datalist_Image.aspx.cs" Inherits="Datalist_Image" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Bind images from Folder to Datalsit</title>
</head>

<body>

<form id="form1" runat="server">

<div>

<asp:DataList ID="DataList1" runat="server" RepeatColumns="5" CellPadding="5">
<ItemTemplate>
<asp:Image Width="100" ID="Image1" ImageUrl='<%# Bind("Name", "~/Images/Books/{0}") %>' runat="server" />
<br />
<asp:HyperLink ID="HyperLink1" Text='<%# Bind("Name") %>' NavigateUrl='<%# Bind("Name", "~/Images/Books/{0}") %>' runat="server"/>
</ItemTemplate>
<ItemStyle BorderColor="silver" BorderStyle="dotted" BorderWidth="3px" HorizontalAlign="Center"
VerticalAlign="Bottom" />
</asp:DataList>

</div>

</form>

</body>

</html>

Code behind/Server side code:
using System;
using System.Collections;
using System.IO;

public partial class Datalist_Image : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{
DirectoryInfo dir = new DirectoryInfo(MapPath("Images/Books"));
FileInfo[] files = dir.GetFiles();
ArrayList list = new ArrayList();
foreach (FileInfo oItem in files)
{
if (oItem.Extension == ".jpg" oItem.Extension == ".jpeg" oItem.Extension == ".gif")
list.Add(oItem);
}
DataList1.DataSource = list;
DataList1.DataBind();
}

}
}

Hope now you can bind server side folder images into the DataList as well as can bind folder image list into the GridView.

Saturday, December 12, 2009

Display hidden files when Folder Options Removed by Virus




Please visit my new Web Site WWW.Codedisplay.com



Some viruses play a hide & seek role with windows users By removing the folder options sub menu from Tools menu. Then user cried that they had lost their valuable files where as the virus just hide those as hidden or system files. Such type of virus mainly came from Indonesia. Basically they are not harmful for your computer except they hide your valuable files & modify the registry to hide the Folder Options from Tools menu.

If you do not found the Folder Options Menu from Tools menu then you can take the below steps.

Step by step figures to recover hidden files:
1. Click on start menu.
2. Click on Run.
3. Type regedit & press Enter.

Fig: Covered up to step 3.

4. Find HKEY_CURRENT_USER node & expand it.

Fig: Covered Step up to 4.

5. Now find Software node & expand it.
6. Now Find Microsoft node & expand it.


Fig: Covered up to Step 6

7. Now go to Windows\Currentversion\Explorer node.
8. Now click on Advannced node.
9. Now double click on Hidden link from right side list.


Fig: Covered up to Step 9.

10. Now change 0 to 1 in Value data field.
11. Now click on OK.

Fig: The End.

Hope now you can view all hidden files from windows even spam or trozan or virus remove Folder Options from your computer Windows XP.

Tuesday, December 8, 2009

Javascript to get CheckBoxList value




Please visit my new Web Site WWW.Codedisplay.com



To read CheckBoxList Selected Value, Selected Index & Selected Text in serverside is an easy job but using javascript its a bit difficult. In this article i want to share with you how one can read CheckBoxList SelectedValue, SelectedIndex & SelectedText by using javascript. Before explanation i want to share with you that getting SelectedIndex & SelectedText from javascript is a bit easy rather than SelectedValue because there is no direct way to read SelectedValue from CheckBoxList. To do that you have to work a bit hard like in databound time of CheckBoxList you have to add an attribute named ALT as value. Then from checkbox array you can read the ALT value as SelectedValue. There is no easy alternative not found yet. If you do not want to read Selected Value of CheckBoxList then please remove the CheckBoxList1_DataBound method from server side code that i will show you later. Also remove variable spanArray, sValue & its related lines from javascript.

If you are looking for Jquery solution then CLICK HERE.

Focus Area:
1. How to get SelectedValue from CheckBoxList using javascript.
2. How to get SelectedText from CheckBoxList using javascript.
3. How to get SelectedIndex from CheckBoxList using javascript.
4. How to bind sql server data into CheckBoxList using Asp.net.


To do that first of all create a table like below:


Here i am considering that the table name is Article. Now add an aspx page in your project and write the HTML Markup like below:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Javascript_Checkbox.aspx.cs" Inherits="Javascript_Checkbox" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Javascript Selectted Item From CheckBoxList</title>

<script type="text/javascript">
function Get_Selected_Value()
{
var ControlRef = document.getElementById('<%= CheckBoxList1.ClientID %>');
var CheckBoxListArray = ControlRef.getElementsByTagName('input');
var spanArray=ControlRef.getElementsByTagName('span');
var checkedValues = '';
var nIndex=0;
var sValue='';

for (var i=0; i<CheckBoxListArray.length; i++)
{
var checkBoxRef = CheckBoxListArray[i];

if ( checkBoxRef.checked == true )
{
var labelArray = checkBoxRef.parentNode.getElementsByTagName('label');


if ( labelArray.length > 0 )
{
if ( checkedValues.length > 0 )
{
checkedValues += ', ';
nIndex += ', ';
sValue += ', ';
}
checkedValues += labelArray[0].innerHTML;
nIndex +=i;
sValue +=spanArray[i].alt;
}
}
}
document.getElementById('<%= lbl_SelectedValue.ClientID %>').innerHTML='<b>Selected Value:</b> '+ sValue;
document.getElementById('<%= lbl_SelectedText.ClientID %>').innerHTML='<b>Selected Text:</b> '+checkedValues;
document.getElementById('<%= lbl_SelectedIndex.ClientID %>').innerHTML='<b>Selected Index:</b> '+nIndex;
}
</script>

</head>
<body>
<form id="form1" runat="server">
<div>
<asp:CheckBoxList ID="CheckBoxList1" runat="server" onclick="Get_Selected_Value();" OnDataBound="CheckBoxList1_DataBound">
</asp:CheckBoxList>
<hr />
<label id="lbl_SelectedValue" runat="server"></label><br />
<label id="lbl_SelectedText" runat="server"></label><br />
<label id="lbl_SelectedIndex" runat="server"></label>
</div>
</form>
</body>
</html>

Now in code behind write the serverside code like below:
using System;
using System.Data;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class Javascript_Checkbox : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connectionString);
using (conn)
{
SqlDataAdapter ad = new SqlDataAdapter(
"SELECT ID,Title from Article", conn);
ad.Fill(dt);
}
CheckBoxList1.DataSource = dt;
CheckBoxList1.DataTextField = "Title";
CheckBoxList1.DataValueField = "ID";
CheckBoxList1.DataBind();
}

protected void CheckBoxList1_DataBound(object sender, EventArgs e)
{
CheckBoxList chkList = (CheckBoxList)(sender);
foreach (ListItem item in chkList.Items)
item.Attributes.Add("alt", item.Value);
}
}

Run the project & you will get an interface like below:


Hope now you can read Selected Value, Selected Text, Selected Index from Asp.net CheckBoxList server control. The SelectedValue may not work in Firefox/Opera. I didn't test yet.

Monday, December 7, 2009

How to detect page refresh using asp.net




Please visit my new Web Site WWW.Codedisplay.com



Most of the forum i found this problem. Thats why in this article i will try to explain how one can detect browser refresh button has been clicked by user. Why detection is necessary because let you have an entry page, when user click to save button then the data saved into the database. The problem is now if user click on browser refresh button then the same set of data again inserted into the database because Refresh button always perform user last action. Which is the problem as well as this is the solution. We will use this technique to resolve this issue.


An Alternative Solution:
Let you have an entry page named frmArticle. Then you can block multiple time insert operation regarding page refresh by just redirect to the same page just after your insertion. Since the refresh works on last event so that if user click on breowser refresh button the frmArticle page will just load again with empty textbox controls.


Now add a page into your project. And write the below MARKUP code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Detect_Page_Refresh.aspx.cs" Inherits="Detect_Page_Refresh" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>How to Detect Page Refresh</title>
</head>

<body>
<form id="form1" runat="server">
<div>

<asp:Button runat="server" ID="btn" Text="Click Me" OnClick="btn_Click" />

</div>
</form>
</body>

</html>

Now in code behind write the below code:
using System;

public partial class Detect_Page_Refresh : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
Session["Check_Page_Refresh"] = DateTime.Now.ToString();
}

protected void Page_PreRender(object sender, EventArgs e)
{
ViewState["Check_Page_Refresh"] = Session["Check_Page_Refresh"];
}

protected void btn_Click(object sender, EventArgs e)
{
if (ViewState["Check_Page_Refresh"].ToString() == Session["Check_Page_Refresh"].ToString())
{
Response.Write("Detect Postback or User Event fired....");
Session["Check_Page_Refresh"] = DateTime.Now.ToString();
}
else
Response.Write("Page Refresh Detected....");
}
}

Output:

Fig: Output

The main logic is we wiil use two variables. One is Session variable & another one ViewStatevariable. Initialy bothe variable contain same time so that when user click on button thenthe condition will be satisfied & execute code that we want. When user click on browser refresh button then the ViewState variable reset to the time of page_load event. So btn_click event condition will not satisfyand write "Page Refresh Detected....". If you cant understand yet then please read the belowexpalanation. Otherwise skip.

Code Explanation:
When you run the project then at first Page_Load evebt fired.Means now Session variable Check_Page_Refresh contains the current date time valuelike "12/7/2009 11:20:14 AM". After that Page_PreRender method will be executed means Viewstate variable Check_Page_Refresh assigned by Session state variable Check_Page_Refresh value. So at that moment both ViewState & Session variable is same.

Now page is open and user click on button. So the Page_Load event wiil be fired but Session variable will not set since the action is PostBack action. After that btn_Click event fired & will display "Detect Postback or User Event fired...." since both Sessionstate variable and ViewState variable are same. And also do not forget that here the Session state variable also reset. Let now the Session state variable holds value "12/7/2009 11:22:14 AM". Now the Page_PreRender event also executed after btn_Click event so that both Session variable and ViewState variable will be same means "12/7/2009 11:22:14 AM". So if user again click on the button same scenario wll be executed since both Session and ViewState variable is same & also both variable reset to the current time.

Ok now if user click on Refresh then what happend? This is the main portion that you have to understand to detect Page Refresh. Steps were: First Page_Load event fired but Session variable not set since the action is PostBack. After that btn_Click event fired. We knew that before click on Refresh button both variable were same but when user click on Refresh button then the ViewState variablere set to previous one before button click event. Means refresh button will perform from user last event. So now the ViewState variable is reset to "12/7/2009 11:20:14 AM" instead of "12/7/2009 11:22:14 AM" where as Session state variable is "12/7/2009 11:22:14 AM". So now you can detect easily that the user click on Refresh button. It will be better to debug the code in each event. So that you can easily understand the cases.

Tuesday, December 1, 2009

The syntax of SQL Server Cursor




Please visit my new Web Site WWW.Codedisplay.com



Cursor means memory address where SQL data processed. By using cursor one can process data one by one by looping through all records within a cursor. So when developer thinks that there is no way to accomplish an operation by writing a single query then the alternative solution is cursor. That means cursor ease our life by providing a looping mechanism through all records. But one thing keep in mind that SQL Server cursor performance is very bad than oracle cursor. So think twice when you want to write a cursor. In my next article i will show you how one can avoid cursor by using simple while loop statement. This is not the focus area of my current Article. Better I will discuss very basics on SQL Server Cursor. One thing is clear that when we need to process row one by one or if we cant built a logic by a single SQL statement then we will use SQL Server cursor. Steps to follow to write a cursor in SQL Server:

1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. Deallocate cursor


Before starting to write a real life example it will be better to discuss the syntax of SQL Server cursor.

Syntax:

DECLARE cursor_name CURSOR
[ LOCAL GLOBAL ]
[ FORWARD_ONLY SCROLL ]
[ STATIC KEYSET DYNAMIC FAST_FORWARD ]
[ READ_ONLY SCROLL_LOCKS OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

Description:
cursor_name is the name of the Transact-SQL server cursor defined. cursor_name must conform to the rules for identifiers.

LOCAL
specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBAL
specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

Note If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server, in which all cursors were global. The default of this option may change in future versions of SQL Server.

FORWARD_ONLY
specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

STATIC
defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

KEYSET
specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMIC
defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

FAST_FORWARD
specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

READ_ONLY
prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

SCROLL_LOCKS
specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft® SQL Server™ locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.

OPTIMISTIC
specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING
specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.

select_statement
is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.

FOR UPDATE
[OF column_name [,...n]] defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

EXAMPLE:
DECLARE @Slab1From INT
DECLARE @Slab2From INT
DECLARE @Slab3From INT
DECLARE Slab_cursor CURSOR FOR
SELECT CONVERT(INT,SUBSTRING(Slab1A_Time_From,1,2)),
CONVERT(INT,SUBSTRING(Slab2A_Time_From,1,2)),
CONVERT(INT,SUBSTRING(Slab3A_Time_From,1,2))
FROM tariff_table_test

OPEN Slab_cursor
FETCH NEXT FROM Slab_cursor INTO @Slab1From,@Slab2From,@Slab3From

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Slab1From>@Slab2From
BEGIN
Print 'Slab1 is greater than Slab2'
-- Other logic here
END

IF @Slab1From>@Slab3From
BEGIN
Print 'Slab1 is greater than Slab3'
--Other logic here
END

FETCH NEXT FROM Slab_cursor INTO @Slab1From,@Slab2From,@Slab3From
END

CLOSE Slab_cursor
DEALLOCATE Slab_cursor

Indications:
Steps to write SQL Server that i have discussed at the beginning of this article is given below:


Code Explanation:
In the above example here i need to apply some business rules when slab1 is greater than slab2 or slab3. SUBSTRING is used since my data was in different format like 08:17:59. And i convert it into INT since I need to compare with Slab2 & Slab3. Here what i did actually doesn't a matter. The matter is how one can write SQL Server Cursor. And hope now you can write SQL Server cursor from simple to complex one.

Remarks:
If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:

If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.

STATIC and FAST_FORWARD cursors default to READ_ONLY.

DYNAMIC and KEYSET cursors default to OPTIMISTIC.

Permissions:
DECLARE CURSOR permissions default to any user that has SELECT permissions on the views, tables, and columns used in the cursor.

This is an Introduction on how to Write SQL Server Cursor. For better understanding you can read below links.

REF:
http://msdn.microsoft.com/en-us/library/ms180169.aspx
http://www.teratrax.com/articles/sql_server_cursor.html

Wednesday, November 25, 2009

Bind GridView with MS Access Database table in Asp.net




Please visit my new Web Site WWW.Codedisplay.com



In many forums i found that developers struggle "To connect with MS Access Database from Asp.net". Thats why in this post i will show you how one can access MS Access database from Asp.net ASPX page to bind GridView. To do that first open any MS Access Database & then create below table:


Fig: Entity

And ofcourse copy your MS Access mdb Database into your application root folder.

Ok, Now our MS Access Database Article table is ready. So add an aspx page in your asp.net project & then drag & drop a GridView within the page. Named it by gvArticle. Full HTML Markup Language is given below:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MSACCESSGV.aspx.cs" Inherits="MSACCESSGV" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Test MS ACCESS DATABASE CONNECTION</title>
</head>

<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="gvArticle" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Id" />
<asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Title" />
<asp:BoundField DataField="Published" HeaderText="Published" SortExpression="Visit" />
<asp:BoundField DataField="Modified" HeaderText="Modified" SortExpression="Modified" />
</Columns>
</asp:GridView>

</div>
</form>

</body>
</html>

Now you need to write code in server side to bind GridView gvArticle. First make connection string for your MS Access Database. And then read data from MS Access database. Complete server side code is given below:

using System;
using System.Data;
using System.Web.UI;
using System.Data.OleDb;

public partial class MSACCESSGV : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{
string sFilePath = Server.MapPath("MSACCESS.mdb");
DataTable dt;
OleDbConnection Conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";");

using (Conn)
{
Conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM ARTICLE", Conn);
OleDbDataAdapter oDA = new OleDbDataAdapter(cmd);
dt = new DataTable();
oDA.Fill(dt);
}

gvArticle.DataSource = dt;
gvArticle.DataBind();

}
}
}

Now run your page. And you will get an output like below:

Fig: Output

Hope now you can make connection with MS-Access Database & also can retrieve data from MS-Access from your asp.net application.

Tuesday, November 24, 2009

SQL Server 2008 Error: Saving changes is not permitted




Please visit my new Web Site WWW.Codedisplay.com



Few days ago I have migrated one of my Database from SQL Server 2005 to SQL Server 2008. Actually what I did during migration? I have created a Database first in SQL Server 2008. Then I have imported all of my SQL Server 2005 tables into the SQL Server 2008 Database. I thought that Everything is perfect but when I live my application for testing then I found that System can not insert data into few tables. Then I debug my application & found that my imported tables has missed identity constraint. So what can I do? According to SQL Server 2005 my understanding was I can apply identity constraint on numeric columns if the column has a sequence of number. So I open my table (which has lost identity constraint) in design mode then click on ID column then from ColumnProperties select identity = yes & then save. But at that time I found the below error:

Saving changes is not permitted. The changes you have made require the
following tables to be dropped and re-created. You have either made changes to
a table that can’t be re-created or enabled the option Prevent saving changes
that require the table to be re-created.

The snapshot is given below:



Fig: Error


The error is self explanatory & helpful enough to find a solution clearly.

Then after few minutes i found the below solution:
GO TO Tools >> Options >> Designers

Then you will found the option "Prevent saving changes that require table re-creation" is checked. Just uncheck the option will resolve your problem.

Please find the step by step screenshots from below:
GO TO Tools >> Options



Fig: Step 1

Now just uncheck the option will resolve your problem:



Fig: Step 2


Hope it will help you.

Monday, November 23, 2009

How to pass SP parameters into Dynamic SQL




Please visit my new Web Site WWW.Codedisplay.com



In my first article I wrote An Introduction on creating Dynamic SQL in SQL Server Stored Procedure (SP). In this article I will try to write a bit advance Dynamic SQL. Here i will show you How one can transfer or pass parameter into Dynamic SQL query & get scalar value as output parameter or can Store output data into a temporary table.

CLICK HERE To read "How can invoke/read SQL Server SP from ASP.net ASPX page".

Focus Area:
1. How one can pass an Input Parameter to Dynamic SQL
2. How one can pass OUTPUT Parameter to Dynamic SQL
3. How one can store Dynamic SQL data into a Temporary table

To do that first create a table Article like below:
ID - bigint - Unchecked
CategoryID - bigint - Unchecked
Title - varchar(500) - Unchecked
Published - datetime - Unchecked
ModifedDate - datetime - Checked
Active - bit - Unchecked
TotalView - bigint - Checked

Then insert below rows into Article table:
INSERT INTO Article
VALUES(1,1,'How to start AJAX','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(2,2,'How to write Dynamic SQL','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(3,1,'Pass parameters','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(4,1,'Advance SQL Sored Procedure','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',0,1005)

INSERT INTO Article
VALUES(5,1,'Advance JQUERY Articles','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',0,1005)


Now our environment is ready for testing. Let our requirement is list all articles based on Active or not. So need to write a stored procedure which will take one parameter for Active or inactive & then we need to pass dynamic sql result into a temprary table.

So first write a Stored Procedure(SP) in SQL Server like below:
CREATE Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'
EXEC(@sSQL)

END

The SP was created. Now run the Above SP by invoking below code:
EXEC ParamToDynamicSQL 1

UFFS i found an error. The error is:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@bActive".


Yes you can not use parameters within the Dynamic SQL directly in a SP. That’s why we need to use the power of SP_EXECUTESQL built in method to pass parameters into dynamic sql instead of EXEC or EXECUTE method. Which i have discussed in my first article on "An Introduction on creating Dynamic SQL in SQL Server Stored Procedure(SP)".

How one can pass an Input Parameter to Dynamic SQL:
To do that first declare a NVARCHAR type variable to store all parameters & then pass it through SP_EXECUTESQL method like below:
ALTER Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'

SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive

END

Now run the above SP by invoking the below command:
EXEC ParamToDynamicSQL 1

Fig: OUTPUT

How one can pass OUTPUT Parameter to Dynamic SQL:
So I think initial work around is done. Now I will try to show you how we can use OUTPUT parameter in Dynamic SQL. Lets now our requirement is to show number of active articles. So we need to modify our previous SP like below:

ALTER Procedure ParamToDynamicSQL(@bActive bit,@TotalCount int OUTPUT)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit,@TotalCount int OUTPUT'

SET @sSQL='SELECT @TotalCount=COUNT(*) FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive,@TotalCount=@TotalCount OUTPUT

END

And you can invoke the Stored procedure in the following way:
Declare @TotalCount int
EXEC ParamToDynamicSQL 1,@TotalCount OUTPUT
print @TotalCount


Fig: OUTPUT

Ok now I hope you can pass parameter value into Dynamic SQL as well as can retrieve OUTPUT parameter value from Dynamic SQL.

How one can store Dynamic SQL data into a Temporary table:
As you know developers life is not so easy. The above techniques may not ease your life. We know that if we want to write a complex SQL then we like to break this SQL in different parts. To do that we use either view or temporary table to break down the complex SQL which will more readable & easy to modify. Here I will show you how we can store Dynamic SQL OUTPUT into temporary table. So that you can use this temporary table with another table to make SQL JOINS like Inner Join, Left Join & Right Join also you can then apply SET operation. One of the examples is given below:

ALTER Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @Article TABLE
(
ID bigint,
CategoryID bigint,
Title varchar(500),
Published datetime,
ModifedDate datetime,
Active bit,
TotalView bigint
)

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'
SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'

INSERT @Article
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive

SELECT * FROM @Article

END

Now you can invoke the above SP like below:
EXEC ParamToDynamicSQL 1


Fig: OUTPUT

This is all about Dynamic SQL. Hope now you can write runtime Dynamic SQL in SQL Server Stored Procedure (SP) to meet the client requirements.

Tuesday, November 17, 2009

Write & Execute Dynamic SQL in SQL Server Stored Procedure




Please visit my new Web Site WWW.Codedisplay.com



Most often we have to Create Dynamic SQL for different conditions to bring back results in our Asp.net applications. If we make the SQL in page code behind then its simple & easy. But think when one need to reuse the query with another set of values then what he can do? Copy the code block & paste it into the newly created aspx page? No here i will try to give you examples on How To Create a Dynamic SQL on the fly in SQL Server SP & reuse it. So lets start with an example:

A simple requirement:
Let you want to pass a table name into a Stored Procedure then collect all data to bind with a GridView. So our dynamic sql should be:
CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
SELECT TOP @topN * FROM @tblName

Uffs you will get the below error:
Msg 102, Level 15, State 1, Procedure GetAllRows, Line 3
Incorrect syntax near '@topN'.

OR
Msg 1087, Level 15, State 2, Procedure GetAllRowss, Line 3
Must declare the table variable "@tblName".

This is the limitation of Dynamic SQL. Don't worry SQL Server provide us two different ways to built a dynamic SQL Statement. These ways are as follows:
1. EXEC()
2. sp_executesql()

Using EXEC():
EXEC takes only one parameter which will be your Dynamic SQL. Its easy to use. If you want to pass few parameters into stroed procedure & then generate Dynamic SQL then its your easy choice. So we can rewrite the previous SP in the following way:
CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
DECLARE @sSQL nvarchar(MAX)
SET @sSQL='SELECT TOP '+CONVERT(varchar(MAX),@topN)+' * FROM '+@tblName
EXEC(@sSQL)

The below command will invoke your SP:
EXEC GetAllRows 10,'AnyTableName'

Now you will get 10 rows from your provided table name.

Using sp_executesql():
The sp_executesql() is a built in System Stored Procedure, an alternative and most flexible upgradation of EXEC(). By using sp_executesql() you will get some advantages like passing parameters into the dynamic sql which i will write later in my another post. Here i will show you a simple example on using sp_executesql():
CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
BEGIN
DECLARE @sSQL nvarchar(MAX)
SET @sSQL='SELECT TOP '+CONVERT(varchar(MAX),@topN)+' * FROM '+@tblName
execute sp_executesql @sSQL
END


The below command will invoke your SP:
EXEC GetAllRows 10,'AnyTableName'


I will suggest if & only if badly needed then use dynamic sql since it has a security hole as well as performence issues may arises. The other thing is difficult to debug since you are creating a string which will execute after invoke. So becareful before going live.

This post is basically an introductory article on "How to write Dynamic SQL". I will try to give more explanation on next articles. Untill then happy programming.

CLICK HERE TO READ PART II.

Friday, October 30, 2009

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints




Please visit my new Web Site WWW.Codedisplay.com



Few days ago i found an error in RDLC report after published. The client claims that sometimes the report shows fine but sometimes shows the error. Then i test this report in my development PC but no luck. I can not generate any error. After that in my testing i found an error like below:

"An error has occurred during report processing.
Exception has been thrown by the target of an invocation.
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."


Then i start googling what was the problem. I found few solutions but these does not work for me. Then i have started to test again to produce this error. Here i want to show you how i can generate this error in my development PC. To ease the example i will show you by a simple query which caueses this problem. To generate this error add a DataSet into your project. Then right click on it to configure. Select Use SQL statements radio button then next next finish. The SQL for DataAdapter which i have used to produce this error is given below:

SELECT Title,Amount,Sort FROM
(

SELECT 'None' Title,100 Amount,1 Sort

UNION

SELECT 'Sale of subscriptions and connections',150,2

UNION

SELECT 'Sale of airtime for prepaid',200,3
UNION

SELECT 'Broadband/Internet',220,4

UNION

SELECT 'Prepaid services',200,5

UNION

SELECT 'Partner/3rd party - revenues/ Partner/3rd party - revenues',190,6

UNION

SELECT 'Other',240,7

UNION

SELECT 'Costs/revenue sharing',250,8

) tbl Order BY Sort

Ok our data adapter now configured. Now add a RDLC report. Drag a Table object on it. And drag & drop Title & Amount column in the table details row.

Ok now our RDLC report also configured. Now open the default.aspx page or add another aspx page into your project which will contain the RDLC report within ReportViewer control. Now add a ReportViewer control in your page. Add an ObjectDataSource into your page. Align DataSet with your ObjectDataSource. Now assign the DataSourceObject in the ReportViewer Control.

Now run the project you will get the below screenshort:
Fig: RDLC Report Error


Solution:
After different of tests i found that the problem is the length constraints. In DataSet i found the backend code generated length of 37 for Title column. So when the resultset try to retrieve a record more than length of 37 then report generates the error: Failed to enable constraints. Look at the auto generated code behind of DataSet:

Fig: DataSet Markup Code

So just change the length for the column will resolve the problem: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Note:
This type of error may appeared for different type of scenarios here i just shared one of them which i did not get in GOOGLE.

Thursday, October 29, 2009

Create Autocomplete TextBox using AJAX in Asp.net 3.5




Please visit my new Web Site WWW.Codedisplay.com



Asp.net 3.5 ease our life. As you knew that Microsoft community published a series of controls named ASP.NET AJAX Control Toolkt. Which you can download from http://www.asp.net/ajax . The AutoCompleteExtender is one of them. You can use this AutoCompleteExtender in your page to make an autocomplete textbox within just few minutes. You can't imagine how much its easy. In this AJAX tutorial i will show you how one can create Autocomplete TextBox using AJAX in ASP.NET 3.5. The Autocomplete TextBox provides user a nice & cool experience while entering data. Let in your page one of the TextBox is used to enter referrer name. You knew that to the referrer name is a tedious job for yur application user. So you can incorporate autocomplete facilty to give your user best UI experience.

Desired Output:


To make an Autocomplete Textbox first create a project. Then opne the default.aspx page in design view. Add ScriptManager , TextBox , Autocomplete Extender from toolbox. Now your HTML markup will be:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">
<title>Ajax Autocomplete Extender Tutorial</title>
</head>

<body>
<form id="form1" runat="server">
<div>

<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

<asp:Label runat="server" ID="lblReferrerName" Text="Referrer: "></asp:Label>

<asp:TextBox ID="txtName" runat="server"></asp:TextBox>

<cc1:AutoCompleteExtender
ID="AutoCompleteExtender1" runat="server" TargetControlID="txtName"
MinimumPrefixLength="2" CompletionInterval="10" EnableCaching="true" CompletionSetCount="3"
UseContextKey="True" ServiceMethod="GetCompletionList">
</cc1:AutoCompleteExtender>

</div>
</form>
</body>
</html>

Don't confuse for ServiceMethod="GetCompletionList" line from the above code. I will show you how you can create webservice method for Autocomplete Extender. Move your mouse on the TextBox. Then from TextBox control smart tag, select the Add AutoComplete page method option from the provided menu, shown in below:


After that you will found that a webservice method will be added in your default.aspx.cs page named GetCompletionList. Now you need to modify this method to return your expected set of data. Now look at my code from below.

using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Collections.Generic;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
public static string[] GetCompletionList(string prefixText, int count, string contextKey)
{
string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
// Try to use parameterized inline query/sp to protect sql injection
SqlCommand cmd = new SqlCommand("SELECT TOP "+count+" Name FROM tblAgent WHERE Name LIKE '"+prefixText+"%'", conn);
SqlDataReader oReader;
conn.Open();
List CompletionSet = new List();
oReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (oReader.Read())
CompletionSet.Add(oReader["Name"].ToString());
return CompletionSet.ToArray();
}
}

Don't forget to add the namespace using System.Collections.Generic;

Code Explanation:
If you observe the web method GetCompletionList you will found three parameters named
prefixText, count, contextKey. Which you can use to enhance your method. Here i have used first two parameters. The prefixText parameter will give you the text that user enter into the textbox. So you can use prefixText parameter in your sql as LIKE clause to match data. The second one count which is used to return no of data. If you specify CompletionSetCount="3" then you will get maximum 3 data. So set the count as per client requirement.

Hope everything is clear now. ANd now you can create AJAX AutoComplete TextBox in your project smoothly.

Friday, October 16, 2009

Partial page updates using ASP.NET Ajax




Please visit my new Web Site WWW.Codedisplay.com



In my previous tutorial or article i have explained how to start ASP.NET AJAX. In this ajax tutorial or ajax article i will show you how we can update partial page using asp.net ajax or how we can use multiple update panel in aspx page. Before going to an example i want to let you know some basics on partial page loading using ajax.

1. If you use muliple update panel in a page & initiate triger for this section within the update panel then you have no problem.

2. If you want to put a portion in a update panel & want ajax enabled render from an event outside of Update Panel control then you have to learn Triggers.

3. If you want to update more than one update panel control by a click event(outside of all Update Panel) its also possible.

4. Bydefault for each async or sync post back each update panel will be rendered. You can restrict the invocation by setting the UpdateMode="Conditional". Means bydefault UpdateMode="Always".


So ok we want to examine each issues by using the below example.

Add a page then copy the below HTML mark up code into your page.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PartialUpdate.aspx.cs" Inherits="PartialUpdate" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Label runat="server" ID="lbl1"></asp:Label>
<br /><br />
<asp:Button runat="server" ID="Button1" Text="Async PostBack 1"
onclick="Button1_Click" />
</ContentTemplate>
<Triggers><asp:AsyncPostBackTrigger ControlID="Button1_ext" EventName="Click" /></Triggers>
</asp:UpdatePanel>

<asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<asp:Label runat="server" ID="lbl2"></asp:Label>
<br /><br />
<asp:Button runat="server" ID="Button2" Text="Async PostBack 2"
onclick="Button2_Click" />
</ContentTemplate>
<Triggers><asp:AsyncPostBackTrigger ControlID="Button1_ext" EventName="Click" /></Triggers>
</asp:UpdatePanel>

<br />
<br />

<asp:Button runat="server" ID="Button1_ext" Text="Render Update Panels Content"
onclick="Button1_ext_Click" />

</div>
</form>
</body>
</html>


Server Side Code:

protected void Button1_Click(object sender, EventArgs e)
{
lbl1.Text = "Current Time Is: " + DateTime.Now;
}

protected void Button2_Click(object sender, EventArgs e)
{
lbl2.Text = "Current Time Is: " + DateTime.Now;
}

protected void Button1_ext_Click(object sender, EventArgs e)
{
lbl1.Text = "Current Time Is: " + DateTime.Now;
lbl2.Text = "Current Time Is: " + DateTime.Now;
}

Output:


Ok now run the project. What you get corresponding buttons click event updates corresponding label of its Update Panel. The third button which is outside the Update Panels will render both Update Panels without refreshing the page? Why because here i used Triggers in both Update Panel for this Button control. Now remove Triggers tag from second Update Panel & examine the output. Hope now you can understand all basic aspects on partial page loading using asp.net ajax. So keep experimenting.
Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash