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

Please visit my new Web Site https://coderstechzone.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.

Create Autocomplete TextBox using AJAX in Asp.net 3.5

Please visit my new Web Site https://coderstechzone.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.

Partial page updates using ASP.NET Ajax

Please visit my new Web Site https://coderstechzone.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.

How to start Ajax in asp.net 3.5

Please visit my new Web Site https://coderstechzone.com

In my First Ajax article or Ajax tutorial i have explained generic Ajax basics. In this ajax article or ajax tutorial i will explain "How to learn Ajax in Asp.net 3.5". That means all about asp.net 3.5. In Visual Studio (VS) 2005 ajax required seperate installation but in .Net framework 3.5 you do not need to install anything for ajax. If you are using an Asp.Net version prior to Asp.Net 3.5, then you need to visit www.asp.net/ajax to get the components rquired to work with AJAX. So now when you create a new web application in asp.net 3.5 its automatically ajax enabled. So start to learn ajax in asp.net 3.5 with a common example of getting server datetime.

Building a simple ASP.Net page with AJAX:
Steps are:
1. File --> New --> Web Site
2. Choose ASP.NET Web Site from Visual Studio Templates
3. Give a name for this project
4. Select Language
5. Now add a Script Manager server control to the top of the page
6. Now add an Update panel server control
7. Put a label & a button within the Update Panel Content Template tag.
8. Write code under Button click event.
9. Run the project & click on the button to see the effect.

If you understand the above steps that i have explained to learn ajax then basically thats enough to give a kick to start asp.net 3.5 AJAX.

Ok now we will go to learn ajax by using an example. Keep in mind two things to create an ajax application that first one is adding a Script Manager & the second one is Adding Update Panel. Update Panel wont work if you don't include Script Manager. Since Script Manager handles all http requests by maintaing a javascript library. You can add only one Script Manager in a page but you can add more than one Update Panel in a page. If you need to post back a simple portion of your page then wrap those portion into an Update Panel. So hope now you can understand how Update Panel works in asp.net. For better understanding we will make an example where we keep a portion under Update Panel & keep other portion out side the Update Panel. So theoratically what will happen? When you generate any post back event within Update Panel then the page will not refresh. But when you click on outside control then the page will refresh. Now implement the scenario in step by step.


Fig: Create a web site


Fig: Adding Script manager & Update Panel control in aspx page

In the first figure i have created a web site. In the second first i have added a Script Manager into the page plus added an Update Panel into the page. If you look at the 2nd image then you found that i have kept a label & a button control outside the update panel and also kept a label & button control inside the Update Panel. Full aspx Html code block is given below:

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

<!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>Start Ajax Asp.net3.5 / Learn Asp.Net Ajax</title>
</head>
<body>
<form id="form1" runat="server">
<div>

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

<asp:Label runat="server" ID="lblServerTime"></asp:Label>
<br /><br />
<asp:Button runat="server" ID="cmdPostBack" Text="PostBack to Server"
onclick="cmdPostBack_Click" />
<br />
<br />
<hr />

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Label runat="server" ID="lbltime"></asp:Label>
<br /><br />
<asp:Button runat="server" ID="cmdGetTime" Text="Call Ajax"
onclick="cmdGetTime_Click" />
</ContentTemplate>
</asp:UpdatePanel>

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

Server Side Code:

protected void cmdGetTime_Click(object sender, EventArgs e)
{
lbltime.Text = DateTime.Now.ToString();
}

protected void cmdPostBack_Click(object sender, EventArgs e)
{
lblServerTime.Text = DateTime.Now.ToString();
}

So now run the project & click on both button Inside the Update Panel Control and Outside the Update Panel Control. Did you get AJAX feelings???

Output:


So hope now you can start to learn "Asp.net Ajax".

XMLHttpRequest to make Ajax Call using Javascript

Please visit my new Web Site https://coderstechzone.com

In this AJAX Tutorial i will try to explain "What is AJAX" & how you can start to learn AJAX from very basic. Ajax means Asynchronous JavaScript and XML. Asynchronous means that when you send a request, you wait for the response to come back, but are free to do other things while you wait. Ajax is not a new technology but in these days we found it in a new way. Currently almost all developers want to implement Ajax functionality to achieve asynchronus call back. ASP.NET gives us huge facility to implement Ajax functionality by using ScriptManager and UpdatePanel within very short time. But in this tutorial i will discuss Ajax basics menas XMLHttpRequest which is the generic HTTP client for JavaScript. To explain here i am using an example which described how to call server side aspx page using javascript. The serverside aspx page gives us the current server time & we will display the time into the page using Ajax.

Output Looks Like:


To implement the example add two page into your project. The first one contains the javascript code to call the second page using Ajax. And the second page returns server date time. You cab set any name for the first page but set the name for second aspx page is "ReceiveAjaxCall.aspx". Because we will call this page using the page name from javascript Ajax method.

Steps to call a serverside page by javascript using AJAX:
1. Declare an XMLHttpRequest object.
2. Instantiate the object.
3. Write a function to receive server side data
4. Make a call to server asynchronusly
5. Send

If you follow my code examples of first page then will get all steps one by one. The HTML markup code for first page is given below:

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

<!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>XMLHTTP Test</title>

<script language="javascript" type="text/javascript">


function CallAjaxFunction()
{
var AjaxObject;

try
{
// For Other Browsers Like Opera, Mozilla Firefox, Safari
AjaxObject = new XMLHttpRequest();
}
catch (e)
{
// For Internet Explorer
try
{
AjaxObject = new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e)
{
try
{
AjaxObject = new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e)
{
// Something might wrong here
alert("Your browser does not spport XMLHTTP request!");
return false;
}
}
}

// Create a function that will receive data sent from the server
AjaxObject.onreadystatechange = function()
{
if(AjaxObject.readyState == 4) // Response came / server side processing completed
{
document.getElementById('<%= lblTime.ClientID %>').innerHTML='Current Server Date Time is: '+AjaxObject.responseText;
}
}
AjaxObject.open("GET", "ReceiveAjaxCall.aspx", true);
AjaxObject.send(null);
}
</script>

</head>


<body>
<form id="form1" runat="server">
<div>
<label runat="server" id="lblTime" style="font-family:Tahoma;font-weight:bold;color:Blue;font-size:1.2em"></label>
<br />
<br />
<asp:Button runat="server" id="cmd" OnClientClick="CallAjaxFunction(); return false;" Text="Get Server Time (Server Side Button)" />
<input type="button" onclick="CallAjaxFunction();" value="Get Server Time (HTML Button)" />
</div>
</form>
</body>


</html>

Look the difference between IE & non IE browsers ajax object instantiation block. Otherwise your page will not support cross-browser. If you look at the onreadystatechange property then you found that here i have declared a fixed value 4 to determine that the server call is completed. Yes 4 denotes that "The request is complete". Onreadystatechange has another 4 values like 0,1,2,3 denotes "The request is not initialized", "The request has been set up", "The request has been sent", "The request is in process" respectively. But we need to check only 4. When we get the value 4 then we write the server return value into our page label.

The open method is used to set the request type (GET, POST, PUT, or PROPFIND), the URL of the page being requested, and whether the call will be asynchronous or not. The basic syntax for open method is given below:

open(type,url,isAsync,username,password)

where username, password is optional.

And the the send method makes the connection to the URL specified in open.

To write the datetime in the page i added a lblTime label control and to make postback to the server i added two buttons one is HTML button & another one is server side button control.

OK now our receving data part is completed. We will go to implement second page which is called by first page through javascript XMLHttpRequest. Since we will send simple date time, just write the below codes into the page load event.

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Clear();
Response.Write(DateTime.Now);
Response.End();
}
}

Thats all. Hope now you know the basics of asp.net AJAX.

Show grand total running total in GridView Footer

Please visit my new Web Site https://coderstechzone.com

In many scenarios we need to display Running total as well as Grand total in GridView footer. In this post i will try to explain in an easy way that how we can display running total & Grand total in a GridView footer combindly. To explain this solution using an example here i use a sales order report. The report contains all sales order amount in a tabular way. I will use a GridView to display sales order amount and use GridView footer to display Running total & Grand total. Let we have a customer table with id and name column plus an orders table with OrderID,CustomerID,OrderDate & Amount. Now our goal is to show all customers order with amount as well as page wise running total & grand total. Note that running total is necessary when you enable paging in a GridView where as Grand total is always you can consider.

The output will be:

Fig: Page 1 runnig total

When you click on another page within GridView then you will get that page Running total with Grand total also.

Fig: Page 2 runnig total

To accomplish the above example please find the HTML markup code for your aspx page from below:

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

<!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>Gridview</title>
</head>

<body>

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

<div>

<asp:GridView runat="server" ID="gvEdit" AllowPaging="true" PageSize="5" ShowFooter="true" OnPageIndexChanging="gvEdit_PageIndexChanging" OnRowDataBound="gvEdit_RowDataBound">
<Columns>
<asp:BoundField DataField="Code" HeaderText="Order Code">
</asp:BoundField>
<asp:BoundField DataField="Name" HeaderText="Name">
</asp:BoundField>

<asp:TemplateField HeaderText="Date" FooterStyle-BorderWidth="1px" FooterStyle-BorderColor="maroon">
<ItemTemplate>
<asp:Label ID="lblDate" runat="server" Text='<%# Convert.ToDateTime(Eval("Date")).ToString("dd-MM-yy")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lbltxtRTotal" runat="server">Running Total:</asp:Label><br />
<asp:Label ID="Label1" runat="server">Grand Total:</asp:Label><br />
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Amount" FooterStyle-BorderWidth="1px" FooterStyle-BorderColor="maroon">
<ItemTemplate>
<asp:Label ID="lblAmount" runat="server" Text='<%# Eval("Amount").ToString()%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblRTotal" runat="server"></asp:Label><br />
<asp:Label ID="lblGTotal" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

</div>

</form>

</body>
</html>

To accomplish the above example please find the server side code for your aspx page from below:

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

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


decimal RunningTotal = 0;
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 OrderID Code,B.Name,OrderDate Date,Amount from "+
"orders A INNER JOIN customer B ON A.Customerid=B.ID", conn);
ad.Fill(dt);
}

decimal GrandTotal = 0;
foreach (DataRow oRow in dt.Rows)
GrandTotal += Convert.ToDecimal(oRow["Amount"]);

ViewState["GrandTotal"] = GrandTotal;

gvEdit.DataSource = dt;
gvEdit.DataBind();
ViewState["dt"] = dt;
}
}


protected void gvEdit_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvEdit.PageIndex = e.NewPageIndex;
gvEdit.DataSource = (DataTable)ViewState["dt"];
gvEdit.DataBind();
}


protected void gvEdit_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
RunningTotal += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Amount"));

if (e.Row.RowType == DataControlRowType.Footer)
{
((Label)e.Row.FindControl("lblRTotal")).Text = RunningTotal.ToString();
((Label)e.Row.FindControl("lblGTotal")).Text = ViewState["GrandTotal"].ToString();
}
}
}

Code explanation:
If you look at the page load event where i bind the datasource to the GridView control then i use a viewstate variable to store the retrieved sql server data because i do not go to the database server again when user click on another page of the gridview. At that time i also calculate Grand total value from the datatable & stored it in viewstate which i use in GridView RowDataBound event.

When i assign the datasource property to the GridView control then the RowDataBound event automatically fired. In this event i need to check that does the DataControl Row Type is DataRow or not. If its a DataRow that means i found the DataItem object which will be currently rendered in our page. So by accumulating the amount value will give me the Running Total and Grnad total value we have calculated previously in page load event. So now check the DataControl Row Type for Footer row. If you find the footer row then you knew that in Fotter row we have placed two label named lblRTotal and lblGTotal. Now by using the FindControl method you can catch both labels. Now just put the running total in lblRTotal Text property and Grand total in lblGTotal label Text property. That's it.